Enabling MySQL logs and understanding them

A log file is a recording of everything that goes in and out of a particular server. It is a concept much like the black box of an airplane that records everything going on with the plane in the event of a problem. If you are a web developer you need to know various log files in order to debug or improve the performace of your application.

MySQL Server has several logs that can help you find out what activity is taking place.

1. Error log :
Problems encountered starting, running, or stopping Mysql Server
2. General query log :
This contains general record of what mysqld is doing. (connect,disconnect, queries)
3. Slow query log :
Queries that took more than long_query_time seconds to execute.

Enabling MySQL logs from configuration file

Logging parameters are located under [mysqld] section.

Edit MySQL configuration file:
nano /etc/mysql/my.cnf

This is the default location for my.cnf. But the location may vary. In that case try

locate my.cnf

Sample my.cnf file

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /Applications/MAMP/tmp/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
# log files can be added here.
socket = /Applications/MAMP/tmp/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

Error log:

Error log contains information about when a mysqld was started,stopped and any critical errors that occur while server is running.

To enable error logs add the following line in [mysqld]

log_error=/var/log/mysql/mysql_error.log

Once the changes are done the server has to be restarted.

service mysql restart

To make sure if log file is working perform any database operation and open the log file.

ALSO READ  Creating a cron job in cPanel to run PHP script

Enabling MySQL logs


General Query log:

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed.

To enable error logs you have to set general_log variable to 1 add specify the location of log file in [mysqld]

general_log_file = /var/log/mysql/mysql.log
general_log = 1

enter image description here


Slow Query Log

One of the main requirements for a fast web server is to has efficient and effective SQL statements or queries that are optimized.

Any non-optimal SQL (Structured Query Language) commands or statements that take too long or lengthy time to execute will use up a lot of system resources, causing MySQL database to run slower, and then more and more queries backlogs queuing up, and when connection limit is reached, visitors are been denied or refused connection. In worst case scenario, your web server will go down as well, or continuously underperform.

MySQL has built-in functionality to capture slow query log or identify queries that are not optimal and take a long time to finish.

To enable slow query log, simply add the following lines to MySQL configuration file (my.cnf) and then restart the MySQL server:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
slow_query_log = 1
log-queries-not-using-indexes

After enabling slow query log, MySQL will create, capture and log to the log file with all SQL statements that took more than long_query_time seconds to execute, which is by default set to 10 seconds.(In our case 2 seconds)

ALSO READ  Single Responsibility Principle in Object Oriented Design

long_query_time is the threshold for query execution time beyond which it is logged. Any queries taking longer than the threshold are logged, regardless of whether they use an index or not.

log_queries_not_using_indexes tells mysql to additionally log all queries that do not use an index to limit the number of rows returned.


Enabling MySQL logs on the fly:

To enable logs at runtime, login to mysql client (mysql -u root -p ) and give:

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

To disable logs at runtime, login to mysql client (mysql -u root -p ) and give:

 SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

This doesn’t require a server restart.


View Error logs:

#tail -f path-of-log-file

tail -f mysql-general.log

enter image description here

From MySQL 5.1, they have introduced one more parameter called “log_output” for server log tables where you can set the output fomat. For the log_output parameter value can be a TABLE (log to tables), FILE (log to files), or NONE (do not log to tables or files).

You can also set multiple values with comma means if you want to log statements in both TABLE and FILE than you can set variable like

mysql> SET GLOBAL log_output = 'TABLE,FILE';

To verify your log_output

mysql> show global variables like '%output%';

Enabling MySQL logs and understanding them

Examples:

To view queries that are captured by slow log run the below command if you had set log_output to TABLE else you can view the log file.

mysql> select * from mysql.slow_log;

For getting queries, that takes more than 5 seconds to execute

mysqld> SELECT query_time, lock_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log WHERE query_time > '00:00:05'

For getting queries from slow log between time interval

SELECT query_time, lock_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log WHERE start_time BETWEEN 'START TIMESTAMP' AND 'END TIMESTAMP'

There are also few 3rd party tools for viewing log files
1. percona toolkit link
2. mysqlsla link

Leave a Reply