MySQL Optimization on VPS: 10 FAQs [Solved]

Configuration Essentials

Start by knowing what you have. Run SHOW VARIABLES to dump every setting, and SHOW STATUS for runtime counters. Compare Max_used_connections to max_connections. If they match, you're hitting the limit. If Max_used is under half, you're wasting memory.

The my.cnf file lives at /etc/my.cnf or /etc/mysql/my.cnf depending on your distro. Always back it up before editing: cp /etc/my.cnf /etc/my.cnf.backup. After changes, test the syntax with mysqld --help --verbose and restart with systemctl restart mysqld or systemctl restart mariadb.

How do I tune innodb_buffer_pool_size?

Watch buffer pool efficiency with SHOW STATUS LIKE 'Innodb_buffer_pool_read%'. Innodb_buffer_pool_read_requests should be 100x higher than Innodb_buffer_pool_reads. If not, increase the pool size until the ratio improves or you run out of RAM.

• innodb_buffer_pool_size = 2G
• Restart MySQL and monitor free -h output
• If swap usage appears, reduce by 512MB

What's the right max_connections value?

Each connection costs memory for buffers and thread stack. The default is often 151, which wastes RAM if your app only opens 20 connections. Check Max_used_connections from SHOW STATUS. Set max_connections to 1.5x that number for headroom.

A web app with 50 concurrent users typically needs 30-50 MySQL connections. Calculate memory per connection: read_buffer_size + sort_buffer_size + join_buffer_size + thread_stack. Default total is around 1MB per connection. On a 4GB VPS, 100 connections reserves 100MB, leaving more for the buffer pool.

Query Optimization and Indexing

After a few hours, run mysqldumpslow -s t /var/log/mysql/mysql-slow.log to see the slowest queries sorted by total time. Copy the worst one and prepend EXPLAIN to see the execution plan. Look for rows scanned in the hundreds of thousands and type: ALL, which means no index.

• slow_query_log = 1
• slow_query_log_file = /var/log/mysql/mysql-slow.log
• long_query_time = 2

How do I add an index without breaking production?

Run the query again with EXPLAIN. Verify type changed from ALL to ref and rows dropped from millions to dozens. Index creation locks the table on older MySQL versions. On InnoDB with MySQL 5.6+, it rebuilds online, but still adds I/O load. Schedule index adds during low-traffic windows.

• CREATE INDEX idx_user_status ON orders(user_id, status);

Memory and Swap Management

Monitor with vmstat 5 to watch swap in/out columns. Any non-zero value under si or so means active swapping, which kills query latency. Swap should stay at zero during peak load. If it doesn't, your config exceeds available RAM.

• echo 'vm.swappiness = 10' >> /etc/sysctl.conf
• sysctl -p

What about query_cache_size?

Query cache was removed in MySQL 8.0 because it caused more lock contention than it saved. If you run MySQL 5.7 or MariaDB 10.2 and older, query_cache_size = 64M can help read-heavy workloads with identical queries.

Check effectiveness with SHOW STATUS LIKE 'Qcache%'. Compare Qcache_hits to Com_select. If hits are under 20% of selects, the cache isn't helping. Set query_cache_size = 0 and reclaim the RAM for the buffer pool.

How often should I run mysqltuner?

mysqltuner.pl reads your MySQL stats and suggests config changes. Download it from GitHub and run weekly: perl mysqltuner.pl. It checks buffer pool hit rate, table cache efficiency, and connection usage.

Don't apply every recommendation blindly. It warns about max_connections even if your usage is fine. Focus on metrics with red warnings: buffer pool too small, high table locks, or join queries without indexes. Use it as a checklist, not gospel.

Monitoring and Baseline Metrics

Take snapshots before and after each change. Compare query response time from your app logs, not just MySQL metrics. A 20% increase in buffer pool hit rate means nothing if page load time stayed the same.

• SHOW STATUS LIKE 'Threads_connected'
• SHOW STATUS LIKE 'Questions'
• SHOW STATUS LIKE 'Innodb_buffer_pool_read%'
• SHOW STATUS LIKE 'Slow_queries'

Log File Growth and Disk Space

Binary logs grow fast if you don't set expire_logs_days. They pile up in /var/lib/mysql and fill the disk. Set expire_logs_days = 7 in my.cnf to auto-delete logs older than a week. Check current size with du -sh /var/lib/mysql/mysql-bin.*.

Slow query logs grow too. Rotate them with logrotate or truncate manually: > /var/log/mysql/mysql-slow.log. Set up a cron job to archive logs older than 30 days. Disk full errors stop MySQL cold, so monitor free space with df -h and alert at 80%.

Quick Reference Table

Test changes one at a time. Restart MySQL after editing my.cnf and verify the new value with SHOW VARIABLES. If performance degrades, revert to the backup config and restart again.

• innodb_buffer_pool_size: 2-2.8G (50-70% of RAM)
• max_connections: 50-100 (1.5x peak usage)
• innodb_log_file_size: 256M (larger for write-heavy apps)
• slow_query_log: 1 (always enabled)
• long_query_time: 2 (log queries over 2 seconds)
• query_cache_size: 0 (disabled on MySQL 8.0+)
• expire_logs_days: 7 (auto-delete old binlogs)
• innodb_flush_log_at_trx_commit: 2 (faster writes, slight crash risk)
• table_open_cache: 2000 (increase if you have hundreds of tables)
• thread_cache_size: 16 (reduce thread creation overhead)