On Mon, Jun 09, 2003 at 04:39:22PM -0700, Dan Edwards wrote: > > 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:
I'm not sure why you "chopped" it up. It's much harder to read this way. :-( > > 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 Ah, MySQL is mostly idle. This is probably horribly disk-bound. Can you grab the output of 'vmstat' or iostat or something that'll measure disk I/O next time this happens? > The machine is a Athon XP 1700 with 1 gig ram, and a 40gig ide drive. Ah, yes. A single disk. Database servers is I/O intensive creatures. > 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. What'd help more is adding more disks not a single faster one--unless the SCSI disk is an order of magnitude faster. > Is there any solutions other then more hardware? Not off the top of my head. You could do the ALTER TABLE on another machine (a slave) and the copy it to the master. But that has other problems. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 199,812,150 queries (345/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
