Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Location
    Tokyo, Japan
    Posts
    333

    Very slow MySQL Queries on better hardware

    Hi,
    I'm having a problem with a MySQL servers.
    I had:
    Core2Duo E6600
    4GB Ram
    320GB SATA
    Now I have:
    Dell SC1435
    Opteron 2200
    4GB Ram
    73GB 15k RPM SAS

    Now here's the problem.
    I have a very big IPB board and it takes about 10 seconds to load a single page.
    (Servers are clustered)
    I just changed the datacenter and only difference in spec is that MySQL server.
    From looking at it, I can pretty much can say this is a problem with MySQL server as it comes up in slow-queries-log.

    The server has CentOS 5 with MySQL version 5.0.22

    I have copied the database using mysqlhotcopy.

    Here is my my.cnf:
    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    old_passwords=1
    max_connections = 50000
    key_buffer = 256M
    key_buffer_size = 64M
    myisam_sort_buffer_size = 32M
    join_buffer_size = 1M
    read_buffer_size = 2M
    read_rnd_buffer_size = 4M
    sort_buffer_size = 8M
    table_cache = 1024
    thread_cache_size = 286
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 2M
    query_cache_size = 128M
    query_cache_type = 1
    tmp_table_size = 16M
    skip-innodb
    log-slow-queries = /var/log/mysql-slow.log
    long_query_time = 1
    [mysqld_safe]
    open_files_limit = 8192
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    [mysqldump]
    quick
    max_allowed_packet = 16M
    [myisamchk]
    key_buffer = 32M
    sort_buffer = 32M
    read_buffer = 16M
    write_buffer = 16M
    [mysqlhotcopy]
    interactive-timeout
    Here's the slow query log:
    Code:
    # Time: 080327 22:29:51
    # [email protected]: db1[db1] @  [IP ADDRESS]
    # Query_time: 2  Lock_time: 0  Rows_sent: 20  Rows_examined: 216916
    SELECT a.attach_pid, a.attach_date, a.attach_file, a.attach_ext, a.torrent_id, p.topic_id, p.pid, t.size, t.fid, t.completed, t.seeders, t.leechers, t.numfiles, b.title, b.tid, b.forum_id, f.id AS forumid, f.name AS forum_name, m.id, m.name
                                                                            FROM ibf_attachments a
                                                                            LEFT JOIN ibf_posts p ON (a.attach_pid=p.pid)
                                                                            LEFT JOIN torrents t ON (a.torrent_id=t.fid)
                                                                            LEFT JOIN ibf_members m ON (a.attach_member_id=m.id)
                                                                            LEFT JOIN ibf_topics b ON (p.topic_id=b.tid)
                                                                            LEFT JOIN ibf_forums f ON (b.forum_id=f.id)
                                                                            WHERE a.attach_ext='torrent' ORDER BY t.fid DESC LIMIT 0, 20;
    # Time: 080327 22:29:54
    # [email protected]: db1[db1] @  [IP ADDRESS]
    # Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 368475
    SELECT p.pid, p.queued, t.approved, t.forum_id
                                                        FROM ibf_posts p
                                                         LEFT JOIN ibf_topics t on (t.tid=p.topic_id)
                                                        WHERE   t.forum_id IN (67,1,14,21,48,88,110,125,6,7,47,11,16,8,50,51,59,117,96,86,111,19,33,80,24,81,108,44,18,13,49,25,60,61,62,63,68,40,98,27,28,29,52,69,76,91,90,41,118,119,120,126)
                                                         AND p.queued=0
                                                          AND ( LOWER(p.post) LIKE '%10000 B\.C\.%' );
    # Time: 080327 22:30:26
    # [email protected]: db1[db1] @  [IP ADDRESS]
    # Query_time: 3  Lock_time: 0  Rows_sent: 94  Rows_examined: 368475
    SELECT p.pid, p.queued, t.approved, t.forum_id
                                                        FROM ibf_posts p
                                                         LEFT JOIN ibf_topics t on (t.tid=p.topic_id)
                                                        WHERE   t.forum_id IN (67,1,14,21,48,88,110,125,6,7,47,11,16,8,50,51,59,117,96,86,111,19,33,80,24,81,108,44,18,13,49,25,60,61,62,63,68,40,98,27,28,29,52,69,76,91,90,41,118,119,120,126)
                                                         AND p.queued=0
                                                          AND ( LOWER(p.post) LIKE '%inside out%' );
    
    
    # Time: 080325  7:06:48
    # [email protected]: db1[db1] @  [IP ADDRESS]
    # Query_time: 3  Lock_time: 1  Rows_sent: 1  Rows_examined: 319366
    select id, name, members_display_name, members_created_remote, email, mgroup, member_login_key, ip_address, login_anonymous, login_attempts, lock_true, lock_time
                                    from ibf_members
                                    where LOWER(name)='username-was-here';
    Here's the EXPLAIN Query for each query above:
    Code:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 235618 to server version: 5.0.22-log
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> use db1;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> EXPLAIN select id, name, members_display_name, members_created_remote, email, mgroup, member_login_key, ip_address, login_anonymous, login_attempts, lock_true, lock_time
        ->                                 from ibf_members
        ->                                 where LOWER(name)='username-was-here';
    +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
    | id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
    +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
    |  1 | SIMPLE      | ibf_members | ALL  | NULL          | NULL | NULL    | NULL | 319995 | Using where |
    +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
    1 row in set (0.01 sec)
    
    mysql> EXPLAIN SELECT p.pid, p.queued, t.approved, t.forum_id
        ->                                                     FROM ibf_posts p
        ->                                                      LEFT JOIN ibf_topics t on (t.tid=p.topic_id)
        ->                                                     WHERE   t.forum_id IN (67,1,14,21,48,88,110,125,6,7,47,11,16,8,50,51,59,117,96,86,111,19,33,80,24,81,108,44,18,13,49,25,60,61,62,63,68,40,98,27,28,29,52,69,76,91,90,41,118,119,120,126)
        ->                                                      AND p.queued=0
        ->                                                       AND ( LOWER(p.post) LIKE '%inside out%' );
    +----+-------------+-------+------+----------------------------+----------+---------+--------------------+-------+-------------+
    | id | select_type | table | type | possible_keys              | key      | key_len | ref                | rows  | Extra       |
    +----+-------------+-------+------+----------------------------+----------+---------+--------------------+-------+-------------+
    |  1 | SIMPLE      | t     | ALL  | PRIMARY,last_post,forum_id | NULL     | NULL    | NULL               | 79339 | Using where |
    |  1 | SIMPLE      | p     | ref  | topic_id                   | topic_id | 5       | db1.t.tid,const |     5 | Using where |
    +----+-------------+-------+------+----------------------------+----------+---------+--------------------+-------+-------------+
    2 rows in set (0.01 sec)
    
    mysql> EXPLAIN SELECT p.pid, p.queued, t.approved, t.forum_id
        ->                                                     FROM ibf_posts p
        ->                                                      LEFT JOIN ibf_topics t on (t.tid=p.topic_id)
        ->                                                     WHERE   t.forum_id IN (67,1,14,21,48,88,110,125,6,7,47,11,16,8,50,51,59,117,96,86,111,19,33,80,24,81,108,44,18,13,49,25,60,61,62,63,68,40,98,27,28,29,52,69,76,91,90,41,118,119,120,126)
        ->                                                      AND p.queued=0
        ->                                                       AND ( LOWER(p.post) LIKE '%10000 B\.C\.%' );
    +----+-------------+-------+------+----------------------------+----------+---------+--------------------+-------+-------------+
    | id | select_type | table | type | possible_keys              | key      | key_len | ref                | rows  | Extra       |
    +----+-------------+-------+------+----------------------------+----------+---------+--------------------+-------+-------------+
    |  1 | SIMPLE      | t     | ALL  | PRIMARY,last_post,forum_id | NULL     | NULL    | NULL               | 79339 | Using where |
    |  1 | SIMPLE      | p     | ref  | topic_id                   | topic_id | 5       | db1.t.tid,const |     5 | Using where |
    +----+-------------+-------+------+----------------------------+----------+---------+--------------------+-------+-------------+
    2 rows in set (0.01 sec)
    
    mysql> EXPLAIN SELECT a.attach_pid, a.attach_date, a.attach_file, a.attach_ext, a.torrent_id, p.topic_id, p.pid, t.size, t.fid, t.completed, t.seeders, t.leechers, t.numfiles, b.title, b.tid, b.forum_id, f.id AS forumid, f.name AS forum_name, m.id, m.name
        ->                                                                         FROM ibf_attachments a
        ->                                                                         LEFT JOIN ibf_posts p ON (a.attach_pid=p.pid)
        ->                                                                         LEFT JOIN torrents t ON (a.torrent_id=t.fid)
        ->                                                                         LEFT JOIN ibf_members m ON (a.attach_member_id=m.id)
        ->                                                                         LEFT JOIN ibf_topics b ON (p.topic_id=b.tid)
        ->                                                                         LEFT JOIN ibf_forums f ON (b.forum_id=f.id)
        ->                                                                         WHERE a.attach_ext='torrent' ORDER BY t.fid DESC LIMIT 0, 20;
    +----+-------------+-------+--------+---------------+---------+---------+---------------------------+-------+----------------------------------------------+
    | id | select_type | table | type   | possible_keys | key     | key_len | ref                       | rows  | Extra                                        |
    +----+-------------+-------+--------+---------------+---------+---------+---------------------------+-------+----------------------------------------------+
    |  1 | SIMPLE      | a     | ALL    | NULL          | NULL    | NULL    | NULL                      | 33009 | Using where; Using temporary; Using filesort |
    |  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 4       | db1.a.attach_pid       |     1 |                                              |
    |  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 4       | db1.a.torrent_id       |     1 |                                              |
    |  1 | SIMPLE      | m     | eq_ref | PRIMARY       | PRIMARY | 3       | db1.a.attach_member_id |     1 |                                              |
    |  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | db1.p.topic_id         |     1 |                                              |
    |  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY | 2       | db1.b.forum_id         |     1 |                                              |
    +----+-------------+-------+--------+---------------+---------+---------+---------------------------+-------+----------------------------------------------+
    6 rows in set (0.00 sec)
    What I have tried:
    Try 1:
    Code:
    cd /var/lib/mysql/db1
    myisamchk -r --sort-index --analyze *.MYI
    Try 2:
    Code:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 235625 to server version: 5.0.22-log
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> use db1;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> optimize table ibf_attachments;
    +------------------------+----------+----------+----------+
    | Table                  | Op       | Msg_type | Msg_text |
    +------------------------+----------+----------+----------+
    | db1.ibf_attachments | optimize | status   | OK       |
    +------------------------+----------+----------+----------+
    1 row in set (1.77 sec)
    
    mysql> optimize table ibf_posts;
    +------------------+----------+----------+----------+
    | Table            | Op       | Msg_type | Msg_text |
    +------------------+----------+----------+----------+
    | db1.ibf_posts | optimize | status   | OK       |
    +------------------+----------+----------+----------+
    1 row in set (1 min 39.11 sec)
    
    mysql> optimize table ibf_members;
    +--------------------+----------+----------+----------+
    | Table              | Op       | Msg_type | Msg_text |
    +--------------------+----------+----------+----------+
    | db1.ibf_members | optimize | status   | OK       |
    +--------------------+----------+----------+----------+
    1 row in set (18.23 sec)
    Any clues?
    Thanks
    Yudai Yamagishi

  2. #2
    Join Date
    Apr 2003
    Location
    Melbourne, AU
    Posts
    539
    From looking at it, I can pretty much can say this is a problem with MySQL server as it comes up in slow-queries-log.
    Judging from the few queries you've posted, my opinion is that it's the queries are just slow. Doing things like changing server variables or running OPTIMIZE on the tables won't help significantly.

    Going into each query..

    ... WHERE a.attach_ext='torrent' ORDER BY t.fid...

    Possibly add an index to ibf_attachments.attach_ext


    ... LOWER(p.post) LIKE '%10000 B\.C\.%' ...
    ... LOWER(p.post) LIKE '%inside out%' ...


    Holy moly, that's a substring search - very expensive! No amount of tuning can save you for this one. I'm pretty sure this is some custom code - IPB won't be written like this.

    ... where LOWER(name)='username-was-here' ...

    Unless you have 100000s of members, I don't see why this query runs so slowly. If you really do have 100000s of members, I suggest you remove LOWER() from the query (it's irrelevant for MySQL) and index on ibf_members.name.
    WK Woon
    CTO | http://www.aflexi.net - A flexible Network
    Building the next generation CDN platform - DEMO .... coming soon

  3. #3
    Join Date
    Jun 2007
    Location
    Tokyo, Japan
    Posts
    333
    Well the thing is it was running very fast on the old server.
    Around 15ms page generation time.

    Any comments on why it runs fast on the old server and doesn't on the new server?
    Yudai Yamagishi

  4. #4
    Join Date
    Dec 2006
    Posts
    477
    Any query where the rowsexamined is in the 100,000s for an interactive web based application is a poor query and needs better indexing. However with forums, these queries are the result of extensions/plugins written by 3rd party programmers who don't think about database performance so don't add new indexes to the forums database, or use the existing ones properly.

    As to why the poor performance:

    There is no such config setting as key_buffer - you can remove that.
    key_buffer_size of 64Mb on a 4Gb machine is probably too small depending on how big your databases are.

    Run http://hackmysql.com/mysqlreport for a report on how effective your settings are. There is guide on the website on how to interpret the report and which settings to change. That site also has some tutorials on how to interpret the output of the EXPLAIN queries and decide what indexes to create.

    (edit: Apologies for the bad formatting in the initial version of this post - it seems the noscript Firefox extension makes these forums concatenate everything you type into one long sentence)
    Last edited by RBBOT; 03-28-2008 at 10:42 AM.

  5. #5
    Join Date
    Apr 2003
    Location
    Melbourne, AU
    Posts
    539
    Quote Originally Posted by YYamagishi View Post
    Well the thing is it was running very fast on the old server.
    Around 15ms page generation time.

    Any comments on why it runs fast on the old server and doesn't on the new server?
    Don't know. Do you still access to the old servers? Are there any other variables? (increased traffic, data, etc)
    WK Woon
    CTO | http://www.aflexi.net - A flexible Network
    Building the next generation CDN platform - DEMO .... coming soon

  6. #6
    Join Date
    Jul 2004
    Location
    Hong Kong
    Posts
    195
    Quote Originally Posted by YYamagishi View Post
    Well the thing is it was running very fast on the old server.
    Around 15ms page generation time.

    Any comments on why it runs fast on the old server and doesn't on the new server?
    If everything is exactly the same, then the only reason there is hardware failure or driver problem.

    Run Prime to burn the system and mem86 for memory test(but it should require a support staff to burn the mem86 boot disc and you monitor the result via KVM). You may want to do some BIOS tweaking too.

    http://mersenne.org/freesoft.htm
    http://memtest86.com/

  7. #7
    Join Date
    Jun 2007
    Location
    Tokyo, Japan
    Posts
    333
    I had to move back to the old datacenter as it seemed to be a datacenter's issues.
    I wasn't able to solve the problem
    Yudai Yamagishi

  8. #8
    Join Date
    Jul 2004
    Location
    Hong Kong
    Posts
    195
    Quote Originally Posted by YYamagishi View Post
    I had to move back to the old datacenter as it seemed to be a datacenter's issues.
    I wasn't able to solve the problem
    Your new server is(was) indeed better for DB purpose. AMD has better memory performance and you are on SCSI 15krpm.

    It's very common to see performance go downhill when heavy loading if there are some hardware have stability issues. You may not experience crash if the problem isn't big enough but you definitely will feel something wrong.

Posting Permissions

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