We already have a feature request for including such thing into server status :-).

Excellent.

So yes, we're definitely interested (please note that your class needs to be licensed under GPLv2 compatible license so that it can be included in phpMyAdmin).

Not an issue. I will put it under whatever license PMA is.

The only question is whether to integrate it into some existing page (what I would prefer) or to create new one.

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.

Thanks!

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.

In infinite wisdom Michal Čihař michal@cihar.com wrote:

Dne Wed, 31 Mar 2010 00:36:18 -0700 Raj Shekhar rajlist@rajshekhar.net napsal(a):

...

Initial working version checked in at github -> http://github.com/lunatech/myq_tuner (details on how to checkout/copy in the readme itself)

Looks good!

Please do not use short open tags (<?) but full version instead (<?php).

Thanks. Will make these changes.

As there is not that much of output, I'd integrate this into server_status (some of the information is already available there).

makes sense. Will put in these changes by April 08.