{"id":25455,"date":"2010-07-19T19:51:04","date_gmt":"2010-07-19T19:51:04","guid":{"rendered":"http:\/\/euvolution.com\/futurist-transhuman-news-blog\/audit-your-mysql-memory-usage\/"},"modified":"2010-07-19T19:51:04","modified_gmt":"2010-07-19T19:51:04","slug":"audit-your-mysql-memory-usage","status":"publish","type":"post","link":"https:\/\/www.euvolution.com\/futurist-transhuman-news-blog\/planetology\/audit-your-mysql-memory-usage.php","title":{"rendered":"Audit Your MySQL Memory Usage"},"content":{"rendered":"

\"MatthewEver wonder why your MySQL server runs out of memory or your server starts swapping it like crazy? It could be that you are allowing too many connections or have a buffer that isn’t being used. Here are some simple formulas you can use to determine how much memory your MySQL server can use.<\/p>

All of these variable values can be seen by using “SHOW GLOBAL VARIABLES<\/code>” at the MySQL client prompt. They are given in bytes so you must convert to KB, MB or GB by dividing the value returned by 1024, 1048576 or 1073741824, respectively.<\/p>

Remember: Each connection from your application is referred to as a thread by MySQL.<\/p>

Per-Thread Memory Use<\/strong> (The amount of memory a single connection can use):<\/p>

read_buffer_size<\/code> + read_rnd_buffer_size<\/code> + sort_buffer_size<\/code> + thread_stack<\/code> + join_buffer_size<\/code><\/p>

Example: 1048576 + 2097152 + 1048576 + 262144 + 131072 = 4587520 bytes
Divide that by 1048576 to get the usage in MB, and you find that each thread can use up to 4.375 MB of memory.<\/p>

Now, take that amount and multiply by your max_connections<\/code>, and you’ll find the total potential memory usage. For this example, let’s set max_connections<\/code> to 350. The example server could use 1,531.25 MB or 1.49 GB if all 350 connections were in use at a given time. If we have 4GB of RAM in the server, that accounts for almost one third of our available memory.<\/p>

And that’s not all! There are several other “global buffers” that MySQL creates depending on which table engines you are using. The formula below assumes you have a mix of MyISAM and InnoDB tables and you are using the query cache:<\/p>

Base MySQL Memory Usage<\/strong><\/p>

key_buffer_size<\/code> + max_heap_table_size<\/code> + innodb_buffer_pool_size<\/code> + innodb_additional_mem_pool_size<\/code> + innodb_log_buffer_size<\/code> + query_cache_size<\/code><\/p>

Example: 1073741824 + 33554432 + 2147483648 + 134217728 + 10485760 + 536870912 = 3936354304 bytes
To get the usage in GB, we divide by 1073741824 and see that 3.66 GB is being used!<\/p>

Again, in a 4GB server, base MySQL – with no connections – can use 3.66GB, or about 90 percent of my server’s physical memory. Yikes! When you see that, your first move should be to contact your sales rep to get more memory installed. We advise, for system stability, never to assign more than 80 percent of overall system memory to MySQL (or any process for that matter).<\/p>

One other area to check for memory reduction is your client application code. While the PHP manual says you don’t have to explicitly call mysql_close<\/code> or mysqli_close<\/code>, we highly recommend it as a best practice because there’s a known PHP bug that prevents connections from being properly closed on script termination. And if each connection to MySQL eats up a base amount of memory whether it’s being used or not, your server will suffer for no reason.<\/p>

-Matthew, CMDBA 5.0<\/p>

Related Posts:<\/h4>