Linux 3.10.0-1062.4.3.el7.x86_64 #1 SMP Wed Nov 13 23:58:53 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
CentOS Linux release 7.7.1908 (Core)
mysql Ver 8.0.17 for Linux on x86_64 (Source distribution)

Thank you for using the latest stable version.

Can you explain why you manually run 'DELETE from ..' ?

Usually, it's a task for a housekeeper to clean the old data a little later according to the settings mentioned:

grep "HousekeepingFrequency=\|MaxHousekeeperDelete=" /etc/zabbix/zabbix_server.conf

I warmly suggest to take a look on Monitoring -> Graphs -> Host group: Zabbix server -> Host: Zabbix server.

There are 5 graphs (starts with name "Zabbix ..") which indicates the internal health of the core server.

Result for my houskeeping - default

$ grep "HousekeepingFrequency=|MaxHousekeeperDelete=" /usr/local/etc/zabbix_server.conf

1. With HousekeepingFrequency=0 the housekeeper can be only executed using the runtime control option.
2. HousekeepingFrequency=1
3. MaxHousekeeperDelete=5000

The health for my zabbix is fine for all processes and queues. I dont run mannualy this query.

I have problem with button "clean history and trends" - it call query immediately from site. For example I want clear history by button for 3 elements, ids: 35187,35188,52548. When I click button from front zabbix - start query:

DELETE FROM history_uint WHERE itemid IN (35187,35188,52548)

This query locked all query by zabbix_server (from zabbix server_log):

17220:20200213:140645.789 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [insert into history_log (itemid,clock,ns,timestamp,source,severity,value,logeventid) values$
17218:20200213:140715.829 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [insert into history_uint (itemid,clock,ns,value) values (130831,1581595580,277996631,160),($

Table history_uint has no key for itemid:

history_uint | CREATE TABLE `history_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` bigint(20) unsigned NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_uint_1` (`itemid`,`clock`)
)

This query for me - around 7 minutes. So now I must hide this button from other administrators - because system crashed on click.

I attach some screen.

Thanks!

I have already added info for problem

Hello,

This can be a problem while using MyISAM type instead of InnoDB. I see you already mentioned you got InnoDB in the description.

Please also ensure it's active at table level. 

show create table history_uint\G

It should show:

CREATE TABLE `history_uint` (
`itemid` bigint unsigned NOT NULL,
`clock` int NOT NULL DEFAULT '0',
`value` bigint unsigned NOT NULL DEFAULT '0',
`ns` int NOT NULL DEFAULT '0',
KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Hello! Yes, I'm use InnoDB.

mysql Ver 8.0.17 for Linux on x86_64 (Source distribution)

Table: history_uint
Create Table: CREATE TABLE `history_uint` (
 `itemid` bigint(20) unsigned NOT NULL,
 `clock` int(11) NOT NULL DEFAULT '0',
 `value` bigint(20) unsigned NOT NULL DEFAULT '0',
 `ns` int(11) NOT NULL DEFAULT '0',
 KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Hi pixart

Sorry for the long delay.

This seems like a performance issue with MySQL database, have you done any performance tuning to it? The button is supposed to clear all entries when clicked and if there is a lot of data to clear then it will take longer to delete it all.

Regards,
Kristiāns