[Phpmyadmin-devel] RFC: Integerating mysqltuner into phpmyadmin
MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved. More info at <http://blog.mysqltuner.com/faq/> I am currently porting this script into a PHP class, that will have these public functions - - constructor - analyze - get_variable_tuneups - get_general_tuneups - get_engine_tuneups (not yet ready) I wanted to see if there is some interest from the PMA developers in integerating this into PMA. Currently there are tabs to show the variables and status counters (server_status.php and server_variables.php). I was thinking of a server_tuneups.php script that would print out tuning recommendations. This is not a GSoC project. Let me know what you think. Thanks, Raj Shekhar
Hi Dne Tue, 30 Mar 2010 13:58:58 +0000 (UTC) Raj Shekhar <rajlist@rajshekhar.net> napsal(a):
I wanted to see if there is some interest from the PMA developers in integerating this into PMA. Currently there are tabs to show the variables and status counters (server_status.php and server_variables.php). I was thinking of a server_tuneups.php script that would print out tuning recommendations.
This is not a GSoC project.
Let me know what you think.
We already have a feature request for including such thing into server status :-). https://sourceforge.net/tracker/?func=detail&aid=1450298&group_id=23067&atid... 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). The only question is whether to integrate it into some existing page (what I would prefer) or to create new one. -- Michal Čihař | http://cihar.com | http://blog.cihar.com
Hi
Dne Tue, 30 Mar 2010 13:58:58 +0000 (UTC) Raj Shekhar <rajlist@rajshekhar.net> napsal(a): me know what you think.
We already have a feature request for including such thing into server status :-).
https://sourceforge.net/tracker/?func=detail&aid=1450298&group_id=23067&atid...
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!
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!
Hi Dne Tue, 30 Mar 2010 14:49:40 +0000 (UTC) Raj Shekhar <rajlist@rajshekhar.net> napsal(a):
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.
Great, thanks! -- Michal Čihař | http://cihar.com | http://blog.cihar.com
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. -- 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>
Hi Dne Wed, 31 Mar 2010 00:36:18 -0700 Raj Shekhar <rajlist@rajshekhar.net> napsal(a):
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)
Looks good! Please do not use short open tags (<?) but full version instead (<?php). As there is not that much of output, I'd integrate this into server_status (some of the information is already available there). -- Michal Čihař | http://cihar.com | http://blog.cihar.com
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. -- 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>
participants (3)
-
Michal Čihař -
Raj Shekhar -
rajlist＠rajshekhar.net