Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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 ?

Thanks

UPDATE:

Let me take an example to explain what I want to ask. After I start mysql server, it use 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.

Hope I've explained myself clearly.

share|improve this question

1 Answer

up vote 2 down vote

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.

share|improve this answer
I usually use 75% instead of 80% when a client wants to use the query cache. Some have the tendency to use the query cache with InnoDB. In many cases, you do not need it. Either number can work when the query_cache_size is just right. +1 for mentioning mysqltuner. – RolandoMySQLDBA Dec 27 '12 at 2:52
Thanks for your answer. It's great to know the tool mysqltuner. But I don't think you've answered my question. I 've updated my question to make my point clear. – Eagle Dec 27 '12 at 15:49
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). – Jonathan Nicol Dec 28 '12 at 17:34
I don't agree. I've found something here: mysqlab.net/knowledge/kb/detail/topic/informix/id/5004 – Eagle Jan 3 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. – Jonathan Nicol Jan 4 at 17:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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