MySQL startup error How to solve MySQL startup error resolution

Under normal circumstances MySQL startup error is very easy to troubleshoot, but today we have to say that the situation is not general. Get a server, install MySQL after the boot, startup error is as follows.

Some classmates say, dude, are you a buffer pool setting is too large to have 96G of RAM. This obviously indicates that memory cannot be allocated. If this is true, it is not here to share, haha.

My server memory is 128G. The following figure:

Server memory Usage:

so the question is, since there is so much memory left, why is the hint unable to allocate memory?

1. The first thought is that there will be a few memory bad? So Yun-dimensional classmate carried out a check, give me feedback is the hardware all normal.

2. The MySQL configuration parameters have been checked again, did not find any problems, the line has been using these parameters.

3. Another copy of the file to another machine, the other server can be started normally (2 machine hardware configuration consistent).

Then if the hardware problem is eliminated, MySQL configuration problem, then the rest of the operating system is only the kernel parameter configuration. So the two servers were compared, and finally found a kernel parameter inconsistency.

Copy the code as follows:

Vm.overcommit_memory

MySQL starts the normal server to change the value of the parameter is 0, and the MySQL startup error of this server the value is 2.

so the question is, what the hell is this parameter?

The MySQL failed to allocate memory, which eventually prevented it from starting. After the inquiry information to know what the ghost of Vm.overcommit_memory.

Vm.overcommit_memory

The default value is: 0

As you can see from the kernel documentation, this parameter has three values, respectively:

0: When user space requests more memory, the kernel tries to estimate the remaining available memory.

1: When the value of this parameter is 1 o'clock, the kernel allows for excessive use of memory until it is exhausted, mainly for scientific calculations.

2: When the value of this parameter is 2 o'clock, the kernel will use an algorithm that never overuse memory, that is, the entire memory address space of the system can not exceed the swap+50% RAM value, and the 50% parameter setting is set in Overcommit_ratio.

Vm.overcommit_ratio

The default value is: 50

This parameter value is only valid if it is vm.overcommit_memory=2.

So let's take a look at the total memory address can not be more than how much. In fact, it can be viewed directly.

[Root@yayundeng 3306]# cat/proc/meminfo |grep-i commit
commitlimit:  70144396 kB
committed_as:   135196 KB

You can tell by looking at the 70G. So how is this calculated? This is a formula mentioned above. swap+50% RAM value, the 50% parameter setting is set in Overcommit_ratio.

Total virtual memory = Free physical memory x percent + swap partition

[Root@yayundeng 3306]# Cat/proc/meminfo | grep memtotal
memtotal:    132096808 kB
[Root@yayundeng 3306]# 
[Root@yayundeng 3306]#
       -free-k Total    used    free   shared  buffers   cached
Mem:   132096808  1583944 130512864     0   10240   133220
-/+ buffers/cache:  1440484 130656324
Swap:   4095992     0  4095992
[Root@yayundeng 3306]# cat/proc/sys/vm/overcommit_ratio 
50

Total virtual Memory =132096808 * 50% + 4095992= 70144396 KB

The final result is that the buffer pool cannot exceed 70144396 kB-135196 kb=70009200 kb=66g. Actually tested, the buffer pool can only be set to 57G.

Finally, look at the total virtual memory situation:

Commitlimit: Maximum available virtual memory

Committed_as: Virtual memory is used

[Root@yayundeng 3306]# cat/proc/meminfo |grep-i commit
commitlimit:  70144396 kB
committed_as:  65539208 KB

If you restore the kernel parameter vm.overcommit_memory to the default value of 0, then it will not be constrained.

Copy the code as follows:

echo 0 >/proc/sys/vm/overcommit_memory

reference materials:

Http://serverfault.com/questions/606185/how-does-vm-overcommit-memory-work
http://linuxperf.com/?p=102

Summary:

Say so much, then why do you want to change the value of kernel parameter vm.overcommit_memory? This is because this server ran over the Greenplum database before, when I got my hand, I did not reload the system, then it is recommended that if you get the machine ran before other business, then the insurance method or reload the system, and then deploy their business, otherwise there will really be inexplicable problems.