{"id":319,"date":"2026-01-22T02:00:04","date_gmt":"2026-01-22T02:00:04","guid":{"rendered":"https:\/\/virtualserversvps.com\/blog\/?p=319"},"modified":"2026-06-19T02:39:50","modified_gmt":"2026-06-19T02:39:50","slug":"everything-you-need-to-know-about-vps-hosting-virtual-private-server","status":"publish","type":"post","link":"https:\/\/virtualserversvps.com\/blog\/everything-you-need-to-know-about-vps-hosting-virtual-private-server\/","title":{"rendered":"How to Optimize MariaDB on Your VPS for Better Query Performance"},"content":{"rendered":"\n

MariaDB is one of the most popular database servers for VPS-hosted applications, from WordPress sites to custom web apps. But default MariaDB configurations are conservative \u2014 optimized for a wide range of hardware rather than your specific workload. With a few targeted adjustments, you can dramatically improve query throughput, reduce latency, and make better use of your VPS’s available memory. For VPS plans with enough RAM to handle a database workload, check out the provider comparison table<\/a>.<\/p>\n\n\n\n

Before You Start: Benchmark Your Baseline<\/h2>\n\n\n\n

Always measure before and after. Install sysbench<\/code> to run a standard OLTP benchmark:<\/p>\n\n\n\n

sudo apt install -y sysbench\n# Prepare test data\nsysbench \/usr\/share\/sysbench\/oltp_read_write.lua --table-size=100000 prepare\n# Run benchmark\nsysbench \/usr\/share\/sysbench\/oltp_read_write.lua --table-size=100000 --threads=4 run<\/code><\/pre>\n\n\n\n
Record the transactions per second (TPS) and latency numbers. You’ll compare these after tuning.<\/p>\n\n\n\n
1. InnoDB Buffer Pool \u2014 The Most Important Setting<\/h2>\n\n\n\n
The InnoDB buffer pool caches table data and indexes in memory. This is the single most impactful setting for MariaDB performance. Set it to 50-70% of your VPS’s total RAM<\/strong> (but leave enough for the OS and other processes):<\/p>\n\n\n\n
# For a VPS with 4 GB RAM \u2014 set buffer pool to 2 GB\n[mysqld]\ninnodb_buffer_pool_size = 2G<\/code><\/pre>\n\n\n\n
On servers with 8 GB or more, consider using multiple buffer pool instances to reduce contention:<\/p>\n\n\n\n
innodb_buffer_pool_instances = 4<\/code><\/pre>\n\n\n\n
Set innodb_buffer_pool_instances<\/code> to the same number of CPU cores for best results.<\/p>\n\n\n\n
2. Query Cache \u2014 Use It Carefully<\/h2>\n\n\n\n
The query cache was removed in MySQL 8.0 but is still available in MariaDB. For read-heavy workloads with repetitive queries (like WordPress), it can help:<\/p>\n\n\n\n
query_cache_type = 1\nquery_cache_size = 64M\nquery_cache_limit = 2M<\/code><\/pre>\n\n\n\n
For write-heavy workloads, leave the query cache disabled (query_cache_type = 0<\/code>) \u2014 the invalidation overhead can actually slow things down.<\/p>\n\n\n\n
3. Log File and Redo Log Tuning<\/h2>\n\n\n\n
The InnoDB log file size affects write performance during crash recovery and checkpoint operations. A larger log file means fewer checkpoints and better write performance:<\/p>\n\n\n\n
innodb_log_file_size = 512M\ninnodb_log_buffer_size = 64M<\/code><\/pre>\n\n\n\n
Note: Changing innodb_log_file_size<\/code> requires shutting down MariaDB, removing old log files, and restarting. Plan for brief downtime.<\/p>\n\n\n\n
4. Connection and Thread Settings<\/h2>\n\n\n\n
Default connection limits are low. For a web application behind a connection pooler, increase these:<\/p>\n\n\n\n
max_connections = 150\nthread_cache_size = 8\n# Thread pool (MariaDB specific) \u2014 great for high concurrency\nthread_handling = pool-of-threads\nthread_pool_size = 4  # Set to number of CPU cores<\/code><\/pre>\n\n\n\n
5. Temporary Table and Sort Buffer Tuning<\/h2>\n\n\n\n
Queries involving GROUP BY<\/code>, ORDER BY<\/code>, or DISTINCT<\/code> use temporary tables and sort buffers. If your application runs many such queries, increase these:<\/p>\n\n\n\n
tmp_table_size = 64M\nmax_heap_table_size = 64M\nsort_buffer_size = 2M\njoin_buffer_size = 2M<\/code><\/pre>\n\n\n\n
Be careful with sort_buffer_size<\/code> and join_buffer_size<\/code> \u2014 these are per-connection buffers, so setting them too high can exhaust memory under heavy concurrency.<\/p>\n\n\n\n
6. Complete Configuration Example<\/h2>\n\n\n\n
Here’s a complete \/etc\/mysql\/mariadb.conf.d\/99-vps-tuning.cnf<\/code> for a 4 GB VPS running a read-heavy web application:<\/p>\n\n\n\n
[mysqld]\n# InnoDB\ninnodb_buffer_pool_size = 2G\ninnodb_buffer_pool_instances = 4\ninnodb_log_file_size = 512M\ninnodb_log_buffer_size = 64M\ninnodb_flush_method = O_DIRECT\ninnodb_file_per_table = 1\n# Query cache\nquery_cache_type = 1\nquery_cache_size = 64M\nquery_cache_limit = 2M\n# Connections\nmax_connections = 150\nthread_cache_size = 8\nthread_handling = pool-of-threads\nthread_pool_size = 4\n# Temp tables\ntmp_table_size = 64M\nmax_heap_table_size = 64M\n# Buffers\nsort_buffer_size = 2M\njoin_buffer_size = 2M\n# Other\nmax_allowed_packet = 64M\nexpire_logs_days = 7<\/code><\/pre>\n\n\n\n
7. Apply and Verify<\/h2>\n\n\n\n
Restart MariaDB and run the benchmark again:<\/p>\n\n\n\n
sudo systemctl restart mariadb\nsysbench \/usr\/share\/sysbench\/oltp_read_write.lua --table-size=100000 --threads=4 run<\/code><\/pre>\n\n\n\n
Compare the new TPS and latency numbers against your baseline. With proper tuning, you should see 2-5x improvement in query throughput, especially for workloads that fit within the buffer pool.<\/p>\n\n\n\n
8. Ongoing Monitoring<\/h2>\n\n\n\n
Use the built-in SHOW STATUS<\/code> and SHOW VARIABLES<\/code> commands to monitor MariaDB health. Key metrics to track:<\/p>\n\n\n\n