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
