The query profiling of MySQL server is a useful method when you want to analyze the database performance. This document will show you some usefull query profiling technology on MySQL server.
Check out the configuration:
mysql> show variables like '%log%';
Logging parameters are located under [mysqld] section in the my.cnf file.
$sudo vim /etc/mysql/my.cnf
All log files are NOT enabled by default MySQL setup on Ubuntu. Default Debian setup sends Error log to syslog. The other log files are not enabled.
# * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf. # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name
Default Debian setup sends Error log to syslog. If you do not want the error log to the syslog. You can comment the following lines in the file “/etc/mysql/conf.d/mysqld_safe_syslog.cnf”
Then, add in /etc/mysql/my.cnf the following lines:
[mysqld_safe] log_error=/var/log/mysql/mysql_error.log [mysqld] log_error=/var/log/mysql/mysql_error.log
To enable General Query Log, uncomment (or add) the relevant lines
general_log_file = /var/log/mysql/mysql.log general_log = 1
To enable Slow Query Log, uncomment (or add) the relevant lines
log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes
$sudo service mysql restart
If you want to change the log configuration without restarting the server:
To enable logs at runtime, login to mysql client:
$mysql -u root -p mysql> SET GLOBAL general_log = 'ON'; mysql> SET GLOBAL slow_query_log = 'ON';
To disable logs at runtime, login to mysql client:
$mysql -u root -p mysql> SET GLOBAL general_log = 'OFF'; mysql> SET GLOBAL slow_query_log = 'OFF';
$sudo tail -f /path/to/your/log/file