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:

# Default Value: 134217728 (128M)
innodb_buffer_pool_size = 512M
# Default Value: 16777216 (16M)
# Default Value: 16777216 (16M)
tmp_table_size = 128M
# Default Value: 131072 (128k)
# Default Value: 262144 (256k)
# Default Value: 134217728 (128M)
key_buffer_size = 256M
# 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

2 responses to “First pass at WikiApiary DB tuning”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: