Deletes in one schema slowing down reads in others

[Sean Maurik]

Hi everyone. We've got a test box running mysql (4.1.14 on mandrake 10 with 2 gigs of ram) and we've noticed that if one of the test schemas (there's a few duplicate schemas used by the dev team on the one DB) is doing a bunch of search and deletes the entire DB slows to a crawl. I think it must be memory related but shrinking the buffer/cache sizes doesn't seem to make a difference, nor does removing indexes (in an attempt to speed the deletes). The tables being "purged" have ~1/2 million rows and the system is picking various rows from 4 of these tables for deleted (in a sense just randomly selecting from table where id = x then deleting that row I guess best describes what its doing). The other tests are doing mixes of searches and inserts into much smaller tables (100 rows type stuff). These small tests fly along until the "purge" test hits and then they just slow to a crawl. here's the TOP from the DB when the purge is running:

top - 13:08:35 up 37 days, 20:56, 2 users, load average: 1.88, 0.70, 0.65
Tasks: 203 total, 2 running, 198 sleeping, 0 stopped, 3 zombie
Cpu(s): 3.2% us, 2.8% sy, 0.2% ni, 44.1% id, 49.3% wa, 0.3% hi, 0.2% si
Mem: 2074808k total, 2028076k used, 46732k free, 37088k buffers
Swap: 1124508k total, 1860k used, 1122648k free, 1611008k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
31286 mysql 17 0 175m 38m 5360 R 3.0 1.9 0:02.89 mysqld
31289 mysql 16 0 175m 38m 5360 S 3.0 1.9 0:02.13 mysqld
31441 mysql 15 0 175m 38m 5360 S 2.3 1.9 0:00.60 mysqld
31504 mysql 15 0 175m 38m 5360 S 1.0 1.9 0:00.32 mysqld
31502 mysql 15 0 175m 38m 5360 S 0.7 1.9 0:00.34 mysqld
31505 mysql 15 0 175m 38m 5360 S 0.7 1.9 0:00.27 mysqld

There's 50 megs of ram free plus lots of swap and the CPU numbers aren't crazy (they pop to 15% sometimes for a split sec). And here's the bits from my cnf file (all tables are InnoDB):

[mysqld]
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2
innodb_data_home_dir = /home/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/mysql/
innodb_log_arch_dir = /home/mysql/
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M


Any ideas? None of the individual queries seem to particularly slow but its weird how the work on one schema is affecting the other schemas (which is I way I figure its a global setting issue of some sort).

Thanks in advance
Sean