Ever 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.
All of these variable values can be seen by using “SHOW GLOBAL VARIABLES” 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.
Remember: Each connection from your application is referred to as a thread by MySQL.
Per-Thread Memory Use (The amount of memory a single connection can use):
read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size
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.
Now, take that amount and multiply by your max_connections, and you’ll find the total potential memory usage. For this example, let’s set max_connections 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.
Hey there!
We have just start blogging to keep you updated with the latest happenings at WEBSOFT.
So keep visiting to get a chance to know us more…
