adding indexes in history_uint table?

[Gutsycat]

Found here https://support.zabbix.com/browse/ZBX-8731 about indexes.

One suggests to create new column using

create index history_uint_2 on history_uint (itemid, clock, ns)

But how zabbix will know about this new column and start using it?

How to enable indexes in history_uint?

[ingus.vilnis]

My two cents here would be that don't go for such change. Mostly because then you will be on your own with your DB problems. These "look, I found this on the internet" DB schema changes typically lead to either damage of the whole DB or break the future upgrade experience (and by that you will not even remember about the index changes you made a year ago).

So unless you are a pro DB admin exactly knowing what you are about to do, don't go for these changes. And then you would not be asking this question in the first place.

Better ask this - what is the problem you are currently having with the default DB schema? Could it be that some DB engine tuning or server hardware upgrade can give much better improvements?

[Gutsycat]

I'm trying to increase the speed of SELECT history_uint accroding to

# Profile
# Rank Query ID Response time Calls R/Call V/M I
# ==== ========================== ================ ======= ======= ===== =
# 1 0x... 13512.2130 47.7% 4105 3.2916 1.68 COMMIT
# 2 0x... 6565.7080 23.2% 327 20.0786 8.84 SELECT history_uint

got from:

pt-query-digest /data/mysql/log/mysql_slow.log > digest; cat digest

What would you suggest?

So unless you are a pro DB admin exactly knowing what you are about to do

I do my best to be pro DB admin

[ingus.vilnis]

Then it is great that you are trying to dig into this issue.

I have been putting quite some effort in understanding the same issue. If you follow typical How Tos on App / DB performance, one of the big suggestions is to work on the queries and DB structure optimization. I am not a big fan of these changes as they are very localized to your particular environment. There are so many big setups of Zabbix using the same DB schema, and but very few decide to go down this path. The main issue as mentioned before will be the upgrade to the next major version in case the DB structure changes for the modified table. At that point the upgrade is broken and you got to track down exactly what needs to be fixed to proceed. It is not something you want to google when you have your production server down. If you decide to turn to support for this case, don't expect that there will be any quick fixes either because the problem is complex, your changes might need to be reverted for the whole table which is huge amount of data etc... you got the idea.

Instead of fixing the app performance, I would still suggest to look into the ways how the DB processes current queries, what can be optimized there, how much data can be stored in InnoDB buffer pool, how can the query handling can be optimized etc.

Your particular queries appear to come from Zabbix web interface, presumably from selecting graphs containing many items with frequent update intervals. In Zabbix you can tell the origin of the query because the web interface uses commands in uppercase but backend server in lowercase.

To answer your initial question - well if you have a test instance where you can try and / or have a backup which you know for 300% you can recover to - ok, go for it. But be prepared for unexpected results. It may work, but better document all you have done to later be able to track the changes properly.

[kloczek]

Found here https://support.zabbix.com/browse/ZBX-8731 about indexes.

One suggests to create new column using

create index history_uint_2 on history_uint (itemid, clock, ns)

But how zabbix will know about this new column and start using it?

How to enable indexes in history_uint?

I don't remember any zabbix query which selecting data from history_uint by index. Almost all are selecting by clock column.
How did you come to the conclusion that this index index is needed?