MySQL server memory usage

Every so often people ask me the question how should they the estimate memory consumption by MySQL Server in given Configurati On. What is the formula they could use.

People often ask me how to estimate memory consumption when I configure MySQL. So what formula should be used to calculate it?

The reasons to worry about memory usage are quite. If You are configure MySQL Server so it uses too small amount of memory it'll likey perform. If You are however configure it so it consumes too much memory the It may is crashing, failing to execute queries or make Operati On to swap seriously slowing down. On now legacy 32bit platforms your could also run out of "so" had to be watched as.
Having said so, I does not have looking for the secret fomula to compute your possible memory usage are the right approach T o This problem. The reasons Are-this formula is very complex nowadays and what is even-more important "theoretically possible" maximum I T provides have does not have the real memory consumptions. In fact typical server with 8GB of memory would often run with maximum theoretical memory to 100GB or more. Furthermore there is no easy "overcommit factor" for you can use-it really depends on application and configuration. Some applications would drive server to 10% the theoretical memory consumptions only to 1%.

The reason to care about how memory is used is understandable. Configuring a MySQL server with too little memory can result in performance not being optimal, and if too much memory is configured, it can cause crashes, inability to execute queries, or slow exchange operations. Under today's 32-bit platform, it is still possible to run out of all the address space and therefore need to be monitored.
That said, I don't think finding a formula to calculate the memory usage can solve the problem well. The reason--now that the formula is complicated, and more importantly, the value that is calculated by it is just "theoretical" is not really a value to be consumed. In fact, regular servers with 8GB of memory often run to the maximum theoretical value-100GB or higher. In addition, you will easily not use the "excess factor"-it actually relies on the application and configuration. Some applications may require 10% of theoretical memory, and some only need 1%.

So what could your do instead? The "Take a look" at global buffers which are allocated in start and always Where-these are key_buffer_size Er_pool_size, Innodb_additional_memory_pool_size, Innodb_log_buffer_size, Query_cache_size. If you are using MyISAM seriously can also add the size of Operation System cache you would like MySQL to use for your Table. Take This number add to it number of memory Operation System and other applications need, add might is 32MB for MySQL The Server code and various small static buffers. This is memory which your can consider used when you just start MySQL Server. The rest of memory is available for connections. For Exampe with 8GB server your might have everything listed adding up to 6GB, so and have 2GB left for your threads.

So, what can we do? First, look at the global buffers that need to be allocated and always exist at startup--Key_buffer_size, Innodb_buffer_pool_size, Innodb_additional_memory_pool_size, Innodb_log_buffer_size, Query_cache_size. If you use MyISAM tables heavily, you can also increase the operating system's cache space so that MySQL can be used. Adding these to the memory values required by the operating system and applications may require a 32MB or more memory to be added to the MySQL server code as well as various small static buffers. These are the memory you need to consider when the MySQL server starts. Other remaining memory is used for the connection. For example, a server with 8GB of memory may listen to all the services and use 6GB of memory, while the remaining 2GB memory is left to the thread.

Each thread connecting to MySQL server would needs its own buffers. About 256K is allocated at once even if thread are Idle-they are used by default thread stack, net buffer etc. If transaction is started some, can add up. Running small queries might only barely increase memory-for consumption thread, given if table would however perform x operations such as full table scans, sorts, or need temporary tables as much as read_buffer_size, sort_buffer_size, read _rnd_buffer_size, tmp_table_size of memory might be allocated. But They are only allocated upon the need and freed once given stage of query are done. Some of them are allocated as single chunk in once others, for example tmp_table_size is rather maximum amount of memory M Ysql'll allocate for this operation. Note it are more complicated than once could think-multiple buffers of the same type might be allocated for Exampe to Handl E subqueries. For some special queries memory usage might is even larger-bulk MaY allocate bulk_insert_buffer_size bytes of memory if done to MyISAM tables. Myisam_sort_buffer_size used for ALTER table, OPTIMIZE table, REPAIR table commands.

Each thread that connects to the MySQL server needs to have its own buffer. You probably need to allocate 256K immediately, even when threads are idle-they use the default thread stack, the network cache, and so on. After the transaction starts, you need to add more space. Running a smaller query may only add a small amount of memory consumption to the specified thread, but if you do complex operations on the datasheet, 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 size of memory space. But they are only allocated when they are needed, and are released after those operations have been completed. Some are immediately allocated to separate chunks, such as tmp_table_size, which may be as high as the maximum memory space that MySQL can allocate to this operation. Note that there is not a single point to consider here-you may be allocating multiple caches of the same type, such as for processing subqueries. Some special queries may have greater memory usage-if you do a mass insert on the MyISAM table, you need to allocate bulk_insert_buffer_size size memory. Executes ALTER table, OPTIMIZE table, and REPAIR the table command to allocate myisam_sort_buffer_size size memory.

For OLTP applications with simple queries memory consumption are often less than 1MB per thread with default buffers, and Y ou really do not need to increase per thread buffers unless for you have complex. Sorting rows would be as fast and 1MB sort buffer as with 16MB (actually 16MB might being even slower but it are other stor Y).

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

Another approach You may take are to come up with amount of the memory you want the MySQL Server to consume at peak. This can is easily computed by the memory needed for the OS, File Cache and other applications. For 32bit envinronment your also should keep 32bit limits into account and probably limit "mysqld" size to about 2.5GB (Exa CT number depens on a lot of factors). Now your can use "PS aux" to the vsz-virtual Memory allocated by MySQL process. can also look at the "resident Memory" but I find it less helpful as it could down because of swapping-not what you would Like to the. Monitor how the value changes so your know memory requirements with current settings and Increase/decrease values Appropria Tely.