MySQL Profiling and Performance tools

I'm working with a MySQL database that has been growing, and has reached about 2 Gb these days. So needless to say we're getting more interested in profiling the database. I started a search for some tools and this is the list thus far, in no particular order.

1. MyTop
The first tool I came across was mytop, which is a little tool inspired by 'top'. I also found this quick tutorial tutorial on how to interpret the screens. It does require depend on some Perl libraries, which you can install using MCPAN:
sudo perl -MCPAN -e shell

then at the cpan prompt:
install DBI
install DBD::mysql
install Term::ReadKey
install Term::ANSIColor
install Time::HiRes

In the directory where you extracted mytop execute:
perl Makefile.PL
make
make test
sudo make install

2. Maatkit
MySQL toolkit is now called Maatkit. Maatkit can be found on sourceforge and more recently on code.google. It looks like it can come in very handy with running replicated databases. Peter Zeitsev has nice blog entries on what it can do, or you can just read the docs.

3. MySQL Query Profiling
Recently MySQL added a profiling tool in the server itself called MySQL Query Profiler. This tool provides very detailed information on where the time is spend for a query.

4. MyProfi
MyProfi is a log analyzer and profiler. Extracts the most popular queries grouping them by their normalized form and shows the statistics for each group. Helps you recognize the most frequently run queries to be able to optimize overall db performance.

Switch jUDDI on JBossESB over to Postgres

By default JBossESB uses HSQL to handle persistence for jUDDI. For production it is recommended to switch it to for example Postgres.

1. First you will need to download the Postgres JDBC driver. I selected the '8.2-507 JDBC 2EE' driver. You can copy it to either the jboss_home/server/default/lib directory, or to the jbossesb.sar/lib directory.

2. Create the juddi database. Run the pgAdmin a shown in Figure 1.



Figure 1. The Postgres Admin

and create a juddi user, which has create rights to create tables, see Figure 2.



Figure 2. Add a user called 'juddi'


3. Next you should update the datasource for jUDDI, which is defined in the jbossesb.sar/juddi-ds.xml. Comment out the Hypersonic configuration and add the postgres datasource configuration like



4. Finally we have to tell jUDDI where it can find the DDL to create the juddi schema. For this we edit the jbossesb.sar/esb.juddi.xml. Modify the 'juddi.sqlFiles' setting by replacing the 'hsqldb' occurances to 'postgresql'.



Now on startup JBossESB will create the jUDDI tables in the Postgres juddi database.

For an overview of jUDDI in JBossESB in pdf format see the Registry Guide.