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? 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.
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. 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.
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).
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. 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...
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?
I have seen that you require GSoC applicants to be developers of phpmyadmin, so I will go and check if there's anything in the tracker that I can fix or build within the next days.
Indeed, we require at least some knowledge of the code to see that student is actually able to contribute.
I've already submitted a patch to change the AJAX loading messages not having a 5 second timeout but instead fade out once the data is loaded. But currently its implemented only when adding fields to a new table. So I will be working to implement this everywhere within the next days. I hope that qualifies, otherwise I'll look for something more to improve =)
P.S.: Your changelog (http://www.phpmyadmin.net/documentation/changelog.php) linked from (http://www.phpmyadmin.net/home_page/docs.php) seems to be broken.
Thanks for noticing, fixed it to redirect to demo server.
-- Michal Čihař | http://cihar.com | http://blog.cihar.com
Thanks for your time!