MySQL server memory usage _ MySQL

MySQL Server memory uses Every so often people ask me the question how shocould they estimate memory consumption by MySQL Server in given configuration. What is the formula they cocould use.

I am often asked how to estimate the memory consumption when configuring MySQL. So what formula should we use for calculation?

The reasons to worry about memory usage are quite understandable. if you configure MySQL Server so it uses too small amount of memory it will likey perform suboptimally. if you however configure it so it consumes too much memory it may be crashing, failing to execute queries or make operation to swap seriously slowing down. on now legacy 32bit platforms you cocould also run out of address space so that had to be watched as well.
Having said so, I do not think looking for the secret fomula to compute your possible memory usage is the right approach to this problem. the reasons are-this formula is very complex nowadays and what is even more important "theoretically possible" maximum it provides have nothing to do with real memory consumptions. in fact typical server with 8 GB of memory will often run with maximum theoretical memory usage of 100 GB or more. furthermore there is no easy "overcommit factor" you can use-it really depends on application and configuration. some applications will drive server to 10% of theoretical memory consumptions others only to 1%.

The reason for concern about memory usage is understandable. If the MySQL server is configured to use too little memory, it will lead to poor performance. if too much memory is configured, it will lead to a crash, the query cannot be executed, or the switching operation will seriously slow down. In the current 32-bit platform, it is still possible that all the address space is used up, so you need to monitor it.
Even so, I don't feel that finding the formula to calculate memory usage can solve this problem well. The reason is that the formula is complicated now. More importantly, the calculated value is just a "theoretical possibility" and is not the actually consumed value. In fact, regular servers with 8 GB of memory often run to the maximum theoretical value-GB or even higher. In addition, you will not easily use "excess factors"-it actually depends on applications and configurations. Some applications may need 10% of the theoretical memory, while some require only 1%.

So what coshould you do instead? First take a look at global buffers which are allocated at start and always where-these are key_buffer_size, innodb_buffer_pool_size, bytes, innodb_log_buffer_size, query_cache_size. if you're using MyISAM seriously you can also add the size of Operation System cache you wowould like MySQL to use for your table. take this number add to it number of memory Operation System and other applications need, add might be 32 MB more for MySQL Server code and various small static buffers. this is memory which you can consider used when you just start MySQL Server. the rest of memory is available for connections. for exampe with 8 GB server you might have everything listed adding up to 6 GB, so you have 2 GB left for your threads.

So what can we do? First, let's take a look at the global buffer that needs to be allocated and always exists at startup -- key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cac. 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. These are also added to the memory values required by the operating system and applications. you may need to add 32 MB or more memory to the MySQL server code and various small static buffers. These are the memory you need to consider when starting the MySQL server. The remaining memory is used for connection. For example, a server with 8 GB memory may use 6 GB memory to listen to all services, and the remaining 2 GB memory will be left for the thread to use.

Each thread connecting to MySQL server will needs its own buffers. about 256 K is allocated at once even if thread is idle-they are used by default thread stack, net buffer etc. if transaction is started some more space can add up. running small queries might only barely increase memory consumption for given thread, however if table will perform complex operations such as full table scans, sorts, or need temporary tables as much as read_buffer_size, large, 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 is done. some of them are allocated as single chunk at once others, for example tmp_table_size is rather maximum amount of memory MySQL will allocate for this operation. note it is more complicated than once may think-multiple buffers of the same type might be allocated for exampe to handle subqueries. for some special queries memory usage might be even larger-bulk inserts 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.