[Z3005] query failed: [1206] The total number of locks exceeds the lock table size

[cesarsj]

When trying to delete a history of an item the following message is occurring in zabbix_server.log. We have over 800 hosts and our housekeeper is still disabled. I think a lot of errors came up when I activated the housekeeper, for a long time just protecting the data and never doing the cleanup.

17245:20190827:081239.926 [Z3005] query failed: [1206] The total number of locks exceeds the lock table size [insert into history (itemid,clock,ns,value) values (27909,1566904359,726899222,0.000000),(27894,15669 04359,729123611,0.000000),(39039,1566904359,729209 357,0.000000),(25849,1566904359,732774076,0.110000 ),(24699,1566904359,732984270,0.000000),(27914,156 6904359,734648543,0.000000),(27884,1566904359,7387 78350,24931.015917),(29649,1566904359,740018338,0. 000000),(29799,1566904359,740400253,99.727152),(26 559,1566904359,741775535,0.700175),(22804,15669043 59,742037766,23.256797),(28204,1566904359,74330424 0,240.627722),(27919,1566904359,746662576,129167.7 52286),(22809,1566904359,747605964,7.759418),(3339 9,1566904359,749906377,7.229662),(27889,1566904359 ,751573277,0.000000),(25839,1566904359,754095501,2 93.078688),(26199,1566904359,761325917,5.842456),( 23679,1566904359,761899105,259168.072951),(28499,1 566904359,762033314,13.082822),(22799,1566904359,7 63630647,0.000000),(35019,1566904359,775087385,0.0 00000),(30939,1566904359,778729564,100.000000),(35 079,1566904359,779033763,0.000399),(27939,15669043 59,780902019,0.120000),(27924,1566904359,782681326 ,0.000000),(22794,1566904359,785750009,0.000000),( 39939,1566904359,787384877,2879.482119),(22719,156 6904359,787945244,0.000000),(39759,1566904359,7908 01288,59.016463),(23259,1566904359,791864788,0.000 000),(27699,1566904359,793526437,0.016686),(26319, 1566904359,793805041,96.289951),(26859,1566904359, 795210002,0.003750),(27904,1566904359,797397798,48 9.703779),(31539,1566904359,797561283,6.688154),(3 7719,1566904359,805596561,0.016669);
]

The database is mysql. I read that the solution would be to increase the value of innodb_buffer_pool_size in my.cnf. I would like to confirm this. The current value is:

innodb_buffer_pool_size = 256MB

The VM that supports Zabbix Server has 8GB of RAM, Linux OS with 4G of SWAP. CPU 2 Core.

PS: I haven't activated housekeeper yet because my boss wants us to leave a 10-year track record for some items, and I haven't been confirmed yet if the 3600d value really won't cause any problems.

[Atsushi]

If you still have free memory, try setting innodb_buffer_pool_size to a slightly larger size.

[cesarsj]

If you still have free memory, try setting innodb_buffer_pool_size to a slightly larger size.

I increased it to 12G (total of RAM is 16G) and it really solved the problem. Thank you for your help.

[gofree]

BTW in my opininon youre on the way to hell 10years history, is your DB at least partitioned ? if not your DB file will grow and grow and grow ( ibdata db file ) and youll soon run out of space, memory, zabbix will become slow, frontend unusable, no possibility to maintain DB, upgrades of zabbix will take long - update of DB schema >>>>> everybody will hate it

Some reading about history and trends - usually I go for 1month history and 1year trends. If youd like to have 10years of history maybe consider elasticsearch export.

P.S. I had couple of funny bosses too

[cesarsj]

If youd like to have 10years of history maybe consider elasticsearch export.
P.S. I had couple of funny bosses too

Could you explain to me right what would be ElasticSearch export? I didn't get this part right.

[gofree]

still considered experimental https://www.zabbix.com/documentation...c_search_setup