Lock wait timeout exceeded

[mjski]

We are noticing the following error (lock wait timeout) occuring frequently, where the frequency is directly related to how many host monitors are 'Activated' on the zabbix web interface; The less hosts that are activated the less frequent this err occurs..

Has anyone seen this before?

NOTE: We have the TransactionDeadlockDetectionTimeout in mysql config set to 12000 at the moment:

11194:20081211:102722 Query::select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.history,i.lastvalue,i.prevvalue,i.ho stid,h.status,i.value_type,h.errors_from,i.snmp_po rt,i.delta,i.prevorgvalue,i.lastclock,i.units,i.mu ltiplier,i.snmpv3_securityname,i.snmpv3_securityle vel,i.snmpv3_authpassphrase,i.snmpv3_privpassphras e,i.formula,h.available,i.status,i.trapper_hosts,i .logtimefmt,i.valuemapid,i.delay_flex,h.dns,i.para ms,i.trends,h.useipmi,h.ipmi_port,h.ipmi_authtype, h.ipmi_privilege,h.ipmi_username,h.ipmi_password,i .ipmi_sensor from hosts h, items i where i.nextcheck<=1229012845 and i.status in (0,3) and i.type not in (2,7,9,12) and h.status=0 and h.disable_until<=1229012840 and h.errors_from=0 and h.hostid=i.hostid and (h.proxy_hostid=0 or i.type in (5)) and mod(i.itemid,5)=2 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid between 100000000000000 and 199999999999999 order by i.nextcheck
11193:20081211:102722 Query failed:Lock wait timeout exceeded; try restarting transaction [1205]
11194:20081211:102722 Query failed:Lock wait timeout exceeded; try restarting transaction [1205]
11196:20081211:102722 Query::select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.history,i.lastvalue,i.prevvalue,i.ho stid,h.status,i.value_type,h.errors_from,i.snmp_po rt,i.delta,i.prevorgvalue,i.lastclock,i.units,i.mu ltiplier,i.snmpv3_securityname,i.snmpv3_securityle vel,i.snmpv3_authpassphrase,i.snmpv3_privpassphras e,i.formula,h.available,i.status,i.trapper_hosts,i .logtimefmt,i.valuemapid,i.delay_flex,h.dns,i.para ms,i.trends,h.useipmi,h.ipmi_port,h.ipmi_authtype, h.ipmi_privilege,h.ipmi_username,h.ipmi_password,i .ipmi_sensor from hosts h, items i where i.nextcheck<=1229012845 and i.status in (0,3) and i.type not in (2,7,9,12) and h.status=0 and h.disable_until<=1229012840 and h.errors_from=0 and h.hostid=i.hostid and (h.proxy_hostid=0 or i.type in (5)) and mod(i.itemid,5)=4 and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid between 100000000000000 and 199999999999999 order by i.nextcheck
11196:20081211:102722 Query failed:Lock wait timeout exceeded; try restarting transaction [1205]
11210:20081211:102722 Query::select distinct i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snmp_oid,h .useip,h.ip,i.history,i.lastvalue,i.prevvalue,i.ho stid,h.status,i.value_type,h.errors_from,i.snmp_po rt,i.delta,i.prevorgvalue,i.lastclock,i.units,i.mu ltiplier,i.snmpv3_securityname,i.snmpv3_securityle vel,i.snmpv3_authpassphrase,i.snmpv3_privpassphras e,i.formula,h.available,i.status,i.trapper_hosts,i .logtimefmt,i.valuemapid,i.delay_flex,h.dns,i.para ms,i.trends,h.useipmi,h.ipmi_port,h.ipmi_authtype, h.ipmi_privilege,h.ipmi_username,h.ipmi_password,i .ipmi_sensor from hosts h, items i, functions f where h.hostid=i.hostid and h.status=0 and i.status=0 and f.function in ('nodata','date','dayofweek','time','now') and i.itemid=f.itemid and h.hostid between 100000000000000 and 199999999999999
11210:20081211:102722 Query failed:Lock wait timeout exceeded; try restarting transaction [1205]

[MrKen]

What a coincidence, I've just recently suffered from the same Error message.

The tables lock, data is gathered haphazardly and graphs have missing bits.
I found quite a number of threads here on the forum that helped. Basically it has something to do with Buffers and your my.cnf

The cure is to download a script called mysqltuner from here. The script will read through your sql config, hardware setup, etc, and then output suggested changes to your my.cnf

Example Output
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
key_buffer_size (> 6.3G)
query_cache_size (> 8M)
join_buffer_size (> 8.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 64)
innodb_buffer_pool_size (>= 11G)


Try it. It helped me!

MrKen

[cesarsj]

How reliable or mysqltuner would it be? I tested this script on a server clone and it reported to me:

General recommendations:
Remove Anonymous User accounts - there are 2 anonymous accounts.
DELETE FROM mysql.user WHERE user ='';
Set up a Secure Password for localhost user: SET PASSWORD FOR ''@'SpecificDNSorIp' = PASSWORD('secure_password');
Set up a Secure Password for zabbix user: SET PASSWORD FOR ''@'SpecificDNSorIp' = PASSWORD('secure_password');
Set up a Secure Password for [email protected] user: SET PASSWORD FOR 'root'@'SpecificDNSorIp' = PASSWORD('secure_password');
Set up a Secure Password for root@::1 user: SET PASSWORD FOR 'root'@'SpecificDNSorIp' = PASSWORD('secure_password');
Set up a Secure Password for rootzabbix user: SET PASSWORD FOR 'root'@'SpecificDNSorIp' = PASSWORD('secure_password');
Set up a Secure Password for localhost user: SET PASSWORD FOR ''@'SpecificDNSorIp' = PASSWORD('secure_password');
Set up a Secure Password for zabbix user: SET PASSWORD FOR ''@'SpecificDNSorIp' = PASSWORD('secure_password');
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce or eliminate unclosed connections and network issues
Set thread_cache_size to 4 as a starting value
Performance schema shouldn't be activated for MariaDB 10.0 for performance issue
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
thread_cache_size (start at 4)
performance_schema = OFF disable PFS
innodb_buffer_pool_size (>= 94.2G) if possible.
innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=12)