What the hell??!? (blah, blah of a wannabe alien)
Multiple Instances Of mysqld
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… 
| Print article | This entry was posted by Shawn on May 15, 2006 at 11:26 am, and is filed under Server Admin. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site. |
about 5 years 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?
about 5 years ago
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.
about 5 years ago
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!
about 5 years ago
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).