List:General Discussion« Previous MessageNext Message »
From:Jia Chen Date:August 28 2009 3:17pm
Subject:Very Slow Query
View as plain text  
Hi all,

One seemingly simple query that joins two tables takes a long time for me.

This is my library.

mysql> show table status from nber1999;
+-----------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows     | Avg_row_length 
| Data_length | Max_data_length  | Index_length | Data_free | 
Auto_increment | Create_time         | Update_time         | Check_time 
| Collation         | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| compusta1 | MyISAM |      10 | Dynamic    |     4906 |             77 
|      379464 |  281474976710655 |         1024 |         0 |           
NULL | 2009-08-27 23:56:47 | 2009-08-27 23:56:47 | NULL       | 
latin1_swedish_ci |     NULL |                |         |
| pat1      | MyISAM |      10 | Dynamic    |  2089903 |             96 
|   201936072 |  281474976710655 |         1024 |         0 |           
NULL | 2009-08-27 23:55:48 | 2009-08-27 23:55:56 | NULL       | 
latin1_swedish_ci |     NULL |                |         |
+-----------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
5 rows in set (0.00 sec)

And the relevant rows in my slow query log file is:

/usr/sbin/mysqld, Version: 5.0.75-0ubuntu10.2-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 090828 10:36:17
# User@Host: root[root] @ localhost []
# Query_time: 478  Lock_time: 0  Rows_sent: 0  Rows_examined: 1251
use nber1999;
create table nber1999.pat select a.*, b.assname, b.cname, b.cusip, 
b.own, b.pname, b.sname
                 from nber1999.pat1 as a inner join nber1999.compusta1 as b
                on a.assignee=b.assignee;

My operating system is ubuntu 9.04.  I set configuration variables as 
follows:
[mysqld]
key_buffer = 1024M
table_cache = 256
query_cache_type    = 1
query_cache_limit       = 2M
query_cache_size        = 20M

[isamchk]
key_buffer        = 16M

Can anyone give me some hint on how to speed this query up?  Thanks.

I tried to tune mysql by using a script from 
http://mediakey.dk/~cc/optimize-mysql-performance-with-mysqltuner/
and got

 >>  MySQLTuner 1.0.0 - Major Hayden <major@stripped>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering        
Please enter your MySQL administrative login: root                        
Please enter your MySQL administrative password:

-------- General Statistics 
--------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.75-0ubuntu10.2-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics 
-------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 584M (Tables: 6)
[OK] Total fragmented tables: 0

-------- Performance Metrics 
-------------------------------------------------
[--] Up for: 30m 47s (131 q [0.071 qps], 42 conn, TX: 35K, RX: 7K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 1.0G global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.3G (34% of installed RAM)
[OK] Slow queries: 0% (1/131)
[OK] Highest usage of available connections: 2% (2/100)
[OK] Key buffer size / total MyISAM indexes: 1.0G/74.0K
[!!] Query cache efficiency: 0.0% (0 cached / 67 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 19% (21 on disk / 107 total)
[OK] Thread cache hit rate: 95% (2 created / 42 connections)
[OK] Table cache hit rate: 75% (24 open / 32 opened)
[OK] Open file limit used: 4% (49/1K)
[OK] Table locks acquired immediately: 100% (41 immediate / 41 locks)

-------- Recommendations 
-----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
    query_cache_limit (> 2M, or use smaller result sets)

Best,
Jia
Thread
Very Slow QueryJia Chen28 Aug
  • Re: Very Slow QueryDan Nelson28 Aug
    • Re: Very Slow QueryJia Chen28 Aug
      • Re: Very Slow QueryDan Nelson28 Aug
        • Re: Very Slow QueryJia Chen28 Aug
      • RE: Very Slow QueryJohn28 Aug