this is more of a best practices question than a techical one but when/why has anyone run multiple mysql instances on the same server in production?
I have a ring replicated pair of mysql servers with ~12 logical databases, each associated with a different application/set of functionality on our site. I am getting ready to do an upgrade of those boxes and am debating whether it might make sense to run multiple instances (though obviously not 12). the reason for doing so being that one of the twelve applications does ~70% of the DML, another is politically high-profile but prone to "anti-social" DB behavior (i.e. currently if it breaks the ring it does so for everything) and the others are basically along for the ride. the benefits I would expect from multiple instances are: 1. one application doing something stupid can't break replication for everything. 2. effectively multithreads replication 3. can restart mysql for my.cnf changes w/o restarting all 12 applications the drawbacks I would expect are: 1. complicates install, configuration, management & troubleshooting 2. inefficiencies with duplicated shared memory resources 3. inefficiencies if application needs to access multiple databases what are the indicators/contraindicators for multiple instances? what "gotchas" has anyone come across doing this in production? what am I not asking that I don't know I need to ask (& what are the answers?)? I've done this w/Oracle for years (still do) but this would be my 1st time w/mysql... any advice (particularl the "speaking from experience" kind) is greatly appreciated!
