Shulou(Shulou.com)06/01 Report--

Editor to share with you a sample analysis of memory problems in MYSQL. I hope you will gain something after reading this article. Let's discuss it together.

First of all, the memory in a MYSQL system is roughly divided into, here we only discuss servers that provide only MYSQL services.

1 the system uses memory, including kernel operation, system cache, etc.

2 MYSQL's own system fixed memory, innodb_buffer_pool query cache, etc.

3 MYSQL workload, such as join, buffers join buffer sort buffer for each query, etc.

4 memory used by MYSQL replication and log such as binary log cache, replicatiton connection, etc.

Problem 1 OOM caused by memory leak or insufficient memory

Generally speaking, it is not the right thing for DB to judge whether there is a memory leak, but sometimes it does no harm to know a little more

Formula for memory leaks: the value of centos 6.x used-buffers-cached is compared with the value of used

Compare the value of centos 7.x available-free with the value of buff/cache

As you can see, according to the above formula 3019-2200, there is no significant difference compared with buff/cache 2684, indicating that there is no memory leak. generally speaking, no more than 10% is considered to be a sign of memory leakage compared with buffer/cache.

Check SWAP, there is a debate, some enterprises directly disable SWAP, such enterprises will generally allocate a larger amount of memory to the system, if the memory is exhausted, the system OOM will not be afraid of the KILL process that consumes the most resources. Another part of enterprises still use SWAP to fear OOM, but the downside is that if SWAP analog memory is used, the performance of MYSQL will deteriorate sharply, so it depends on how you choose to use SWAP.

The main reason for the lack of memory, excluding the concurrency of the system or the lack of resources, in most cases depends on whether the statement has been optimized, or there are fewer problems.

And because many systems are not self-developed, so generally encounter such a problem, in addition to the unit can optimize the system, the high probability is to add memory.

Question 2 exactly how to set up my innodb_buffer_pool_size

Most DB people probably think that this is not a problem. I will just set the innodb_buffer_pool_size according to 60-80% of the size of the machine you give me.

In fact, this has been set up. First of all, we need to know how much work our machine can carry. If it exceeds the workload that the machine can handle, we need to talk to the relevant personnel.

Instead of talking later, although you can mend it, in the leader's mind, you belong to hindsight, not prophecy.

In addition, if the system has been running for some time, how do we know that innodb_buffer_pool_size is reasonable?

SELECT engine

-> count (*) as TABLES

-> concat (round (sum (table_rows) / 1000000 table_rows 2),'M') rows

-> concat (round (sum (data_length) / (1024 / 1024 / 1024), 2),'G') DATA

-> concat (round (sum (index_length) / (1024 / 1024 / 1024), 2),'G') idx

-> concat (round (sum (data_length+index_length) / (1024 / 1024 / 1024), 2),'G') total_size

-> round (sum (index_length) / sum (data_length), 2) idxfrac

-> FROM information_schema.TABLES

-> WHERE table_schema not in ('mysql',' performance_schema', 'information_schema')

-> GROUP BY engine

-> ORDER BY sum (data_length+index_length) DESC LIMIT 10

Through the above query, we can see the approximate number of tables in the current system, and some suggestions say that an appropriate amount of innodb_buffer_pool_size should be obtained according to the overall amount of data.

Some of the suggestions below give a formula to calculate the order of magnitude that your innodb_buffer_pool_size should be set by using the formula in the figure below.

SELECT CEILING (Total_InnoDB_Bytes*1.6/POWER (1024)) RIBPS_GB FROM (SELECT SUM (data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A

At the same time, you can also pay attention to the innodb_buffer_pool_reads in a period of system status to see the amount of data the system reads from disk at a specified (business busy) time. If you often read a lot, and your innodb_buffer_pool_size O system is not very often, it is recommended to increase innodb_buffer_pool_size to meet the needs of the system as much as possible.

Question 3, my innodb_buffer_pool_size is set high, but the query is still slow.

After troubleshooting performance problems caused by improper innodb_buffer_pool_size settings, you need to pay attention to the following buffer

Read_buffer_size

Read_rnd_buffer_size

Sort_buffer_size

Join_buffer_size

The above BUFFER can solve the following problems

1 when there is no suitable index in the queried table, or if the index cannot be used, a full table scan and a full index scan will be carried out. In this case, the data will be sequentially read into read_buffer_size. When the read data is enough to be saved in read_buffer_size, the buffer data will be returned to the upper layer to speed up this kind of query. In general, such as primary key (ordered) or the extraction of ordered data related to dates, will be used.

2 if there are no sequential queries but a large number of random queries, and there is no index or valid index, the random data will be stored directly.

3 when multiple tables JOIN, in the absence of a valid index, in order to reduce the number of reads with the driven table, put the data that needs to be read into join_buffer to improve the efficiency of JOIN. If the JOIN_buffer_size is insufficient, the previously written data will be cleaned up after new data writing is needed, and if the data is also in use, it will clean up the data that is now in use. As a result, the query is slow and multiple visits to Ibank O are made.

4 Sort_buffer_size because MYSQL 5.x does not support reverse order, in addition, if there is no index, sorting is also necessary for filesort, and a large enough sort can reduce the interaction between the query and the disk during sorting, and solve it in memory, so for systems with many sorting operations and without any optimization, large sort_buffer_size is very useful.

So if you are being ravaged by junk SQL and cannot be changed, you can improve the memory settings in these locations, which may give you a moment's breath.

Finally, in addition to some things on the MYSQL memory, you can actually view the current memory situation through the SYS library or some tables in performance_schema to facilitate the adjustment of the current system.

For example, most MYSQL systems open performance_schema to collect system performance information, and through the collection of related information, you can see some detailed allocation of related memory.

Some of the current memory allocations are shown below

Of course, looking at the memory allocation in each buffer pool, or looking at show engine innodb status will be faster to see the relevant details.

In a word, the memory of MYSQL is not as simple as innodb_buffer_pool_size. With the update of the version, more analysis and view of memory information will be transferred to the related tables of sys library and preformance_schema library.

After reading this article, I believe you have some understanding of "sample Analysis of memory problems in MYSQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.