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
2 responses to “First pass at WikiApiary DB tuning”
[…] wrote about some of that work last Friday, but I did some more tinkering and the site seems to be working better. A brief summary of what I […]
[…] wrote about max_statement_time earlier, so I’ll talk a bit about what I did with the crawlers […]