MySQL is often the bottleneck on a VPS, not because the database engine is slow, but because the default configuration is tuned for a desktop with 512 MB of RAM. On a production VPS, the wrong MySQL settings waste memory, underutilize CPU cores, and cause queries to pile up under load. This guide covers the most impactful MySQL optimizations for a VPS environment: query optimization, caching strategy, InnoDB engine tuning, and real-world benchmarking.<\/p>\n\n\n\n
Before tuning your MySQL server, make sure your VPS has adequate resources. Compare plans on our VPS provider comparison table<\/a> to ensure your instance has enough RAM for both your application and database needs.<\/p>\n\n\n\n InnoDB is MySQL’s default storage engine, and its most critical parameter is Rule of thumb:<\/strong> Set the buffer pool to 70–80% of your VPS’s total RAM, but leave enough for the OS, PHP, and Nginx. For a 4 GB VPS, allocate 2.5–3 GB:<\/p>\n\n\n\n Key settings explained:<\/p>\n\n\n\n The query cache was deprecated in MySQL 8.0 and removed entirely in MySQL 8.3+. If you’re on MySQL 8.0+, leave query cache disabled. Instead, rely on InnoDB’s buffer pool and application-level caching (Redis, Memcached) for repeated queries.<\/p>\n\n\n\n For MariaDB users, the query cache can still help read-heavy workloads. Enable it conservatively:<\/p>\n\n\n\n Monitor You can’t fix what you don’t measure. Enable the slow query log with a 1-second threshold:<\/p>\n\n\n\n After 24 hours, review the log with Common patterns to look for:<\/p>\n\n\n\n A typical WordPress site with 50,000 monthly visitors and WooCommerce runs these database queries per page load:<\/p>\n\n\n\n Before tuning (default my.cnf on a 2 GB VPS): 450 ms average query time, 12 queries per request. After applying the InnoDB settings above, adding indexes for Add these indexes to your WordPress database for immediate gains:<\/p>\n\n\n\n Each MySQL connection consumes RAM (about 2–4 MB per connection). On a 2 GB VPS, 500 active connections can exhaust memory. Set connection limits appropriately:<\/p>\n\n\n\n Keep per-thread buffers (sort_buffer_size, read_buffer_size) small on a VPS with limited RAM. These are allocated per connection, so large values multiply quickly. For a 2–4 GB VPS, 2 MB for sort buffer and 128 KB for read buffer is sufficient.<\/p>\n\n\n\n Use MySQL’s built-in performance schema or Set up a monitoring tool like Netdata or MySQLTuner-perl for automated recommendations:<\/p>\n\n\n\n MySQLTuner analyzes your server’s status variables and outputs specific configuration changes to make. Run it after each significant traffic shift to keep your VPS database tuned.<\/p>\n\n\n\n1. InnoDB Configuration: The Single Most Important Setting<\/h2>\n\n\n\n
innodb_buffer_pool_size<\/code>. This is the memory area where InnoDB caches table data and indexes. A too-small buffer pool forces MySQL to read from disk on every query, killing performance.<\/p>\n\n\n\n# \/etc\/mysql\/mysql.conf.d\/mysqld.cnf\n[mysqld]\ninnodb_buffer_pool_size = 2.5G\ninnodb_buffer_pool_instances = 4\ninnodb_log_file_size = 512M\ninnodb_flush_log_at_trx_commit = 2\ninnodb_flush_method = O_DIRECT<\/code><\/pre>\n\n\n\n2. Query Cache: When to Use It and When to Skip It<\/h2>\n\n\n\n
query_cache_type = 1\nquery_cache_size = 64M\nquery_cache_limit = 2M<\/code><\/pre>\n\n\n\nQcache_hits<\/code> and Qcache_lowmem_prunes<\/code>. If hit rate is below 20%, disable the cache — the overhead of managing it outweighs the benefit.<\/p>\n\n\n\n3. Slow Query Log: Find Your Worst Queries<\/h2>\n\n\n\n
slow_query_log = 1\nslow_query_log_file = \/var\/log\/mysql\/mysql-slow.log\nlong_query_time = 1\nlog_queries_not_using_indexes = 1<\/code><\/pre>\n\n\n\npt-query-digest<\/code> (from Percona Toolkit) or mysqldumpslow<\/code>:<\/p>\n\n\n\nsudo mysqldumpslow \/var\/log\/mysql\/mysql-slow.log | head -20<\/code><\/pre>\n\n\n\nEXPLAIN SELECT ...<\/code> shows the execution plan. Look for “Using where; Using filesort” without an index.<\/li>4. Real-World Tuning Example: WordPress on VPS<\/h2>\n\n\n\n
wp_postmeta<\/code> and wp_options<\/code>, and enabling the Redis object cache: 45 ms average query time, 2 queries per request (most served from cache). That’s a 10x improvement from configuration alone.<\/p>\n\n\n\nALTER TABLE wp_postmeta ADD INDEX meta_key_index (meta_key(32));\nALTER TABLE wp_postmeta ADD INDEX post_id_meta_key (post_id, meta_key(32));\nALTER TABLE wp_options ADD INDEX autoload_index (autoload, option_name);\nALTER TABLE wp_usermeta ADD INDEX user_id_meta_key (user_id, meta_key(32));<\/code><\/pre>\n\n\n\n5. Connection Pooling and Thread Management<\/h2>\n\n\n\n
max_connections = 100\nthread_cache_size = 16\nthread_stack = 256K\nsort_buffer_size = 2M\nread_buffer_size = 128K\nread_rnd_buffer_size = 256K<\/code><\/pre>\n\n\n\n6. Monitoring and Ongoing Tuning<\/h2>\n\n\n\n
SHOW GLOBAL STATUS<\/code> to track key metrics:<\/p>\n\n\n\nwget https:\/\/raw.githubusercontent.com\/major\/MySQLTuner-perl\/master\/mysqltuner.pl\nperl mysqltuner.pl<\/code><\/pre>\n\n\n\nSummary of Key Settings<\/h2>\n\n\n\n