查询僵死，无法kill掉

select * from performance_schema.threads;
也提供下

yejr 发表于 2022-8-30 11:00
select * from performance_schema.threads;
也提供下

threads如附件。

beckhann 发表于 2022-8-30 11:10
threads如附件。

麻烦再提供2个信息（用文本方式，别压缩了）
1. my.cnf配置内容
2. 无法killed的两个select count... SQL的执行计划

yejr 发表于 2022-8-30 11:19
麻烦再提供2个信息（用文本方式，别压缩了）
1. my.cnf配置内容
2. 无法killed的两个select count... SQL ...

1. mysql> explain SELECT COUNT(1) FROM t_irs_busi_notice WHERE status = '1' AND send_type = 'wxwork' AND user_code = '8001449' AND notice_status = 'finish' AND send_time >= date_sub(NOW(), INTERVAL 1 * 30 DAY);
   
2. +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
   
3. | id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
   
4. +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
   
5. |  1 | SIMPLE      | t_irs_busi_notice | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 29144 |     0.00 | Using where |
   
6. +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
   
7. 1 row in set, 1 warning (0.00 sec)

复制代码

1. [root@ mysql3306]# cat my3306.cnf
   
2. # my.cnf example for GreatSQL
   
3. # last update, 2021/8/20
   
4. #
   
5. # 下面参数选项设置仅作为参考，且假设服务器可用内存为256G
   
6. #
   
7. [client]
   
8. socket    = /data/greatsql/mysql3306/data/mysql3306.sock
   
9. [mysql]
   
10. loose-skip-binary-as-hex
    
11. prompt="(\\D)[\\u@GreatSQL][\\d]>"
    
12. no-auto-rehash
    
13. [mysqld]
    
14. user    = mysql
    
15. port    = 3306
    
16. server_id = 273306
    
17. basedir = /u01/mysql/GreatSQL-8.0.25-16-Linux-glibc2.28-x86_64
    
18. datadir    = /data/greatsql/mysql3306/data
    
19. socket    = /data/greatsql/mysql3306/data/mysql3306.sock
    
20. pid-file = mysql.pid
    
21. character-set-server = UTF8MB4
    
22. lower_case_table_names = 1
    
23. skip_name_resolve = 1
    
24. #若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
    
25. default_time_zone = "+8:00"
    
26. default-authentication-plugin=mysql_native_password
    
27. 
    
28. #performance setttings
    
29. lock_wait_timeout = 3600
    
30. open_files_limit    = 65535
    
31. back_log = 1024
    
32. max_connections = 2048
    
33. max_connect_errors = 1000000
    
34. table_open_cache = 2048
    
35. table_definition_cache = 2048
    
36. thread_stack = 512K
    
37. sort_buffer_size = 4M
    
38. join_buffer_size = 4M
    
39. read_buffer_size = 8M
    
40. read_rnd_buffer_size = 4M
    
41. bulk_insert_buffer_size = 64M
    
42. thread_cache_size = 768
    
43. interactive_timeout = 600
    
44. wait_timeout = 600
    
45. tmp_table_size = 96M
    
46. max_heap_table_size = 96M
    
47. 
    
48. #log settings
    
49. log_timestamps = SYSTEM
    
50. log_error = /data/greatsql/mysql3306/logs/error.log
    
51. log_error_verbosity = 3
    
52. slow_query_log = 1
    
53. log_slow_extra = 1
    
54. slow_query_log_file = /data/greatsql/mysql3306/logs/slow.log
    
55. long_query_time = 10
    
56. log_queries_not_using_indexes = 1
    
57. log_throttle_queries_not_using_indexes = 60
    
58. min_examined_row_limit = 0
    
59. log_slow_admin_statements = 1
    
60. log_slow_slave_statements = 1
    
61. log_slow_verbosity = FULL
    
62. log_bin = /data/greatsql/mysql3306/logs/binlog
    
63. binlog_format = ROW
    
64. sync_binlog = 1
    
65. binlog_cache_size = 4M
    
66. max_binlog_cache_size = 4G
    
67. max_binlog_size = 2G
    
68. binlog_rows_query_log_events = 1
    
69. binlog_expire_logs_seconds = 604800
    
70. binlog_checksum = CRC32
    
71. gtid_mode = ON
    
72. enforce_gtid_consistency = TRUE
    
73. 
    
74. #myisam settings
    
75. key_buffer_size = 32M
    
76. myisam_sort_buffer_size = 128M
    
77. 
    
78. #replication settings
    
79. relay_log_recovery = 1
    
80. slave_parallel_type = LOGICAL_CLOCK
    
81. #并行复制线程数可以设置为逻辑CPU数量的2倍
    
82. slave_parallel_workers = 32
    
83. binlog_transaction_dependency_tracking = WRITESET
    
84. slave_preserve_commit_order = 1
    
85. slave_checkpoint_period = 2
    
86. 
    
87. #启用InnoDB并行查询优化功能
    
88. loose-force_parallel_execute = ON
    
89. #设置每个SQL语句的并行查询最大并发度
    
90. loose-parallel_default_dop = 8
    
91. #设置系统中总的并行查询线程数，可以和最大逻辑CPU数量一样
    
92. loose-parallel_max_threads = 16
    
93. #并行执行时leader线程和worker线程使用的总内存大小上限，可以设置物理内存的5-10%左右
    
94. loose-parallel_memory_limit = 2G
    
95. 
    
96. #mgr settings
    
97. loose-plugin_load_add = 'mysql_clone.so'
    
98. loose-plugin_load_add = 'group_replication.so'
    
99. loose-group_replication_group_name = "f760789f-d037-11ec-8ecf-005056a31010"
    
100. #MGR本地节点IP:PORT，请自行替换
     
101. loose-group_replication_local_address = "192.168.100.35:33061"
     
102. #MGR集群所有节点IP:PORT，请自行替换
     
103. loose-group_replication_group_seeds = '192.168.100.35:33061,192.168.100.29:33061,192.168.100.33:33061'
     
104. loose-group_replication_start_on_boot = OFF
     
105. loose-group_replication_bootstrap_group = OFF
     
106. loose-group_replication_exit_state_action = READ_ONLY
     
107. loose-group_replication_flow_control_mode = "DISABLED"
     
108. loose-group_replication_single_primary_mode = ON
     
109. loose-group_replication_majority_after_mode = ON
     
110. loose-group_replication_communication_max_message_size = 10M
     
111. loose-group_replication_arbitrator = 0
     
112. loose-group_replication_single_primary_fast_mode = 1
     
113. loose-group_replication_request_time_threshold = 100
     
114. loose-group_replication_primary_election_mode = GTID_FIRST
     
115. loose-group_replication_unreachable_majority_timeout = 30
     
116. loose-group_replication_member_expel_timeout = 5
     
117. loose-group_replication_autorejoin_tries = 288
     
118. report_host = "192.168.100.35"
     
119. 
     
120. #innodb settings
     
121. innodb_buffer_pool_size = 20G
     
122. innodb_buffer_pool_instances = 8
     
123. innodb_data_file_path = ibdata1:12M:autoextend
     
124. innodb_flush_log_at_trx_commit = 1
     
125. innodb_log_buffer_size = 32M
     
126. innodb_log_file_size = 2G
     
127. innodb_log_files_in_group = 4
     
128. innodb_doublewrite_files = 2
     
129. innodb_max_undo_log_size = 4G
     
130. # 根据您的服务器IOPS能力适当调整
     
131. # 一般配普通SSD盘的话，可以调整到 10000 - 20000
     
132. # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
     
133. innodb_io_capacity = 4000
     
134. innodb_io_capacity_max = 8000
     
135. innodb_open_files = 65534
     
136. innodb_flush_method = O_DIRECT
     
137. innodb_lru_scan_depth = 4000
     
138. innodb_lock_wait_timeout = 10
     
139. innodb_rollback_on_timeout = 1
     
140. innodb_print_all_deadlocks = 1
     
141. innodb_online_alter_log_max_size = 4G
     
142. innodb_print_ddl_logs = 1
     
143. innodb_status_file = 1
     
144. innodb_status_output = 0
     
145. innodb_status_output_locks = 1
     
146. innodb_sort_buffer_size = 64M
     
147. 
     
148. #innodb monitor settings
     
149. innodb_monitor_enable = "module_innodb"
     
150. innodb_monitor_enable = "module_server"
     
151. innodb_monitor_enable = "module_dml"
     
152. innodb_monitor_enable = "module_ddl"
     
153. innodb_monitor_enable = "module_trx"
     
154. innodb_monitor_enable = "module_os"
     
155. innodb_monitor_enable = "module_purge"
     
156. innodb_monitor_enable = "module_log"
     
157. innodb_monitor_enable = "module_lock"
     
158. innodb_monitor_enable = "module_buffer"
     
159. innodb_monitor_enable = "module_index"
     
160. innodb_monitor_enable = "module_ibuf_system"
     
161. innodb_monitor_enable = "module_buffer_page"
     
162. innodb_monitor_enable = "module_adaptive_hash"
     
163. 
     
164. #pfs settings
     
165. performance_schema = 1
     
166. #performance_schema_instrument = '%memory%=on'
     
167. performance_schema_instrument = '%lock%=on'

复制代码

beckhann 发表于 2022-8-30 11:38

收到，我先分析下，有结果再回复