6

At startup, MySQL will use some memory.

Some of the memory is pre-allocated for "key_buffer" and "innodb_buffer_pool_size" (and other things).

Is it possible to determine how much memory is pre-allocated (for data) and how much memory has been used?

Let me take an example to explain what I want to ask:

After I start MySQL server, it uses some memory as part of "key buffer". Say, I set key_buffer=1G. Maybe at startup, MySQL server has pre-allocated 200M as "key buffer". Then when users begin to do queries against this server. The server can only get extra 800M as "key buffer".

200 is a random number I pick. And apparently besides key_buffer, there are other things (such as innnodb buffer).

What I want to know is the exact size or the way to calculate it.

1 Answer 1

6

Yes, and you absolutely should! Don't set MySQL to have a potential maximum use more than about 80% of available memory.

MySQL has two types of buffers, global and per-connection. Maximum possible memory use is: global buffers + (connection buffers * max connections).

The mysqltuner.pl script will calculate this for you (http://mysqltuner.com/). Here are the values this script uses for calculating:

Global buffers:

key_buffer_size
max_tmp_table_size
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size
query_cache_size

Per-connection buffers:

read_buffer_size
read_rnd_buffer_size
sort_buffer_size
thread_stack
join_buffer_size

You can find how much memory is currently used from your Operating System.

4
  • It sounds like you're asking how much memory for global buffers is pre-allocated immediately on startup. I don't know this for sure, but my guess is close to zero, the buffers only have memory allocated when they are used (i.e. data is queried that needs caching). Commented Dec 28, 2012 at 17:34
  • I don't agree. I've found something here: mysqlab.net/knowledge/kb/detail/topic/informix/id/5004
    – Eagle
    Commented Jan 3, 2013 at 9:44
  • I don't think that's true, but I can't find any official documentation either way. MySQL definitely checks if there is enough memory for the buffer pool on startup, and will fail if there isn't. But I've seen plenty of servers not using as much memory as their maximum setting. Twitter and Percona both have patches to allow fully allocating the pool on startup. Commented Jan 4, 2013 at 17:20
  • No formula works. There even some settings that apply "per subquery".
    – Rick James
    Commented Mar 31, 2023 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.