[Phpmyadmin-devel] GSoC Idea: Improved Status Page with Statistics
Hello fellow developers, I am a pre-graduate Student at the Technical University of Vienna / Austria and I would enjoy to join the GSoC 2011 Program. PHPMyAdmin has become my tool of the trade for managing my website databases. I've been using it for over a decade and grew to love working with it. 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: - Instead of using anchor links put all runtime information groups into jquery-tabs where users can click through. - Using the recently added pchart libarary, implement a bunch of charts to give a graphical history of the - amount of queries - traffic load - amount of concurrent connections - In the process adjust the current chart color to fit with the currently selected theme (That snake-green over the gray color of pmahomme looks rather odd ;). - Improve look & feel of the status page where applicable 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. Despite those 2 configuration annoyances it would be a useful addition nonetheless, I think. After all, we can just hide everything behind a small "Enable server load recording"-Link where you can set up the recording. I am a great fan of building statistics and speeding up websites using AJAX so I'd love to hear your Ideas to my proposal or anything that is related to it. I could need a few more tasks :-) 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. 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 your time!
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.
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.
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.
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
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!
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. -- Michal Čihař | http://cihar.com | http://phpmyadmin.cz
participants (2)
-
Michal Čihař -
Tyron Madlener