I am dealing with a big WPMU site. More than 10,000 users. It sits on a dedicated linux server runninng ubuntu, with 2 gigs of RAM. MySql 5.0.
The site keeps crashing. It looks like the MySql tables themselves keep locking up. Is this common? I have to ssh to the server and kill mysql and then restart it. Then everything is fine. The spikes in traffic seem to be doing something to MySql?
Have others had this problem? How to handle it?
Oleg Butuzov answers:
same as with other sites... debuging, profiling...
2) make a query log... look on it ...
3) what tables? innoDB? MyIsam ?
how much reads / writes? whats with hdd?
4) what size of db? what file system?...
wish you luck man, i know its hard situation.
Everything in wordpress is MyIsam, right?
Starting logging now, thanks. Will gather data.
What patterns should I look for?
Oleg Butuzov comments:
innodb that a tables by default =). on my projects where most reads that writes i making myisam tables. since foreign key not used it works pretty well and even better that innodb.
patterns? everything that ends with a crash.
Oleg Butuzov comments:
Just info (thats bad that i can't comment your and Jarret tread) . Take also look to the eAccelerator (this is to make php faster not MySQL). also here is good book for making MySQL better "MySQL performance" by guys from Percoona...
So it helps to change InnoDb to myisam tables?
Jarret Minkler answers:
Perhaps applying the caching plugins would give you some lift, and keep queries out of the database aas much as possible. Also, do you know if you have access to APC on your server?
Also see if you can get this written into your /etc/my.cnf file
max_write_lock_count = 1
as explained here http://blog.taragana.com/index.php/archive/one-mysql-configuration-tip-that-can-dramatically-improve-mysql-performance/, since WP in not InnoDB
I can write to /etc/my.cnf. i will try this tip.
Jarret Minkler comments:
Monster Coder answers:
can you kindly provide the link. you may give in private. i want to see what error it is showing. may be tuning mysql needed.
When I look in phpMyAdmin, I get errors, or it does not load, or sometimes I look at web pages in browser and errors on page say "table wp_posts locked" or something about row locks. It is not happening now.
What kind of MySql tuning do you suggest?
Buzu B answers:
The info you provide is really not enough to identify the problem, but you could read on this post:
This can be useful. I would suggest following the tips there even if they aren't the solution to this particular problem. Having a Database is like having a car, you need to maintain it to ensure it will run well. Keep your Databases healthy.
That is good tip. but i want more to know how to avoid crashes, instead of recover from them afterwards