MySQL startup error solution and mysql error Solution

MySQL startup error solution and mysql error Solution

Generally, it is easy to troubleshoot mysql startup errors. Get a server and start mysql after installation. The startup error is as follows:

Some people may say that if you set the buffer pool too large, you have set the 96 GB memory. This obviously prompts that the memory cannot be allocated. If so, I will not share it here, haha.

My server memory is 128 GB. For example:

Server memory usage:

So the question is, since there is so much memory left, why does it prompt that the memory cannot be allocated ??. What do you think of kids shoes?

1. First of all, how many memory entries are broken? So the O & M personnel checked and gave me feedback that the hardware was normal.

2. Check the mysql configuration parameters again without any problems. These parameters are always used online.

3. copy the file to another server. The other server can be started normally (the hardware configurations of the two servers are consistent ).

If the hardware and mysql configuration problems are eliminated, only the kernel parameters of the operating system are configured. So we compared the two servers and finally found a Kernel Parameter inconsistency.

Copy codeThe Code is as follows: vm. overcommit_memory

When mysql is started normally, the parameter value is changed to 0, while if mysql is started incorrectly, the value of this server is 2.

So the question is, what is this parameter? It will make mysql fail to allocate memory, and thus cannot be started. After querying the information, I know what the vm. overcommit_memory is.

Vm. overcommit_memory

The default value is 0.
According to the kernel documentation, this parameter has three values:
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, the kernel allows excessive memory usage until it is used up, mainly for scientific computing.
2: When this parameter is set to 2, the kernel will use an algorithm that never overuses the memory, that is, the entire memory address space of the system cannot exceed the RAM value of swap + 50%, 50% the parameter is set in overcommit_ratio.

Vm. overcommit_ratio
Default Value: 50

This parameter takes effect only when vm. overcommit_memory = 2.

Let's take a look at the total memory address. In fact, it can be viewed directly.

[root@yayundeng 3306]# cat /proc/meminfo |grep -i commitCommitLimit:  70144396 kBCommitted_AS:   135196 kB[root@yayundeng 3306]# 

You can see what it looks like at 70 GB. How is this calculated? This is a formula mentioned above. The RAM value of swap + 50%. The 50% parameter is set in overcommit_ratio.

Total virtual memory = available physical memory x percentage + swap Partition

[root@yayundeng 3306]# cat /proc/meminfo | grep MemTotalMemTotal:    132096808 kB[root@yayundeng 3306]# [root@yayundeng 3306]# free -k       total    used    free   shared  buffers   cachedMem:   132096808  1583944 130512864     0   10240   133220-/+ buffers/cache:  1440484 130656324Swap:   4095992     0  4095992[root@yayundeng 3306]# cat /proc/sys/vm/overcommit_ratio 50[root@yayundeng 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, after testing, the buffer pool can only be set to 57 GB.

Finally, let's look at the total virtual memory:

CommitLimit: maximum available virtual memory

Committed_AS: Virtual Memory Used

[root@yayundeng 3306]# cat /proc/meminfo |grep -i commitCommitLimit:  70144396 kBCommitted_AS:  65539208 kB

If the Kernel Parameter vm. overcommit_memory is restored to the default value 0, no constraints will be imposed.

Copy codeThe Code is as follows: echo 0>/proc/sys/vm/overcommit_memory

References:

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

Summary:

So many times, why modify the value of Kernel Parameter vm. overcommit_memory? This is because this server ran the GreenPlum database before and did not reinstall the system when I got it. We recommend that you run other services if you got the machine before, so the insurance method is to reinstall the system and deploy your own business, otherwise there will be some inexplicable problems.