0

Are there any open source MySQL performance tools for optimizing SQL queries?

1
  • explain <query>.
    – jordanm
    Commented Apr 4, 2013 at 4:19

2 Answers 2

0

To profile a mysql command you could use SHOW PROFILE

mysql> SET profiling = 1;
mysql> [type your query here]
mysql> SHOW PROFILE;

and to get more information about what mysql would do in order to get your desired result, you could use EXPLAIN.

mysql> EXPLAIN [type your query here]

Good starting points for optimizations are the usage of indices and the increase of memory usage in my.cnf (the default parameters could be very low). If you delete a lot of data in a table OPTIMIZE TABLE could also be helpful.

OPTIMIZE TABLE table;

In any case you should use the output of SHOW PROFILE to determine which parts need a lot of time and are worth to be optimized.

Be aware to switch of the query cache while optimizing. You would trick yourself otherwise.

0

First, no amount of optimization and caching can replace physical hardware. The best form of optimization is to add more memory to your server and then perform optimization steps.

Optimizing MySQL is an ongoing procedure. You should be performing optimizations on MySQL every week. This will allow you to tune your MySQL configuration.

Five years ago when this question was asked, we were on Trusty Tahr, the one I remember the most and with it there was a file my.cnf that you would edit, fast forward to Xenial, if you open /etc/mysql there is no my.cnf file to edit. What it is now is a link to /etc/alternatives/my.cnf

The file you want to edit with Xenial (Ubuntu 16), is in another directory and that is the /etc/mysql/mysql.conf.d. The file you will be editing for those viewing this post that I am now bringing up to speed is mysqld.cnf.

Please back up your file, then go back to your home directory and use mysqlcheck to optimize your databases.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .