Not sure if this is a known issue or not.. but I haven't seen it
documented anywhere.
Anyway. My past thinking was that you should always use as many
connections as you have tables (at least with myisam). This way in the
worst case scenario you could have locks open on all tables instead of
one lock on one table holding back all other tables.
This is a BAD idea if you're trying to reach max qps with INSERTs.
What happens is that the disk controller is able to command queue (and
other opterations) to optimize IO on the master since technically you
have multiple INSERTs happening at once (one for each table). Then on
the slave since there's only one thread replaying the DML it will back
up since the disk controller isn't able to optimize the IO.
We were actually running a master with RAID5 and a slave with RAID0 and
the slave still couldn't keep up. The problem was only fixed when we
told our client to only use one connection.
While this is a temporary fix this limits the scalability of MySQL as I
could easily see a LOT more QPS going through these boxes.
It might be possible place an intelligent scheduler to bulk up INSERTS
and use FK relationships to allow non-dependent SQL to pass forward.
You could also have one thread per slave per connection on the master.
Then in the binary log you could flag the thread ID that performed the
modification on the master and use the same thread on the slave.
The downside being that you'd need more resources on SLAVE boxes.
Seems like a wiki page in the making........
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com.
See irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator, Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
