How to check MySQL performance?

Question

i have mysql that is used on production server for php webshop application.

sometimes it works very slow. so, i will change indexes for several tables.

but before that, i have to make some kind of "snapshot" of current performances (several times per day). after that, i will change indexes, and create new "performance snapshot". then i will made some more changes in database, and made another "performance snapshot".

how can i make that "performance snapshot"? is it possible to use some kind of tool, or to ckeck some logs, or...?

if you can help me how to do that.

thank you in advance!

Solution

If you want to buy a commercial product, there is the MySQL Query Analyzer

Otherwise, you could use the SQL Profiler which is already included with MySQL.

The SQL Profiler is built into the database server and can be dynamically enabled/disabled via the MySQL client utility. To begin profiling one or more SQL queries, simply issue the following command:

mysql> set profiling=1;

Thereafter, you will see the duration of each of your queries as you run them.