MySQL 4.0.12 (I know, old)
Production server is Windows 2008 R2 on ESX. Dedicated to DB.
Test servers Redhat 4 and Windows XP perform fine.
Production server recently started to perform extremely poorly for our single innodb table. All other tables are ISAM.
32,000 rows in table. size on disk is 198Meg
select count(*) from table takes .07 on test machines but full 7 seconds on production machine.
A particular query we are working on takes up to 30 seconds on production machine (But occasionally, 10% of time, less then 1 second). On test machines it takes less then 1 second.
The query is simple (in pseudo code): select a couple of fields from table where date_fieldA > ‘someDate’ and date_fieldB < ‘someDate’ order by field1, field2
Machine CPU is low, memory is reasonable, but disk I/O shoots up with above query when the query takes 30 seconds.
I have read www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-w alk-through/ and the sections on SEMAPHORES and BUFFER POOL AND MEMORY seem to be the place to start based on our status below.
SEMAPHORES looks most suspicious but not sure what to do about it. The spin waits, rounds, share spins, and OS Waits are quite higher on production then on test.
innodb_buffer_pool_size from show variables says "67108864’ but from show innodb status its ‘4096’. That doesn’t make sense to me unless the variable value is way to big but with a 198Meg innodb file I would think the actual used pool would be much higher.
Any help on this is of course greatly appreciated.
mysql> show variables like ‘%innodb%’;±--------------------------------±-----------------------+| Variable_name | Value |±--------------------------------±-----------------------+| have_innodb | YES || innodb_additional_mem_pool_size | 2097152 || innodb_buffer_pool_size | 67108864 || innodb_data_file_path | ibdata1:10M:autoextend || innodb_data_home_dir | || innodb_file_io_threads | 16 || innodb_force_recovery | 0 || innodb_thread_concurrency | 8 || innodb_flush_log_at_trx_commit | 0 || innodb_fast_shutdown | ON || innodb_flush_method | || innodb_lock_wait_timeout | 50 || innodb_log_arch_dir | .\ || innodb_log_archive | OFF || innodb_log_buffer_size | 4194304 || innodb_log_file_size | 16777216 || innodb_log_files_in_group | 2 || innodb_log_group_home_dir | .\ || innodb_mirrored_log_groups | 1 |±--------------------------------±-----------------------+19 rows in set (0.00 sec)
SHOW INNODB STATUS\G gives
=====================================121219 22:35:01 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 16 seconds----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 715700, signal count 688703Mutex spin waits 648325, rounds 12965580, OS waits 46RW-shared spins 1431331, OS waits 715483; RW-excl spins 189, OS waits 171------------TRANSACTIONS------------Trx id counter 0 5945912Purge done for trx’s n:o < 0 5945747 undo n:o < 0 0Total number of lock structs in row lock hash table 0LIST OF TRANSACTIONS FOR EACH SESSION:--------FILE I/O--------I/O thread 0 state: wait Windows aioI/O thread 1 state: wait Windows aioI/O thread 2 state: wait Windows aioI/O thread 3 state: wait Windows aioI/O thread 4 state: wait Windows aioI/O thread 5 state: wait Windows aioI/O thread 6 state: wait Windows aioI/O thread 7 state: wait Windows aioI/O thread 8 state: wait Windows aioI/O thread 9 state: wait Windows aioI/O thread 10 state: wait Windows aioI/O thread 11 state: wait Windows aioI/O thread 12 state: wait Windows aioI/O thread 13 state: wait Windows aioI/O thread 14 state: wait Windows aioI/O thread 15 state: wait Windows aioPending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0Pending flushes (fsync) log: 0; buffer pool: 05822514 OS file reads, 21155 OS file writes, 9655 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf for space 0: size 1, free list len 0, seg size 2,10 inserts, 10 merged recs, 10 mergesHash table size 276707, used cells 15077, node heap has 16 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s—LOG—Log sequence number 0 615680910Log flushed up to 0 615680910Last checkpoint at 0 6156809100 pending log writes, 0 pending chkp writes4285 log i/o’s done, 0.00 log i/o’s/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 81266588; in additional pool allocated 358016Buffer pool size 4096Free buffers 0Database pages 4080Modified db pages 0Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0Pages read 5822404, created 134, written 149670.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool activity since the last printout--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queueMain thread id 1708, state: waiting for server activityNumber of rows inserted 450, updated 8806, deleted 55, read 643477040.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================