-
Incident report
-
Resolution: Fixed
-
Major
-
1.8.5
-
Following patch is only tested on MySQL but should work on all databases as the SQL query is quite simple.
The Zabbix Server sends a lot of queries like this to the database:
SELECT value FROM history_text WHERE itemid =33839 ORDER BY id DESC LIMIT 1;
There can be a lot of rows for one specific itemid in the history table. MySQL has to collect them all, sort them and return one single value. This is how MySQL does this job:
mysql> explain SELECT value FROM history_text WHERE itemid =33839 ORDER BY id DESC LIMIT 1;
----------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | history_text | ref | history_text_2,history_text_1 | history_text_1 | 8 | const | 24552 | Using where; Using filesort |
----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
The attached patch uses additional informations from the items table to help the database to use the index efficently. This is how this query looks with the patch applied:
SELECT h.value FROM history_text as h, items as i WHERE i.itemid=33839 and h.itemid=i.itemid and h.clock=i.lastclock ORDER BY id DESC LIMIT 1;
And how MySQL does it's job:
mysql> explain SELECT h.value FROM history_text as h, items as i WHERE i.itemid=33839 and h.itemid=i.itemid and h.clock=i.lastclock ORDER BY id DESC LIMIT 1;
--------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | i | const | PRIMARY | PRIMARY | 8 | const | 1 | Using filesort |
| 1 | SIMPLE | h | ref | history_text_2,history_text_1 | history_text_1 | 12 | const,const | 1 | Using where |
--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL needs one more step but had only one row in each step. So it's no longer sorting 24552 rows but doing two index lookups which is much more efficient.
It's a bit different in src/libs/zbxserver/expression.c as there are two separate queries. I modified the first one to also return the lastclock field from items and used this value in the second query to allow an index lookup here too.
With this patch I reduced the CPU load of mysqld from about 400% to 20% on a six-core machine. (140 monitored hosts, 10k items, 200vps)
- duplicates
-
-
- Closed
-