Nihal, > FILE I/O ... > 223.89 reads/s, 72228 avg bytes/read
> ---------------------- > BUFFER POOL AND MEMORY > ---------------------- ... > Pending reads 131 ... > 1923.08 reads/s, 0.00 creates/s, 0.00 writes/s it is disk-read-bound. It is reading a whopping 1900 pages per second! That is 30 MB/s. Do you have many SELECT COUNT(*) FROM tablename queries? InnoDB scans the whole table to get the row count of a table. If the tables do not change often, and you run the SELECT COUNT(*) in the AUTOCOMMIT=1 mode, then the MySQL query cache will help. " [mysqld] set-variable=query_cache_size=256M set-variable=query_cache_limit=2M set-variable=query_cache_type=1 " In 4.1.1, the query cache works also in the AUTOCOMMIT=0 mode. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: ""Nihal"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Wednesday, November 05, 2003 8:38 PM Subject: switch from gemini to innodb bottlnecks > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
