Beaver in Action: Practical MySQL Optimization

Clients with an existing application sometimes ask me to fix bugs, improve efficiency by speeding up the application, or add a new feature to some existing software. The first stage of this is researching the original code – so-called reverse engineering. With SQL databases, it is not always immediately obvious which SQL queries were executed - especially if these queries were generated by a framework or some kind of external library.

In this article, I will talk specifically about MySQL and present a common optimization use case which might come in handy if you run into a similar problem one day.

MySQL allows you to save all queries into a log.

Open MySQL console:

mysql --user=USERNAME --password=PASSWORD

This turns on the log:

set global general_log = "on";

This saves it to a table:

set global log_output = "table";

To view the logs you've saved:

select * from mysql.general_log;

To compress your logs:

truncate mysql.general_log;

However, viewing logs in the MySQL's console is not as convenient as it could be because:

• There are no highlighting or formatting options
• It's hard to locate problematic queries among the huge list you get from running parallel processes.

As I often work with logs, I developed a tool called "Beaver MySQL logger" to optimize and make them easier for the user to analyze.

Continue reading %Beaver in Action: Practical MySQL Optimization%

by Kirill Zhirnov via SitePoint