Getting error "Deadlock found when trying to get lock" while inserting trigger

[kanchan]

Dear All,

I am using Zabbix 2.0.9 with Mysql DB (Version - 5.6.19).
I am trying to add new triggers in existing linux template which has too many hosts tagged and getting below error :

Error in query [INSERT INTO functions (itemid,triggerid,function,parameter,functionid) VALUES ('200200000743538','200200008794039','avg','120',' 200200009111515')] [Deadlock found when trying to get lock; try restarting transaction]
Cannot implode expression "{<HOSTNAME>:vfs.dev.read[,ops].avg(120)}>3000". SQL statement execution has failed "INSERT INTO functions (itemid,triggerid,function,parameter,functionid) VALUES ('200200000743538','200200008794039','avg','120',' 200200009111515')".


Now days we are facing this problem frequently. In past when we used to try to insert this task couple of time then it was able to execute query but now it is continuously throwing deadlock error. Please suggest how to fix this issue as it will be very difficult for us to manage if it wont work.

[batchenr]

Dear All,

I am using Zabbix 2.0.9 with Mysql DB (Version - 5.6.19).
I am trying to add new triggers in existing linux template which has too many hosts tagged and getting below error :

Error in query [INSERT INTO functions (itemid,triggerid,function,parameter,functionid) VALUES ('200200000743538','200200008794039','avg','120',' 200200009111515')] [Deadlock found when trying to get lock; try restarting transaction]
Cannot implode expression "{<HOSTNAME>:vfs.dev.read[,ops].avg(120)}>3000". SQL statement execution has failed "INSERT INTO functions (itemid,triggerid,function,parameter,functionid) VALUES ('200200000743538','200200008794039','avg','120',' 200200009111515')".


Now days we are facing this problem frequently. In past when we used to try to insert this task couple of time then it was able to execute query but now it is continuously throwing deadlock error. Please suggest how to fix this issue as it will be very difficult for us to manage if it wont work.

please post my.cnf + new trigger configuration

[kanchan]

Thanks for your reply.

Please find my mysql configuration.

[root@zabbix ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections=600

skip-name-resolve
read_buffer_size=1M
max_allowed_packet=32M
join_buffer_size=1M
myisam_sort_buffer_size=64M
tmp_table_size=16M
read_rnd_buffer_size=1M

local-infile=0
symbolic-links=0

innodb_checksum_algorithm=INNODB
binlog_checksum=NONE
skip_secure_auth
table_open_cache=6000
innodb_open_files=1500
table_definition_cache=6000

slow_query_log=0
slow_query_log_file=/var/log/mysqld.slow.log
long_query_time=1

#For open db and slow query
general_log=0
log_output=TABLE

#### To disable the replication of mysql.general log from master #############
replicate-ignore-table=mysql.general_log
replicate-wild-ignore-table=mysql.log%

#### To disable the replication of slow log from master #############
replicate-ignore-table=mysql.slow_log

slave-compressed-protocol=1


# To resolve inderterministic behavior of sysdate() (makes it binlog safe)
sysdate-is-now

# ## # To suppress the mysql error log from warnings
log-warnings=0


tmpdir=/var/lib/mysql

innodb_buffer_pool_size=72G
innodb_buffer_pool_instances=8
innodb_log_file_size=512M
innodb_log_buffer_size=16M
thread_cache=64
innodb_file_per_table
log_bin_trust_function_creators = 1
performance_schema=OFF
innodb_purge_threads=1
innodb_thread_concurrency=64

core-file

# Mysql suggested Parameter On 10-Mar-10
innodb_stats_on_metadata=0

# The default storage engine to be used when new tables are created
default_storage_engine=InnoDB

server-id = 53

# changes made to make this master
log-bin=/usr/binlogs/mysql-bin
slave-compressed-protocol=1
binlog-format=statement


#changes according to Mysql Support suggested
max_heap_table_size=32M
sort_buffer_size=1M
key_buffer=64M
query_cache_size=48M
query_cache_type=1
innodb_flush_log_at_trx_commit=2
group_concat_max_len = 16000

[mysql.server]
user=mysql

[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid

#########################################

Please find trigger expression which need to be added :

{Template_Linux:vfs.dev.read[,ops].avg(120)}>3000
{Template_Linux:vfs.dev.write[,ops].avg(120)}>3000


Please let me know if you need any more details.

[batchenr]

Thanks for your reply.

Please find my mysql configuration.

[root@zabbix ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections=600

skip-name-resolve
read_buffer_size=1M
max_allowed_packet=32M
join_buffer_size=1M
myisam_sort_buffer_size=64M
tmp_table_size=16M
read_rnd_buffer_size=1M

local-infile=0
symbolic-links=0

innodb_checksum_algorithm=INNODB
binlog_checksum=NONE
skip_secure_auth
table_open_cache=6000
innodb_open_files=1500
table_definition_cache=6000

slow_query_log=0
slow_query_log_file=/var/log/mysqld.slow.log
long_query_time=1

#For open db and slow query
general_log=0
log_output=TABLE

#### To disable the replication of mysql.general log from master #############
replicate-ignore-table=mysql.general_log
replicate-wild-ignore-table=mysql.log%

#### To disable the replication of slow log from master #############
replicate-ignore-table=mysql.slow_log

slave-compressed-protocol=1


# To resolve inderterministic behavior of sysdate() (makes it binlog safe)
sysdate-is-now

# ## # To suppress the mysql error log from warnings
log-warnings=0


tmpdir=/var/lib/mysql

innodb_buffer_pool_size=72G
innodb_buffer_pool_instances=8
innodb_log_file_size=512M
innodb_log_buffer_size=16M
thread_cache=64
innodb_file_per_table
log_bin_trust_function_creators = 1
performance_schema=OFF
innodb_purge_threads=1
innodb_thread_concurrency=64

core-file

# Mysql suggested Parameter On 10-Mar-10
innodb_stats_on_metadata=0

# The default storage engine to be used when new tables are created
default_storage_engine=InnoDB

server-id = 53

# changes made to make this master
log-bin=/usr/binlogs/mysql-bin
slave-compressed-protocol=1
binlog-format=statement


#changes according to Mysql Support suggested
max_heap_table_size=32M
sort_buffer_size=1M
key_buffer=64M
query_cache_size=48M
query_cache_type=1
innodb_flush_log_at_trx_commit=2
group_concat_max_len = 16000

[mysql.server]
user=mysql

[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid

#########################################

Please find trigger expression which need to be added :

{Template_Linux:vfs.dev.read[,ops].avg(120)}>3000
{Template_Linux:vfs.dev.write[,ops].avg(120)}>3000


Please let me know if you need any more details.

ok so,
1.is the zabbix db is myisam or innodb?
2.is the trigger you send is one trigger ? it can explain the issue which your asking to read and write at the same time and it gets locked.

(again im no expert it is what i think.)

this is my setting after workd hard to make mysql work faster
see if you can benafit from it :

datadir=/var/lib/mysql_orig/mysql
socket=/var/lib/mysql_orig/mysql.sock
user=mysql
innodb_file_per_table=1
key_buffer_size=1G
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=9G
innodb_flush_log_at_trx_commit=0
query_cache_size=32M
query_cache_type=0
log_slow_queries = 1
skip-name-resolve
open_files_limit=6740
join_buffer_size=128K
innodb_max_dirty_pages_pct=90
innodb_flush_log_at_trx_commit=0
innodb_support_xa = false
table_cache = 80
thread_cache_size = 4
table_open_cache = 90
innodb_log_buffer_size= 1M


***dont use innodb_file_per_table if you didnt make all the necessary changes for it*** http://dba.stackexchange.com/questio...bdata-in-mysql

[kanchan]

We are using innodb and those are 2 separate triggers which I am trying to add.
Thanks for sharing your mysql configuration. I will go through it.