求助：MySQL内存持续增加问题。

yaya888

有一台MySQL数据库服务器，SSD硬盘，24G内存。
MySQL version 5.1.48，INNODB引擎，程序使用DBCP连接池，每天约INSERT&DELETE各300万数据。
运行一段时间后，占用内存持续增加，只增不减，现在已经占用93%，担心哪一天会挂掉。

-------------------------------------------------------------------------
top - 17:51:44 up 276 days, 18:32,  1 user,  load average: 0.37, 0.17, 0.11
Tasks: 200 total,   1 running, 199 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.1%us,  0.2%sy,  0.0%ni, 96.5%id,  0.0%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  24660072k total, 24532700k used,   127372k free,   215000k buffers
Swap:  1959920k total,  1628040k used,   331880k free,   604976k cached
-------------------------------------------------------------------------

my.cnf具体配置参数如下：

1. [client]
   
2. #password        = [your_password]
   
3. port                = 3306
   
4. socket                = /var/lib/mysql/mysql.sock
   
5. 
   
6. [mysqld]
   
7. # generic configuration options
   
8. port                = 3306
   
9. socket                = /var/lib/mysql/mysql.sock
   
10. back_log = 500
    
11. 
    
12. max_connections = 300
    
13. 
    
14. max_connect_errors = 100
    
15. 
    
16. table_open_cache = 2048
    
17. 
    
18. max_allowed_packet = 16M
    
19. 
    
20. binlog_cache_size = 1M
    
21. 
    
22. max_heap_table_size = 64M
    
23. 
    
24. sort_buffer_size = 8M
    
25. 
    
26. join_buffer_size = 8M
    
27. 
    
28. thread_cache_size = 8
    
29. 
    
30. thread_concurrency = 24
    
31. 
    
32. query_cache_size = 0
    
33. 
    
34. ft_min_word_len = 4
    
35. 
    
36. default-storage-engine = MYISAM
    
37. 
    
38. thread_stack = 192K
    
39. 
    
40. transaction_isolation = REPEATABLE-READ
    
41. 
    
42. tmp_table_size = 64M
    
43. 
    
44. log-bin=/usr/local/mysql/data/mysql-bin
    
45. 
    
46. binlog-ignore-db=mysql
    
47. binlog-ignore-db=nagios
    
48. expire_logs_days=1
    
49. # binary logging format - mixed recommended
    
50. binlog_format=mixed
    
51. 
    
52. log_slave_updates
    
53. 
    
54. slow_query_log
    
55. 
    
56. long_query_time = 10
    
57. server-id = 31
    
58. 
    
59. 
    
60. 
    
61. key_buffer_size = 32M
    
62. 
    
63. read_buffer_size = 2M
    
64. 
    
65. read_rnd_buffer_size = 16M
    
66. 
    
67. bulk_insert_buffer_size = 64M
    
68. 
    
69. myisam_sort_buffer_size = 128M
    
70. 
    
71. myisam_max_sort_file_size = 10G
    
72. 
    
73. myisam_repair_threads = 1
    
74. 
    
75. myisam_recover
    
76. 
    
77. innodb_additional_mem_pool_size = 16M
    
78. 
    
79. innodb_buffer_pool_size = 17G
    
80. 
    
81. innodb_data_file_path = ibdata1:10M:autoextend
    
82. 
    
83. innodb_file_per_table
    
84. skip-name-resolve
    
85. innodb_file_io_threads = 4
    
86. 
    
87. innodb_thread_concurrency = 16
    
88. 
    
89. sync-binlog=1
    
90. innodb_flush_log_at_trx_commit = 1
    
91. 
    
92. innodb_log_buffer_size = 8M
    
93. 
    
94. innodb_log_file_size = 1G
    
95. 
    
96. innodb_log_files_in_group = 3
    
97. 
    
98. innodb_max_dirty_pages_pct = 90
    
99. 
    
100. innodb_flush_method=O_DIRECT
     
101. 
     
102. innodb_lock_wait_timeout = 120
     
103. 
     
104. 
     
105. [mysqldump]
     
106. quick
     
107. 
     
108. max_allowed_packet = 16M
     
109. 
     
110. [mysql]
     
111. no-auto-rehash
     
112. 
     
113. [myisamchk]
     
114. key_buffer_size = 512M
     
115. sort_buffer_size = 512M
     
116. read_buffer = 8M
     
117. write_buffer = 8M
     
118. 
     
119. [mysqlhotcopy]
     
120. interactive-timeout
     
121. 
     
122. [mysqld_safe]
     
123. open-files-limit = 81920
     

复制代码

cenalulu

Memory的Usage 不说明问题。
关键是SWAP的使用率是不是很高。 目前也就用了1G的样子（不过还是有少量换出）。

楼主top看下MySQL的实际占用内存是多少。 目前这个配置在20G以内的占用属于正常情况。

PS： 是独立的数据库服务器么？ 有其他占内存的应用不？

devilkin0312

配置：
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 17G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table
skip-name-resolve
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
sync-binlog=1
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1G

个人建议：
   1.这应该核心的db 了，配置参数分类放还是随便放，交叉放，为了便于管理，有个规范好点。在innodb的配置中读到skip-name-resolve，，，
   2.正如楼主担心的内存问题，我的感觉这份配置文件就看着网上各种优化手册改的，后续也许有更多的问题你担心，因为对系统到底使用多少内存，没底。
   3.具体的参数：innodb_buffer_pool_size = 17G  既然都这么大了，innodb_additional_mem_pool_size 为何是16M？额外的缓存不重要？
      innodb_log_buffer_size = 8M  innodb_log_file_size = 1G  innodb的undo log 对性能的影响一定比想象中大。记得没错的话官方配置文件都有建议log buffer多大合适。
      。。。。

小版主杀手

不会挂的 还好 可以在检查下大数据表的情况，定期做好处理

yaya888

回复 2# cenalulu
top查看MySQL实际占用内存93%
是独立的数据库服务器，没有其他应用。
采用master-slave结构，这台是master。
slave服务器采用性能一般的SATA硬盘。


   

yaya888

补充SHOW INNODB STATUS信息：

1. =====================================
   
2. 120612 13:22:50 INNODB MONITOR OUTPUT
   
3. =====================================
   
4. Per second averages calculated from the last 32 seconds
   
5. ----------
   
6. SEMAPHORES
   
7. ----------
   
8. OS WAIT ARRAY INFO: reservation count 54008112, signal count 50453824
   
9. Mutex spin waits 0, rounds 10127967236, OS waits 12747231
   
10. RW-shared spins 164912833, OS waits 15844113; RW-excl spins 1613239184, OS waits 21422102
    
11. --------
    
12. FILE I/O
    
13. --------
    
14. I/O thread 0 state: waiting for i/o request (insert buffer thread)
    
15. I/O thread 1 state: waiting for i/o request (log thread)
    
16. I/O thread 2 state: waiting for i/o request (read thread)
    
17. I/O thread 3 state: waiting for i/o request (write thread)
    
18. Pending normal aio reads: 0, aio writes: 0,
    
19. ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    
20. Pending flushes (fsync) log: 0; buffer pool: 0
    
21. 18328067 OS file reads, 209359193 OS file writes, 106585251 OS fsyncs
    
22. 1.50 reads/s, 19456 avg bytes/read, 5.66 writes/s, 5.53 fsyncs/s
    
23. -------------------------------------
    
24. INSERT BUFFER AND ADAPTIVE HASH INDEX
    
25. -------------------------------------
    
26. Ibuf: size 1, free list len 1203, seg size 1205,
    
27. 3370447 inserts, 3370447 merged recs, 371552 merges
    
28. Hash table size 38249683, node heap has 128616 buffer(s)
    
29. 8930.85 hash searches/s, 9807.01 non-hash searches/s
    
30. ---
    
31. LOG
    
32. ---
    
33. Log sequence number 481 1846483500
    
34. Log flushed up to   481 1846483500
    
35. Last checkpoint at  481 516827519
    
36. 0 pending log writes, 0 pending chkp writes
    
37. 86001520 log i/o's done, 4.91 log i/o's/second
    
38. ----------------------
    
39. BUFFER POOL AND MEMORY
    
40. ----------------------
    
41. Total memory allocated 21189269114; in additional pool allocated 16777216
    
42. Dictionary memory allocated 5603384
    
43. Buffer pool size   1179648
    
44. Free buffers       0
    
45. Database pages     1051032
    
46. Modified db pages  194290
    
47. Pending reads 0
    
48. Pending writes: LRU 0, flush list 0, single page 0
    
49. Pages read 44852084, created 33258919, written 314446357
    
50. 1.78 reads/s, 17.25 creates/s, 24.00 writes/s
    
51. Buffer pool hit rate 1000 / 1000
    
52. --------------
    
53. ROW OPERATIONS
    
54. --------------
    
55. 0 queries inside InnoDB, 0 queries in queue
    
56. 12 read views open inside InnoDB
    
57. Main thread process no. 11429, id 1190848832, state: sleeping
    
58. Number of rows inserted 2712616480, updated 958817341, deleted 2650670338, read 1975317074980
    
59. 712.04 inserts/s, 641.17 updates/s, 658.92 deletes/s, 6711.63 reads/s
    

复制代码

yaya888

回复 3# devilkin0312

补充SHOW INNODB STATUS信息，麻烦指点一下。
   

chinafenghao

回复 7# yaya888

你的这个情况是比较正常的，一个是你数据量应该是接近18G或者大于18G，还有就是估计是某些大的查询或者存储过程涉及了非常多的记录，这种查询一多，内存占用就高。不过你不用太担心，因为你是ssd，就算写到swap问题也不大，系统也不会挂。
要优化的话，可以考虑优化一些返回数据量过大的sql，或者增加机器内存，内存条也不贵。


   

ruochen

linux下的内存使用原则，Lz还没有弄清楚

具体瓶颈在哪里，上面几个同学已经有提示