How to Optimize MySQL and MariaDB on a Low-RAM WordPress Server

Give the InnoDB buffer pool enough memory to hold the data WordPress actually reads, which on a small site is often 128 to 256 MB, cap connections and in-memory temporary tables, leave the durability settings alone, and use the slow query log to find the queries that really cost you time. Measure first: a database that already fits in memory gains nothing from a bigger buffer pool. This guide covers MySQL 8.0 and MariaDB, calls out the differences, and works together with the guides on PHP-FPM workers for limited RAM and optimizing WordPress on a 1 GB server.

The commands assume Ubuntu 24.04. Before changing anything, take a backup of the databases and the configuration, as shown in Step 6.

Where database memory goes

Five areas account for almost all of a small WordPress database’s memory:

• The InnoDB buffer pool. It caches table and index pages and is the largest fixed allocation. The default is 128 MB in both MySQL 8.0 and MariaDB.
• Per-connection memory. A connection can allocate sort, join and read buffers when a query needs them. Many connections multiplied by large buffers is how memory disappears.
• Temporary tables. Queries with GROUP BY, ORDER BY or DISTINCT can build temporary tables in memory. In MySQL 8.0 the TempTable engine may use up to 1 GiB of RAM for them across all connections by default, which is far too generous for a small server.
• Performance Schema. MySQL enables this monitoring feature by default, and it holds its own memory.
• Minor caches. Table caches, the thread cache and, on MariaDB, the MyISAM key buffer and Aria page cache. MariaDB ships larger defaults for the last two than a pure InnoDB WordPress site needs, so check them with SHOW VARIABLES if you run MariaDB.

On the 1 GB server measured in the other guides, a MySQL 8.0 process was using about 188 MB resident out of 911 MiB, roughly a fifth of all RAM, while serving a Laravel application and a WordPress blog. That makes the database the largest single consumer on the machine, so it is worth understanding before you touch it.

Step 1: Measure how big your data really is

The buffer pool only needs to hold the data you actually use, so start with the size of your databases:

sudo mysql -e "SELECT table_schema AS db, ROUND(SUM(data_length+index_length)/1024/1024,1) AS size_mb FROM information_schema.tables GROUP BY table_schema ORDER BY size_mb DESC;"

Then look inside your WordPress database (replace wordpress with its name) for the largest tables:

sudo mysql -e "SELECT table_name, ROUND((data_length+index_length)/1024/1024,1) AS size_mb FROM information_schema.tables WHERE table_schema='wordpress' ORDER BY (data_length+index_length) DESC LIMIT 10;"

On a small site the whole database is usually tens of megabytes. Unexpectedly large tables are worth a look: wp_options full of expired transients or autoloaded plugin data, wp_postmeta bloated by an old plugin, or logging tables from scheduled-action and security plugins.

Step 2: See what the database is doing now

Check the current values and how the server has behaved since it last started:

sudo mysql -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','max_connections','tmp_table_size','max_heap_table_size','performance_schema','temptable_max_ram');"
sudo mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Uptime','Max_used_connections','Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests','Created_tmp_tables','Created_tmp_disk_tables');"
ps -C mysqld,mariadbd -o comm=,rss=

(temptable_max_ram exists only on MySQL 8.0; MariaDB simply will not list it.) Read the results like this:

• Uptime is in seconds. Statistics from a database that restarted an hour ago tell you very little.
• Max_used_connections is the highest number of simultaneous connections since startup. It is the honest basis for max_connections.
• The buffer pool hit ratio is 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests). A well-sized pool on a read-heavy site normally shows 99 percent or better. A cold pool right after a restart will not.

On MySQL you can also ask the server where its memory is allocated, as long as Performance Schema is on. With it off, these views return NULL or nothing:

sudo mysql -e "SELECT * FROM sys.memory_global_total;"
sudo mysql -e "SELECT event_name, current_alloc FROM sys.memory_global_by_current_bytes LIMIT 10;"

Step 3: Size the InnoDB buffer pool

The rule is to make the pool large enough for the data and indexes you read regularly, and no larger, because every megabyte you give it is taken from PHP workers and the filesystem cache. On a 1 GB server shared with PHP and the operating system, 128 to 256 MB is a common range for small WordPress sites. As a hypothetical example, if Step 1 shows 90 MB in total, a 128 MB pool holds everything with room to grow, and giving it 512 MB would only starve the rest of the server.

Once your normal traffic has run for a day, check the hit ratio from Step 2. If it is well below 99 percent and your data really does not fit, raise the pool a little at a time. You can resize it without a restart to test a value:

sudo mysql -e "SET GLOBAL innodb_buffer_pool_size = 268435456;"

That is 256 MB in bytes. The change is not permanent, so put the value you settle on in the configuration file in Step 6.

Step 4: Cap connections and temporary tables

Set max_connections a little above the highest Max_used_connections you have seen, with room for cron jobs, backups and your own admin sessions. If PHP-FPM is limited to a handful of workers, 40 is generous. Do not set it too low, because WordPress then shows “Error establishing a database connection” as soon as the limit is reached.

Limit in-memory temporary tables. tmp_table_size and max_heap_table_size together set the largest in-memory temporary table, so lower both to the same modest value such as 32 MB. If Created_tmp_disk_tables is a large share of Created_tmp_tables, a few queries are spilling to disk, and the slow query log in Step 7 will show which.

On MySQL 8.0 only, also cap the TempTable engine:

temptable_max_ram = 64M

Do not put that line in a MariaDB configuration file. MariaDB does not recognise the variable and will refuse to start.

Leave sort_buffer_size, join_buffer_size, read_buffer_size and read_rnd_buffer_size at their defaults. Guides that raise them to several megabytes multiply that cost by every connection that needs them.

Step 5: Decide whether to turn off Performance Schema

Performance Schema is MySQL’s built-in monitoring. It also holds memory, and on a very small server you may prefer to get that back. First check whether it is already off: the performance_schema line in the Step 2 output shows ON or OFF, and if it says OFF there is nothing to do in this step. If it is on, the sys.memory_global_by_current_bytes query in Step 2 shows rows named memory/performance_schema/..., which tell you how much it uses on your server. If the total is significant and you do not rely on the diagnostics, add this and restart:

performance_schema = OFF

The trade-off is that the sys views stop working, so you lose that visibility until you enable it again and restart. The slow query log in Step 7 is separate and keeps working. If you are unsure, leave it on and start with the other settings.

Step 6: Apply the changes safely

First take a backup of the database and the configuration (replace wordpress with your database name):

sudo mysqldump --single-transaction --databases wordpress | gzip > ~/wordpress-$(date +%F).sql.gz
sudo cp -a /etc/mysql ~/mysql-config-backup-$(date +%F)

Put your settings in a separate override file so package updates never overwrite them. On MySQL, name it so that it loads after the packaged mysqld.cnf, for example /etc/mysql/mysql.conf.d/zz-lowram.cnf. On MariaDB use /etc/mysql/mariadb.conf.d/99-lowram.cnf:

[mysqld]
innodb_buffer_pool_size = 128M
max_connections = 40
tmp_table_size = 32M
max_heap_table_size = 32M

These are starting points, not universal answers. Replace them with the values your own Steps 1 to 3 justify, and add the MySQL-only lines from Steps 4 and 5 if they apply. Then validate, restart, and confirm the values took effect:

sudo mysqld --validate-config
sudo systemctl restart mysql
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
sudo tail -n 20 /var/log/mysql/error.log

On MariaDB, restart the mariadb service instead and skip --validate-config. A restart interrupts the site for a few seconds, so pick a quiet time. To roll back, delete your override file and restart. Always confirm the new values with SHOW VARIABLES, because a value set in a file that loads earlier can be silently overridden by a later one.

Step 7: Find the slow queries

Tuning memory cannot fix a bad query. The slow query log shows which queries are slow. Enable it for testing without a restart:

sudo mysql -e "SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;"

New connections pick up the threshold, and MySQL writes to its default slow log file unless you configure another. To make it permanent, add these lines to your override file:

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

After a day of normal traffic, summarise the log, sorted by total time:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Repeated slow queries usually point at a plugin. Use EXPLAIN to see whether a query is scanning a whole table, and check the size of autoloaded options, expired transients and old revisions, as described in the 1 GB WordPress guide. Make sure logrotate covers the slow log so it cannot fill the disk.

What this looked like on a real 1 GB server

These are real numbers from the MySQL 8.0 server on Ubuntu 24.04 that runs a Laravel application and a WordPress blog on 911 MiB of usable RAM. They were taken after 4 hours and 22 minutes of uptime, so treat them as an example of what to read, not as targets.

What they say:

• The buffer pool is already big enough. The hit ratio is 1 – (1,414 ÷ 549,229), about 99.74 percent, so almost every read comes from memory. The misses include the reads that warm the pool after a restart, so the steady-state figure is likely higher. The default 128 MiB is enough here, and raising it would only take memory from PHP and the filesystem cache. For scale, the process measured about 188 MB resident in an earlier snapshot, and the buffer pool is roughly two-thirds of that.
• The connection limit is generous. The busiest moment used 3 connections against a limit of 50. A high limit costs nothing until connections are actually used, so there is no reason to change it.
• Temporary tables stay in memory. 1,187 were created and none went to disk, so the 16 MiB limit is not being hit. The 1 GiB temptable_max_ram default is larger than this machine’s total RAM. Nothing approaches it today, but capping it at something like 64M is a cheap safeguard against a runaway query.
• Performance Schema is off, which is why sys.memory_global_total returned NULL. That is the trade-off from Step 5: it saves memory, but the memory views need it. On this server, use ps to see what the database process uses.

The conclusion is that this database needed no tuning, and that is a legitimate result. Measuring first showed it was already right-sized, and the right move was to leave it alone.

Common mistakes

• Copying a large “my.cnf for performance” from a guide. Those files are written for big servers and can allocate more than your whole machine has.
• Raising per-connection buffers. Their cost multiplies with every connection.
• Weakening durability for speed. Leave innodb_flush_log_at_trx_commit at its default. A crash can then cost you committed data.
• Making the buffer pool bigger than the data. It gains nothing and starves PHP and the filesystem cache.
• Trusting a tuning script blindly. Scripts such as MySQLTuner give useful hints, but their advice is generic and often suggests raising values. Treat it as a checklist to investigate, not instructions.
• Tuning without a backup. Take the dump and the config copy first, every time.

When tuning is not enough

If the buffer pool hit ratio stays low because the working data genuinely exceeds the RAM you can spare, or the server swaps under ordinary load, the fix is more memory or a separate database server, not more settings. You can move to managed WordPress hosting, where the database stack is already tuned, or to a dedicated server for sustained high traffic.

Frequently asked questions

How much RAM should MySQL get on a 1 GB server?

There is no fixed answer. Measure your data size, keep the buffer pool close to it, and leave enough for PHP workers and the filesystem cache. For a small WordPress site, a buffer pool of 128 to 256 MB is a common range.

Is MariaDB lighter than MySQL?

There is no reliable general rule. Both run well on small servers, their defaults differ, and the results depend on your workload. Measure your own server instead of switching on a rumour.

Should I use MySQLTuner?

You can run it as a read-only check, but treat its recommendations as things to investigate, not settings to paste. It knows nothing about your PHP workers or the rest of your memory budget.

Do I need a separate database server?

Not until measurements say so. If the database and PHP fit comfortably together and the server is not swapping, keeping them on one machine is simpler and faster.

The short version: measure the data, size the buffer pool to it, cap connections and temporary tables, verify every change with SHOW VARIABLES, and let the slow query log point you at the real problems.