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.
/etc/my.cnf:
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
INNODB-STATUS:
=====================================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 192.168.120.23 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.
greetings
digisec