MySQL Performance Tuning on Linux VPS

Updated 6 November 2026 · Standard formula, shown below

This guide walks you through practical steps to improve MySQL performance on a Linux VPS. You’ll learn how to adjust key configuration variables, monitor bottlenecks, and apply safe optimizations that reduce query latency and increase throughput, all without needing deep database internals knowledge.

What You’ll Need

Root or sudo access to your Linux VPS
MySQL or MariaDB installed and running
Basic familiarity with the command line and editing files

Step-by-Step

Check current MySQL status and configuration. Run mysqladmin status and mysqladmin variables to see uptime, queries per second, and current settings. Also, locate your config file – typically /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf.
Determine available memory. Use free -h to see total RAM. MySQL should use about 50-70% of available memory for its buffers and caches, leaving enough for the OS and other processes.
Set the InnoDB buffer pool size. In the [mysqld] section of your config, set innodb_buffer_pool_size to 50-70% of your RAM (e.g., 1G for 2GB RAM). This is the most impactful setting for InnoDB tables. Restart MySQL after changing.
Adjust connection limits. Set max_connections to a reasonable number (e.g., 150 for a small VPS). Too high can exhaust memory; too low causes errors. Also set max_connect_errors to 1000 to avoid blocking legitimate hosts.
Optimize query cache (if using MySQL 5.7 or older). Set query_cache_type=1 and query_cache_size to 64M. Note: MySQL 8.0 removed query cache, so skip this if you’re on 8.0+.
Configure temporary table sizes. Set tmp_table_size and max_heap_table_size to 64M each. This reduces disk-based temporary tables for complex queries.
Set table cache and open files limit. Increase table_open_cache to 2000 and open_files_limit to 5000. This helps if you have many tables.
Enable slow query log for analysis. In config, add slow_query_log=1, slow_query_log_file=/var/log/mysql-slow.log, and long_query_time=2. This logs queries taking over 2 seconds, helping you find bottlenecks.
Restart MySQL and verify changes. Run sudo systemctl restart mysql (or mariadb). Then run mysqladmin variables | grep 'innodb_buffer_pool_size' to confirm the new value is active.
Monitor performance. Use mysqladmin status periodically to see uptime, queries per second, and slow queries. Also use SHOW GLOBAL STATUS in mysql client to check key metrics like Threads_connected and Innodb_buffer_pool_read_requests.
Analyze slow queries. Use mysqldumpslow to summarize the slow query log. Identify queries that appear frequently and optimize them (add indexes, rewrite joins).
Consider using a tuning primer script. Tools like tuning-primer.sh (available online) can provide recommendations based on your current status, but always verify changes manually.

Common Mistakes

Setting buffer pool too large. If you allocate too much memory, the OS may swap, causing severe slowdowns. Leave at least 1-2GB for the OS and other services.
Ignoring the storage engine. If your tables are MyISAM, the InnoDB buffer pool won’t help. Use SHOW TABLE STATUS to check, and consider converting to InnoDB for better concurrency.
Overusing query cache. On MySQL 5.7 and earlier, a large query cache can become a bottleneck due to global locking. Keep it modest or disable if you have heavy writes.
Changing too many settings at once. Make one change at a time and test, so you know what works.
Forgetting to restart or reload. Some settings require a restart; others can be set dynamically with SET GLOBAL. Always verify.

Tips

Use the InnoDB metrics. Monitor Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads. The ratio should be high (e.g., 99%+). If not, increase buffer pool.
Enable binary logging only if needed. Binary logs (for replication or point-in-time recovery) consume disk and I/O. Disable if not required.
Optimize your queries first. Often a missing index is the real problem. Use EXPLAIN to see query execution plans.
Use persistent connections in your app. Reduce connection overhead by using connection pooling.
Schedule regular maintenance. Run OPTIMIZE TABLE on heavily fragmented tables, and keep statistics updated with ANALYZE TABLE.

Troubleshooting

MySQL won’t start after changes. Check the error log (usually /var/log/mysql/error.log). Common causes: invalid syntax in config, or buffer pool size larger than available memory. Revert the last change and restart.
Performance didn’t improve. Verify that the settings are actually applied by running SHOW VARIABLES LIKE 'innodb_buffer_pool_size'. Also, check if the bottleneck is CPU, disk I/O, or queries – use top and iostat to see.
Too many connections error. Increase max_connections but also check if your app is leaking connections. Use SHOW PROCESSLIST to see active connections.
Queries still slow. Run EXPLAIN on the slow query. Look for full table scans (type=ALL) and missing indexes. Add indexes as needed.
Disk I/O bottleneck. Consider moving the MySQL data directory to a faster disk (SSD) or using a separate volume. Use iotop to identify heavy I/O processes.

Frequently Asked Questions

How do I know how much memory MySQL is using?

Run mysqladmin status and look for “Memory in use�. For a detailed breakdown, query SHOW GLOBAL STATUS LIKE 'Memory%'.

What is the ideal innodb_buffer_pool_size?

For a dedicated MySQL server, set it to 50-70% of total RAM. For a shared VPS, use 25-50% to leave room for other processes.

Should I use MyISAM or InnoDB?

InnoDB is recommended for most applications due to row-level locking, transactions, and crash recovery. MyISAM is only faster for read-heavy, non-transactional workloads.

How often should I restart MySQL after tuning?

Only when you change static variables in the config file. Dynamic variables can be changed with SET GLOBAL without restart.

Can I tune MySQL without restarting?

Yes, many variables are dynamic. Use SET GLOBAL variable_name = value, but note that some settings like innodb_buffer_pool_size require a restart.

What is the slow query log and how do I use it?

It logs queries that take longer than long_query_time seconds. Enable it, then use mysqldumpslow to find the worst queries and optimize them.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.