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.   

    配置:
    [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
      

  2.   

    先调整tmp_table_size,增大临时表的值。
    然后show processlist,查看当前 mysql 使用频繁的 sql 语句根据情况分析!
      

  3.   

    show processlist;
    看看哪些东西在狂跑
      

  4.   

    SHOW PROCESSLIST未见异常,也就是说客户端软件在此过程没有修改。配置不同的地方是增加了# 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 - end5.5.x新有的东西。
      

  5.   

    先调整这个看看 innodb-max-dirty-pages-pct = 25
      

  6.   

    曾经一度怀疑是MySQL.NET驱动的问题,暂时没有确凿证据
      

  7.   

    调高了 innodb_additional_mem_pool_size 似乎有点效果innodb-max-dirty-pages-pct 调的太低的话,会造成过多的磁盘IO默认75,Google建议80,mysqlperformanceblog建议60
      

  8.   

    -- innodb_additional_mem_pool_size 
    /*
    这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小。应用程序里的表越多,你需要在这里分配越多的内存。
    对于一个相对稳定的应用,这个参数的大小也是相对稳定的,也没有必要预留非常大的值。如果 InnoDB 用光了这个池内的内存, InnoDB 
    开始从操作系统分配内存,并且往 MySQL 错误日志写警告信息。默认值是 1MB ,当发现错误日志中已经有相关的警告信息时,就应该适当
    的增加该参数的大小。
    */
      

  9.   

    看这个数据,这个CPU是不是可以考虑正常呢?上次重启5天前ROW OPERATIONS
    --------------
    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
      

  10.   

    跟踪了很久发现是REPLACE INTO/D-SQL造成的,小小改造一下,基本解决,谢谢。