I have been helping customer today to resolve his lockups in production by downgrading from MySQL 5.1.25 to 5.0 We have other customers (and our own projects as well) which run on MySQL 5.1 successfully but I can’t it is on par with MySQL 5.0 stability yet.
This given customer was running MyISAM on FreeBSD with quite basic queries, so may be there are some issues with FreeBSD and MySQL 5.1 still ?
About the Author
Peter ZaitsevPeter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.
Anything specific?
Depends upon which version of FreeBSD…
FreeBSD < 7 has problems handling mutex contentions. MySQL 5.1 does hit mutexes harder than 5.0.
AFAIK, these issues has been addressed on FreeBSD 7 but I haven’t examined or tried it yet.
This was FreeBSD 6.3 I think
I could blame it on FreeBSD but I seen similar situation – queries locking in various states like “end” “freeing items” “Locked” etc on other systems.
Too bad the MySQL server doesn’t provide the equivalent of SHOW INNODB STATUS so you can get more details on lock holders and waiters.
MyISAM with FreeBSD has been reported have scalability problem to mutex locking, both 6.3 and 7.0.
I have also performance problems with 5.1.25
After some hours I found this optimizer/index problem: http://bugs.mysql.com/bug.php?id=37759
Mark,
Indeed it would be great to get some platform independent diagnostic tool to see where things are stuck.
gslin,
Thanks for report, though MySQL 5.0 and 4.1 work OK for the same workload.
Andreas,
Indeed 5.1.25 in particular adds this “famous” optimizer regression.
I would be very careful with optimizer changes in RC stage – unlike normal “bugs” optimizer changes often make things better in some cases but worse in others and it is hard to predict the impact without wide user testing.
I had severe problems with MySQL 5.1 and FreeBSD back in February / March. This was on FreeBSD7 (Release), using InnoDB tables and a few MyISAM tables. And by severe I mean crashes and lockups every few minutes (!).
After the downgrade to 5.0, everything was running smooth again.
The problem seems to be verified and I hope it would be fixed soon. The leading bug report is http://bugs.mysql.com/bug.php?id=36259
It seems that there are 3 reports at the moment regarding to the same problem.
I have the very same problem on CentOS 5.6 x84_64 on a server with MyISAM tables.
So I guess it’s not because of FreeBSD.
Switching back to 5.0 tonight …