MySQL Startup error troubleshooting

In general, MySQL startup error is still easy to troubleshoot, but today we have to say a little bit of the situation. Get a server, after the installation of MySQL to start, the startup error is as follows:

There's a reunion saying, dude, you're not. The buffer pool setting is too large to set the 96G memory. This obviously indicates that memory cannot be allocated. If that's the case, it's not here to share, haha.

My server memory is 128G. Such as:

Server memory Usage:

So the question is, since there is so much memory left, why is the prompt unable to allocate memory?? What do you think of children's shoes?

1. Do you think there are a few memory broken first? So Yun-dimensional classmates have checked, give me feedback is hardware everything normal.

2. The MySQL configuration parameters have been checked again, no problem has been found, the online has been the use of these parameters.

3. Copy the file to another machine, another server can start normally (2 machine hardware configuration is consistent).

So if you troubleshoot a hardware problem, MySQL configuration problem, then only the operating system kernel parameters are configured. Then the two servers were compared, and finally found a kernel parameter inconsistency.

MySQL startup normal server parameter value is 0, and MySQL boot error This server value is 2.

So here's the question, what the hell is this argument? The MySQL failed to allocate memory, resulting in a failure to start. After querying the information to know Vm.overcommit_memory is what ghost.

Vm.overcommit_memory

The default value is: 0
From the kernel documentation, this parameter has three values, namely:
0: When the user space requests more memory, the kernel attempts to estimate the remaining available memory.
1: When this parameter is set to 1 o'clock, the kernel allows excessive use of memory until it is exhausted, mainly for scientific calculations.
2: When set this parameter value is 2 o'clock, the kernel will use an algorithm that never overuse memory, that is, the whole memory address space of the system cannot exceed swap+50% 's 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 the vm.overcommit_memory=2 is in the same condition.

So let's take a look at the total memory address cannot exceed the number. In fact, it can be viewed directly.

3306 Cat /proc/meminfo | grep -i commitcommitlimit:    70144396  kbcommitted_as:     135196  3306

You can see how it looks in 70G by looking at it. So how is this calculated? This is one of the formulas mentioned above. swap+50% the RAM value, the 50% parameter setting is set in the Overcommit_ratio.

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

 [[email protected] 3306 ]# cat /proc/meminfo | grep   Memtotalmemtotal:  132096808   kb[[email protected]  

 [[email protected] 3306 ]# free -k Total used  free 132096808
       1583944  0  10240  133220 -/+ Buffers/cache : 1440484  swap:  4095992  0  4095992  

3306 cat /proc/sys/vm/3306

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. In fact, the buffer pool can only be set to 57G after testing.

Finally look at the total virtual memory situation:

Commitlimit: Maximum available virtual memory

Committed_as: Virtual memory is used

3306 Cat /proc/meminfo | grep -i commitcommitlimit:    70144396  kbcommitted_as:   65539208 Kb

Then if you restore the kernel parameter vm.overcommit_memory to the default value of 0, you will not be constrained.

Echo 0 >/proc/sys/vm/overcommit_memory

Resources:

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

Summarize:

Having said so much, why modify the value of the kernel parameter vm.overcommit_memory? This is because this server ran past the Greenplum database, get my hands on the time not to reload the system, then it is recommended that if the machine has been run before the other business, then the insurance method is to re-install the system, and then deploy their own business, or there will really be inexplicable problems.

MySQL Startup error troubleshooting