紧急求助: MySQL 内存溢出

lrlfriend

求助, Server运行一段时间后, MySQL内存溢出, 提示 "mysqld: Out of memory (Needed 468120 bytes)"

1. 说明: 这个Server引擎是InnoDB , 数据库操作只有一个 INSERT , 单笔INSERT的字节数大约3K左右.  每天大约8000笔交易.
2. 硬件配置 : 4G内存 , CentOS5.3 .
3. my.cnf 配置文件 .
[client]
#default-character-set=utf8
#password        = your_password
port                = 3306
socket                = /var/lib/mysql/mysql.sock

# The MySQL server
[mysqld]
#default-character-set=utf8
port                = 3306
socket                = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 2M

query_cache_size = 32M
query_cache_type= 1
long_query_time=10
slow_query_log=/data/SmartSP/data/slow_query.log


interactive_timeout=28800
wait_timeout=15
max_connections=24


log-bin=mysql-bin
expire_logs_days=7
binlog-do-db=SmartSP
relay-log=relay-bin

server-id        = 1

innodb_data_home_dir = /data/SmartSP/data/
innodb_data_file_path = ibdata1:200M;ibdata2:200M:autoextend:max:2000G
innodb_log_group_home_dir = /data/SmartSP/data/
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
sync_binlog=1
innodb_support_xa=1

[mysqldump]
quick
max_allowed_packet = 16M

[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



4. ulimit 结果

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 77824
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 77824
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


5. top结果
第一天 :
top - 06:47:32 up 117 days, 16:10,  1 user,  load average: 0.06, 0.07, 0.02
Tasks: 170 total,   1 running, 169 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni, 99.7%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4137112k total,  3895972k used,   241140k free,   262348k buffers
Swap:  5406712k total,       92k used,  5406620k free,  1935420k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND            
21662 root      24   0 1391m 1.2g 3928 S  0.0 30.9   1:27.57 mysqld            
21717 root      18   0  743m 203m 6352 S  0.0  5.0   0:43.43 java               



第五天
top - 08:16:49 up 121 days, 17:40,  0 users,  load average: 0.01, 0.07, 0.05
Tasks: 164 total,   1 running, 163 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni, 99.7%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4137112k total,  3983820k used,   153292k free,   238352k buffers
Swap:  5406712k total,       92k used,  5406620k free,  1379144k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND            
21662 root      22   0 2262m 1.8g 4024 S  0.0 46.8  21:35.23 mysqld            
21717 root      18   0  745m 207m 6352 S  0.0  5.1   6:23.13 java               

6. free结果
第一天:
             total       used       free     shared    buffers     cached
Mem:          4040       3804        235          0        256       1890
-/+ buffers/cache:       1658       2381
Swap:         5279          0       5279


第五天
             total       used       free     shared    buffers     cached
Mem:          4040       3890        149          0        232       1346
-/+ buffers/cache:       2310       1729
Swap:         5279          0       5279


7. vmstat结果
第一天:
      4137112  total memory
      3896096  used memory
      3529248  active memory
       255864  inactive memory
       241016  free memory
       262348  buffer memory
      1935420  swap cache
      5406712  total swap
           92  used swap
      5406620  free swap
      2372190 non-nice user cpu ticks
       174700 nice user cpu ticks
      1644586 system cpu ticks
   8106777222 idle cpu ticks
     21555728 IO-wait cpu ticks
       160402 IRQ cpu ticks
        83208 softirq cpu ticks
            0 stolen cpu ticks
     11997578 pages paged in
    388335994 pages paged out
            0 pages swapped in
           23 pages swapped out
   1735798176 interrupts
   2269942126 CPU context switches
   1312526200 boot time
     10302521 forks

第五天:
      4137112  total memory
      3983944  used memory
      2875900  active memory
       996360  inactive memory
       153168  free memory
       238352  buffer memory
      1379144  swap cache
      5406712  total swap
           92  used swap
      5406620  free swap
      2526571 non-nice user cpu ticks
       180683 nice user cpu ticks
      1691234 system cpu ticks
   8386015925 idle cpu ticks
     22835536 IO-wait cpu ticks
       165927 IRQ cpu ticks
        87825 softirq cpu ticks
            0 stolen cpu ticks
     12176626 pages paged in
    406965094 pages paged out
            0 pages swapped in
           23 pages swapped out
   2092888181 interrupts
   2351495330 CPU context switches
   1312526200 boot time
     10579356 forks


8. Innodb status .

第一天:
Type        Name        Status
InnoDB               
=====================================
111201  6:47:31 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 65195, signal count 65195
Mutex spin waits 0, rounds 181866, OS waits 98
RW-shared spins 129810, OS waits 64832; RW-excl spins 11403, OS waits 221
------------
TRANSACTIONS
------------
Trx id counter 0 1279193
Purge done for trx's n < 0 1279191 undo n < 0 0
History list length 26
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 21662, OS thread id 1792015248
MySQL thread id 3478, query id 48361 localhost root
SHOW INNODB STATUS
--------
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
20187 OS file reads, 27512 OS file writes, 12997 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.27 writes/s, 0.70 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
19 inserts, 19 merged recs, 19 merges
Hash table size 4425293, node heap has 13 buffer(s)
15.37 hash searches/s, 8.62 non-hash searches/s
---
LOG
---
Log sequence number 0 1697311934
Log flushed up to   0 1697311934
Last checkpoint at  0 1697309385
0 pending log writes, 0 pending chkp writes
8383 log i/o's done, 0.45 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1200494764; in additional pool allocated 2097152
Dictionary memory allocated 4844368
Buffer pool size   65536
Free buffers       0
Database pages     65523
Modified db pages  10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 86162, created 387, written 22254
0.00 reads/s, 0.02 creates/s, 1.17 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 21662, id 1812659088, state: sleeping
Number of rows inserted 3423, updated 3407, deleted 0, read 4663633
0.17 inserts/s, 0.17 updates/s, 0.00 deletes/s, 79.72 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================


第五天:
Type        Name        Status
InnoDB               
=====================================
111205  8:16:48 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1489094, signal count 1488987
Mutex spin waits 0, rounds 6136315, OS waits 9428
RW-shared spins 2950564, OS waits 1473468; RW-excl spins 348988, OS waits 1358
------------
TRANSACTIONS
------------
Trx id counter 0 1367767
Purge done for trx's n < 0 1367765 undo n < 0 0
History list length 14
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 21662, OS thread id 1628732304
MySQL thread id 48250, query id 671575 localhost root
SHOW INNODB STATUS
--------
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
138458 OS file reads, 382615 OS file writes, 179544 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.45 writes/s, 0.77 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
78 inserts, 78 merged recs, 77 merges
Hash table size 4425293, node heap has 47 buffer(s)
42.39 hash searches/s, 17.55 non-hash searches/s
---
LOG
---
Log sequence number 0 1819149727
Log flushed up to   0 1819149727
Last checkpoint at  0 1819146388
0 pending log writes, 0 pending chkp writes
115950 log i/o's done, 0.58 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1205320624; in additional pool allocated 2097152
Dictionary memory allocated 4847232
Buffer pool size   65536
Free buffers       0
Database pages     65489
Modified db pages  10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1727843, created 5992, written 311284
0.00 reads/s, 0.06 creates/s, 1.26 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 21662, id 1812659088, state: sleeping
Number of rows inserted 47653, updated 47299, deleted 0, read 86315639
0.26 inserts/s, 0.23 updates/s, 0.00 deletes/s, 639.17 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

注 : 第五天的时候还没有溢出, 但可以明显看到内存增长.

lrlfriend

回复 2# kerlion


    没有呀,  innodb只分配了 25% 的内存, 其他更少,基本可以忽略 .   
    另外, MySQL占用的内存是慢慢不断增长的 , 刚启动的时候只占了30%的内存, 5天后, 变成占了50%了.
    就是说, 运行5天消耗掉了将近 1G的内存 .
    另外,  连接 MySQL的connection保证每次操作后都释放了 .

ooooldman

内存小了，把innodb_buffer_pool_size = 1G  调大一点

lrlfriend

回复 4# ooooldman


    不是这个原因, 以前是2G, 因为内存溢出, 调到了1G .

eugene_jin

配置：
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 2M

第一天：
Total memory allocated 1200494764; in additional pool allocated 2097152

第五天：
Total memory allocated 1205320624; in additional pool allocated 2097152

说明: 这个Server引擎是InnoDB , 数据库操作只有一个 INSERT , 单笔INSERT的字节数大约3K左右.  每天大约8000笔交易.


估计是innodb_additional_mem_pool_size 内存空间不足需要存储其内部字典信息及数据而导致的，而且你设置为2M本身就偏小，与1G的innodb_buffer_pool_size相比较，建议设置为：
innodb_additional_mem_pool_size = 16M

以及从你查阅的信息看，mysqld并没有出现内存溢出，请检查下是不是报内存不足的时候，出现其他的业务操作，比如其他程序在此服务器上运行，mysqldump 等

lrlfriend

回复 6# eugene_jin


    谢谢回复 ,  应该不是innodb_additional_mem_pool_size不够的问题吧 , 如果不够的话error文件里应该有警告信息啊 .

    其实我的表很少, 只有9个, 但有一个表做了分区 (700个分区)  ?

    报内存不足的时候没有别的操作, 另, 因为需要很长时间才能碰到这个问题, 其他信息不太好获取.

    其实我在VMWARE上试过连续100w笔交易, 没问题.  只在生产上发现这个问题, 生产用的是DELL R410 .

haha8277

修改下limits.conf 加入如下几行，多少自己决定吧
* soft nofile 8192
* hard nofile 20480
mysql - memlock unlimite

试试看能解决不

C罗哪儿多

在这里的都是高手啊，我都看不懂啊，唉，路过吧www.nnydh.com

haha8277

这段时间正好查阅mysql性能参数，看到max_connections这个参数，容易引起out of memory的错误，建议你设的比较大点。他默认是100 太大的话容易引起效率低下的问题