My primary MySQL server has been VERY overloaded lately (which is the main reason new blades are on the way), but today I decided to see what I can do about it in the meantime (the parameters have already been tuned as much as possible).
First I toyed around with a single node MySQL Cluster… it didn’t work terribly well. I think you really need 2 or 4 nodes for it to be effective.
Then I decided to run two different copies of mysqld on the same machine. Dude, this works *so* well under a high load that it’s almost unbelievable. While the memory fragmentation issues are still there, it’s 20x better (really). I should have done this a long time ago… 🙂
Nice find. Never occured to me as an option. So why would that be then? Because of CPU resources to spare but no RAM?
But from my experience, mysqld doesn’t seem to be 100% threaded properly. For example you could have a query that locks a table in database A, but then everything in database B is also locked because of it (even though they have nothing to do with each other).
So let’s say database A is low traffic (but sometimes has queries that can take a few seconds). Then database B is ultra high traffic (500 queries per second). If database A has the ability to seize database B, within 2 seconds you have 1000+ concurrent connections to mysqld. When the concurrent connections gets really high is when the memory fragmentation gets really bad (even when the connections go down, the fragmentation still exists).
Running two different instances of mysqld, it makes the databases TRUELY isolated from each other, and the concurrent connections never gets too high.
Right. So you can ‘assign’ databases to each mysqld process or something like that? Or does it do that on the fly, checking which process is already doing its thing in which database?
My 24 queries a second avg. and the rest rarely gets my server over 0.6% load luckily!
At 24 queries/second, you don’t have to worry about anything. 🙂 This db server does 1,000+/second 24/7, so… 🙂
It doesn’t auto-configure them… I assigned a unique data directory for each process.
It sure would be nice if mysqld worked more like Apache, where it forks unique processes as needed (automatically).