I vaguely recall a couple of blog posts recently asking something like “what’s the formula to compute mysqld’s worst-case maximum memory usage?” Various formulas are in wide use, but none of them is fully correct. Here’s why: you can’t write an equation for it.
The most popular equation, of course, is the one in the error log after a crash: “It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections”. This was never true — not even when ISAM and friends were the only storage engines, which was before most of us remember. And at a minimum, most servers these days need to consider the added memory for InnoDB: specifically, the buffer pool. (There’s also an innodb_additional_mem_pool_size but it’s typically set pretty small, like 20M).
But even that’s not enough. Staying with InnoDB, we also need to count the data dictionary. This can take many gigabytes on servers with a lot of tables. We’re trying to fix that, but it’s unbounded in a non-Percona server. And InnoDB has other needs that it doesn’t actually promise to fit within innodb_additional_mem_pool_size, by the way. If it needs more it will allocate it. innodb_additional_mem_pool_size is just a handy way to avoid repeated malloc() calls by doing it all up front in one call.
And what about the query cache and the table cache and the size needed for each thread (thread stack, etc)? Those take memory too. The query cache can use quite a bit if it’s configured to. There are a bunch more global buffers to think about; that’s just a partial list. And there are some “global buffers” that aren’t really global. Suppose you set sort_buffer_size to 128K — I can quite well connect and say “set @@sort_buffer_size := 1 * 1024 * 1024 * 1024;” and my sort_buffer_size is now 1GB. And a single query may cause multiple sort buffers to be allocated. You can’t control that or calculate it at a global level.
But let’s keep going! Any stored code (triggers, stored routines, etc) uses memory to execute; so do temporary tables. The following query creates at least 2 temporary tables:
|
1 |
<br>select * from ( select * from ( select 1 as a ) as b ) as c;<br> |
You can have many of those per query. Sorting and grouping and DISTINCT and UNION may/will also create temp tables; the same applies to views and probably some other things I’m forgetting.
Let’s not forget about the memory needed to parse and optimize and execute queries; mysqld creates internal structures for the query plan, for example, and keeps it around until the query is done. And there are prepared statements, which you can create and never deallocate.
Any more? Of course… I can also set a bunch of user variables — they use memory too. And they can be really big (but no bigger than max_allowed_packet). And keys for user locks! I set my max_allowed_packet bigger and ran the following on my laptop:
|
1 |
<br>mysql> set @a := repeat('a', 1024 * 1024 * 100);<br>Query OK, 0 rows affected (2.34 sec)<br><br>mysql> select get_lock(@a, 1);<br>+-----------------+<br>| get_lock(@a, 1) |<br>+-----------------+<br>| 1 | <br>+-----------------+<br>1 row in set (3.63 sec)<br> |
Now I’ve got a 100M user variable and a 100M lock key. Note how long it took to run these statements! And now mysqld is suddenly using 324M of memory, up from 124M.
I’m sure you can think of other things I’ve forgotten here (the manual page has a bunch). My point is there simply is no formula to compute the maximum possible memory usage, and there cannot be because there are some things that have no defined limits.
It’s not something that’s worth worrying about much, in my opinion. I think it’s far better to use a monitoring tool to watch the actual memory usage over time and see how it varies. When I’m initially setting up a my.cnf for a server, I usually calculate the Big Ones like the buffer pool, query cache etc; leave some memory for other needs; and see what happens. The results are workload-specific.
