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

[Michal Čihař]

Hi

Dne Fri, 25 Mar 2011 00:16:55 +0100
Tyron Madlener <tyronx at gmail.com> napsal(a):

> 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?

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-in-mysql
> & 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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 836 bytes
Desc: not available
URL: <http://lists.phpmyadmin.net/pipermail/developers/attachments/20110326/03e0d69e/attachment.sig>