Monitor SQL statement execution efficiency based on mysql slow logs

Monitor SQL statement execution efficiency based on mysql slow logs

Enable MySQL log-slow-queries (slow query records ).

In Linux, find my. cnf file (usually in/etc/mysql/), and you may find that the file cannot be saved after modification, because you do not have the corresponding permissions, you can see from the attribute that the owner of the file is root. In this case, you must first open the file as root:

Sudo nautilus/etc/mysql

Then open the my. cnf file and find the [mysqld] Tag added below:

Log-slow-queries =/path/slow. log-slow log storage path, which is created by yourself
Long_query_time = 2-queries over 2 seconds
Log-queries-not-using-indexes-the query records do not use indexes

Save and close. If you restart MySQL, the following message is displayed: world-writable config file 'etc my. cnf 'is ignored.

Then you need to run the code again:

Chmod 644/etc/my. cnf-644 indicates rw-r-

Then restart the service.

Note: log-slow-queries =/path/slow. log is the place where slow query logs are stored. This directory requires the write permission of the MySQL running account. Generally, this directory is set to the MySQL data storage directory.

I did this: first use the root user to enter the MySQL data storage directory (usually/var/lib/mysql) and create a new slow. log File (the root user can ensure that he has the permission to create a new file and then run chmod 644 ). Then change the owner and group of the file:

Chown mysql Running Account Name (you can see the owner and group of other files in the same folder) + decimal point + group name (same user name in the method)/path/slow. log

Or:

Chown mysql Running Account Name (you can see the owner and group of other files in the same folder)/path/slow. log

Chgrp group name (same method as user name)/path/slow. log

In this way, MySQL can write logs to slow. log.