Mysql Server Optimierung INNODB
am 25.07.2007 16:10:26 von Matthias BlohmHi,
wir haben ein Problem mit einem Mysql-Server 4.1 und einer sehr großen Datenbank mit mehreren
millionen Datensätzen.
Der Server läuft auf einem Linux Suse SLES9 und allen Service-Packs.
Der Server ist ein Intel DualCore mit 12GB Ram und das Filesystem ist ein ReiserFS mit
angeschlossenenem RAID5. Netzwerk Gigabit natürlich auch.
Das Problem ist folgendes:
Manchmal wenn der Server einige einfache SELECTs mache soll läuft der bei hohem Load von max. 2.00
.. Dann läuft das SELECT 10minuten oder mehr, welches normaler Weise 16 Sekunden dauern sollte. Alle
anderen bekommen in der Zeit ein Timeout wenn diese ein Update auf die Tabelle machen wollen.
Unsere Frage ist natürlich nun, WARUM passiert das und welche Optimierungen kann man durchführen
machen, damit das nicht mehr auftritt.
Hat jemand eine Idee?
Danke schon mal.
Gruss
Matthew
hier die my.cnf:
####################################
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 1M
max_allowed_packet = 128M
table_cache = 1000
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 1M
transaction-isolation = READ-COMMITTED
default-collation = latin1_german2_ci
log_slow_queries
default-table-type = INNODB
max_connections = 200
##
# log = /var/log/mysql/mysqld.log
log-error = /var/log/mysql/mysqlderror.log
#
# skip-networking
# Replication Master Server (default)
# binary logging is required for replication
#
log-bin=/srv/mysql/dba1
binlog-ignore-db=BE_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 omitted
server-id = 1
# Point the following paths to different dedicated disks
tmpdir = /tmp/
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /srv/mysql/
innodb_data_file_path = ibdata1:150G
innodb_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 high
innodb_buffer_pool_size = 8000M
innodb_additional_mem_pool_size = 8M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
# innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 180
# Query cache
query_cache_size = 64M
query_cache_limit = 8M
query_cache_type = 1
old_passwords=1
skip_name_resolve
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
########################
=====================================
070725 15:32:00 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 41 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 98062, signal count 98040
Mutex spin waits 634080, rounds 678716, OS waits 8040
RW-shared spins 177963, OS waits 88873; RW-excl spins 713, OS waits 430
------------
TRANSACTIONS
------------
Trx id counter 0 89633355
Purge done for trx's n:o < 0 89631095 undo n:o < 0 0
History list length 84
Total number of lock structs in row lock hash table 44
LIST OF TRANSACTIONS FOR EACH SESSION:
<...skip...>
---TRANSACTION 0 89626052, not started, process no 1062, OS thread id 1092864352
MySQL thread id 10947, query id 192077 192.168.0.11 mysqluser
show innodb status
---TRANSACTION 0 89630546, ACTIVE 1254 sec, process no 1062, OS thread id 1092663648 inserting,
thread declared inside InnoDB 79
mysql tables in use 3, locked 3
47 lock struct(s), heap size 6752, undo log entries 28652967
MySQL thread id 13628, query id 189350 192.168.0.119 mysqluser Sending data
CREATE TABLE TEL_DIFF SELECT m.DS_NR, m.TIME, m.ZUSATZ_07 FROM TELAKT_07 m inner join TELAKT_MULTI
n where m.DS_NR != n.DS_NR
--------
FILE 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: 0
Pending flushes (fsync) log: 0; buffer pool: 0
384225 OS file reads, 38141 OS file writes, 18978 OS fsyncs
0.12 reads/s, 16384 avg bytes/read, 5.95 writes/s, 4.07 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 5, seg size 7, is empty
Ibuf for space 0: size 1, free list len 5, seg size 7,
0 inserts, 0 merged recs, 0 merges
Hash table size 18479729, used cells 1320747, node heap has 2022 buffer(s)
20184.07 hash searches/s, 483.01 non-hash searches/s
---
LOG
---
Log sequence number 257 1619464938
Log flushed up to 257 1618221698
Last checkpoint at 257 850514509
0 pending log writes, 0 pending chkp writes
10173 log i/o's done, 1.98 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 9174291714; in additional pool allocated 8388608
Buffer pool size 512000
Free buffers 0
Database pages 509978
Modified db pages 49867
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 5241454, created 337976, written 360513
0.12 reads/s, 120.61 creates/s, 118.73 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread process no. 1062, id 1088448864, state: sleeping
Number of rows inserted 47593898, updated 5883, deleted 0, read 533664675
20473.87 inserts/s, 0.00 updates/s, 0.00 deletes/s, 2.95 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================