Tuesday, July 11, 2006

MySQL 5.0, Bug 10210

In my previous post about enhancing the graphs the MySQL Administrator displays I added a remark that there seems to be a difference between MySQL 4.1 and 5.0.

As it seems this has already been reported as MySQL Bug #10210 and fixed, however only for 5.1. Summarizing the bug report is easy: They implemented Heisenberg (or better: the observer effect). You cannot query the counters for e. g. the number of temporary tables created without modifiying it as you go. In 4.1 all the SHOW STATUS... commands (see the manual page) could be executed without modifying the values displayed, because they were immediately sent to the client. In 5.0 a temporary table with the result is created and then sent. This allows the use of the data in stored procedures.

It is however very annoying, because it increases the noise in measuring significantly. One (somewhat clumsy) workaround is to modify the formula for the graphs you define in MySQL Administrator by subtracting the number that would otherwise be displayed on an idle server. This of course only works if you only have one instance of the Adminstrator connected to a particular server (otherwise the other sessions' increments still show up). Moreover this makes statistics on 4.1 servers wrong, so make sure you know how to read the data if you have a multi-version environment.

No comments: