Not the answer you need?
Register and ask your own question!

Cannot start logging of slow queries at run-time

mskidan@ironfx.com[email protected] EntrantCurrent User Role Beginner
Hi!
I cannot configure my Percona-mysql instance to start logging of slow queries:

Server version: 5.6.21-70.1-56-log Percona XtraDB Cluster (GPL), Release rel70.1, Revision 938, WSREP version 25.8, wsrep_25.8.r4150

It is resetting “slow_query_log” every moment.

Checking the “slow_query_log” . It is OFF now:


mysql> show global variables like 'slow_query_log' \G
*************************** 1. row ***************************
Variable_name: slow_query_log
Value: OFF
1 row in set (0.00 sec)


Switching it ON:

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

Checking the “slow_query_log” . It is ON now:

mysql> show global variables like 'slow_query_log' \G
*************************** 1. row ***************************
Variable_name: slow_query_log
Value: ON
1 row in set (0.00 sec)


Checking one more time the “slow_query_log” in a few moments . It is OFF now:

mysql> show global variables like 'slow_query_log' \G
*************************** 1. row ***************************
Variable_name: slow_query_log
Value: OFF
1 row in set (0.00 sec)


Checking the slow log file size:

[[email protected] mysql]# ll /data/mysql/sugardb1-slow.log
-rw-r--r-- 1 mysql root 0 Jan 11 12:23 /data/mysql/sugardb1-slow.log
[[email protected] mysql]# ll /data/mysql/sugardb1-slow.log
-rw-r--r-- 1 mysql root 260 Jan 11 12:23 /data/mysql/sugardb1-slow.log
[[email protected] mysql]# ll /data/mysql/sugardb1-slow.log
-rw-r--r-- 1 mysql root 0 Jan 11 12:23 /data/mysql/sugardb1-slow.log
[[email protected] mysql]# ll /data/mysql/sugardb1-slow.log
-rw-r--r-- 1 mysql root 0 Jan 11 12:24 /data/mysql/sugardb1-slow.log
[[email protected] mysql]# ll /data/mysql/sugardb1-slow.log
-rw-r--r-- 1 mysql root 260 Jan 11 12:24 /data/mysql/sugardb1-slow.log


The file has been resetting.


The system variables are

mysql> show global variables like 'log_slow%' \G
*************************** 1. row ***************************
Variable_name: log_slow_admin_statements
Value: ON
*************************** 2. row ***************************
Variable_name: log_slow_filter
Value:
*************************** 3. row ***************************
Variable_name: log_slow_rate_limit
Value: 10
*************************** 4. row ***************************
Variable_name: log_slow_rate_type
Value: query
*************************** 5. row ***************************
Variable_name: log_slow_slave_statements
Value: ON
*************************** 6. row ***************************
Variable_name: log_slow_sp_statements
Value: ON
*************************** 7. row ***************************
Variable_name: log_slow_verbosity
Value: microtime,query_plan,innodb
7 rows in set (0.00 sec)


mysql> show global variables like 'slow_query%' \G
*************************** 1. row ***************************
Variable_name: slow_query_log
Value: OFF
*************************** 2. row ***************************
Variable_name: slow_query_log_always_write_time
Value: 0.010000
*************************** 3. row ***************************
Variable_name: slow_query_log_file
Value: /data/mysql/sugardb1-slow.log
*************************** 4. row ***************************
Variable_name: slow_query_log_timestamp_always
Value: ON
*************************** 5. row ***************************
Variable_name: slow_query_log_timestamp_precision
Value: microsecond
*************************** 6. row ***************************
Variable_name: slow_query_log_use_global_control
Value: log_slow_filter,log_slow_rate_limit,log_slow_verbosity,long_query_time,min_examined_row_limit
6 rows in set (0.00 sec)

mysql>


mysql> show variables like 'performance_schema' \G
*************************** 1. row ***************************
Variable_name: performance_schema
Value: OFF
1 row in set (0.00 sec)

Can someone give me advice howto resolve this issue?

Comments

  • mskidan@ironfx.com[email protected] Entrant Current User Role Beginner
    Also strange flapping of the system variable:

    mysql> SHOW variables like 'slow_query_log';
    +

    +
    +
    | Variable_name | Value |
    +
    +
    +
    | slow_query_log | ON |
    +
    +
    +
    1 row in set (0.00 sec)

    mysql> SHOW variables like 'slow_query_log';
    +
    +
    +
    | Variable_name | Value |
    +
    +
    +
    | slow_query_log | OFF |
    +
    +
    +
    1 row in set (0.00 sec)

    mysql> SHOW variables like 'slow_query_log';
    +
    +
    +
    | Variable_name | Value |
    +
    +
    +
    | slow_query_log | ON |
    +
    +
    +
    1 row in set (0.00 sec)

    mysql> SHOW variables like 'slow_query_log';
    +
    +
    +
    | Variable_name | Value |
    +
    +
    +
    | slow_query_log | OFF |
    +
    +
    +
    1 row in set (0.00 sec)


    Server version is

    Server version: 5.6.21-70.1-56-log Percona XtraDB Cluster (GPL), Release rel70.1, Revision 938, WSREP version 25.8, wsrep_25.8.r4150
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.