Fix High MySQL Memory Usage on a VPS

High MySQL memory usage on a VPS can cause slow queries, swapping, or even crashes. This guide shows you how to diagnose what’s using memory, tune MySQL’s key buffers and caches, and set limits so the database stays within your VPS’s available RAM. You’ll need SSH access to your server and sudo privileges.

What You’ll Need

• SSH access to your VPS (root or a user with sudo)
• MySQL or MariaDB installed (commands work for both)
• Basic familiarity with the command line

Step-by-Step

1. Check current memory usage. Run free -m to see total, used, and available memory. Then run ps aux --sort=-%mem | grep mysql | head -10 to see how much RSS (resident set size) the MySQL process is using.
2. Identify the biggest memory consumers. Connect to MySQL with mysql -u root -p and run SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; and SHOW VARIABLES LIKE 'key_buffer_size'; – these are usually the top two. Also check SHOW VARIABLES LIKE 'max_connections'; and SHOW VARIABLES LIKE 'table_open_cache';.
3. Check actual usage with performance_schema. Run this query to see memory usage by instrument:

   SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10;

   This shows which components (like innodb_buffer_pool or query cache) are actually consuming RAM.

4. Set a realistic InnoDB buffer pool size. The buffer pool is usually the largest consumer. A common rule of thumb is to set it to 50-70% of your total RAM if MySQL is the main service. For example, on a 4GB VPS, set it to 2.5G. Edit the MySQL config file (usually /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf) and add:

   [mysqld]
   innodb_buffer_pool_size = 2G

   If you have multiple MySQL instances, adjust accordingly.

5. Limit the query cache (if enabled). The query cache is deprecated in MySQL 8.0 and often causes memory spikes. If you’re on MySQL 5.7 or MariaDB, set query_cache_type = 0 and query_cache_size = 0 to disable it, or set a small size like 64M if you must keep it.
6. Reduce max_connections. Each connection can allocate memory for buffers. Check your current peak connections with SHOW STATUS LIKE 'Max_used_connections';. Set max_connections to a value slightly above that, e.g., max_connections = 100 instead of the default 151 or higher. Also set thread_cache_size to a reasonable value like 8.
7. Tune table_open_cache and other caches. Set table_open_cache to a value that matches your number of tables (check with SHOW STATUS LIKE 'Open_tables';). A typical value is 1000-2000. Also reduce performance_schema memory if you don’t need it: set performance_schema = OFF in the config (but note you lose diagnostics).
8. Restart MySQL. After making changes, restart the service with sudo systemctl restart mysql (or mariadb). Then check memory again with free -m and ps aux | grep mysql to see the improvement.
9. Monitor and adjust. Use mysqltuner (install with sudo apt install mysqltuner) or mysqladmin status to get recommendations. Run mysqladmin -u root -p status to see uptime, queries, and open tables.

Common Mistakes

• Setting innodb_buffer_pool_size too high – leaving no room for OS and other processes, causing swapping.
• Forgetting to disable the query cache – it can consume hundreds of MB and is often unnecessary.
• Ignoring per-connection buffers – sort_buffer_size, join_buffer_size, and read_buffer_size are allocated per connection, so multiplying by max_connections can explode memory.
• Copying configs from the internet without checking your actual workload – a config for a high-traffic site may be too aggressive for a small VPS.

Tips

• Use SHOW ENGINE INNODB STATUS\G to see buffer pool hit rate and other stats.
• Consider using MySQL 8.0’s new innodb_buffer_pool_size is dynamic – you can change it without restart using SET GLOBAL innodb_buffer_pool_size = 1073741824; (in bytes).
• If you have spare RAM, increase the buffer pool instead of reducing it – better cache hits reduce disk I/O.
• Set innodb_log_file_size to a reasonable value (e.g., 64M) to avoid frequent redo log flushes, but this does not directly affect memory.

Troubleshooting

MySQL won’t start after changing config. Check the error log at /var/log/mysql/error.log for syntax errors. Run sudo mysqld --validate-config to test the config file.

Memory still high after tuning. Run the performance_schema query again to see if another component (like the binary log cache or temporary tables) is consuming memory. Also check if you have multiple MySQL workers or a memory leak – restart MySQL and monitor over time.

Performance degrades after reducing buffer pool. If you see more disk reads (check SHOW STATUS LIKE 'Innodb_buffer_pool_reads'), you may have reduced it too much. Increase gradually and monitor.

Cannot connect to MySQL. If you set max_connections too low, new connections will be refused. Increase it temporarily via SET GLOBAL max_connections = 200; and then adjust the config file.

Frequently Asked Questions

Why does MySQL use more memory than my innodb_buffer_pool_size?

Because there are many other allocations: per-connection buffers, table caches, the query cache (if enabled), and internal structures like the dictionary cache. Use performance_schema to see the breakdown.

Is it safe to set innodb_buffer_pool_size to 80% of RAM?

Only if MySQL is the only major process and you have no swap. Otherwise, you risk OOM killer. 50-70% is safer for a typical VPS.

What is the difference between key_buffer_size and innodb_buffer_pool_size?

key_buffer_size is for MyISAM tables, while innodb_buffer_pool_size is for InnoDB. If you use only InnoDB, you can set key_buffer_size to a small value like 8M.

How do I know if my MySQL is swapping?

Check free -m for swap used. Also run vmstat 1 and look at the si and so columns – if they are non-zero, swapping is happening.

Can I change settings without restarting MySQL?

Many variables are dynamic. Use SET GLOBAL variable = value; to change them at runtime. To make permanent, add to the config file.

What is a good starting point for a 2GB VPS?

Set innodb_buffer_pool_size to 1G, max_connections to 100, table_open_cache to 1000, and disable query cache. Then monitor and adjust.

Related

• Ninja Forms WordPress: Create, Publish & Fix
• AI in Transportation: 35 Deployments, What’s Proven
• Galaxy S7 & S7 Edge: Fix Disappeared Gallery Pictures