{"id":421,"date":"2026-06-14T08:08:16","date_gmt":"2026-06-14T08:08:16","guid":{"rendered":"https:\/\/virtualserversvps.com\/blog\/?p=421"},"modified":"2026-08-04T22:10:33","modified_gmt":"2026-08-04T22:10:33","slug":"mysql-performance-tuning-vps-optimize-queries-caching-innodb","status":"publish","type":"post","link":"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/","title":{"rendered":"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">A fresh MySQL or MariaDB install ships with conservative defaults written for shared desktops and 4\u20138&nbsp;GB workstations. On a 2&nbsp;GB VPS that means a 128&nbsp;MB InnoDB buffer pool, an oversized log buffer, and per-connection memory buffers that multiply across every active session. The result is a database that sits idle while the disk does work that should be happening in RAM. The fix is a small set of my.cnf settings sized to your actual VPS memory, and this guide walks through the ones that measurably cut query latency.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Before you tune, make sure the underlying hardware can reward the effort. NVMe storage and enough RAM to keep your working set in the buffer pool are what make these settings pay off, so <a href=\"https:\/\/virtualserversvps.com\/#providers\">compare VPS plans on our comparison table<\/a> and pick one with at least 2&nbsp;GB of RAM and SSD or NVMe disks before you start.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Size the InnoDB Buffer Pool First<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">InnoDB is the default engine on both MySQL 8 and MariaDB, and <code>innodb_buffer_pool_size<\/code> is the single most important variable on any VPS. It controls how much of your table and index data stays in memory. A buffer pool that is too small turns every query into disk I\/O; one that is too large pushes the system into swap. The rule of thumb: use 50\u201370% of total RAM for a dedicated database server, and 30\u201340% if the same VPS also runs a web server and PHP-FPM. Check the current value first:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -e \"SHOW VARIABLES LIKE 'innodb_buffer_pool_size';\"\n# 134217728 = 128 MB \u2014 the default you almost never want<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Here is a minimal my.cnf for a 2&nbsp;GB VPS running both the database and a web stack:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[mysqld]\ninnodb_buffer_pool_size = 768M      # 30-40% of a 2G VPS running web + DB\ninnodb_log_file_size    = 128M\ninnodb_log_buffer_size  = 16M\ninnodb_flush_method     = O_DIRECT  # skip double-buffering on SSD\/NVMe\ninnodb_flush_log_at_trx_commit = 2  # 1 for maximum durability<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>innodb_flush_method = O_DIRECT<\/code> tells InnoDB to bypass the OS page cache, which avoids the classic double-buffering problem on SSD and NVMe storage. <code>innodb_flush_log_at_trx_commit = 2<\/code> flushes the redo log once per second instead of on every commit \u2014 roughly a 5\u201310x reduction in fsyncs. Set it to <code>1<\/code> if losing the last second of transactions in a power loss is unacceptable (for example, billing data).<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>VPS RAM<\/th><th>Buffer pool (DB only)<\/th><th>Buffer pool (web + DB)<\/th><th>InnoDB log file<\/th><\/tr><\/thead><tbody><tr><td>2 GB<\/td><td>1.2G<\/td><td>768M<\/td><td>128M<\/td><\/tr><tr><td>4 GB<\/td><td>2.5G<\/td><td>1.5G<\/td><td>256M<\/td><\/tr><tr><td>8 GB<\/td><td>5G<\/td><td>3G<\/td><td>512M<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">If you are about to rent hardware for a database workload, <a href=\"https:\/\/interserver.net\/vps?id=1067805&amp;sid=virtualserversvps\" rel=\"noreferrer noopener sponsored\" target=\"_blank\">InterServer\u2019s VPS plans include NVMe storage and generous RAM at a flat monthly price<\/a> \u2014 a solid home for a tuned MySQL instance where the buffer pool stays warm.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Kill the Query Cache Myths and Tune Connection Buffers<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The query cache was removed in MySQL 8.0 and is deprecated in MariaDB, so stop looking for <code>query_cache_size<\/code> in new configs. On modern versions the buffer pool <em>is<\/em> the cache. Do still set <code>key_buffer_size = 32M<\/code> for any remaining MyISAM tables, but plan to convert them to InnoDB with <code>ALTER TABLE t ENGINE=InnoDB;<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Per-connection buffers are where small VPSes die. Each session gets its own <code>sort_buffer_size<\/code>, <code>join_buffer_size<\/code>, and <code>read_buffer_size<\/code>, so 100 connections \u00d7 a 16&nbsp;MB sort buffer is 1.6&nbsp;GB before the buffer pool even counts. Keep them small \u2014 2&nbsp;MB for sort and join buffers is plenty for OLTP workloads \u2014 and cap temporary tables at 64&nbsp;MB with <code>tmp_table_size<\/code> and <code>max_heap_table_size<\/code> so runaway ORDER BY queries spill to disk instead of swapping the box.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[mysqld]\nmax_connections     = 100    # don't copy the 1000-connection settings from big servers\nthread_cache_size   = 16\nsort_buffer_size    = 2M\njoin_buffer_size    = 2M\nread_buffer_size    = 1M\ntmp_table_size      = 64M\nmax_heap_table_size = 64M\nskip-name-resolve           # skip reverse-DNS lookups on each connection<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Verify the Change, Then Benchmark<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Apply the config with <code>systemctl restart mysql<\/code> (or <code>mariadb<\/code>), then confirm the values took effect and measure the buffer pool hit ratio:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -e \"SHOW VARIABLES LIKE 'innodb_buffer_pool_size';\"\nmysql -e \"SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';\"\n# hit ratio = read_requests \/ (read_requests + reads) \u2014 aim for 99%+<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If the hit ratio is below 95%, your working set does not fit in RAM \u2014 either raise the buffer pool if memory allows, add more memory, or move rarely-read tables to a separate disk. A quick sanity check on connection churn is also worth running: <code>SHOW GLOBAL STATUS LIKE 'Threads_connected';<\/code> during peak hours tells you whether <code>max_connections<\/code> is realistic. Aborted connections spiking at the same time usually mean clients are timing out, which points at network or DNS, not the buffer pool.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Use <code>mysqlslap<\/code> or <code>sysbench<\/code> to capture before-and-after numbers so every tuning decision is based on measurements, not guesses:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqlslap --auto-generate-sql --concurrency=10 --iterations=5 \\\n  --number-of-queries=1000 -u root -p\nsysbench oltp_read_write --table-size=100000 --threads=8 prepare\nsysbench oltp_read_write --table-size=100000 --threads=8 run<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">MariaDB accepts the same directives as MySQL, plus <code>aria_pagecache_buffer_size<\/code> for Aria tables; everything in this guide applies verbatim to both engines.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Tuning is an iterative loop: size the pool, trim per-connection buffers, measure, repeat. When the working set finally fits in memory, the remaining bottleneck is usually the disk \u2014 so <a href=\"https:\/\/virtualserversvps.com\/#providers\">see the full specs and pricing on our comparison table<\/a> before committing to hardware that cannot keep up.<\/p>\n\n","protected":false},"excerpt":{"rendered":"<p>A fresh MySQL or MariaDB install ships with conservative defaults written for shared desktops and 4\u20138&nbsp;GB workstations. On a 2&nbsp;GB VPS that means a 128&nbsp;MB InnoDB buffer pool, an oversized&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":4,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-421","post","type-post","status-publish","format-standard","hentry","category-vps-guides-tutorials"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v26.1 (Yoast SEO v26.1) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter - Virtual Servers VPS Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter\" \/>\n<meta property=\"og:description\" content=\"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter\" \/>\n<meta property=\"og:url\" content=\"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/\" \/>\n<meta property=\"og:site_name\" content=\"Virtual Servers VPS Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-06-14T08:08:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-08-04T22:10:33+00:00\" \/>\n<meta name=\"author\" content=\"Virtual-Servers-Vps-Editor\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Virtual-Servers-Vps-Editor\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/\",\"url\":\"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/\",\"name\":\"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter - Virtual Servers VPS Blog\",\"isPartOf\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#website\"},\"datePublished\":\"2026-06-14T08:08:16+00:00\",\"dateModified\":\"2026-08-04T22:10:33+00:00\",\"author\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0\"},\"breadcrumb\":{\"@id\":\"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/virtualserversvps.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#website\",\"url\":\"https:\/\/virtualserversvps.com\/blog\/\",\"name\":\"Virtual Servers VPS Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/virtualserversvps.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0\",\"name\":\"Virtual-Servers-Vps-Editor\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g\",\"caption\":\"Virtual-Servers-Vps-Editor\"},\"sameAs\":[\"https:\/\/virtualserversvps.com\/blog\"],\"url\":\"https:\/\/virtualserversvps.com\/blog\/author\/virtualserversvps\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter - Virtual Servers VPS Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/","og_locale":"en_US","og_type":"article","og_title":"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter","og_description":"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter","og_url":"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/","og_site_name":"Virtual Servers VPS Blog","article_published_time":"2026-06-14T08:08:16+00:00","article_modified_time":"2026-08-04T22:10:33+00:00","author":"Virtual-Servers-Vps-Editor","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Virtual-Servers-Vps-Editor","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/","url":"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/","name":"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter - Virtual Servers VPS Blog","isPartOf":{"@id":"https:\/\/virtualserversvps.com\/blog\/#website"},"datePublished":"2026-06-14T08:08:16+00:00","dateModified":"2026-08-04T22:10:33+00:00","author":{"@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0"},"breadcrumb":{"@id":"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/virtualserversvps.com\/blog\/mysql-performance-tuning-vps-optimize-queries-caching-innodb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/virtualserversvps.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL and MariaDB Performance Tuning on a VPS: my.cnf Settings That Matter"}]},{"@type":"WebSite","@id":"https:\/\/virtualserversvps.com\/blog\/#website","url":"https:\/\/virtualserversvps.com\/blog\/","name":"Virtual Servers VPS Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/virtualserversvps.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/82a299a8284a66ff49f97c74684724a0","name":"Virtual-Servers-Vps-Editor","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/virtualserversvps.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d820b15f1cd028e97610d9adf536df7be5cb6423869967037d468d5355fa003f?s=96&d=mm&r=g","caption":"Virtual-Servers-Vps-Editor"},"sameAs":["https:\/\/virtualserversvps.com\/blog"],"url":"https:\/\/virtualserversvps.com\/blog\/author\/virtualserversvps\/"}]}},"_links":{"self":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/421","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/comments?post=421"}],"version-history":[{"count":3,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/421\/revisions"}],"predecessor-version":[{"id":796,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/posts\/421\/revisions\/796"}],"wp:attachment":[{"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/media?parent=421"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/categories?post=421"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/virtualserversvps.com\/blog\/wp-json\/wp\/v2\/tags?post=421"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}