Hi,
Currently our web infrastructure has one main MySQL server, to which connections are made by (mostly) mod_perl running under Apache
(on 3 different machines), and several other custom-built application servers on other servers (which have persistant connections,
and do both reads and writes).
This machine answers between 300 and 1200 queries per second, and the load average is generally around 3-5 (falling below 1
off-peak). We have max_connections set to 100, and are starting to hit this limit quite frequently. The machine is a dual PIII
1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 array, and it only runs MySQL.
I am intending on implementing a load-balancing solution, whereby we have a pool of around 3 slaves replicating off the main
database server. mod_perl scripts can then make a connection to a random slave, and send their read queries there. Hopefully we can
then scale this by adding more slaves to the pool.
Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around
80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write
queries? We can determine which queries should be run on which connection using Perl.
I have looked at the master-master replication solution so that any machine in the replication pool can have write queries run on
it; unfortunately several of our tables use auto_increment fields, so this isn't an option.
Here are a couple of suggestions.
You can have auto-inc fields unique between databases by creating a primary index from the AutoInc, and Db_Id columns. Each database would have its own Db_Id so you know which database created the row once it is merged back into the main database. This also works with InnoDb tables as long as the auto-inc field appears first in the key. So the key would be '1,A' for rcd_id=1 for database A, and '1,B' for rcc_id=1 on database B etc.
Another alternative would be to split the application into several databases, one that is replicated for reading (DBR1, DBR2, DBR3 etc), a single database for updates (DBU1) and a master database (DBM). The webservers access the replicated databases (DBR1...DBR3), and updates are made to DBU1. Your web application just uses a different connection to access DBU1 instead of DBRn. The updated rows of DBU1 are then fetched and imported into DBM, the master database. This can be a manual process that is done every 5 minutes or so. When the rows are imported into DBM, the rows are removed from DBU1 so DBU1 has only new rows that haven't been imported yet. The changes made to DBM are then replicated back to DBR1 through DBR3.
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
