Tuesday, July 11, 2006

Enhanced MySQL Administrator Graphs

Update: MySQL 4 and 5 behave differently.
In MySQL5 there is a steady activity on some handlers, probably caused by the status queries themselves. This does not happen in 4.1. I have issued a support call with MySQL and will see what to do about it.

Update 2: See the follow-up post for more information on how to work around this.

MySQL Administrator is one of the graphical tools MySQL provide to manage their database servers. Apart from other things like server daemon control and a log file viewer this tool includes visual controls to display the load of the database server.

Even though the out-of-the-box configuration already contains some useful diagrams, I added some new and modified the existing. If you'd like to use them, please feel free to do so, they can be downloaded here: mysqladmin_health.xml. Please notice, that this is not a java file, but I had to rename it to be able to upload it to JRoller. Just remove the ".java" from the filename.

There is a file in the Administrator installation directory that serves as a default. However for each user a copy is placed in his/her home directory. If this private copy exists, it will be used instead. On Windows you have to put it into the directory you reach by entering %userprofile% into the address bar of an Explorer window.

From there on the further path depends on your Windows language version. On a German Windows it is Anwendungsdaten\MySQL, on an English version it's Application data\MySQL. Please be sure that no instance of MySQL Administrator is running, because on exit it will overwrite that file with its currently running settings.

If you have useful customizations, please feel free to share them, too.

Here you can see the new graphs and a short explanation:

  • Individual counters for sent and received bytes
    Traffic Graph
  • InnoDB pages read/written ratio and number of temporary tables created and how many of them were on disk (as opposed to in memory)
    InnoDB reads and temp tables
  • History of threads in the server and their idle/working ratio
    Idle and working threads
  • Number and type of DML statements performed.
    History and type of DML statements
  • History of calls to the Read_First handler.
    History of Read First Handler calls
  • History of calls to the Read_Next handler.
    History of Read Next Handler calls
  • History of calls to the Read_Prev handler.
    History of Read Prev Handler calls
  • History of calls to the Read_Rnd handler.
    History of Read Random Handler calls
  • History of calls to the Read_Rnd_Next handler.
    Number of Read Random Next Handler calls

For more details on the different handler types, see the "Status variables" tab in MySQL Administrator or the MySQL manual (see the correct manual page for your MySQL version!)

No comments: