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;
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;
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
你这里只输出了名字中含query的变量。
慢查询日志除了 long_query_time 控制按时间设置慢SQL,还有log_queries_not_using_indexes 未用到索引的SQL(这里不管SQL本身快慢),log_slow_admin_statements是否记录管理类SQL 等参数,请参考:https://mariadb.com/kb/en/slow-query-log-overview/
另外Maridb galera cluster环境还有一些 galera 特有的option, 请参考:
https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#option-groups
https://mariadb.com/kb/en/wsrep_provider_options/