mariadb v10.5.8
当前环境变量配置信息如下:
MariaDB [(none)]> show variables like '%query%'; |
---|
Variable_name | Value |
expensive_subquery_limit | 100 |
ft_query_expansion_limit | 20 |
have_query_cache | YES |
long_query_time | 10.000000 |
query_alloc_block_size | 16384 |
query_cache_limit | 4194304 |
query_cache_min_res_unit | 4096 |
query_cache_size | 0 |
query_cache_strip_comments | OFF |
query_cache_type | ON |
query_cache_wlock_invalidate | OFF |
query_prealloc_size | 24576 |
slow_query_log | ON |
slow_query_log_file | /opt/bitnami/mariadb/logs/mysqld.log |
打开慢查询日志,发现日志里记录了所有sql,执行非常快的sql,包括监控检查的sql都会记录,long_query_time 参数的设置貌似不生效。
慢查询日志如下:
SET timestamp=1611752484;
SELECT
page_size, compress_ops, compress_ops_ok, compress_time, uncompress_ops, uncompress_time
FROM information_schema.innodb_cmp;
User@Host: proxysql[proxysql] @ [10.244.35.166]
Thread_id: 32 Schema: QC_hit: No
Query_time: 0.002793 Lock_time: 0.000303 Rows_sent: 1 Rows_examined: 0
Rows_affected: 0 Bytes_sent: 412
SET timestamp=1611752484;
SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='WSREP_LOCAL_STATE') wsrep_local_state, @@read_only read_only, (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='WSREP_LOCAL_RECV_QUEUE') wsrep_local_recv_queue , @@wsrep_desync wsrep_desync, @@wsrep_reject_queries wsrep_reject_queries, @@wsrep_sst_donor_rejects_queries wsrep_sst_donor_rejects_queries, (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='WSREP_CLUSTER_STATUS') wsrep_cluster_status , (SELECT 'DISABLED') pxc_maint_mode;
Time: 210127 21:01:25
User@Host: proxysql[proxysql] @ [10.244.34.243]
Thread_id: 33 Schema: QC_hit: No
Query_time: 0.002554 Lock_time: 0.000302 Rows_sent: 1 Rows_examined: 0
Rows_affected: 0 Bytes_sent: 412
SET timestamp=1611752485;
SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='WSREP_LOCAL_STATE') wsrep_local_state, @@read_only read_only, (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='WSREP_LOCAL_RECV_QUEUE') wsrep_local_recv_queue , @@wsrep_desync wsrep_desync, @@wsrep_reject_queries wsrep_reject_queries, @@wsrep_sst_donor_rejects_queries wsrep_sst_donor_rejects_queries, (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='WSREP_CLUSTER_STATUS') wsrep_cluster_status , (SELECT 'DISABLED') pxc_maint_mode;
请问如下只打印大于long_query_time时间设置的sql,真正记录慢查询sql
收起