[wp-hackers] Mysql crash

[Jeremy Clarke]

MySQL configuration is not a simple matter of having X hardware or Y
plugins on your site. Each server needs a different configuration and
new servers often aren't configured to use all their resources.
Obviously your 16GB of ram is more than enough for anything, but MySQL
can easily be mis-configured to only ever use a subset of that ram
(think about what kind of default config you'd include with debian if
you were releasing it, you wouldn't want to assume 16GB of ram).

I deeply recommend this shell script for tuning your server
https://launchpad.net/mysql-tuning-primer

You run it from the command line and it will look through your MySQL
configuration and tell you what could probably use a change. It is
fairly easy to use and seems to know about most situations that can be
potentially fatal to your server (i.e. "your X value combined with
your Y value means that if enough traffic comes in your server will
crash, lower one or the other"). By carefully following its advice
over time I managed to stop my DB server from crashing. (over time is
important, as mysql needs to run for awhile with the modified settings
so that the script can use the new statistics generated by mysql to
re-diagnose new advice).

WRT category plugins you will find that any fancy category stuff is
very hard on the database. Plugins might make it easy to set up
category exclusion in various ways, but they probably won't make it
easier on the server, and queries with category__and, the standard
WP_Query way, will cause a lot of strain on your server. I recommend
re-assessing the need for this category exclusion especially if you
are doing really fancy stuff. If you can do less category
manipulations on each pageload it will be better for the server
performance and stability.

-- 
Jeremy Clarke | http://jeremyclarke.org
Code and Design | http://globalvoicesonline.org