MariaDB on defaults — a 128M buffer pool on a 55 GB RAM server

I was checking the health of a server five days after finishing a migration. I only meant to check whether anything had been missed during the move, but one line stopped me: innodb_buffer_pool_size was still 128 MB, even though the machine had 55 GB of RAM, an SSD, and 5.81 GB of databases running on it. That meant nobody had touched the config files since installation. So I collected all the numbers, put together a tuning plan, and wanted to walk through the whole process here.

All database and domain names in this article are fictional (db_shop, db_drupal, db_wp), but the numbers, config values, and measured results are real.

Collect a baseline before touching anything

My first rule when changing any config is that without "before" numbers, there is no way to know whether things actually improved or just feel better. So I dumped the full status to files first.

mysql -u da_admin -p -e "
  SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads',
    'Innodb_buffer_pool_wait_free',
    'Innodb_log_waits',
    'Created_tmp_disk_tables',
    'Created_tmp_tables',
    'Open_table_definitions',
    'Open_tables',
    'Max_used_connections',
    'Threads_connected',
    'Queries',
    'Uptime'
  );
" > /root/db-baseline-before.txt

mysql -u da_admin -p -e "SHOW GLOBAL VARIABLES;" > /root/db-variables-before.txt

Here is what those two files showed.

The workload was not light either. The machine had 113 databases serving 77 active domains, with 448,690 accumulated connections over 5.3 days, averaging roughly 78 per second. The average load was 2.36 on a 16 core machine, or just 15%, meaning the CPU was not the bottleneck.

The numbers showing unnecessary disk reads

What convinced me this needed fixing was not the 128 MB value itself, but these three metrics read together.

Fifty million reads in 5.3 days, averaging roughly 5 million per day. That is the number of data pages InnoDB had to read from disk because they were not in its own memory.

Put simply, the 46 GB of memory Linux was using to cache files did help, but with wasted work. Data had to be copied from the page cache into the pool, then InnoDB discarded it because there was no room to keep it. This cycle repeated five million times a day.

First trap: paths and service names on DirectAdmin + Debian differ from most guides

This is the point I most want to flag for anyone following general guides online. Most articles tell you to write a file in /etc/mysql/mariadb.conf.d/ and restart mariadb.service, but on the DirectAdmin + Debian machine I encountered, both were wrong.

# ดูว่าไฟล์ my.cnf สั่งอ่าน config เพิ่มจากที่ไหน
grep -r includedir /etc/mysql/my.cnf
# !includedir /etc/mysql/conf.d/        <- ที่นี่ ไม่ใช่ mariadb.conf.d

ls -la /etc/mysql/conf.d/
# directadmin-infile.cnf
# directadmin-max-allowed-packet.cnf
# directadmin-native-password.cnf
# directadmin-socket.cnf
# 99-row-format.cnf                     <- ของเดิมที่มีคนวางไว้แล้ว อย่าไปแตะ

# ชื่อ unit จริงบนเครื่องนี้
systemctl list-units --type=service | grep -iE 'maria|mysql'
# mysqld.service    loaded active running    MariaDB database server

Another point is that files starting with directadmin- in that directory belong to the control panel and can be overwritten at any time. I named my own file 99-tuning.cnf because 99 makes it load last, so my settings override earlier ones. The name also differs from the pattern the control panel uses, so it will not be overwritten during an update.

Phase 1: change settings live, without restarting yet

Most settings in recent MariaDB versions can be changed while the server is running, including the buffer pool. I did this phase first to see whether the numbers actually changed, without taking the risk of a restart yet.

-- 1) ขยาย buffer pool แบบ online (ใช้เวลาราว 30 วินาที)
SET GLOBAL innodb_buffer_pool_size = 8589934592;   -- 8G

-- ดูความคืบหน้าการขยาย อย่าเพิ่งไปทำอย่างอื่นจนกว่าจะ completed
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

-- 2) โปรไฟล์ I/O สำหรับ SSD (ของเดิมเป็นค่าสำหรับจานหมุน)
SET GLOBAL innodb_io_capacity     = 4000;
SET GLOBAL innodb_io_capacity_max = 10000;

-- 3) ขยาย cache ของ table handle และ table definition
SET GLOBAL table_definition_cache = 4000;
SET GLOBAL table_open_cache       = 4000;

-- 4) ตารางชั่วคราวในหน่วยความจำ ลดการตกลงดิสก์เวลา GROUP BY / ORDER BY ใหญ่
SET GLOBAL tmp_table_size      = 67108864;   -- 64M
SET GLOBAL max_heap_table_size = 67108864;

-- 5) เปิด slow log เพื่อเก็บข้อมูลไว้ใช้รอบหน้า
SET GLOBAL slow_query_log              = ON;
SET GLOBAL long_query_time             = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

-- 6) คืน RAM จาก key buffer ของ MyISAM ให้ pool เพราะข้อมูลส่วนใหญ่เป็น InnoDB แล้ว
SET GLOBAL key_buffer_size = 33554432;       -- 32M

I was comfortable raising innodb_io_capacity from 200 to 4000 because the original value assumes a spinning disk. Checking the actual hardware is straightforward.

lsblk -d -o NAME,ROTA,SIZE,MODEL
# NAME ROTA  SIZE
# sda     0  183G     <- ROTA=0 คือ SSD

This setting does not "speed up" writes by forcing more of them. It tells InnoDB how much background I/O budget it can use. If it is set too low for the hardware, InnoDB holds back flush writes until they pile up at a checkpoint, causing periodic stalls instead.

Phase 2: write the file, then restart once

I watched the results for about four hours. Once I saw a clear drop in the disk read rate and nothing unusual, I wrote the full set of values to a file to make them permanent.

# /etc/mysql/conf.d/99-tuning.cnf
[mysqld]

# === Memory ===
innodb_buffer_pool_size      = 8G
innodb_buffer_pool_instances = 8
innodb_log_file_size         = 512M
innodb_log_buffer_size       = 32M
key_buffer_size              = 32M

# === I/O profile สำหรับ SSD ===
innodb_flush_method          = O_DIRECT
innodb_io_capacity           = 4000
innodb_io_capacity_max       = 10000
innodb_read_io_threads       = 8
innodb_write_io_threads      = 8
innodb_flush_neighbors       = 0
innodb_flush_log_at_trx_commit = 1

# === Concurrency ===
max_connections   = 200
thread_cache_size = 32

# === Caches ===
table_definition_cache = 4000
table_open_cache       = 4000
tmp_table_size         = 64M
max_heap_table_size    = 64M

# === Monitoring ===
slow_query_log              = 1
slow_query_log_file         = /var/log/mysql/slow.log
long_query_time             = 1
log_queries_not_using_indexes = 1

There are two lines I want to explain further because people often ask about them.

innodb_flush_neighbors = 0 disables InnoDB's behavior of writing adjacent pages together to reduce head movement, which is purely a concern for spinning disks. An SSD has no head to move, so grouping writes that way just causes unnecessary writes.

I kept innodb_flush_log_at_trx_commit = 1 at its original value. Setting it to 2 would make writes roughly three times faster, at the risk of losing the last second of data if power fails. On a machine holding other people's data, I do not think that tradeoff is worthwhile. For me, the final answer is 1. If it is your own machine and you can recreate any lost data, that is your choice.

This is the restart procedure I follow every time.

1. Back up the full original state first: SHOW GLOBAL VARIABLES, SHOW GLOBAL STATUS, and the original my.cnf file. Keep them in a dated folder.
2. Always check config syntax first, using a command that has the daemon read the file itself and report whether it can read it.
3. Choose a time between two and four in the morning, when traffic is lowest.
4. Restart and time it, then send requests to every website to check that they really are back, rather than just checking that the service is up.

# 2) ตรวจไฟล์ก่อน ถ้าไวยากรณ์ผิดจะได้รู้ตอนนี้ ไม่ใช่ตอน service ไม่ยอมขึ้น
mysqld --help --verbose > /dev/null && echo "[OK] config อ่านได้"

# 4) restart แล้วจับเวลาจริง
time systemctl restart mysqld

# ตรวจว่าค่าเข้าจริงไหม อย่าเชื่อว่าเขียนไฟล์แล้วคือเสร็จ
mysql -u da_admin -p -e "
  SHOW VARIABLES WHERE Variable_name IN (
    'innodb_buffer_pool_size','innodb_io_capacity',
    'innodb_log_file_size','slow_query_log');"

# ยิงเช็คเว็บทุกโดเมนที่หนักสุด
for d in shop.example blog.example forum.example; do
  printf '%s -> ' "$d"
  curl -s -o /dev/null -w "%{http_code}\n" "https://$d/"
done

The actual result this time was 6.5 seconds of downtime, and all 9 websites I checked returned 200. My estimate on paper was 3 to 5 seconds, so the actual time was slightly longer because the redo log files had to be recreated at the new size. A user loading a page at that moment would see a database connection error once, then a refresh would clear it. If even one second is unacceptable, you need a different approach: set up a replica and switch over, which is a separate subject altogether.

The full before-and-after settings

The reasoning behind the numbers is straightforward. The databases on the machine total 5.81 GB, and I set the pool to 8 GB, larger than all the data. That means all the hot data can potentially stay in memory at all times. I did not set it to 16 GB despite the spare RAM because I also needed to leave room for the web server, mail, and other work on the same machine during peak periods. My worst-case calculation looked roughly like this.

# กรณีแย่สุดที่ MariaDB จะกินได้
#   buffer pool 8G                        = 8,192 MB
#   key_buffer 32M                        =    32 MB
#   200 connection x ~2.9 MB/thread       =   580 MB
#   log buffer 32M + table caches ~50M    =    82 MB
#   ------------------------------------------------
#   รวม                                   = 8,886 MB  (~8.7 GB)
# เหลือให้ Linux + DA + web + mail + buff/cache อีกราว 46 GB

Raising table_definition_cache from 400 to 4000 also has a straightforward reason. This machine has 113 databases with more than five thousand tables in total. A value of 400 means it constantly has to close and reopen table definitions, doing work that serves no useful purpose.

Always prepare rollback first

I never touch database config without writing down a way back first, because the time I need it will be at three in the morning when I can no longer think clearly.

# เตรียมก่อนลงมือ — เก็บของเดิมไว้ให้ครบ
mkdir -p /root/rollback-$(date +%F)/db-tune
cp /etc/mysql/my.cnf /root/rollback-$(date +%F)/db-tune/
mysql -u da_admin -p -e "SHOW GLOBAL VARIABLES;" \
  > /root/rollback-$(date +%F)/db-tune/variables-before.txt

# ถอยจริง — เอาไฟล์ออกแล้ว restart ก็กลับไปค่าเดิมทั้งหมด
mv /etc/mysql/conf.d/99-tuning.cnf /root/99-tuning.cnf.disabled
systemctl restart mysqld

Rollback is this simple because I put the new settings in one separate file, without editing the original my.cnf or the control panel's files. Remove one file and it is done. My personal rule is never to edit files owned by another system; always add a separate file of my own.

Phase 3: around five hundred more MyISAM tables waiting for conversion

During the inspection, I found roughly 500 MyISAM tables still in the system, mostly from older websites carried along through migrations.

The main reason to convert is not speed, but these two points. First, MyISAM locks the entire table during writes; while one client writes, everyone else waits. When several people use a website at once, this can look like the whole site freezing. Second, MyISAM has no crash recovery. If power fails partway through, you may end up with a corrupt table. The fact that InnoDB uses the buffer pool I just expanded to 8 GB, while MyISAM only gets a 32 MB key buffer, is a bonus.

My approach is to have the database generate the ALTER commands itself, so I do not have to type them manually.

-- ดูรายการก่อน เรียงจากตารางเล็กไปใหญ่ เพื่อไล่ทำจากตัวที่เสี่ยงน้อยที่สุด
SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') AS stmt,
       ROUND((data_length + index_length)/1024/1024, 1) AS mb
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY (data_length + index_length) ASC;

# พอดูรายการแล้วพอใจ ค่อยสั่งทำจริงทีละฐานข้อมูล เริ่มจากตัวเล็กสุด
mysql -u da_admin -p -Nse "
  SELECT CONCAT('ALTER TABLE \`db_wp\`.\`', table_name, '\` ENGINE=InnoDB;')
  FROM information_schema.tables
  WHERE engine='MyISAM' AND table_schema='db_wp';
" | mysql -u da_admin -p

I have not finished this phase yet. I plan to work through one website at a time, notifying its owner before each one, because some sites use very old plugins that still issue MyISAM-specific commands. Those need testing first, or I risk causing avoidable problems.

An unexpected side effect

Within 3 seconds of enabling the slow log, the file had already grown to 171 KB. At first I thought I had set long_query_time incorrectly, but when I opened the file, there really were a lot of queries taking more than one second. Most came from plugins and themes on customer websites that issued queries without supporting indexes.

To briefly sum up what I found, this machine had plenty of hardware capacity from day one, but the software was configured to use only a fraction of it because nobody had checked. Increasing the buffer pool from 128 MB to 8 GB and matching the I/O profile to the actual disk took less than half an hour in total, with 6.5 seconds of downtime. There was no need to spend a single baht on anything extra.

If you apply this yourself, do not copy my entire set of numbers. Work out the settings from your machine's actual RAM and database size first. The principle that applies to every machine is that the pool should be large enough to hold all frequently accessed data, while leaving breathing room for other services on the same machine. If I have left anything out or made a typo, I will come back and correct it later.