In order to find bottlenecks on your database you need tools that show those bottlenecks to you. The first step should always be enabling MySQL slow query log. The next logical step is to install mtop, the MySQL Monitoring Tool. On CentOS it can be a bit tricky. Here is how:

Install mtop on CentOS

  1. Download the latest perl-Curses for your build from http://dag.wieers.com/rpm/packages/perl-Curses/ using wget
    At the time of this How To it is: perl-Curses-1.23-1.el5.rf.x86_64.rpm (64bit) or perl-Curses-1.23-1.el5.rf.i386.rpm (32bit) for CentOS 5 – Select accordingly

    # wget  http://dag.wieers.com/rpm/packages/perl-Curses/perl-Curses-1.23-1.el5.rf.x86_64.rpm

  2. Install the RPM:

    # rpm -Uvh perl-Curses-1.23-1.el5.rf.x86_64.rpm

  3. Download and Install perl-DBI from http://dag.wieers.com/rpm/packages/perl-DBI/- This will be installed with the –nodeps option since it requires RPC::PlClient and PlServer which isn’t needed.

    #wget http://dag.wieers.com/rpm/packages/perl-DBI/perl-DBI-1.602-1.el5.rf.x86_64.rpm
    # #rpm --nodeps -Uvh perl-DBI-1.602-1.el5.rf.x86_64.rpm

  4. Download and Install perl-DBD-mysql from http://dag.wieers.com/rpm/packages/perl-DBD-mysql/

    # wget http://dag.wieers.com/rpm/packages/perl-DBD-mysql/perl-DBD-mysql-4.006-1.el5.rf.x86_64.rpm
    # rpm -Uvh perl-DBD-mysql-4.006-1.el5.rf.x86_64.rpm

  5. Download and Install mtop from http://dag.wieers.com/rpm/packages/mtop/ – Again, select the appropriate version

    # wget http://dag.wieers.com/rpm/packages/mtop/mtop-0.6.6-1.2.el5.rf.noarch.rpm
    # rpm -Uvh mtop-0.6.6-1.2.el5.rf.noarch.rpm

  6. Execute mtop

    # mtop

Initially mtop will try to login as mysqltop user. Enter another username such as root followed by the password. mtop is just like top and will show all queries as they are executed.