80%以上是经常出现,连接数20-40,系统负载超过10,未升级之前系统负载2-4服务器8个核,20G内存。SHOW ENGINE INNODB STATUS和my.cnf配置分别如下:mysql> show engine innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
110419 2:20:43 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 29 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 396 1_second, 395 sleeps, 39 10_second, 3 background, 3
flush
srv_master_thread log flush and writes: 397
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2968, signal count 2875
Mutex spin waits 22170, rounds 53304, OS waits 286
RW-shared spins 2558, rounds 76587, OS waits 2548
RW-excl spins 111, rounds 3815, OS waits 127
Spin rounds per wait: 2.40 mutex, 29.94 RW-shared, 34.37 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 3CF7353
Purge done for trx's n:o < 3CB7FA1 undo n:o < 0
History list length 1442
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 3CF6EA8, not started, process no 15586, OS thread id 1494452544
MySQL thread id 258, query id 1005040 ...
---TRANSACTION 3CF6AE5, not started, process no 15586, OS thread id 1494985024
MySQL thread id 257, query id 1002463 ...
---TRANSACTION 0, not started, process no 15586, OS thread id 1497913664
MySQL thread id 98, query id 1010239 ...
show engine innodb status
---TRANSACTION 3CF734D, not started, process no 15586, OS thread id 1497647424
MySQL thread id 112, query id 1010237 .....
---TRANSACTION 3CF6B74, not started, process no 15586, OS thread id 1493920064
MySQL thread id 1, query id 1002900 ....
---TRANSACTION 3CF7352, ACTIVE 0 sec, process no 15586, OS thread id 1498446144
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 213, query id 1010239 Reading event from the relay log
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (read thread)
I/O thread 11 state: waiting for completed aio requests (read thread)
I/O thread 12 state: waiting for completed aio requests (read thread)
I/O thread 13 state: waiting for completed aio requests (read thread)
I/O thread 14 state: waiting for completed aio requests (read thread)
I/O thread 15 state: waiting for completed aio requests (read thread)
I/O thread 16 state: waiting for completed aio requests (read thread)
I/O thread 17 state: waiting for completed aio requests (read thread)
I/O thread 18 state: waiting for completed aio requests (write thread)
I/O thread 19 state: waiting for completed aio requests (write thread)
I/O thread 20 state: waiting for completed aio requests (write thread)
I/O thread 21 state: waiting for completed aio requests (write thread)
I/O thread 22 state: waiting for completed aio requests (write thread)
I/O thread 23 state: waiting for completed aio requests (write thread)
I/O thread 24 state: waiting for completed aio requests (write thread)
I/O thread 25 state: waiting for completed aio requests (write thread)
I/O thread 26 state: waiting for completed aio requests (write thread)
I/O thread 27 state: waiting for completed aio requests (write thread)
I/O thread 28 state: waiting for completed aio requests (write thread)
I/O thread 29 state: waiting for completed aio requests (write thread)
I/O thread 30 state: waiting for completed aio requests (write thread)
I/O thread 31 state: waiting for completed aio requests (write thread)
I/O thread 32 state: waiting for completed aio requests (write thread)
I/O thread 33 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , a
io writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
38248 OS file reads, 36389 OS file writes, 557 OS fsyncs
84.20 reads/s, 16384 avg bytes/read, 821.87 writes/s, 1.93 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 12, seg size 14, 8 merges
merged operations:
insert 79, delete 0, delete 0
discarded operations:
insert 0, delete 0, delete 0
Hash table size 25499809, node heap has 435 buffer(s)
50.86 hash searches/s, 1857.83 non-hash searches/s
---
LOG
---
Log sequence number 42025909977
Log flushed up to 42025887516
Last checkpoint at 42020695311
0 pending log writes, 0 pending chkp writes
35878 log i/o's done, 816.35 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13186891776; in additional pool allocated 0
Dictionary memory allocated 2079831
Buffer pool size 786431
Free buffers 747949
Database pages 38047
Old database pages 14064
Modified db pages 2600
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 37862, created 185, written 467
83.27 reads/s, 3.90 creates/s, 5.21 writes/s
Buffer pool hit rate 991 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 38047, unzip_LRU len: 0
I/O sum[0]:cur[40], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 15586, id 1451694400, state: sleeping
Number of rows inserted 35456, updated 0, deleted 0, read 3842853
815.28 inserts/s, 0.00 updates/s, 0.00 deletes/s, 168.86 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================1 row in set (0.38 sec)
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
110419 2:20:43 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 29 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 396 1_second, 395 sleeps, 39 10_second, 3 background, 3
flush
srv_master_thread log flush and writes: 397
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2968, signal count 2875
Mutex spin waits 22170, rounds 53304, OS waits 286
RW-shared spins 2558, rounds 76587, OS waits 2548
RW-excl spins 111, rounds 3815, OS waits 127
Spin rounds per wait: 2.40 mutex, 29.94 RW-shared, 34.37 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 3CF7353
Purge done for trx's n:o < 3CB7FA1 undo n:o < 0
History list length 1442
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 3CF6EA8, not started, process no 15586, OS thread id 1494452544
MySQL thread id 258, query id 1005040 ...
---TRANSACTION 3CF6AE5, not started, process no 15586, OS thread id 1494985024
MySQL thread id 257, query id 1002463 ...
---TRANSACTION 0, not started, process no 15586, OS thread id 1497913664
MySQL thread id 98, query id 1010239 ...
show engine innodb status
---TRANSACTION 3CF734D, not started, process no 15586, OS thread id 1497647424
MySQL thread id 112, query id 1010237 .....
---TRANSACTION 3CF6B74, not started, process no 15586, OS thread id 1493920064
MySQL thread id 1, query id 1002900 ....
---TRANSACTION 3CF7352, ACTIVE 0 sec, process no 15586, OS thread id 1498446144
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 213, query id 1010239 Reading event from the relay log
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (read thread)
I/O thread 11 state: waiting for completed aio requests (read thread)
I/O thread 12 state: waiting for completed aio requests (read thread)
I/O thread 13 state: waiting for completed aio requests (read thread)
I/O thread 14 state: waiting for completed aio requests (read thread)
I/O thread 15 state: waiting for completed aio requests (read thread)
I/O thread 16 state: waiting for completed aio requests (read thread)
I/O thread 17 state: waiting for completed aio requests (read thread)
I/O thread 18 state: waiting for completed aio requests (write thread)
I/O thread 19 state: waiting for completed aio requests (write thread)
I/O thread 20 state: waiting for completed aio requests (write thread)
I/O thread 21 state: waiting for completed aio requests (write thread)
I/O thread 22 state: waiting for completed aio requests (write thread)
I/O thread 23 state: waiting for completed aio requests (write thread)
I/O thread 24 state: waiting for completed aio requests (write thread)
I/O thread 25 state: waiting for completed aio requests (write thread)
I/O thread 26 state: waiting for completed aio requests (write thread)
I/O thread 27 state: waiting for completed aio requests (write thread)
I/O thread 28 state: waiting for completed aio requests (write thread)
I/O thread 29 state: waiting for completed aio requests (write thread)
I/O thread 30 state: waiting for completed aio requests (write thread)
I/O thread 31 state: waiting for completed aio requests (write thread)
I/O thread 32 state: waiting for completed aio requests (write thread)
I/O thread 33 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , a
io writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
38248 OS file reads, 36389 OS file writes, 557 OS fsyncs
84.20 reads/s, 16384 avg bytes/read, 821.87 writes/s, 1.93 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 12, seg size 14, 8 merges
merged operations:
insert 79, delete 0, delete 0
discarded operations:
insert 0, delete 0, delete 0
Hash table size 25499809, node heap has 435 buffer(s)
50.86 hash searches/s, 1857.83 non-hash searches/s
---
LOG
---
Log sequence number 42025909977
Log flushed up to 42025887516
Last checkpoint at 42020695311
0 pending log writes, 0 pending chkp writes
35878 log i/o's done, 816.35 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13186891776; in additional pool allocated 0
Dictionary memory allocated 2079831
Buffer pool size 786431
Free buffers 747949
Database pages 38047
Old database pages 14064
Modified db pages 2600
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 37862, created 185, written 467
83.27 reads/s, 3.90 creates/s, 5.21 writes/s
Buffer pool hit rate 991 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 38047, unzip_LRU len: 0
I/O sum[0]:cur[40], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 15586, id 1451694400, state: sleeping
Number of rows inserted 35456, updated 0, deleted 0, read 3842853
815.28 inserts/s, 0.00 updates/s, 0.00 deletes/s, 168.86 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================1 row in set (0.38 sec)
[mysqld]
character-set-server = utf8
collation-server = utf8_bin
user = mysql
port = 3306
socket = /data/mysql/mysqld.sock
pid-file = /data/mysql/mysqld.pid
log-error = /data/mysql/mysqld.err
basedir = /usr
datadir = /data/mysql
skip-external-locking
skip-name-resolve
skip-slave-start
back-log = 512
net-read-timeout = 60
net-write-timeout = 60
key-buffer = 96M
max-allowed-packet = 128M
table-cache = 20480
sort-buffer-size = 1572864 # 1.5M
net-buffer-length = 8K
read-buffer-size = 3145728 # 3.0M
read-rnd-buffer-size = 1572864 # 1.5M
myisam-sort-buffer-size = 1048576 # 1.0M
lc-messages = en_US
default-storage-engine = myisammax-connect-errors = 500
# security:
# using "localhost" in connects uses sockets by default
# skip-networking# binary logging is required for replication
log-bin = mysql-bin
# binary logging format - mixed recommended
# binlog-format = mixed
# binlog-format = statement
binlog-format = row
sync-binlog = 1# binlog-ignore-db = tmp
# binlog-ignore-db = mysqlserver-id = 1
# log the updates from the slave thread to the binary log
log-slave-updates = 1
# set slave read-only
read-only = 0
# use compression of the slave/master protocol if both the slave and the master support it
slave-compressed-protocol = 1
# restrict replication to the specified database
replicate-do-db = [...]replicate-ignore-db = mysql
replicate-ignore-db = tmp
# uncomment the following directives for slave or distributor
relay-log = /data/mysql/relay-bin
relay-log-index = /data/mysql/relay-bin.index
master-info-file = /data/mysql/master.info
relay-log-info-file = /data/mysql/relay-log.info
# automatic purging of relay logs as soon as they are not needed any more
relay-log-purge = 1
# point the following paths to different dedicated disks
tmpdir = /tmp/
# log-update = /path-to-dedicated-directory/hostname
# you need the debug USE flag enabled to use the following directives,
# if needed, uncomment them, start the server and issue
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's happening in your server ;)
# log = /tmp/mysqld.sql
# gdb
# debug = d:t:i:o,/tmp/mysqld.trace
# one-thread
# skip-bdb# the following is the InnoDB configuration
# if you wish to disable innodb instead
# uncomment just the next line
# skip-innodb
#
innodb-buffer-pool-size = 12G
innodb-buffer-pool-instances = 1
innodb-additional-mem-pool-size = 16M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-(
# and upstream wants things to be under /var/lib/mysql/, so that's the route
# we have to take for the moment
innodb-data-home-dir = /data/mysql
# innodb-log-arch-dir = /data/mysql
innodb-log-group-home-dir = /data/mysql
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb-data-file-path = ibdata1:1G:autoextend:max:8G
memlock
innodb-log-file-size = 128M
innodb-log-buffer-size = 10M
innodb-log-files-in-group = 2
innodb-flush-log-at-trx-commit = 2
innodb-lock-wait-timeout = 60
innodb-file-per-table = 1
transaction-isolation = READ-COMMITTED
innodb-thread-concurrency = 16
innodb-commit-concurrency = 16
innodb-support-xa = 0
innodb-file-io-threads = 4# MySQL 5.5.x - begin
innodb-write-io-threads = 16
innodb-read-io-threads = 16
innodb-io-capacity = 500
innodb-use-native-aio = 1
innodb-max-dirty-pages-pct = 60
innodb-purge-threads = 1
innodb-adaptive-flushing = 0
innodb-doublewrite = 1
innodb-file-format = Barracuda
innodb-file-format-max = Barracuda
innodb-use-sys-malloc = 1
# MySQL 5.5.x - endinnodb-table-locks = 0
innodb-flush-method = O_DIRECT
query-cache-type = 2
query-cache-size = 16M
query-cache-limit = 16K
query-cache-min-res-unit = 2K
thread-concurrency = 16
thread-cache = 512
concurrent-insert = 2
max-heap-table-size = 64M
long-query-time = 10
join-buffer-size = 1M
max-connections = 800
interactive-timeout = 300
wait-timeout = 5
expire-logs-days = 5
slow-query-log = 1
slow-query-log-file = /data/mysql/mysql-slow.log
log-queries-not-using-indexes = 1
low-priority-updates = 0
然后show processlist,查看当前 mysql 使用频繁的 sql 语句根据情况分析!
看看哪些东西在狂跑
innodb-write-io-threads = 16
innodb-read-io-threads = 16
innodb-io-capacity = 500
innodb-use-native-aio = 1
innodb-max-dirty-pages-pct = 60
innodb-purge-threads = 1
innodb-adaptive-flushing = 0
innodb-doublewrite = 1
innodb-file-format = Barracuda
innodb-file-format-max = Barracuda
innodb-use-sys-malloc = 1
# MySQL 5.5.x - end5.5.x新有的东西。
/*
这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小。应用程序里的表越多,你需要在这里分配越多的内存。
对于一个相对稳定的应用,这个参数的大小也是相对稳定的,也没有必要预留非常大的值。如果 InnoDB 用光了这个池内的内存, InnoDB
开始从操作系统分配内存,并且往 MySQL 错误日志写警告信息。默认值是 1MB ,当发现错误日志中已经有相关的警告信息时,就应该适当
的增加该参数的大小。
*/
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 5972, id 1462503744, state: flushing log
Number of rows inserted 20948618, updated 26172416, deleted 23151312, read 5122283096
0.17 inserts/s, 200.07 updates/s, 0.00 deletes/s, 1610.16 reads/s另外信号量/日志 有没有问题
---
LOG
---
Log sequence number 73376818669
Log flushed up to 73376721696
Last checkpoint at 73192378114
0 pending log writes, 0 pending chkp writes
95348306 log i/o's done, 400.59 log i/o's/second----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 662651, signal count 687036
Mutex spin waits 123259678, rounds 266517389, OS waits 502234
RW-shared spins 151048, rounds 2612568, OS waits 78996
RW-excl spins 90433, rounds 1665051, OS waits 27278
Spin rounds per wait: 2.16 mutex, 17.30 RW-shared, 18.41 RW-excl