緊急求助: MySQL 內(nèi)存溢出

lrlfriend

求助, Server運(yùn)行一段時(shí)間后, MySQL內(nèi)存溢出, 提示 "mysqld: Out of memory (Needed 468120 bytes)"

1. 說明: 這個(gè)Server引擎是InnoDB , 數(shù)據(jù)庫操作只有一個(gè) INSERT , 單筆INSERT的字節(jié)數(shù)大約3K左右.  每天大約8000筆交易.
2. 硬件配置 : 4G內(nèi)存 , 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 結(jié)果

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結(jié)果
第一天 :
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結(jié)果
第一天:
             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結(jié)果
第一天:
      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
============================

注 : 第五天的時(shí)候還沒有溢出, 但可以明顯看到內(nèi)存增長.

lrlfriend

回復(fù) 2# kerlion


    沒有呀,  innodb只分配了 25% 的內(nèi)存, 其他更少,基本可以忽略 .   
    另外, MySQL占用的內(nèi)存是慢慢不斷增長的 , 剛啟動的時(shí)候只占了30%的內(nèi)存, 5天后, 變成占了50%了.
    就是說, 運(yùn)行5天消耗掉了將近 1G的內(nèi)存 .
    另外,  連接 MySQL的connection保證每次操作后都釋放了 .

ooooldman

內(nèi)存小了，把innodb_buffer_pool_size = 1G  調(diào)大一點(diǎn)

lrlfriend

回復(fù) 4# ooooldman


    不是這個(gè)原因, 以前是2G, 因?yàn)閮?nèi)存溢出, 調(diào)到了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

說明: 這個(gè)Server引擎是InnoDB , 數(shù)據(jù)庫操作只有一個(gè) INSERT , 單筆INSERT的字節(jié)數(shù)大約3K左右.  每天大約8000筆交易.


估計(jì)是innodb_additional_mem_pool_size 內(nèi)存空間不足需要存儲其內(nèi)部字典信息及數(shù)據(jù)而導(dǎo)致的，而且你設(shè)置為2M本身就偏小，與1G的innodb_buffer_pool_size相比較，建議設(shè)置為：
innodb_additional_mem_pool_size = 16M

以及從你查閱的信息看，mysqld并沒有出現(xiàn)內(nèi)存溢出，請檢查下是不是報(bào)內(nèi)存不足的時(shí)候，出現(xiàn)其他的業(yè)務(wù)操作，比如其他程序在此服務(wù)器上運(yùn)行，mysqldump 等

lrlfriend

回復(fù) 6# eugene_jin


    謝謝回復(fù) ,  應(yīng)該不是innodb_additional_mem_pool_size不夠的問題吧 , 如果不夠的話error文件里應(yīng)該有警告信息啊 .

    其實(shí)我的表很少, 只有9個(gè), 但有一個(gè)表做了分區(qū) (700個(gè)分區(qū))  ?

    報(bào)內(nèi)存不足的時(shí)候沒有別的操作, 另, 因?yàn)樾枰�很長時(shí)間才能碰到這個(gè)問題, 其他信息不太好獲取.

    其實(shí)我在VMWARE上試過連續(xù)100w筆交易, 沒問題.  只在生產(chǎn)上發(fā)現(xiàn)這個(gè)問題, 生產(chǎn)用的是DELL R410 .

haha8277

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

試試看能解決不

C羅哪兒多

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

haha8277

這段時(shí)間正好查閱mysql性能參數(shù)，看到max_connections這個(gè)參數(shù)，容易引起out of memory的錯誤，建議你設(shè)的比較大點(diǎn)。他默認(rèn)是100 太大的話容易引起效率低下的問題