Upgrade to MySQL 5.7 to solve the partition problem.

Upgrade to MySQL 5.7 to solve the partition problem.

Preface

Some friends often ask, How is MySQL partition? Can I use it? Are there many bugs? I don't know why MySQL partitions have such an impression on the general public. However, in the impression of Inside Jun, the following is an example of the bug that has a big impact on partitions (it is hard to say that it is a bug). It is also the most common problem for our friends to consult Inside Jun partition. However, this bug has been fixed in version 5.7 (precisely, 5.7 supports Native Paritition ). It seems that there is another reason to upgrade to 5.7. To sum up, is it necessary to create a partition in the production environment every day?

Body

A month ago, Scott and his colleagues found that the company had a master of a MySQL MHA cluster (assuming the master machine is named hostA) which would be hung once every week (that is, MySQL crashed ), in a few weeks, MHA has been switched back and forth several times.

According to international practice, Scott looks up the problem in the following order:

1. Read MySQL error log first, and no exception was found.

2. Then I flipped through the Linux System Log File. As a result, I flipped through the following content:

Nov 26 13:05:38 hostA kernel: mysql invoked oom-killer: gfp_mask = 0x280da, order = 0, oom_adj = 0, oom_score_adj = 0

......

Nov 26 13:05:38 hostA kernel: Out of memory: Kill process 32271 (mysqld) score 976 or sacrifle ice child

Nov 26 13:05:38 hostA kernel: Killed process 32271, UID 496, (mysqld) total-vm: 83064212kB, anon-rss: 64204132kB, file-rss: 4544kB

The physical memory size of this machine is 62 GB. From the above logs, MySQL has indeed fully occupied it. MySQL innodb_buffer_pool = 31G on this machine. Scott thinks this is quite conservative, and we use the default values for various buffer_sizes. The number of user connections in MySQL OOM is 100 +,

There are no problems with these visual tests, but OOM still occurs, which is incredible. At that time, I felt that the memory was not enough. I did not find the specific cause. Later, the memory of 62G was added to 125 GB (innodb_buffer_pool_size increased to 64 GB, which is indeed quite conservative ), or OOM occurs.

Scott discovered an earlier problem on this machine at the beginning, because the maximum number of files opened in the system is not enough, so the xtrabackup backup on this machine is always unsuccessful, for specific reasons, please wait for Scott to sort out the more detailed process of xtrabackup backup. Then I checked the number of *. ibd files and *. frm files on the machine, and I was shocked (say, Inside is also scared ):

[UserA @ hostA mysql] $ sudo find.-name '*. ibd' | wc-l

169577

[UserA @ hostA mysql] $ sudo find.-name '*. frm' | wc-l

2534

That is to say, the machine actually has 0.17 million ibd files, but there are only 2534 tables. It is obvious that there are a large number of partitions in the partition table.

[UserA @ hostA mysql] $ sudo find.-name '* par *' | wc-l

1882

Scott carefully compared the differences between this machine and other non-problematic machines, and found that this machine has too many partitions, which is the only difference, this leaves Scott unable to doubt the problem caused by partitions.

Scott is still in accordance with international practice, the first time to check the official documentation of MySQL 5.6, no result... (Although the official documentation is not omnipotent, it is still the first reference for problems ). Go to the MySQL bugs page and search for the bug about partition... When I went to google, I found that some complicated websites wrote that the too many MySQL partitions caused the problem of memory depletion. However, the content I mentioned in this article is not very correct.

Finally, I read this article at the guidance of instructor Jiang:

Http://mysqlserverteam.com/innodb-native-partitioning-early-access/

The above is an article about InnoDB Native Partitioning written by the MySQL development team. In this article, the partition information is maintained at the MySQL Server layer (in. the InnoDB Engine layer does not know the concept of partitions. the InnoDB Engine layer treats each partition as a normal InnoDB table. When opening a partition table, multiple partitions are opened. Opening these partition tables is equivalent to opening an equivalent number of InnoDB tables, this requires more memory to store InnoDB table metadata and various cache and handler information related to ibd file opening. In MySQL 5.7, InnoDB introduced Native Partitioning, which moved the partition information from the Server layer to the InnoDB layer. Opening a partition table basically has the same memory overhead as opening an InnoDB table.

If we compare the amount of memory used when opening a single instance of this table, first using the old generic non-native partitioning, and then with InnoDB Native Partitioning we see the following:

One open instance of the table takes 49% less memory (111 MB vs 218 MB) with the current state of Native Partitioning support. with ten open instances of the table, we take up 90% less memory (113 MB vs. 1166 MB )!

It may take some time to upgrade to 5.7. At present, the number of partitions has been reduced to about 20 GB for 20 days, which also indicates that the number of partitions is too large.

This article permanently updates the link address: