Hi!
While ago we perform in our company short experiment in the framework of
mysql tests.
One of our databases is mysql-4.0.15 on i386 Linux. It is MyISAM-based.
Traffic on it consists of mix of selects (quite big share of count
aggregates) and dml operations with quite high share of the latter in
the whole. We haven't had any bigger problems with this database. We
tried to replace it with mysql-4.0.26 on x86_64 Solaris 10 on better
hardware.
So as You see - version upgrade, architeture and os change, also
different hardware configuration.
We didn't take any export-import approach - and just move all data/index
files to the machine with new enviroment.
As we moved, firstly the database server responded quite well. However
after few minutes operations on some more intensively used tables slowed
down to unacceptable level (from milisec. to hundreds of sec.). SHOW
PROCESSLIST indicated, that those operations were "Locked".
As a first step we tried to tune server (os and db server). We have
changed query cache settings, key buffer settings and some specific
Solaris parameters with no effect. During this time neither memory nor
processor resources were exhausted.
Then we switched from x86_64 to i386 version of mysql. This didn't
solve the problem, too.
And then we went back to i386 4.0.15 version, though for Solaris 2.8
(and not Linux).
Firstly things seemed to be as usually, but after few minutes SHOW
PROCESSLIST output changed and there were no more problems. DML
operations didn't lock, counts didn't lock and explain operations didn't
lock (all, what was the case in 4.0.26)
Concluding - it seems that the main change driver was mysql version.
I've googled few hours, but haven't found anything relevant (may be
beside information, that MyISAM at high traffic rate has problems with
DML, but we actually didn't switch to InnoDB, so this is not the case).
Any comments on this issue?
Thanks in advance
Remigiusz
