Very slow DELETE, UPDATE, INSERT on a new server
Posted by:
Ash Urge
Date: March 20, 2009 08:32AM
We had forum's DB on Xeon 2,4, 3Gb, FreeBSD 5.4 32-bit, php 4.4.9, Mysql 5.0.51a server.
Basically, MyISAM tables, several InnoDB and one table session in MEMORY. Everything worked properly, but it started becoming cramped.
SELECT commands appeared more frequently in slow-log.
Recently we've moved for more powerful one (2xXeon 3,0, 32Gb, FreeBSD 7.1 64-bit, php 5.2.9, Mysql 5.1.32) and strange bugs began.
It may run pretty fast, but at the moment (without any reason) hangups for 20-60 secs.
At this moment slow-log is full with the same by timestamp commands , basically DELETE, UPDATE or INSERT, mostly addressed to the MEMORY table.
Maybe, cause it refreshs most frequently, or by a kind of some other reason. Sometimes slow-log contains usual Select to the session table, which is usually executed within 0.001 sec.
Setting
concurrent_insert=2
low_priority_updates=1
partially fixes the problem, but not to the very end. Those bugs appear rarely now, but they remain.
Can anybody give a piece of advice of where and what we should fix? Why do those commands make slow on the new server, while never have been noticed in slow-log at the old one?