Hi
Dne Fri, 25 Mar 2011 00:16:55 +0100 Tyron Madlener tyronx@gmail.com napsal(a):
On Thu, Mar 24, 2011 at 1:49 PM, Michal Čihař michal@cihar.com wrote:
Hi
Dne Tue, 22 Mar 2011 21:05:06 +0100 Tyron Madlener tyronx@gmail.com napsal(a):
I sometimes missed a feature to see how much load the MySQL Server receives. The phpmyadmin status page unfortunately only shows current and total values. So my proposal is:
Yes, the reason for this is problematic collection of historical data. Please check mailing list archives, this topic has been heavily discussed last year, when charts were being implemented.
Thanks, I read up the 2 conversations from last years GSoC. Were there any other?
I don't remember exactly, but that should be it.
It was mentioned that phpMyAdmin could use something like the mytop utility. I fully agree on that, I wonder why it didn't get any response.
mytop just displays current state as well, it just peridically refreshes it, right? However displaying live status, eventually with chart keeping history of (some) status variables could be useful.
Unfortunately making such charts would probably require the user to install some sort of cronjob and we have to store a history of records to make the charts. So it either would need to pollute the mysql database with a table or we store it in a text file.
You should come with acceptable solution to this. cron job is something you really can not do on most hostings, what is quite limiting.
Well, theoretically it could be an automated mechanism that attempts to add a cronjob via the exec() function.
This usually won't work on a hosting.
Or actually before that, check if we can call the cronjob tool at all. So it will be just available to people who actually have access to cronjobs, and everyone else will just get to see an error message. Alternatively, there are also free cronjob services (e.g. http://www.setcronjob.com/) for which we could supply a php file to call and record the data.
This would need to handle authentication somehow, what I find problematic.
What I could do however without a cronjob or custom data recording is to build some query statistics. We can enable MySQLs general_log (http://dev.mysql.com/doc/refman/5.1/en/query-log.html) globally and it does all the recording for us (in the 'mysql' database).
Which will be disabled in most cases due to performance reasons.
Or even better, when setting these variables
set global log_output="TABLE"; set global slow_query_log=On; set global long_query_time=0; (only works with MySQL 5.1.21 and above)
... we can actually record the query time, rows examined, rows sent and more data about each query. With one huge drawback however, logging to a table gives us only an accuracy of 1 second.
This would not work for regular users I guess.
Nonetheless, that Information is still better than none. Also for a extended analysis we could also enable profiling for one session and "replay" the recorded SELECT queries and get the exact time as well. Well, not that exact since the database may have changed by then.
A few ideas what information we could extract from this 2 logs:
- Chart with list of most accessed tables (including the type of the
query: SELECT/INSERT/UPDATE/DELETE)
- List of slowest queries
- Most queried SELECTs that use non-indexed fields in the WHERE
clause. That definitely would have helped me on some occasions ;)
- Most common queries (groupable by database, table, field?)
- Some more food for thought:
http://www.mysqlperformanceblog.com/2011/01/10/how-to-identify-bad-queries-i... & http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools...
Yes, slow query log analysis would be useful, however AFAIK there is no way to access the log from within MySQL, you need to access filesystem of MySQL server, what complicates things (MySQL server is often remote).
I could build some code around this in a modular way so other developers could easily add other types of statistics or perfomances measures.
What do you think?
There is definitely some information, which can be obtained, but it can be hard in many cases.