innodb设置：
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 80M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
#innodb_flush_method = O_DIRECT
查看innodb状态变量：
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_restore_at_startup | 0 |
| innodb_buffer_pool_shm_checksum | ON |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 8589934592 |
| innodb_change_buffering | all |
查看innodb status:
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 8824815616; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 141610832 (141606616 + 4216)
Page hash 8851208 (buffer pool 0 only)
Dictionary cache 35440940 (35403184 + 37756)
File system 126232 (82672 + 43560)
Lock system 21250944 (21250568 + 376)
Recovery system 0 (0 + 0)
Dictionary memory allocated 37756
Buffer pool size 524287
Buffer pool size, bytes 8589918208
Free buffers 524032
Database pages 255
mysql日志：
140807 16:44:54 InnoDB: Initializing buffer pool, size = 8.0G
140807 16:44:55 InnoDB: Completed initialization of buffer pool
140807 16:44:55 InnoDB: highest supported file format is Barracuda.
140807 16:44:55 InnoDB: Waiting for the background threads to start
140807 16:44:56 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 41875501
从几个方面来看,innodb_buffer_pool_size都是8G，可是为啥TOP内存只使用了3G呢：
top - 17:07:25 up 1 day, 8:51, 7 users, load average: 0.00, 0.00, 0.00
Tasks: 256 total, 1 running, 255 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16424784k total, 2991160k used, 13433624k free, 176528k buffers
Swap: 8191992k total, 0k used, 8191992k free, 1019024k cached
7 个解决方案
#1
内存不是一下子都分配的，所以会出现你说的那个情况
#2
好如果是这样的话，到真正使用分配时，内存不足了，是不是MySQL就出错了
#3
经测试，真的是这样的，到了MYSQL真正想用物理内存时就会出错，但是这里也有个很奇妙的地方就是Linux下的OOM Killer
假如我一个程序A已经运行，并且分配了22G内存（机器配置是物理内存16G+SWAP 8G），但是没有真正的映射到物理内存，这是MySQL启动，innodb_buffer_pool_size=8G，启动正常，然后程序A开始实际分配物理内存，一下子只剩下2G SWAP内存了，这时MYSQL也开始实际使用内存，因为只有2G SWAP，所以性能很差，再超过2G时，内存耗尽，OOM Killer开始杀进程，怎么杀呢，谁占用内存多杀谁，于是将进程A杀掉了，内存一下子回来了，MySQL不会退出。但是一般系统中MySQL都是分配内存最大的，所以经常性的是MySQL被杀掉
Linux OOM Killer可参见： 理解和配置 Linux 下的 OOM Killer
#4
你设置 的大小是最大的情况实际的是运行分配，是不一次分配8G的
#5
“然后程序A开始实际分配物理内存，一下子只剩下2G SWAP内存了，这时MYSQL也开始实际使用内存，因为只有2G SWAP，所以性能很差，再超过2G时，内存耗尽，OOM Killer开始杀进程，怎么杀呢，谁占用内存多杀谁，于是将进程A杀掉了，内存一下子回来了，MySQL不会退出。但是一般系统中MySQL都是分配内存最大的，所以经常性的是MySQL被杀掉”
你的程序A和mysql数据库放在一台服务器上面？
你的程序A和mysql数据库放在一台服务器上面？
#6
the article you referred to is misleading...OOM actually has a heuristic algorithm based upon weight. So, the statement of "always kill the process that consumes most of memory" is not accurate:
The answer lies inside mm/oom_kill.c of the Linux source code. This C code represents the so-called OOM killer of the Linux kernel. The function badness() give a score to each existing processes. The one with highest score will be the victim. The criteria are:
#1, VM size. This is not the sum of all allocated pages, but the sum of the size of all VMAs owned by the process. The bigger the VM size, the higher the score.
#2, Related to #1, the VM size of the process's children are important too. The VM size is cumulative if a process has one or more children.
#3, Processes with task priorities smaller than zero (niced processes) get more points.
#4, Superuser processes are important, by assumption; thus they have their scores reduced.
#5, Process runtime. The longer it runs, the lower the score.
#6, Processes that perform direct hardware access are more immune.
#7, The swapper (pid 0) and init (pid 1) processes, as well as any kernel threads immune from the list of potential victims.
The process with the biggest score "wins" the election and the OOM killer will kill it very soon.
#7
没有用到自然不会去申请占用
#1
内存不是一下子都分配的，所以会出现你说的那个情况
#2
好如果是这样的话，到真正使用分配时，内存不足了，是不是MySQL就出错了
#3
经测试，真的是这样的，到了MYSQL真正想用物理内存时就会出错，但是这里也有个很奇妙的地方就是Linux下的OOM Killer
假如我一个程序A已经运行，并且分配了22G内存（机器配置是物理内存16G+SWAP 8G），但是没有真正的映射到物理内存，这是MySQL启动，innodb_buffer_pool_size=8G，启动正常，然后程序A开始实际分配物理内存，一下子只剩下2G SWAP内存了，这时MYSQL也开始实际使用内存，因为只有2G SWAP，所以性能很差，再超过2G时，内存耗尽，OOM Killer开始杀进程，怎么杀呢，谁占用内存多杀谁，于是将进程A杀掉了，内存一下子回来了，MySQL不会退出。但是一般系统中MySQL都是分配内存最大的，所以经常性的是MySQL被杀掉
Linux OOM Killer可参见： 理解和配置 Linux 下的 OOM Killer
#4
你设置 的大小是最大的情况实际的是运行分配，是不一次分配8G的
#5
“然后程序A开始实际分配物理内存，一下子只剩下2G SWAP内存了，这时MYSQL也开始实际使用内存，因为只有2G SWAP，所以性能很差，再超过2G时，内存耗尽，OOM Killer开始杀进程，怎么杀呢，谁占用内存多杀谁，于是将进程A杀掉了，内存一下子回来了，MySQL不会退出。但是一般系统中MySQL都是分配内存最大的，所以经常性的是MySQL被杀掉”
你的程序A和mysql数据库放在一台服务器上面？
你的程序A和mysql数据库放在一台服务器上面？
#6
the article you referred to is misleading...OOM actually has a heuristic algorithm based upon weight. So, the statement of "always kill the process that consumes most of memory" is not accurate:
The answer lies inside mm/oom_kill.c of the Linux source code. This C code represents the so-called OOM killer of the Linux kernel. The function badness() give a score to each existing processes. The one with highest score will be the victim. The criteria are:
#1, VM size. This is not the sum of all allocated pages, but the sum of the size of all VMAs owned by the process. The bigger the VM size, the higher the score.
#2, Related to #1, the VM size of the process's children are important too. The VM size is cumulative if a process has one or more children.
#3, Processes with task priorities smaller than zero (niced processes) get more points.
#4, Superuser processes are important, by assumption; thus they have their scores reduced.
#5, Process runtime. The longer it runs, the lower the score.
#6, Processes that perform direct hardware access are more immune.
#7, The swapper (pid 0) and init (pid 1) processes, as well as any kernel threads immune from the list of potential victims.
The process with the biggest score "wins" the election and the OOM killer will kill it very soon.
#7
没有用到自然不会去申请占用