Against my better judgement I went ahead and added a field to a table with 875,000 records, it took over 10 minutes. That pretty much makes our games unplayable during that time. During this time other queries that normally take a few milliseconds took 1-30 seconds. For smooth operation it needs to maintain 40-50 queries per second. Here is a chopped up processlist:
+--------+-------------+--------------+--------+----------------+---------+---------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+--------+-------------+--------------+--------+----------------+---------+---------------------------------------------+------------------------------------------------------------------------------------------------------+
| 11 | lobby | 10.0.0.2 | lobby | Query | 25 | end | UPDATE users SET Host='XXX.XXX.XXX.XXX', HostType=0 WHERE ID=70183 |
| 204098 | lobby | 10.0.0.2 | lobby | Query | 28 | end | update stats SET Score=242, Moons=0, Suns=0, Hands=10, Place=-20 where GameID=2162416 and Seat=0 and |
| 204105 | lobby | 10.0.0.2 | lobby | Query | 24 | end | update euchre_stats SET Score=0, Hands=5, Place=-20 where GameID=561890 and Seat=0 and UserID=267921 |
| 212246 | lobby | 10.0.0.2 | lobby | Query | 28 | end | UPDATE spades_users SET GamesStarted=GamesStarted+1 WHERE ID=144746 |
| 538186 | root | 192.168.0.75 | Track | Query | 115 | copy to tmp table | ALTER TABLE `DLEvents` ADD `QuitPoll` TINYINT DEFAULT "0" |
| 543003 | root | localhost | NULL | Query | 0 | NULL | show processlist
|
| 543800 | web | 10.0.0.2 | ads | Query | 17 | end | update eventcounts set ViewCount=ViewCount+2 where AdID=3 and User=77348 |
| 543801 | web | 10.0.0.2 | ads | Query | 7 | Locked | select count(*) from eventcounts where AdID=9 and User=98936 |
So if you can unwrap that processlist chart you'll see 2 minutes into the alter table there are queries to other db's that are taking upto 28 seconds when normally they're almost instant.
Here is a bit of top:
3:52pm up 17 days, 16:00, 2 users, load average: 5.31, 2.61, 1.11
71 processes: 70 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 0.7% user, 6.6% system, 0.0% nice, 92.6% idle
Mem: 1031036K av, 1021452K used, 9584K free, 0K shrd, 15592K buff
Swap: 522104K av, 13472K used, 508632K free 695568K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 4992 root 16 0 1096 1096 852 R 4.9 0.1 0:27 top 750 mysql 15 0 267M 266M 12592 S 2.0 26.4 1221m mysqld
The machine is a Athon XP 1700 with 1 gig ram, and a 40gig ide drive.
I'm planning on upgrading to a scsi drive soon. I'm sure that will help, but still don't think it is reasonable that just because a 10 minute alter table is going on that a 10 ms query turns into 30+ seconds.
Is there any solutions other then more hardware?
Thanks! Dan
Jeremy Zawodny wrote:
On Mon, Jun 09, 2003 at 01:05:43PM -0700, 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.
Hmm.
Stalls are expected if the system gets CPU or disk I/O bottlenecked during the ALTER TABLE operation.
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.
It shouldn't block entirely.
Can you snapshot the output for SHOW PROCESSLIST when this happens? Or maybe watch interactively with mytop?
Also, which MySQL and operating system are you using?
Jeremy
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
