30 Mar
2010
30 Mar
'10
10:58 a.m.
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
30 Mar
30 Mar
11:11 a.m.
Hi
Dne Tue, 30 Mar 2010 13:58:58 +0000 (UTC)
Raj Shekhar <rajlist(a)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=2…
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
11:35 a.m.
Hi
Dne Tue, 30 Mar 2010 13:58:58 +0000 (UTC)
Raj Shekhar <rajlist(a)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=2…
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!
11:49 a.m.
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!
11:58 a.m.
Hi
Dne Tue, 30 Mar 2010 14:49:40 +0000 (UTC)
Raj Shekhar <rajlist(a)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
31 Mar
31 Mar
4:36 a.m.
In infinite wisdom Raj Shekhar <rajlist(a)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>
5:24 a.m.
Hi
Dne Wed, 31 Mar 2010 00:36:18 -0700
Raj Shekhar <rajlist(a)rajshekhar.net> napsal(a):
In infinite wisdom Raj Shekhar
<rajlist(a)rajshekhar.net> wrote:
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
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)
4:36 p.m.
In infinite wisdom Michal Čihař <michal(a)cihar.com> wrote:
Thanks. Will make these changes.
Dne Wed, 31 Mar 2010 00:36:18 -0700
Raj Shekhar <rajlist(a)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).
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>
5274
days inactive
5275
days old
7 comments
3 participants
participants (3)
-
Michal Čihař -
Raj Shekhar -
rajlist＠rajshekhar.net