First pass at WikiApiary DB tuning

---

Today, after asking for help with DB tuning on WikiApiary, Bernhard Krabina gave me some settings that had been used on a wiki with what looked like a ton of memory.

I didn’t realize this at first, of course. No, I just put the settings into mysqld.cnf.

That worked for a few minutes, but then the site died again. I ended up with OOM messages in the /var/log/syslog:

systemd[1]: mariadb.service: A process of this unit has been killed by the OOM killer.

Once I did more careful checking, I saw that they were allocating 20G of RAM for some settings. I don’t have that, so, of course the OOM killer struck.

I did some research to find more reasonable settings and made some changes. So far, so good.

In case it helps someone else, here are the settings I ended up with on a system with about 6GB of RAM:

# https://mariadb.com/docs/reference/mdb/system-variables/innodb_buffer_pool_size/
# Default Value: 134217728 (128M)
innodb_buffer_pool_size = 512M
# https://mariadb.com/docs/reference/mdb/system-variables/max_heap_table_size/
# Default Value: 16777216 (16M)
max_heap_table_size=128M
# https://mariadb.com/docs/reference/mdb/system-variables/tmp_table_size/
# Default Value: 16777216 (16M)
tmp_table_size = 128M
# https://mariadb.com/docs/reference/mdb/system-variables/read_buffer_size/
# Default Value: 131072 (128k)
read_buffer_size=10M
# https://mariadb.com/docs/reference/mdb/system-variables/join_buffer_size/
# Default Value: 262144 (256k)
join_buffer_size=1M
# https://mariadb.com/docs/reference/mdb/system-variables/key_buffer_size/
# Default Value: 134217728 (128M)
key_buffer_size = 256M
# https://mariadb.com/docs/reference/mdb/system-variables/sort_buffer_size/
# Default Value: 2097152 (2M)
sort_buffer_size = 10M

Later update: ok, that worked for a bit, but I still ended up with some long running DB queries that flooded the server and the bot’s aren’t even crawling yet. Fine. I’ve taken a sledgehammer to the connection time allowed by setting MAX_STATEMENT_TIME to 30 for the DB user that php is using.

Image credit: Retired electrician, CC0, via Wikimedia Commons