告警主机:
告警地址:
告警时间: 2024.03.15 09:38:33
告警等级: High
告警信息: Load average is too high (per CPU load over 3 for 5m)
问题详情: Load average (1m avg):28.93
当前状态: PROBLEM:28.93
现象：
show processlist能看到有sql的线程有56个,大部分是如下的sql执行中
执行计划如下
mysql> desc select basefile0_.c_id as c_id1_23_, basefile0_.crt_time as crt_time2_23_, basefile0_.crt_user_id as crt_user3_23_, basefile0_.crt_user_name as crt_user4_23_, basefile0_.deleted as deleted5_23_, basefile0_.depart_id as depart_i6_23_, basefile0_.org_name as org_name7_23_, basefile0_.tenant_id as tenant_i8_23_, basefile0_.upd_time as upd_time9_23_, basefile0_.upd_user_id as upd_use10_23_, basefile0_.upd_user_name as upd_use11_23_, basefile0_.c_file_id as c_file_12_23_, basefile0_.c_file_name as c_file_13_23_, basefile0_.c_file_path as c_file_14_23_, basefile0_.c_file_type as c_file_15_23_, basefile0_.c_obj_id as c_obj_i16_23_, basefile0_.c_obj_type as c_obj_t17_23_, basefile0_.c_validity_date as c_valid18_23_, basefile0_.c_validity_date_start as c_valid19_23_ from t_base_file basefile0_ where basefile0_.c_obj_id='07b83a5d3fc03000' and basefile0_.c_obj_type='RETURN_BILL_ADJUNCT' and basefile0_.c_file_name='hr2024-03-15 08:53:571710464037173.jpg' and basefile0_.c_file_type=null and basefile0_.deleted=0G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: basefile0_
partitions: NULL
type: ref
possible_keys: inx_id,inx_type,inx_id_type
key: inx_id
key_len: 1023
ref: const
rows: 13724
filtered: 0.25
Extra: Using where
1 row in set, 1 warning (0.00 sec)
explain format=json的执行计划
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "basefile0_",
"access_type": "ref",
"possible_keys": [
"inx_id"
],
"key": "inx_id",
"used_key_parts": [
"c_obj_id"
],
"key_length": "1023",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": "5.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.01",
"prefix_cost": "1.20",
"data_read_per_join": "370"
},
"used_columns": [
"c_id",
"crt_time",
"crt_user_id",
"crt_user_name",
"deleted",
"depart_id",
"org_name",
"tenant_id",
"upd_time",
"upd_user_id",
"upd_user_name",
"c_file_id",
"c_file_name",
"c_file_path",
"c_file_type",
"c_obj_id",
"c_obj_type",
"c_validity_date",
"c_validity_date_start"
],
"attached_condition": "((`ilp_basic_db`.`basefile0_`.`deleted` = 0) and (`ilp_basic_db`.`basefile0_`.`c_obj_type` = 'RETURN_BILL_ADJUNCT') and (`ilp_basic_db`.`basefile0_`.`c_file_name` = 'hr2024-03-15 08:53:571710464037173.jpg') and (`ilp_basic_db`.`basefile0_`.`c_file_type` = NULL))"
}
}
}
1 row in set, 1 warning (0.04 sec)
mysql> show index from t_base_file;
+-------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_base_file | 0 | PRIMARY | 1 | c_id | A | 52080532 | NULL | NULL | | BTREE | | |
| t_base_file | 1 | inx_crt_time | 1 | crt_time | A | 43423464 | NULL | NULL | YES | BTREE | | |
| t_base_file | 1 | inx_id | 1 | c_obj_id | A | 14115387 | NULL | NULL | YES | BTREE | | |
| t_base_file | 1 | inx_type | 1 | c_obj_type | A | 3781 | NULL | NULL | YES | BTREE | | |
| t_base_file | 1 | inx_id_type | 1 | c_obj_id | A | 17401726 | NULL | NULL | YES | BTREE | | |
| t_base_file | 1 | inx_id_type | 2 | c_obj_type | A | 18389838 | NULL | NULL | YES | BTREE | | |
+-------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.02 sec)
查看下执行时间，不长0.37秒
mysql> select count(*)
-> from t_base_file basefile0_
-> where basefile0_.c_obj_id = '07b83a5d3fc03000'
-> and basefile0_.c_obj_type = 'RETURN_BILL_ADJUNCT'
-> and basefile0_.c_file_name = 'hr2024-03-15 08:53:571710464037173.jpg'
-> and basefile0_.c_file_type = null
-> and basefile0_.deleted = 0;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.37 sec)
优化思路
mysql> select count(*) from t_base_file basefile0_ where basefile0_.c_obj_id = '0777192574802000';
+----------+
| count(*) |
+----------+
| 35378 |
+----------+
1 row in set (0.33 sec)
mysql> select count(*) from t_base_file basefile0_ where basefile0_.c_file_type = null;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (3 min 45 sec)
mysql> select count(*) from t_base_file basefile0_ where basefile0_.c_obj_id = '0777192574802000' and basefile0_.c_file_type = null;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (5.27 sec)
创建优化的索引
mysql> create index idx_c_file_type on t_base_file(c_file_type);
Query OK, 0 rows affected (6 min 32.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
测试执行时间
mysql> select count(*) from t_base_file basefile0_ where basefile0_.c_obj_id = '0777192574802000' and basefile0_.c_file_type = null;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
查看执行计划
mysql> desc select count(*) from t_base_file basefile0_ where basefile0_.c_obj_id = '0777192574802000' and basefile0_.c_file_type = null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.02 sec)
mysql> desc select count(*) from t_base_file basefile0_ force index(inx_id) where basefile0_.c_obj_id = '0777192574802000' and basefile0_.c_file_type = null;
+----+-------------+------------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
| 1 | SIMPLE | basefile0_ | NULL | ref | inx_id | inx_id | 1023 | const | 70536 | 10.00 | Using where |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+-------+----------+-------------+
1 row in set, 1 warning (0.08 sec)
完整sql执行计划
mysql> desc select basefile0_.c_id as c_id1_23_, basefile0_.crt_time as crt_time2_23_, basefile0_.crt_user_id as crt_user3_23_, basefile0_.crt_user_name as crt_user4_23_, basefile0_.deleted as deleted5_23_, basefile0_.depart_id as depart_i6_23_, basefile0_.org_name as org_name7_23_, basefile0_.tenant_id as tenant_i8_23_, basefile0_.upd_time as upd_time9_23_, basefile0_.upd_user_id as upd_use10_23_, basefile0_.upd_user_name as upd_use11_23_, basefile0_.c_file_id as c_file_12_23_, basefile0_.c_file_name as c_file_13_23_, basefile0_.c_file_path as c_file_14_23_, basefile0_.c_file_type as c_file_15_23_, basefile0_.c_obj_id as c_obj_i16_23_, basefile0_.c_obj_type as c_obj_t17_23_, basefile0_.c_validity_date as c_valid18_23_, basefile0_.c_validity_date_start as c_valid19_23_ from t_base_file basefile0_ where basefile0_.c_obj_id='07b83a5d3fc03000' and basefile0_.c_obj_type='RETURN_BILL_ADJUNCT' and basefile0_.c_file_name='hr2024-03-15 08:53:571710464037173.jpg' and basefile0_.c_file_type=null and basefile0_.deleted=0G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: no matching row in const table
1 row in set, 1 warning (0.00 sec)
explain format=json
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"message": "no matching row in const table"
}
}
1 row in set, 1 warning (0.09 sec)
测试完整sql的执行时间
mysql> select basefile0_.c_id as c_id1_23_, basefile0_.crt_time as crt_time2_23_, basefile0_.crt_user_id as crt_user3_23_, basefile0_.crt_user_name as crt_user4_23_, basefile0_.deleted as deleted5_23_, basefile0_.depart_id as depart_i6_23_, basefile0_.org_name as org_name7_23_, basefile0_.tenant_id as tenant_i8_23_, basefile0_.upd_time as upd_time9_23_, basefile0_.upd_user_id as upd_use10_23_, basefile0_.upd_user_name as upd_use11_23_, basefile0_.c_file_id as c_file_12_23_, basefile0_.c_file_name as c_file_13_23_, basefile0_.c_file_path as c_file_14_23_, basefile0_.c_file_type as c_file_15_23_, basefile0_.c_obj_id as c_obj_i16_23_, basefile0_.c_obj_type as c_obj_t17_23_, basefile0_.c_validity_date as c_valid18_23_, basefile0_.c_validity_date_start as c_valid19_23_ from t_base_file basefile0_ where basefile0_.c_obj_id='07b83a5d3fc03000' and basefile0_.c_obj_type='RETURN_BILL_ADJUNCT' and basefile0_.c_file_name='hr2024-03-15 08:53:571710464037173.jpg' and basefile0_.c_file_type=null and basefile0_.deleted=0;
Empty set (0.00 sec)
