performance problems, maintence on one table slows down others

Mon, 17 Nov 2003 20:06:54 -0800

Hi, I have a performance issue I've tried resolving and I can't get rid of it. Basically I have a database called lobby that any queries to it must be as fast as possible inserts, and selects. It must do about 60 queries a second with no queries taking more then 50ms. I also have another database called Track that I need to do some maintance on some of its tables. Right now I need to archive off records to seperate tables by month. I wrote a C program to do it. The problem is when I run it, it slows down the queries to the lobby database so severly that queries that normaly take less then 10 ms, take a second or more, which is absolutely unacceptable. (The tables are the backend of online card games, and database stalls cause lag for players)

To solve this problem I bought a fast server with a fast scsi mirror raid dedicated to the lobby database. The server is a Redhat 9, kernel 2.4.20-20.9smp, mysql 4.0.16, dual p4 2.4 xeon (Dell 1600SC), with 1 gig ram, 4 36 gig 15k rpm scsi HDs. 2 mirrored for the os and most databases and 2 mirrored dedicated to the lobby database.

The program that archives tables basically does this:
insert into Events_Sep select * from Events where EventID>=1000 and EventID<1100;
delete from Events where EventID>=1000 and EventID<1100;
And then pauses 1 second.

The Events table has over 2 million records in it, and is 2.3GB in Events.MYD and 170M in MYI.


I thought the original problem was the lobby and track databases where on the same hard drive. But this new hardware hasn't solved the problem. It is better, but still far from good enough.

Here is a bit of top while the archiveing is running:
 18:39:40  up 11 days,  8:20,  2 users,  load average: 0.11, 0.12, 0.14
77 processes: 76 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 5.2% user 2.1% system  0.0% nice   0.0% iowait  92.2% idle
CPU1 states: 4.1% user 4.4% system  0.0% nice   0.0% iowait  91.0% idle
CPU2 states: 0.0% user 0.1% system  0.0% nice   0.0% iowait  99.4% idle
CPU3 states: 0.2% user 0.1% system  0.0% nice   0.0% iowait  99.2% idle
Mem:  1159456k av, 1149228k used, 10228k free,  0k shrd,   60804k buff
                    795764k actv,    6452k in_d,   47432k in_c
Swap: 1052152k av,  308140k used,  744012k free           694920k cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
 7098 mysql     15   0  399M 304M  1348 S    16.6 26.9 647:22   2 mysqld

Some dumps from iostat -k 2 with archive running, prety much alternates between these two:
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
dev8-0 414.50 120.00 2408.00 240 4816
dev8-1 427.50 60.00 2408.00 120 4816
dev8-2 139.50 24.00 604.00 48 1208
dev8-3 135.50 0.00 604.00 0 1208

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dev8-0            5.00        18.00        34.00         36         68
dev8-1           13.50        56.00        34.00        112         68
dev8-2            2.00         6.00        12.00         12         24
dev8-3            1.50         2.00        12.00          4         24


Some typical iostat dumps without the archive running:

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dev8-0            5.00         0.00        40.00          0         80
dev8-1            5.50         2.00        40.00          4         80
dev8-2          118.50         2.00       522.00          4       1044
dev8-3          119.00         4.00       522.00          8       1044

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
dev8-0            4.00         2.00        18.00          4         36
dev8-1            3.50         0.00        18.00          0         36
dev8-2            1.00         4.00         0.00          8          0
dev8-3            0.50         4.00         0.00          8          0


The OS/Track tables are dev8-0 and dev8-1, The lobby tables are on dev8-2 and dev8-3.

Here is important bits of my.cnf:
[mysqld]
server-id=4
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin
skip-locking
log-slow-queries
set-variable    = key_buffer=384M
set-variable    = max_allowed_packet=16M
set-variable    = table_cache=512
set-variable    = sort_buffer=2M
set-variable    = record_buffer=2M
set-variable    = myisam_sort_buffer_size=64M
set-variable    = thread_cache=8
set-variable    = thread_concurrency=8
set-variable    = tmp_table_size=256M



One theory I have is that since the os/track harddrive is slowed down so much that writes to the bin log are delayed enough to cause delays in queries to lobby tables.

Anyone else have any other theories or suggestions of what I should try next. I would really like to avoid putting the databases on totally seperate servers.

Thanks for any insight, I know mysql has the power to pull this off, we just need to figure out how to tap into it!
Dan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to