[Phpmyadmin-devel] GSoC Idea: Improved Status Page with Statistics

[Tyron Madlener]

On Thu, Mar 24, 2011 at 1:49 PM, Michal Čihař <michal at cihar.com> wrote:
> Hi
>
> Dne Tue, 22 Mar 2011 21:05:06 +0100
> Tyron Madlener <tyronx at 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-in-mysql
& 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!