MySQL Defaults Are Not Optimized for VPS

Default MySQL configuration uses minimal resources (128MB buffer pool). Your Hostxpeed VPS likely has 2-8GB RAM; leaving MySQL under-tuned wastes performance. This guide covers key variables to adjust for better throughput.

Step 1: Determine Available RAM

Rule of thumb: allocate 60-70% of RAM to InnoDB buffer pool on dedicated database server. For web + DB VPS, allocate 30-50% (leave for web server, PHP). Example: 4GB VPS -> set innodb_buffer_pool_size = 2GB. Check memory: free -h. Monitor MySQL memory usage with SHOW ENGINE INNODB STATUSG.

Step 2: Set InnoDB Buffer Pool Size

In my.cnf (or /etc/mysql/my.cnf): [mysqld] innodb_buffer_pool_size = 2G (adjust). For larger VPS, use 8G+ but leave OS memory. innodb_buffer_pool_instances = 4 (if >2GB). Recommendation: set size 70% of RAM if dedicated DB; 50% for mixed workload.

Step 3: InnoDB Log File Size

Default 48MB too small for write-heavy workloads. innodb_log_file_size = 512MB (or 1GB). Change requires shutdown, delete old logs, restart (careful). Better for VPS with moderate writes. Also innodb_log_buffer_size = 16MB.

Step 4: Query Cache (Disable in MySQL 8.0)

MySQL 8.0 removed query cache. For MySQL 5.7, query_cache_size=0 (disabled). Reason: query cache causes contention on high-concurrency server. Use application-level caching (Redis, Memcached) instead.

Step 5: Connection Limits

max_connections default 151. For VPS, set based on RAM: each connection uses ~1-2MB. For 4GB VPS, max_connections=300 should be safe. Use max_connections=500 for 8GB. Monitor with SHOW STATUS LIKE 'Max_used_connections'.

Step 6: Other Key Variables

innodb_flush_log_at_trx_commit = 2 (better performance, less durability). innodb_file_per_table = 1. innodb_read_io_threads = 4, innodb_write_io_threads = 4. join_buffer_size = 1M (not huge). tmp_table_size = 32M, max_heap_table_size = 32M. thread_cache_size = 8.

Tools for Tuning

MySQLTuner (Perl script): curl -L https://github.com/major/MySQLTuner-perl/tarball/master | tar xz, cd, perl mysqltuner.pl. Provides recommendations. Also pt-variable-advisor (Percona Toolkit). After changes, monitor performance with mysqltuner re-run.

Example: WordPress MySQL Tuning (4GB VPS)

innodb_buffer_pool_size=1.5G, innodb_log_file_size=256M, max_connections=200, query_cache_type=0 (if MySQL 5.7). Also key_buffer_size=32M (MyISAM, less relevant). Use mysqltuner weekly.

Conclusion

Tuning MySQL can double query throughput. Start with innodb_buffer_pool_size and log file. Use MySQLTuner to identify bottlenecks. For Hostxpeed managed databases, these tunings applied automatically; for unmanaged VPS, apply manually.