[Phpmyadmin-devel] RFC: Integerating mysqltuner into phpmyadmin

[Raj Shekhar]

In infinite wisdom Raj Shekhar <rajlist at 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.
-- 
Raj Shekhar
-
If there's anything more important than my ego around, I want it
caught and shot now.
- 
Read the latest at my blog: "casio's g-shock watch" <http://rajshekhar.net/blog/archives/360-casios-g-shock-watch.html>