I converted 3 of 30 MyISAM tables to InnoDB. Since then I have been constantly reaching my limit of 100 connections because Selects on the InnoDB tables are slow.
If I cut off traffic and execute a SELECT, it takes less than 0.01 seconds. If a execute the exact same query again with traffic, it takes 18+ seconds. More than 1 query appears to do this (maybe all). I have a Dual 2ghz Xeon dedicated server with (2) 73GB 10k RPM SCSI hard drives. It has 1GB of RAM. (Only 1 hard drive is used and and CPU usage is about 50-60%) The server is not dedicated to just databases, it also has apache (with PHP) running on it. My current InnoDB settings are: | innodb_additional_mem_pool_size | 1048576 | innodb_buffer_pool_size | 262144000 | innodb_data_file_path | ibdata1:10M:autoextend | innodb_data_home_dir | | innodb_file_io_threads | 4 | innodb_force_recovery | 0 | innodb_thread_concurrency | 4 | innodb_flush_log_at_trx_commit | 1 | innodb_fast_shutdown | ON | innodb_flush_method | | innodb_lock_wait_timeout | 50 | innodb_log_arch_dir | ./ | innodb_log_archive | OFF | innodb_log_buffer_size | 20971520 | innodb_log_file_size | 5242880 | innodb_log_files_in_group | 2 | innodb_log_group_home_dir | ./ | innodb_mirrored_log_groups | 1 | innodb_max_dirty_pages_pct | 90 I have also tried a buffer_pool_size of 8MB, 70MB, 100MB, 150MB, and 350MB. Some traffic stats are: (shared between InnoDB and MyISAM tables) 20 connections per second. 200 queries per second. InnoDB Table #1 is 84MB [230,000 rows] InnoDB Table #2 is 166MB [280,000 rows] InnoDB Table #3 is 151MB [570,000 rows] MySQL says: 'InnoDB free: 6144 kB' The InnoDB Monitor gives this: -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 1; buffer pool: 0 26279 OS file reads, 24278 OS file writes, 10809 OS fsyncs 0.80 reads/s, 16384 avg bytes/read, 3.40 writes/s, 3.40 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 380, seg size 382, 125 inserts, 125 merged recs, 57 merges Hash table size 1155127, used cells 529328, node heap has 695 buffer(s) 922.82 hash searches/s, 338.13 non-hash searches/s --- LOG --- Log sequence number 0 479156959 Log flushed up to 0 479156959 Last checkpoint at 0 479148210 1 pending log writes, 0 pending chkp writes 10188 log i/o's done, 3.40 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 316173844; in additional pool allocated 1038720 Buffer pool size 16000 Free buffers 0 Database pages 15305 Modified db pages 54 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 31305, created 9, written 14289 0.80 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 4 queries inside InnoDB, 48 queries in queue Main thread process no 21521, state: flushing log Number of rows inserted 209, updated 15138, deleted 0, read 1547848 0.20 inserts/s, 4.20 updates/s, 0.00 deletes/s, 958.41 reads/s In the Transaction Section of the InnoDB monitor I am seeing things like: 'Trx read view will not see trx with id >= 0 2505453, sees < 0 2505360' a lot. I am assuming that I have a configuration problem. I know InnoDB tables can be about 30% slower and I see that when I execute a query on an idle server. That same server with traffic suddenly makes queries 10 to 2000 times slower. Has anyone seen this issue before? Can anyone give any suggestions for finding the problem or solution? Thanks. __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
