In infinite wisdom Raj Shekhar rajlist@rajshekhar.net wrote:
Let me create the class, and a driver script for that. Once you have seen how it works, you should have an idea where it would fit.
Initial working version checked in at github -> http://github.com/lunatech/myq_tuner (details on how to checkout/copy in the readme itself)
Here is a sample run on my development box -
php drive.php
========== Info ========== qps : 0.000 TX : 599.5 Kb RX : 7.9 Kb Reads to Writes ratio: 100% / 0% Total buffers: 34.0 Mb global + 2.7 Mb per thread (100 max threads)
========== No problems detected in following ========== slow queries: Slow queries: 0% (0/173) available connections: Highest usage of available connections: 1% 1/100 Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts) Temporary tables created on disk: 0% (0 on disk / 108 total) Open file limit used: 2% (14/510)
========== Problems ========== query cache: Query cache is disabled Thread cache: Thread cache is disabled
========== General tuneups ========== Enable the slow query log to troubleshoot bad queries Set thread_cache_size to 4 as a starting value Increase table_cache gradually to avoid file descriptor limits
========== Variable tuneups ========== query_cache_size (>= 8M) thread_cache_size (start at 4) table_cache (> 64)
Here is the output on a box which hosts a few blogs/sites
========== Info ========== qps : 7.324 TX : 1.5 Gb RX : 1.3 Gb Reads to Writes ratio: 99% / 1% Total buffers: 50.0 Mb global + 2.6 Mb per thread (150 max threads)
========== No problems detected in following ========== slow queries: Slow queries: 0% (6/9 Million Query cache prunes per day: 0 Sorts requiring temporary tables: 0% (0 temp sorts / 1 Million sorts) Thread cache hit rate: 92% (30 Thousand created / 391 Thousand connections) Open file limit used: 12% (128/1 Thousand)
========== Problems ========== Joins performed without indexes: 26763 Temporary tables created on disk: 44% (747 Thousand on disk / 1 Million total) Table cache hit rate: 0% (64 open / 87 Thousand opened)
========== General tuneups ========== Enable the slow query log to troubleshoot bad queries Reduce or eliminate persistent connections to reduce connection usage Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits
========== Variable tuneups ========== Highest connection usage: 100% 151/150 max_connections (> 150) wait_timeout (< 150) interactive_timeout (< 28800) Query cache efficiency: 0.0% (0 cached / 8 Million selects) join_buffer_size (> 128.0 Kb, or always use indexes with joins) tmp_table_size (> 32 Mib) max_heap_table_size (> 16 Mib)
I am working on getting engine stats and calculating tuneups based on that. Michal, let me know where do you think this can fit in and any other thoughts anyone has about this.
Thanks.