MySQL performance tuning one key thing is memory optimization. Excessive memory usage can crash your server and stop working your WordPress sites. When using MySQL tuner or similar optimization monitoring tools, they may show “Maximum possible memory usage” above the installed RAM (over 100%) and warn you, “Overall possible memory usage with other process exceeded memory” or “MySQL’s maximum memory usage is dangerously high“.
This can happen when overallocated memory for MySQL or MariaDB. When you use WordOps, Webinoly, or EasyEngine, type the LEMP stack installation method. These installation scripts & tools are not explicitly optimized MySQL memory usage according to your VPS resources. Alternatively, if you installed the LEMP stack manually, you have to optimize it for better performance. This issue is applicable for MySQL as well as MariaDB.
Before we dive into the reduced MySQL Maximum possible memory usage, I’ll give brief information about MySQL memory usage. When the client connects to the MySQL server, it creates a thread. Each of these threads has maximum possible memory usage. When the number of threads increases memory usage increases. RAM Memory plays a significant role when handling concurrent threads and running big queries.
There are mainly two possible options to optimize MySQL memory usage. It is either changes the memory usage by adjusting the memory allocation parameters or upgrading your VPS RAM. The second option is not a bright idea. Because with current traffic, your server may not reach its maximum resources usage.
Why does MySQL consume so much memory?
The reason for MySQL’s high memory usage is to apply the improper configuration. When reducing MySQL memory usage, you have to consider the MySQL total buffers. MySQL memory usage calculates by combining global memory and per-thread memory. Thread means MySQL max_connections. You can reduce MySQL memory usage on your server by adjusting the following configuration parameters.
Maximum MySQL Memory usage = Global buffer + (thread buffer × max connections).
MySQL Reduces Maximum Possible Memory Usage.
MySQL Tuner shows mainly two memory-related warnings. It is a “Maximum possible memory usage High” (over 100%) warning and reduces the “mysqltuner maximum reached memory usage.”
Mainly we can limit MySQL memory usage by changing the Global buffer and Thread buffer memory allocation. Adjusting these buffers can reduce MySQL memory usage in WordPress sites.
How to Change MySQL Global Buffer?
Global buffer is the combination of innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size. Global buffer memory allocation not change according to the max_connections (number of threads). This is a static memory allocation. Adjusting following parameters you can reduce your server MySQL global memory usage.
Global buffer parameters.
- innodb_buffer_pool_size
- innodb_additional_mem_pool_size
- innodb_log_buffer_size
- key_buffer_size
- query_cache_size
How to Change MySQL Thread buffer?
MySQL thread buffer is the combination of max_allowed_packet + read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size + tmp_table_size.
Total threads buffer (memory) is equal to “thread buffer” × “max_connections.” Depending on the traffic of your site, you can adjust these configurations. When the number of MySQL connections increases in your server, thread buffer memory usage increases, and your VPS MySQL memory usage keeps increasing. This will apply vice versa.
Thread buffer parameters.
- max_allowed_packet
- read_buffer_size
- read_rnd_buffer_size
- sort_buffer_size
- thread_stack
- join_buffer_size
- binlog_cache_size
- tmp_table_size
What are the optimum MySQL max_connections for WordPress sites?
Generally, a moderate traffic site that enables page cached, 50 max_connections is enough. The default value is 100. When the number of “max_connections” getting increases, then the server memory usage increases. Therefore first check past 24 hours or 48 hours how many max_connections were utilized. If it is lower than the current specified limit, you can reduce it.
If you encounter “Too many connections” in MySQL, you need to increase it by editing my.cnf file. My recommendation is to install a MySQL tuner or similar performance monitoring script and check the server’s last 24 or 48 hours maximum connection usage. If it is below 50% you can reduce it to optimize memory usage.
How much memory should I allocate to MySQL?
You have to allocate enough memory for your MySQL. If you give lower RAM, it will end up with “Fatal error: cannot allocate memory for the buffer pool.” Or, if you are overallocated RAM, other processes cannot run smoothly and crash your system.
The ideal memory allocation for MySQL is 50% to 70% of the installed RAM. If you have a dedicated MySQL server (run only MySQL), you can allocate 70% to 80% of the installed RAM. There should be reserve memory for other processes like running operating systems, other packages like Nginx, PHP, Postfix, firewall.,
You can use this calculator to estimate your server MySQL memory usage. However, it is not considered a few thread buffer parameters.
