I've recently switched from GEMINI to INNODB, to be able to get continued support and upgrades of MySQL.
I'm running into to some major performance issues. The section in the MySQL manual is not very thorough on optimizing INNODB. I'm getting two problems, either all my queries pile up, or a 'show processlist' shows 50 sleeping processes that should be hard at work, either way everything comes to a crawl. The database server is a dedicated 2CPU pIII 2.6Ghz, 2GB RAM, with a 14 hardrive (10k) Raid5 array. Pretty much every query uses indexed fields, so things should be fast, I get the impression that there is a locking issue going on, but as I understood it, INNDOB is supposed to do row level locking? I haven't had this problem with GEMINI in the past. Can anyone give me some improvement suggestions? ===================================== 031105 10:51:32 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 2 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3528797, signal count 2864755 --Thread 1105938 has waited at btr0cur.c line 390 for 1.00 seconds the semaphore: S-lock on RW-latch at 92b43f74 created in file buf0buf.c line 444 a writer (thread id 1105938) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1134643 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92fa9b84 created in file buf0buf.c line 444 a writer (thread id 167960) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1069100 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92b3c734 created in file buf0buf.c line 444 a writer (thread id 1294399) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1294399 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 92b3c734 created in file buf0buf.c line 444 a writer (thread id 1294399) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 390 Last time write locked in file buf0buf.c line 1404 --Thread 1282108 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: S-lock on RW-latch at 922d78f4 created in file buf0buf.c line 444 a writer (thread id 1282108) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0sea.c line 753 Last time write locked in file buf0buf.c line 1404 Mutex spin waits 96547444, rounds 309622046, OS waits 1589531 RW-shared spins 3471561, OS waits 1434935; RW-excl spins 1022533, OS waits 145621 ------------ TRANSACTIONS ------------ *** LOTS OF STUFF HERE *** -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: doing file i/o (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 192, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 3135978 OS file reads, 80521 OS file writes, 41212 OS fsyncs 4 pending preads, 0 pending pwrites 223.89 reads/s, 72228 avg bytes/read, 1.00 writes/s, 1.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 31, free list len 29, seg size 61, 110608 inserts, 100175 merged recs, 14569 merges Hash table size 4980539, used cells 665918, node heap has 702 buffer(s) 7813.59 hash searches/s, 3283.86 non-hash searches/s --- LOG --- Log sequence number 18 429778985 Log flushed up to 18 429778985 Last checkpoint at 18 429608118 0 pending log writes, 0 pending chkp writes 22237 log i/o's done, 2.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 1470181283; in additional pool allocated 9935488 Buffer pool size 76800 Free buffers 0 Database pages 76084 Modified db pages 168 Pending reads 131 Pending writes: LRU 0, flush list 0, single page 0 Pages read 16098598, created 12247, written 85799 1923.08 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 977 / 1000 -------------- ROW OPERATIONS -------------- 8 queries inside InnoDB, 48 queries in queue Main thread process no. 1225, id 28680, state: sleeping Number of rows inserted 292454, updated 132038, deleted 139429, read 359279518 0.50 inserts/s, 0.50 updates/s, 1.00 deletes/s, 12631.68 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ============================ my.cnf # Uncomment the following if you are using Innobase tables innodb_data_home_dir = /raid/data2/ innodb_log_group_home_dir = /raid/data2/logs/ innodb_log_arch_dir = /raid/data2/logs/ innodb_data_file_path = ibdata01:4000M;ibdata02:4000M;ibdata03:4000M;ibdata04:4000M;ibdata05:400 0M;ibdata06:4000M;ibdata07:4000M;ibdata08:4000M;ibdata09:4000M;ibdata10: 4000M;ibdata11:4000M;ibdata12:4000M;ibdata13:4000M;ibdata14:4000M;ibdata 15:4000M;ibdata16:4000M;ibdata17:4000M;ibdata18:4000M;ibdata19:4000M;ibd ata20:4000M;ibdata21:4000M;ibdata22:4000M;ibdata23:4000M;ibdata24:4000M; ibdata25:4000M;ibdata26:4000M;ibdata27:4000M;ibdata28:4000M;ibdata29:400 0M #set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=4 set-variable = innodb_log_file_size=64M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 #innodb_log_archive=0 set-variable = innodb_buffer_pool_size=1200M set-variable = innodb_additional_mem_pool_size=100M set-variable = innodb_file_io_threads=4 ============================ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
