Help Optimizing Large Query Sort (Detailed)
Hello,
I'm hoping i can find help with this here because I've reached the end of my understanding with the problem I'm facing. I manage a db, but my experience with db administration has been limited prior to this.
The db contains a metadata repo for a canned application. The majority of the interaction for this application is on two tables. They have grown to about 2million+ and 800k+ each. Whenever the webui queries the tables, they timeout while sorting the results.
Server: MySQL 5.0 @ 72GB/16Cores
Pertinent Settings:
--------
Innodb Pool - 16GB
Table_cache - 1024
tmp_table_size/max_heap_table_size - 128M
sort_buffer_size = 256k (tested 64/128 no noticeable difference)
query_cache_size = 128M
Utilization:
--------
Light, mostly metadata queries, and low number of webusers.
Table - 800k+ Rows - 50GB on disk.
The query:
mysql> explain SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time FROM WF_JOBS t0 WHERE t0.bean_type = 'WorkflowJobBean' ORDER BY t0.created_time DESC LIMIT 0, 50;
+----+-------------+-------+------+-----------------+-----------------+---------+-------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+--------+-----------------------------+
| 1 | SIMPLE | t0 | ref | I_WF_JOBS_DTYPE | I_WF_JOBS_DTYPE | 34 | const | 352580 | Using where; Using filesort |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+--------+-----------------------------+
The problem I'm having is that this query is generally very slow especially when it's the first time it's being run for that day. It takes 4.5seconds to complete whenever the table has been previously scanned into memory, and will timeout if this is the first time the table is being called(eventually completes after 3k+ seconds). After doing some research, it appears our nightly backup pushes this table data out of the buffer pool, and it has to rescan it from the disk the next time it's called... this causes a timeout on the WebUI until the server is able to complete the first query.
Example:
First run -
mysql> SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time FROM WF_JOBS t0 WHERE t0.bean_type = 'WorkflowJobBean' ORDER BY t0.created_time DESC LIMIT 0, 50;
..
..
50 rows in set (10 min 25.92 sec)
Rerun -
mysql> SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time FROM WF_JOBS t0 WHERE t0.bean_type = 'WorkflowJobBean' ORDER BY t0.created_time DESC LIMIT 0, 50;
50 rows in set (4.12 sec)
Is there anything I can do to improve the speed of this particular query? The status in the processlist sits at"Sorting result". The performance is "acceptable" at 4-5seconds when the table data is appears to be cached but would it be possible to speed this up any further? Is a 4 second sort on approximately 350k rows reasonable performance or can this be tuned faster on the server side? How can I keep my backup from pushing this table out of RAM on version 5.0 of mysql?
Any help would be appreciated... and i can provide any other necessary details..
Thanks!
Edited 5 time(s). Last edit at 03/04/2013 03:29PM by mysqluser 0013.