[ZBX-4840] Adding another index on "zabbix.history" table greatly improves performance Created: 2012 Apr 05  Updated: 2017 May 30  Resolved: 2012 Apr 12

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 1.9.8 (beta)
Fix Version/s: 2.0.0rc3

Type: Incident report Priority: Minor
Reporter: Michael Tewner Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: database, mysql
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

Hi Guys -

As shown below, I added an index to the "history" table on the itemid, clock, and ns fields. This greatly improved performance of many of the common queries which were previously performing filesorts. One common query went from 200 seconds to <.03 seconds. There is a tradeoff that the index is on almost all of the fields. Perhaps an index-only table would help here (if they exist in MySQL).

CREATE index history_itemid_clock_ns on history (itemid,clock,ns)

mysql> show indexes from history ;
------------------------------------------------------------------------------------------------------------------------+

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment

------------------------------------------------------------------------------------------------------------------------+

history 1 history_1 1 itemid A 17 NULL NULL   BTREE  
history 1 history_1 2 clock A 117385964 NULL NULL   BTREE  
history 1 history_itemid_clock_ns 1 itemid A 17 NULL NULL   BTREE  
history 1 history_itemid_clock_ns 2 clock A 117385964 NULL NULL   BTREE  
history 1 history_itemid_clock_ns 3 ns A 117385964 NULL NULL   BTREE  

------------------------------------------------------------------------------------------------------------------------+

Before Index:
mysql> describe select value from history where itemid=24847 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
---------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

---------------------------------------------------------------------------------------------+

1 SIMPLE history range history_1 history_1 12 NULL 420374 Using where; Using filesort

---------------------------------------------------------------------------------------------+

After Index:
mysql> describe select value from history where itemid=22922 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
---------------------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

---------------------------------------------------------------------------------------------------------------+

1 SIMPLE history range history_1,history_itemid_clock_ns history_itemid_clock_ns 12 NULL 371280 Using where

---------------------------------------------------------------------------------------------------------------+

And the performance gain:

mysql> select value from history ignore index (history_itemid_clock_ns) where itemid=24847 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
------------

value

------------

64533.2620

------------
1 row in set (3 min 22.60 sec)

mysql> select value from history where itemid=24847 and clock<1333520700 order by itemid,clock desc,ns desc limit 1;
------------

value

------------

64533.2620

------------
1 row in set (0.00 sec)

 Comments   
Comment by Alexey Pustovalov [ 2012 Apr 06 ]

I think it worth to do it.
This index change will not impact on ability of database partitioning. But this change is good for performance!

Comment by Alexander Vladishev [ 2012 Apr 12 ]

Fixed in the development branch svn://svn.zabbix.com/branches/dev/ZBX-4840.

The problem was solved by change of SQL queries. The database schema remained without changes.

Comment by dimir [ 2012 Apr 13 ]

Successfully tested, excellent improvement.

Comment by Alexander Vladishev [ 2012 Apr 13 ]

Fixed in version pre-2.0.0rc3 revision 26813.

Generated at Fri Apr 19 12:16:11 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.