Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2007
    Location
    Detroit
    Posts
    7

    Unanswered: Query slow on production but fast on test server...

    I used to have another user for dbforums but it has been lost like so many pairs of socks. For the life of me I cannot figure out what is going on with this...

    When I run the query on my test Windows MySQL 5.0.45 server it runs fast (< 1 second) and when I run it on my production Linux MySQL 5.1 server it runs slow (~20 seconds). Both my.cnf are similar but the problem could be in here. The articles table contains a modest 45000 rows and each article has 1 category. There are 15 categories. If an article leaves the train station traveling westbound at 45 MPH how long will it take for me to go crazy...

    My production server had 5.0.27 on it with the same problems which is why I upgraded to 5.1, to get the profiler. It was at this point I found out 5.1 does't have the profiler yet becase of blah blah blah... yay for me. When the query runs the bulk of the time seems to be in the "sending data" phase when I do a show full processlist;. Does anyone have any ideas what might be going on? I spent the last couple days on this and it is delaying the launch of my project.... ugh. Enough to make me start smoking again.

    Here is my schema:

    Code:
    CREATE TABLE `articles` (
      `article_id` int(7) unsigned NOT NULL auto_increment,
      `views` int(6) unsigned NOT NULL default '0',
      `title` varchar(200) NOT NULL,
      `author` varchar(35) NOT NULL,
      `text` mediumtext NOT NULL,
      `created` datetime NOT NULL default '2020-07-27 12:00:00',
      PRIMARY KEY  USING BTREE (`article_id`),
      KEY `author` USING BTREE (`author`,`created`),
      KEY `views` USING BTREE (`views`,`created`),
      KEY `created` USING BTREE (`created`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
    
    CREATE TABLE `article_cats` (
      `category_id` smallint(4) unsigned NOT NULL,
      `article_id` int(7) unsigned NOT NULL,
      PRIMARY KEY  (`category_id`,`article_id`),
      UNIQUE KEY `pk_rev` USING BTREE (`article_id`,`category_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    
    CREATE TABLE `categories` (
      `category_id` smallint(4) unsigned NOT NULL auto_increment,
      `parent` smallint(4) unsigned default NULL,
      `name` varchar(30) NOT NULL,
      PRIMARY KEY  (`category_id`),
      KEY `parent` (`parent`),
      KEY `name` (`name`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

    I have tried many variations of this query. All of which run great on my windows test server and slow on the production.

    Code:
    select a.* 
    from articles a
    	inner join article_cats ac 
    		on ac.article_id = a.article_id and ac.category_id = 1
    where `created` between '2007-12-18 21:28:46' and '2007-12-20 21:28:46'
    order by `created` desc
    limit 0,10
    And this is what explain tells me (deleted ID, key_len and ref to conserve space)...
    Code:
    +-------------+-------+--------+-----------------+---------+------+-------------+
    | select_type | table | type   | possible_keys   | key     | rows | Extra       |
    +-------------+-------+--------+-----------------+---------+------+-------------+
    | SIMPLE      | a     | range  | PRIMARY,created | created | 6516 | Using where |
    | SIMPLE      | ac    | eq_ref | PRIMARY,pk_rev  | PRIMARY |    1 | Using index |
    +-------------+-------+--------+-----------------+---------+------+-------------+
    If you have read this far, I owe you a beer.

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    I mostly have questions.

    What method/client are you using to connect to the mysql server and execute the query?

    I assume you have the same identical data on your test server as the production server?

    The only thing that comes to mind concerning slow queries due to an access problem (the "sending data" phase) is when some reverse DNS lookup is enabled (I don't recall the exact setting, but it is a common problem and searching the Internet should return some results) in the mysql server (even when you use an IP address to connect instead of a DNS hostname.)

    The only thing that stands out in the query is your ON clause should only contain the condition the JOIN is using and any selection condition should be in the WHERE clause. The and ac.category_id = 1 should be moved out of the ON clause and into the WHERE clause.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you owe me a beer

    you showed us one schema but didn't show both

    you showed us one EXPLAIN but didn't show both

    have you compared them fully yourself?

    because what you posted looks like it will perform extremely well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dbmab
    The only thing that stands out in the query is your ON clause should only contain the condition the JOIN is using and any selection condition should be in the WHERE clause. The and ac.category_id = 1 should be moved out of the ON clause and into the WHERE clause.
    no, that's not right

    conditions should be in the ON clause

    conceptually, it's like the difference between bringing all the different types of beer home from the store, and throwing all of them away except your favourite brand, versus bringing only your favourite brand home

    although i'm sure the optimizer will figure it out either way

    still it is better to write the condition into the ON clause because that makes it abundantly clear to anyone reading the query what the intent is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    Detroit
    Posts
    7
    @dbmab

    I am querying using PHP and the mysql client.

    Data is identical on both servers. I dumped the test DB and imported on the prod. before I ran tests.

    The MySQL server is running locally on the machine. Could the DNS issue still cause problems even I was connecting to localhost?

    And concerning the ac.category_id = 1; I've tried it both ways with identical results.



    @r937

    Just PM me your address and beer of choice and i'll be glad to send one out, with a christmas bow on it even... if that's your thing.

    I've compared the explains and they are identical.

    I just checked the schemas and they are identical.

    It does perform extremely well... locally on my test server

    What I've done since I last posted was downgrade mysql from 5.1 to 5.0.45 on the prod. server (on a sidenote... 5.1 is buggy). This should allow me to run the profiler and find out exactly where its hanging up. What a mess this is turning out to be! I'll post back here when I have the results.

    Thank you for the beer related analogy! All analogies posted in forums should relate to beer somehow imo.

  6. #6
    Join Date
    Dec 2007
    Location
    Detroit
    Posts
    7
    Wow. The profiler was significantly less helpful then I thought it'd be.

    The results are....

    Code:
    mysql> show profile for query 1;
    +--------------------------------+-----------+
    | Status                         | Duration  |
    +--------------------------------+-----------+
    | (initialization)               | 0.000011  |
    | checking query cache for query | 0.000132  |
    | Opening tables                 | 0.000023  |
    | System lock                    | 0.000015  |
    | Table lock                     | 0.000052  |
    | init                           | 0.000053  |
    | optimizing                     | 0.000035  |
    | statistics                     | 0.000172  |
    | preparing                      | 0.0000660 |
    | executing                      | 0.000019  |
    | Sending data                   | 33.335793 |
    | end                            | 0.000027  |
    | query end                      | 0.000014  |
    | storing result in query cache  | 0.000014  |
    | freeing items                  | 0.000028  |
    | closing tables                 | 0.000023  |
    | logging slow query             | 0.050754  |
    +--------------------------------+-----------+
    Which tells me what I already knew: the sending data phase is taking the longest. Normally if it was a complex query I could fix it... but its just that simple query...


    And for giggles... here is the mysqld part of my config file. I've been up for a good while now and may have missed something. Do you guys see anything? (I cut out all the innodb stuff since these tables are myisam.

    Code:
    [mysqld]
    port            = 3306
    socket          = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer = 128M
    max_allowed_packet = 1M
    table_cache = 128
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 32M
    thread_cache_size = 8
    query_cache_size= 16M
    thread_concurrency = 8
    
    set-variable=long_query_time=5
    log-slow-queries=/var/log/mysql/log-slow-queries.log
    default-character-set=utf8
    
    log-bin=mysql-bin
    server-id       = 1
    Oh and I should probably note this is a VPS with 256 MB of ram that only serves web pages.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by astro-turf
    Thank you for the beer related analogy! All analogies posted in forums should relate to beer somehow imo.
    thats probably why the beer icon was added....

  8. #8
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Directly from the mysql manual -
    Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.
    Putting conditions in the WHERE clause will only select those rows from the database, resulting in less work during the JOIN.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have found yet another instance where the manual is misleading

    try doing what you suggested with a LEFT OUTER JOIN and see what happens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    besides, as you know, the JOIN is executed first, followed by elimination of joined rows based on WHERE conditions afterwards
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2007
    Location
    Detroit
    Posts
    7
    Ugh...sleep deprivation is not a good thing...

    So the query I quoted above wasn't entirely accurate... the actual query was the same except it contained SQL_CALC_FOUND_ROWS.

    I think I have multiple things working against me...

    I need to remove both SQL_CALC_FOUND_ROWS and get rid of any date comparison on the created column and the query works.... of course this destroys the nature of my query, but hey. This makes me think there is a problem with the index, so just to doubly make sure, I dropped the index and readded it. It took about 4 minutes to drop the index and 15 seconds to add it back, compared to a few seconds for both on my testing server.

    Either something obvious is wrong and I'm just missing it or this is some kind of bug???? Unless someone can convince me otherwise I'm going to report it as a bug... as much as I hate reporting bugs.

    The bastardized working version of the query is below....

    Code:
    select count(*) as total_rows 
    from article_cats ac
    	inner join articles a on a.article_id=ac.article_id
    where ac.category_id=1;
    
    select a.* 
    from article_cats ac
    	inner join articles a on a.article_id=ac.article_id
    where ac.category_id=1
    limit 0,10;
    Last edited by astro-turf; 12-21-07 at 19:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •