inserts locking whole tables?

[rlrevell]

Hello,

We are experiencing extremely slow performance with our Zabbix 1.6.1 instance, the dashboard takes 10-20 seconds to load. The database server is completely CPU bound, and doing 40-80K context switches per second.

Looking at "show innodb status" shows that every insert, delete, or update locks the whole table and that many many threads are sleeping in the InnoDB queue. My reading of the MySQL docs is that tables will only be locked if an explicit LOCK TABLE is issued, but I see no such thing in the code, and, inserts continue to lock the table even if I revoke LOCK TABLES from the Zabbix user.

I'm sure I am missing something obvious, but can't figure out what it is.

Thanks,

Lee

[richlv]

before doing anything else, i'd strongly suggest upgrading to 1.6.9.
1.6.1 is not only old, it's also one of the first releases after a major version

[bashman]

I’ve seen MySQL DB opens a lot of threads which seem not be closed. I've defined “max_connections” variable to 400 connections, and Zabbix opens around 300 threads.

Is this behavior normal?, I'm using MySQL 5.0.75 with MyISAM Engine and all tables in zabbix and zabbix proxy schemas use InnoDB tables.

Should I change the engine to InnoDB?

Thanks in advance.
Regards,

bashman

[MrKen]

Hello,

We are experiencing extremely slow performance with our Zabbix 1.6.1 instance, the dashboard takes 10-20 seconds to load. The database server is completely CPU bound, and doing 40-80K context switches per second.

Looking at "show innodb status" shows that every insert, delete, or update locks the whole table and that many many threads are sleeping in the InnoDB queue. My reading of the MySQL docs is that tables will only be locked if an explicit LOCK TABLE is issued, but I see no such thing in the code, and, inserts continue to lock the table even if I revoke LOCK TABLES from the Zabbix user.

I'm sure I am missing something obvious, but can't figure out what it is.

Thanks,

Lee

What do you get for the following in mysql:

mysql>
mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

My understanding is that Myisam locks tables, but Innodb only locks rows.

MrKen