Performance Optimizing INNODB

Hi ,
we are having a problem with a Mysql-Server which is having a hugh database and a lot of problems.

The Server is running under SuSE SLES9 all patches installed.
The Server is a Intel DualCore 3.66ghz with 12GB RAM. We have Gigabit attached network and the filesystem ist ReiserFS over a Raid 5 Storagesystem.

The Problem :
sometimes the Server hangs a while and is running on a load of 2.00 . A small SELECT is running at this time more than 10 Minutes - normaly 15 sec. -. And all other users got a Timeout if they running an UPDATE command.

We are a little astonished of the perfomance of that server, but there is nothing to see why.

So my question. Is there anything to see inside the config, if there is a problem, we did not notice.


The following options will be passed to all MySQL clients[client]#password = your_passwordport = 3306socket = /var/lib/mysql/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]port = 3306socket = /var/lib/mysql/mysql.sockskip-lockingkey_buffer = 1Mmax_allowed_packet = 128Mtable_cache = 1000sort_buffer_size = 1Mnet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 1Mtransaction-isolation = READ-COMMITTEDdefault-collation = latin1_german2_cilog_slow_queriesdefault-table-type = INNODBmax_connections = 200## # log = /var/log/mysql/mysqld.loglog-error = /var/log/mysql/mysqlderror.log## skip-networking# Replication Master Server (default)# binary logging is required for replication#log-bin=/srv/mysql/vewa-dba1binlog-ignore-db=BE_JUH_TEST# required unique id between 1 and 2^32 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 1# Point the following paths to different dedicated diskstmpdir = /tmp/# Uncomment the following if you are using InnoDB tablesinnodb_data_home_dir = /srv/mysql/innodb_data_file_path = ibdata1:150Ginnodb_log_group_home_dir = /srv/mysql/innodb_log_arch_dir = /srv/mysql/# You can set …_buffer_pool_size up to 50 - 80 %# of RAM but beware of setting memory usage too highinnodb_buffer_pool_size = 8000Minnodb_additional_mem_pool_size = 8M# Set …_log_file_size to 25 % of buffer pool sizeinnodb_log_file_size = 512Minnodb_log_buffer_size = 8M# innodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 180# Query cachequery_cache_size = 64Mquery_cache_limit = 8Mquery_cache_type = 1old_passwords=1skip_name_resolve[safe_mysqld]err-log=/var/lib/mysql/mysqld.log[mysqldump]quickmax_allowed_packet = 64M[mysql]no-auto-rehash[isamchk]key_buffer = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[myisamchk]key_buffer = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout


=====================================070725 13:32:09 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 58 seconds----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 93484, signal count 93481Mutex spin waits 608735, rounds 561357, OS waits 4012RW-shared spins 177256, OS waits 88527; RW-excl spins 535, OS waits 379------------TRANSACTIONS------------Trx id counter 0 89626168Purge done for trx’s n:o < 0 89626167 undo n:o < 0 0History list length 14Total number of lock structs in row lock hash table 0LIST OF TRANSACTIONS FOR EACH SESSION:—TRANSACTION 0 89626052, not started, process no 1062, OS thread id 1092864352MySQL thread id 10947, query id 170644 mysqlusershow innodb statusFILE 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: waiting for i/o request (read thread)I/O thread 3 state: waiting for i/o request (write thread)Pending 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: 0381056 OS file reads, 21266 OS file writes, 8553 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.25 writes/s, 0.18 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf for space 0: size 1, free list len 5, seg size 7, is emptyIbuf for space 0: size 1, free list len 5, seg size 7,0 inserts, 0 merged recs, 0 mergesHash table size 18479729, used cells 3598606, node heap has 5904 buffer(s)0.21 hash searches/s, 0.67 non-hash searches/s—LOG—Log sequence number 256 1698086532Log flushed up to 256 1698086532Last checkpoint at 256 16980865320 pending log writes, 0 pending chkp writes5375 log i/o’s done, 0.11 log i/o’s/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 9174318658; in additional pool allocated 8388608Buffer pool size 512000Free buffers 0Database pages 506096Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages read 5238001, created 77983, written 959820.00 reads/s, 0.00 creates/s, 0.13 writes/sBuffer pool hit rate 1000 / 1000--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queueMain thread process no. 1062, id 1088448864, state: sleepingNumber of rows inserted 2814203, updated 5208, deleted 0, read 4823498810.00 inserts/s, 0.09 updates/s, 0.00 deletes/s, 0.29 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================

Would be very nice, if there is someoune outside to help me.

You should have posted SHOW INNODB STATUS from the time server is suffering and takes a lot of time to run that query.

This one is from Idle server so it is hard to tell what that could be

Likely Innodb Concurrency issues but may be something else.

MySQL version is also helpful