How to estimate the memory consumption when MySQL, how to calculate the formula?

Often people ask how to estimate the memory consumption when configuring MySQL. So what formula should be used to calculate it?

The reason to care about how memory is used is understandable. Configuring a MySQL server to use too little memory can cause performance to be suboptimal, and if too much memory is configured, it can crash, fail to execute the query, or cause the swap operation to be severely slowed. Under the current 32-bit platform, it is still possible to run out of all the address space, so you need to monitor it.

That being said, I don't think it's a good solution to find a formula that calculates the use of memory. The reason for this is that the formula is now very complex, and more importantly, the value that is calculated by it is simply that "theory may" is not really a value to be consumed. In fact, regular servers with 8GB of memory can often run to the maximum theoretical value of--100GB or even higher. In addition, you will easily not use the "overage factor"-it actually relies on the application and configuration. Some applications may require 10% of theoretical memory and some only need 1%.

So what can we do? First, take a look at the global buffers that need to be allocated at startup and always present--key_buffer_size,innodb_buffer_pool_size,innodb_additional_memory_ Pool_size,innodb_log_buffer_size,query_cache_size. If you use a large number of MyISAM tables, you can also increase the operating system cache space so that MySQL can also be used. Adding these to the memory values required by the operating system and the application may require adding 32MB or more of memory to MySQL server code and a variety of small static buffers. These are the memory you need to consider when the MySQL server starts up. The rest of the memory is used for the connection. For example, a server with 8GB of memory, may listen to all services to use 6GB of memory, the remaining 2GB of memory is left to use for the thread.

Database programming tips for "MySQL server memory usage":

Strong> Substrb

SUBSTRB returns the specified part of a string. 2881064151 This function is very similar to substr, except that the start and length two parameters of the function are in bytes, not according to the characters. This difference is only meaningful if you use a variety of different character sets.

Each thread that connects to the MySQL server needs to have its own buffer. You probably need to allocate 256k at once, even when threads are idle-they use the default thread stacks, network caches, and so on. After the transaction begins, you need to add more space. Running a smaller query may only add a small amount of memory to the specified thread, but if you do complex operations such as scanning, sorting, or requiring temporary tables, you need to allocate approximately read_buffer_size,sort_buffer_size,read_rnd_buffer _size,tmp_table_size the size of the memory space. However, they are allocated only when they are needed and are released after those operations have been done. Some are immediately allocated as separate blocks, such as tmp_table_size, which can be as high as MySQL can allocate to the maximum memory space for this operation. Note that there is not a single point to consider here-it is possible to allocate multiple caches of the same type, for example, to handle subqueries. The memory usage of some special queries can be even greater-if you do a batch insert on the MyISAM table, you need to allocate bulk_insert_buffer_size-sized memory. You need to allocate myisam_sort_buffer_size-sized memory when you execute the ALTER table,optimize table,repair table command.

Only the memory consumption of a simple query OLTP application is often less than 1MB per thread using the default buffering, unless you need to use a complex query to increase the buffer size of each thread. Using a 1MB buffer to sort 10 rows of records and buffering with 16MB is almost as fast (actually 16MB may be slower, but that's something else).

Also, find out the peak memory consumption of MySQL server. This makes it easy to calculate the memory, file cache, and other applications required by the operating system. In a 32-bit environment, you also need to take into account the 32-bit limit, which limits the value of "mysqld" to about 2.5g (there are actually many other factors to consider). 2881064151 now run the "PS aux" command to view the value of the Vsz--mysql process allocated virtual memory. You can also look at the value of "resident memory", but I think it may not be useful because it will be smaller because of the exchange-it's not what you want to see. By monitoring the value of the memory change, you will know that you need to increase/decrease the current memory value.

There may be people who want to say that we want the server to guarantee that 100% will not run out of memory, regardless of the query and what kind of user you decide to use. Unfortunately, this is actually very unwise and impossible, because:

The following are few MySQL server memory requirements to consider

Each thread may need to allocate buffers more than once. Consider, for example, subqueries-each layer needs to have its own read_buffer,sort_buffer,tmp_table_size and so on.

Many variables may need to be reset in each connection. If developers want to set their own variable values to run certain queries, they cannot continue to use global values.

There may be multiple index caches. Multiple index caches may be created in order to match the execution of the query.

Memory is required for parsing queries and optimizations. The memory is usually small and can be ignored, but some queries require a lot of memory in this step, especially those that are designed to be more specific.

Stored procedures. Complex stored procedures can require a lot of memory.

Prepares query statements and cursors. A single link may have a lot of prepared statements and cursors. The number of them can finally be limited, but still consumes a lot of memory.

InnoDB table Cache. The Innnodb table has its own cache, which holds the metadata for each table accessed from the beginning. They have never been cleared, and if there are a lot of innodb tables, then this amount is very large. This means that users with CREATE TABLE permissions may run out of memory on the MySQL server.

Lao Zhang AC qq:2881064151

How to estimate the memory consumption when MySQL, how to calculate the formula?