Check or trace slow query in MariaDB or MySQL

Enable the Slow Query log setting for tracing slow queries in MariaDB

Check the Slow Query log setting in enabled or disabled:

show variables like '%slow_query_log%'

Variable_name      |Value                  |
-------------------+-----------------------+
slow_query_log     |OFF                    |

--For Enable the SLOW QUERY LOG:
SET GLOBAL slow_query_log=1;

--For Disable the SLOW QUERY LOG:
SET GLOBAL slow_query_log=0;

Note: For new connection it will start logging the slow SQL queries depend upon your settings.

Configure the timing for the SLOW Query which you want to monitor( default is 10 Seconds)

show variables like 'long_query_time'

Variable_name  |Value    |
---------------+---------+
long_query_time|10.000000|

--Change the Value to 50 Seconds:

SET GLOBAL long_query_time=50;

Note: For see the effect of these parameter, you need to make new connection on existing connection is show 10.

Check the table or file which log mode you need for the log:

show variables like 'log_output'

Variable_name|Value|
-------------+-----+
log_output   |FILE |

--Change the log mode to file or table
SET GLOBAL log_output='FILE';
OR
SET GLOBAL log_output='TABLE';

Note: i changed to table for view in table form:

SET GLOBAL log_output='TABLE';
show variables like 'log_output'
Variable_name|Value|
-------------+-----+
log_output   |TABLE|

Check the output for file or table mode:

For file select the path and filename, then open the file at OS level:
slow_query_log_file = /var/log/mysql/slow-query

For table access:
SELECT * FROM mysql.slow_log;

Truncate table if not used:

truncate table mysql.slow_log;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.