-
Problem report
-
Resolution: Unresolved
-
Major
-
6.0.34, 7.0.5, 7.2.0alpha1
-
None
-
Support backlog
Steps to reproduce:
- Insert more then 1,000,000 rows into alerts table
- Create a test action with multiple steps (1, 2, 3-0)
- Trigger some trigger
- After first step, disable the host
Result:
A lot of similar log in zabbix_server.log:
1263:20241101:125231.015 slow query: 148.064865 sec, "select userid,mediatypeid,subject,message,esc_step from alerts where alertid in (select max(alertid) from alerts where actionid=316 and mediatypeid is not null and alerttype=0 and acknowledgeid is null and eventid=265058805 group by userid,mediatypeid,esc_step) order by userid,mediatypeid,esc_step desc"
And slow log mysql (after analyzed):
{
"lock_time": "0.000133",
"rows_sent": "1",
"rows_examined": "4283201",
"instance_id": "********",
"db_name": "zabbix",
"query_sql": "select userid,mediatypeid,subject,message,esc_step from alerts where alertid in (select max(alertid) from alerts where actionid=316 and mediatypeid is not null and alerttype=0 and acknowledgeid is null and eventid=265058819 group by userid,mediatypeid,esc_step) order by userid,mediatypeid,esc_step desc",
"user_name": "zabbix",
"user_host": "********",
"query_time": "140.069441",
"node_id": "********"
}
Explain this query got: (note: after manually delete data to reduce query time)
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: alerts
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1178229
filtered: 100.00
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: alerts
partitions: NULL
type: ref
possible_keys: alerts_1,alerts_3,alerts_5,c_alerts_6,alerts_8
key: alerts_3
key_len: 8
ref: const
rows: 8
filtered: 1.25
Extra: Using where; Using temporary
Expected:
Primary key 'alertid' should be used in query.
Or housekeeper should clean old data to reduce query time.
Additional information:
It's probably introduced by ZBX-20249 to fixed escalation message issue with Oracle DB.
But i don't know why mysql not use index to query data in PRIMARY query. If run two query independently (replace subquery use result). Everything work just fine.🤔
