DuffMan, I assume you are running 4.0.14? What is your Linux version? Please post the whole output of SHOW INNODB STATUS\G
Are you using the MySQL query cache? Remember that any insert, update, or delete invalidates it. To test the impact of fsync(), try running with innodb_flush_log_at_trx_commit=0. Your log files are too small compared to the buffer pool size. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: "DuffMan" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, July 24, 2003 5:07 AM Subject: Poor InnoDB Performance > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
