📪Server Runbook: MariaDB + PHP-FPM Performance Tuningtled

This document summarizes what we diagnosed, what we changed, why it helped, and how to check the same things again later.

1) Initial symptoms

From htop and later checks, the main issue was:

Disk IO saturation
High disk reads
Low CPU usage
Plenty of free RAM

That usually means the system is waiting on storage, not CPU or RAM.

What we saw

CPU load was low
RAM was mostly free
Disk usage was the main bottleneck
php-fpm workers were present, but not obviously too large
mariadbd memory usage looked too small for a busy database server on a 64 GB machine

2) Root cause

The main bottleneck was MariaDB, not PHP-FPM itself.

Why

MariaDB was configured with a very small InnoDB buffer pool:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

It returned:

134217728

That is only 128 MB.

On a server with about 64 GB RAM, that is far too small for a production workload if the database is serving real traffic.

Effect of small InnoDB buffer pool

A small buffer pool means:

Hot table/index pages do not stay in memory
MariaDB must read from disk much more often
Disk becomes saturated
PHP requests wait on DB
Site feels slow even though CPU and RAM look fine

3) Process priority after diagnosis

First priority

mariadbd

Second priority

php-fpm: pool example.de

Why PHP-FPM was not the first fix

php-fpm workers were around 120 MB to 180 MB RSS each, which was acceptable on this machine.

The real issue was that PHP requests were driving expensive DB access while MariaDB had too little cache.

4) PHP-FPM facts we checked

Command

ps -eo pid,ppid,cmd,%mem,rss --sort=-rss | grep 'php-fpm: pool example.de' | head -20

Result pattern

Workers were around:

119 MB
136 MB
148 MB
164 MB
168 MB
174 MB
182 MB

This was not the primary issue.

PHP-FPM pool config we found

grep -E '^(pm|max_children|max_requests|request_slowlog_timeout|slowlog)' /etc/php/*/fpm/pool.d/*.conf

Relevant values:

pm = ondemand
pm.max_children = 20
pm.max_requests = 500
pm.process_idle_timeout = 10s

Important note

When pm = ondemand, these settings are ignored:

pm.start_servers
pm.min_spare_servers
pm.max_spare_servers

5) MariaDB facts we checked

Check DB process memory

ps -eo pid,cmd,%cpu,%mem,rss --sort=-rss | egrep 'mysql|mariadb|postgres'

We saw MariaDB around:

RSS ≈ 1.65 GB before tuning

That was suspiciously low for this server.

Check InnoDB buffer pool size

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Before change:

134217728

After change:

8589934592

That means:

Before: 128 MB
After: 8 GB

6) Buffer pool efficiency checks

Command

mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

Before change

Innodb_buffer_pool_read_requests = 6235622312
Innodb_buffer_pool_reads         = 304543174

Approximate physical-read ratio:

304543174 / 6235622312 ≈ 4.88%

That is high for a warm production InnoDB workload.

After change

Innodb_buffer_pool_read_requests = 992953
Innodb_buffer_pool_reads         = 11419

Approximate physical-read ratio:

11419 / 992953 ≈ 1.15%

That is a major improvement.

7) Slow query we caught in processlist

We found an expensive MariaDB query through:

mysql -e "SHOW FULL PROCESSLIST;"

The query had several bad scaling patterns:

many LEFT JOIN
many OR clauses
many LIKE '%term%'
GROUP BY
HAVING
ORDER BY
ranking/scoring calculation in SQL

Why that is expensive

Queries using:

LIKE '%something%'

usually cannot use normal indexes efficiently because of the leading wildcard.

That forces scans and increases disk reads.

8) MariaDB change we made

We created a dedicated MariaDB tuning file.

File

/etc/mysql/mariadb.conf.d/60-example-tuning.cnf

Content

[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 4
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1

9) Command used to create/update the tuning file

sudo tee /etc/mysql/mariadb.conf.d/60-example-tuning.cnf >/dev/null <<'EOF'
[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 4
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
EOF

10) Restart MariaDB

sudo systemctl restart mariadb

Verify service health

systemctl --no-pager --full status mariadb

Expected good signs:

active (running)
ready for connections
no restart loop

11) Verify new buffer pool size

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Expected:

8589934592

12) Check server RAM before/after

free -h

Useful for confirming there is enough headroom.

In our case, the machine had plenty of free/available memory, so increasing MariaDB cache was appropriate.

13) Check MariaDB runtime memory usage

ps -eo pid,cmd,%cpu,%mem,rss,vsz --sort=-rss | grep mariadbd

Important note

Even after setting innodb_buffer_pool_size = 8G, MariaDB RSS may not instantly show the full amount immediately after restart.

That is normal. It often grows as the workload warms the cache.

14) Disk IO check command

iostat -xz 1

What to watch

%util
await
r/s
rkB/s
w/s
wkB/s
overall %iowait

Before tuning

Symptoms looked like:

very high disk read pressure
disk near saturation
htop showed disk 100%

After tuning

We saw:

much lower iowait
much lower %util
much lower read pressure

That confirmed the MariaDB cache increase helped.

15) Enable and verify slow query log

Check if slow query logging is enabled

mysql -e "SHOW VARIABLES LIKE 'slow_query_log';"
mysql -e "SHOW VARIABLES LIKE 'long_query_time';"
mysql -e "SHOW VARIABLES LIKE 'slow_query_log_file';"

Expected values:

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

16) Fix slow log path if the file does not exist

Create directory and file

sudo install -d -o mysql -g adm -m 0750 /var/log/mysql
sudo touch /var/log/mysql/mariadb-slow.log
sudo chown mysql:adm /var/log/mysql/mariadb-slow.log
sudo chmod 0640 /var/log/mysql/mariadb-slow.log

Restart MariaDB after that

sudo systemctl restart mariadb

Verify

ls -ld /var/log/mysql
ls -lh /var/log/mysql/

17) Test slow query log manually

A safe test query:

mysql -e "SELECT SLEEP(2);"

Because long_query_time = 1, this should appear in the slow query log.

Read the last entries

sudo tail -n 50 /var/log/mysql/mariadb-slow.log

Live watch

sudo tail -f /var/log/mysql/mariadb-slow.log

18) Useful slow-query workflow

Open one terminal and run:

sudo tail -f /var/log/mysql/mariadb-slow.log

Then reproduce slow actions in the application, for example:

customer search
order search
slow admin pages
imports
scheduled tasks

Then copy the resulting query block for analysis.

What to look for in slow queries

high Query_time
high Rows_examined
many LEFT JOIN
many LIKE '%...%'
many OR
GROUP BY
HAVING
ORDER BY

19) Command to inspect active running queries

mysql -e "SHOW FULL PROCESSLIST;"

This helps when the system is slow right now and you want to catch the live query.

20) Optional: lower slow-query threshold temporarily

If the app still feels slow but nothing interesting appears in the slow log, lower the threshold briefly:

mysql -e "SET GLOBAL long_query_time = 0.5;"

Check it:

mysql -e "SHOW VARIABLES LIKE 'long_query_time';"

Set it back later:

mysql -e "SET GLOBAL long_query_time = 1;"

Note: SET GLOBAL applies until restart unless persisted in config.

21) Commands used to inspect PHP-FPM settings

Show PHP-FPM worker sizes

ps -eo pid,ppid,cmd,%mem,rss --sort=-rss | grep 'php-fpm: pool example.de' | head -20

Show pool config values

grep -E '^(pm|max_children|max_requests|request_slowlog_timeout|slowlog)' /etc/php/*/fpm/pool.d/*.conf

Validate PHP-FPM config after changes

sudo php-fpm8.3 -t

Reload PHP-FPM

sudo systemctl reload php8.3-fpm

Check PHP-FPM service

sudo systemctl status php8.3-fpm --no-pager

22) Recommended PHP-FPM tuning for later

We did not apply this yet, but this was the recommended next-step tuning once MariaDB was stabilized.

Suggested PHP-FPM pool config

pm = dynamic
pm.max_children = 12
pm.start_servers = 4
pm.min_spare_servers = 2
pm.max_spare_servers = 6
pm.max_requests = 300
request_terminate_timeout = 120s
request_slowlog_timeout = 5s
slowlog = /var/log/php8.3-fpm/example-slow.log

Why

keeps a few workers warm
avoids excessive process churn from ondemand
limits concurrency a bit more conservatively than 20 workers
helps avoid overloading MariaDB with too many simultaneous requests

23) Suggested PHP-FPM change procedure for later

Backup config

sudo cp /etc/php/8.3/fpm/pool.d/example.de.conf /etc/php/8.3/fpm/pool.d/example.de.conf.bak

Edit config

sudo nano /etc/php/8.3/fpm/pool.d/example.de.conf

Test config

sudo php-fpm8.3 -t

Reload service

sudo systemctl reload php8.3-fpm

24) Revert MariaDB tuning if needed

If you need to revert:

Option A: remove custom tuning file

sudo rm /etc/mysql/mariadb.conf.d/60-example-tuning.cnf
sudo systemctl restart mariadb

Option B: edit and reduce the values

sudo nano /etc/mysql/mariadb.conf.d/60-example-tuning.cnf
sudo systemctl restart mariadb

25) Revert PHP-FPM tuning if needed later

sudo cp /etc/php/8.3/fpm/pool.d/example.de.conf.bak /etc/php/8.3/fpm/pool.d/example.de.conf
sudo php-fpm8.3 -t
sudo systemctl reload php8.3-fpm

26) Health-check command set for future troubleshooting

Use this group together when the server feels slow:

General memory

free -h

Disk IO

iostat -xz 1

MariaDB memory + CPU

ps -eo pid,cmd,%cpu,%mem,rss,vsz --sort=-rss | grep mariadbd

PHP-FPM memory

ps -eo pid,ppid,cmd,%mem,rss --sort=-rss | grep 'php-fpm: pool example.de' | head -20

InnoDB buffer pool size

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

InnoDB read efficiency

mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

Slow query log status

mysql -e "SHOW VARIABLES LIKE 'slow_query_log';"
mysql -e "SHOW VARIABLES LIKE 'long_query_time';"
mysql -e "SHOW VARIABLES LIKE 'slow_query_log_file';"

Live slow queries

sudo tail -f /var/log/mysql/mariadb-slow.log

Active DB queries

mysql -e "SHOW FULL PROCESSLIST;"

MariaDB service status

systemctl --no-pager --full status mariadb

PHP-FPM service status

sudo systemctl status php8.3-fpm --no-pager

27) Summary of what was actually done

We identified

Disk IO was the main bottleneck
MariaDB had an extremely undersized InnoDB buffer pool (128M)
A heavy search query pattern was contributing to DB load
PHP-FPM was not the first root cause

We changed

innodb_buffer_pool_size from 128M to 8G
enabled slow query logging
created/fixed the slow query log path

Result

disk pressure dropped significantly
iowait improved
InnoDB physical-read ratio improved
MariaDB became much less likely to force disk reads for hot data

28) Recommended next actions

Keep slow query log running during real traffic
Capture the real expensive queries from the app
Review customer/admin search behavior
Tune PHP-FPM pool only after DB behavior is confirmed stable
If needed later, consider raising innodb_buffer_pool_size further to 12G if DB workload remains heavy and RAM stays comfortable

29) One-line conclusion

The biggest fix was increasing MariaDB's InnoDB buffer pool from 128 MB to 8 GB, because the server was disk-bound due to under-cached database reads, not CPU or raw PHP memory pressure.

PreviousOur Features NextUnderstanding Projects

Last updated 23 minutes ago

hashtag1) Initial symptoms

hashtagWhat we saw

hashtag2) Root cause

hashtagWhy

hashtagEffect of small InnoDB buffer pool

hashtag3) Process priority after diagnosis

hashtagFirst priority

hashtagSecond priority

hashtagWhy PHP-FPM was not the first fix

hashtag4) PHP-FPM facts we checked

hashtagCommand

hashtagResult pattern

hashtagPHP-FPM pool config we found

hashtagImportant note

hashtag5) MariaDB facts we checked

hashtagCheck DB process memory

hashtagCheck InnoDB buffer pool size

hashtag6) Buffer pool efficiency checks

hashtagCommand

hashtagBefore change

hashtagAfter change

hashtag7) Slow query we caught in processlist

hashtagWhy that is expensive

hashtag8) MariaDB change we made

hashtagFile

hashtagContent

hashtag9) Command used to create/update the tuning file

hashtag10) Restart MariaDB

hashtagVerify service health

hashtag11) Verify new buffer pool size

hashtag12) Check server RAM before/after

hashtag13) Check MariaDB runtime memory usage

hashtagImportant note

hashtag14) Disk IO check command

hashtagWhat to watch

hashtagBefore tuning

hashtagAfter tuning

hashtag15) Enable and verify slow query log

hashtagCheck if slow query logging is enabled

hashtag16) Fix slow log path if the file does not exist

hashtagCreate directory and file

hashtagRestart MariaDB after that

hashtagVerify

hashtag17) Test slow query log manually

hashtagRead the last entries

hashtagLive watch

hashtag18) Useful slow-query workflow

hashtagWhat to look for in slow queries

hashtag19) Command to inspect active running queries

hashtag20) Optional: lower slow-query threshold temporarily

hashtag21) Commands used to inspect PHP-FPM settings

hashtagShow PHP-FPM worker sizes

hashtagShow pool config values

hashtagValidate PHP-FPM config after changes

hashtagReload PHP-FPM

hashtagCheck PHP-FPM service

hashtag22) Recommended PHP-FPM tuning for later

hashtagSuggested PHP-FPM pool config

hashtagWhy

hashtag23) Suggested PHP-FPM change procedure for later

hashtagBackup config

hashtagEdit config

hashtagTest config

hashtagReload service

hashtag24) Revert MariaDB tuning if needed

hashtagOption A: remove custom tuning file

hashtagOption B: edit and reduce the values

hashtag25) Revert PHP-FPM tuning if needed later

hashtag26) Health-check command set for future troubleshooting

hashtagGeneral memory

hashtagDisk IO

hashtagMariaDB memory + CPU

hashtagPHP-FPM memory

hashtagInnoDB buffer pool size

hashtagInnoDB read efficiency

hashtagSlow query log status

hashtagLive slow queries

hashtagActive DB queries

hashtagMariaDB service status

hashtagPHP-FPM service status

1) Initial symptoms

What we saw

2) Root cause

Why

Effect of small InnoDB buffer pool

3) Process priority after diagnosis

First priority

Second priority

Why PHP-FPM was not the first fix

4) PHP-FPM facts we checked

Command

Result pattern

PHP-FPM pool config we found

Important note

5) MariaDB facts we checked

Check DB process memory

Check InnoDB buffer pool size

6) Buffer pool efficiency checks

Command

Before change

After change

7) Slow query we caught in processlist

Why that is expensive

8) MariaDB change we made

File

Content

9) Command used to create/update the tuning file

10) Restart MariaDB

Verify service health

11) Verify new buffer pool size

12) Check server RAM before/after

13) Check MariaDB runtime memory usage

Important note

14) Disk IO check command

What to watch

Before tuning

After tuning

15) Enable and verify slow query log

Check if slow query logging is enabled

16) Fix slow log path if the file does not exist

Create directory and file

Restart MariaDB after that

Verify

17) Test slow query log manually

Read the last entries

Live watch

18) Useful slow-query workflow

What to look for in slow queries

19) Command to inspect active running queries

20) Optional: lower slow-query threshold temporarily

21) Commands used to inspect PHP-FPM settings

Show PHP-FPM worker sizes

Show pool config values

Validate PHP-FPM config after changes

Reload PHP-FPM

Check PHP-FPM service

22) Recommended PHP-FPM tuning for later

Suggested PHP-FPM pool config

Why

23) Suggested PHP-FPM change procedure for later

Backup config

Edit config

Test config

Reload service

24) Revert MariaDB tuning if needed

Option A: remove custom tuning file

Option B: edit and reduce the values

25) Revert PHP-FPM tuning if needed later

26) Health-check command set for future troubleshooting

General memory

Disk IO

MariaDB memory + CPU

PHP-FPM memory

InnoDB buffer pool size

InnoDB read efficiency

Slow query log status

Live slow queries

Active DB queries

MariaDB service status

PHP-FPM service status