I've done a rather poor job of explaining this. It's easiest understood when you think about how replication works: the master serializes commands out to a log file, which is then read, command by command, by the slave (on 4.0 servers by an IO thread, on 3.23 servers by a combined IO/SQL thread). The slave then executes each command as it comes in, command by command. If the next command takes a long time, it will be a long time before the following command can be executed. Imagine you have two commands in the queue that take the following times:
1) ALTER <some big table> : 303 secs
2) UPDATE <some table> SET <some important thing> : 1 sec
The ALTER will begin executing on the slave and will not complete for 303 seconds. For this time, no more commands will be read from the slave and executed: it will wait until the ALTER is complete, and then carry on again. This is necessary to maintain consistency (imagine the UPDATE modified the same table that was just ALTER'd).
Anyway, that may explain your problem. Or it may be way off base. :) Hope it helps a little,
Chris
Dan Edwards wrote:
I have a mysql server containing multiple databases, one of the databases is very important that it is not slowed down by other databases. This database is used for a real time game server (card games), and any stalls causes all players to stall, they hate that. I've mostly solved this problem with a master/slave setup. The problem now is the other databases in the same mysql instance are getting larg. When I make changes to them with alter table, it can slow down and even block queries to the other databases for minutes at a time. My question is how do I prevent alter table in one database from blocking queries in another database? What I need is some way to say that any queries to this database (or specific tables) have top priority, always.
Thanks for any assistance! Dan
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
