High CPU usage mysql process
Article ID: 381076
Updated On:
Products
Issue/Introduction
OneClick server has high CPU alerts since a few days ago. The mysqld process is consuming between 60 - 80 % of CPU.
Environment
Spectrum: all releases
Cause
As per the slow query log, the following select query on the event table is taking more than 10 seconds:
SET timestamp=1729606117;
select event_key from event where model_key=39921 and type=4293920088 and UNIX_TIMESTAMP(time)=1729598335;
# Time: 2024-10-22T14:13:07.843470Z
# User@Host: OC_user[OC_user] @ localhost [127.0.0.1] Id: 65
# Query_time: 411.357463 Lock_time: 0.000003 Rows_sent: 28 Rows_examined: 0
SET timestamp=1729605976;
select event_key from event where model_key=13445 and type=67585 and UNIX_TIMESTAMP(time)=1729429870;
# Time: 2024-10-22T14:15:02.832107Z
# User@Host: OC_user[OC_user] @ localhost [127.0.0.1] Id: 66
# Query_time: 212.879333 Lock_time: 0.000003 Rows_sent: 9 Rows_examined: 0
SET timestamp=1729606289;
select event_key from event where model_key=39921 and type=4293919949 and UNIX_TIMESTAMP(time)=1729598335;
# Time: 2024-10-22T14:17:25.442680Z
# User@Host: OC_user[OC_user] @ localhost [127.0.0.1] Id: 65
# Query_time: 257.591730 Lock_time: 0.000002 Rows_sent: 13 Rows_examined: 0
SET timestamp=1729606387;
select event_key from event where model_key=13445 and type=95485990 and UNIX_TIMESTAMP(time)=1729429870;
# Time: 2024-10-22T14:18:13.964851Z
# User@Host: OC_user[OC_user] @ localhost [127.0.0.1] Id: 66
# Query_time: 191.129230 Lock_time: 0.000002 Rows_sent: 9 Rows_examined: 0
Resolution
Please run this query to create a combined index on the events table. This index should help in faster processing:
cd $SPECROOT/mysq/bin/
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<password> reporting -A
mysql> create index `idx_model_key_time_type` on event( model_key, type, time );
Additional Information
You can also check the new index created, there should be three new rows like as below:
mysql> show indexes from event;
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| event | 1 | idx_model_key_time_type | 1 | model_key | A | 70223 | NULL | NULL | | BTREE | | | YES | NULL |
| event | 1 | idx_model_key_time_type | 2 | type | A | 170405 | NULL | NULL | | BTREE | | | YES | NULL |
| event | 1 | idx_model_key_time_type | 3 | time | A | 471605 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
Feedback
