Reducing memory use on my MariaDB/Mysql Wordpress docker stack
I found that after migrating in from a locally installed Wordpress duo on a 2GB VPS, my new instance started crashing once a day, due to running out of memory.
I have a 1GB VPS server (AWS Lightsail) running two Wordpress sites in a Traefik/Docker stack. I found that after migrating in from a locally installed Wordpress duo on a 2GB VPS, my new instance started crashing once a day, due to running out of memory.
On a fresh boot, my total memory use is around 500GB. Soon after, usage creeps up to the 700-800MB range. Then, at some point an extra load of an extra process would pass the point of no return with memory use, and the system would start swapping, with CPU rising to 100%. Either that or one of the two MariaDB instances would fail.
A quick docker stats command reveals the current usage is quite high. Note mariadb2 here has been freshly restarted with reduced my.cnf parameters. Earlier today it was using 120MB. It's good to compare with mariadb1, which is out of the box with no tuning applied. The difference between 35MB and 152MB is significant.
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
a455488efbea traefik_mariadb2_1 0.03% 35.36MiB / 984MiB 3.59% 586B / 0B 115MB / 4.31MB 30
11185cb412f2 traefik_mariadb1_1 0.03% 152.1MiB / 984MiB 15.46% 18.8MB / 44MB 3.62GB / 1.59GB 32
afb662528173 traefik_nginx-wp1_1 0.00% 2.445MiB / 984MiB 0.25% 1.53MB / 1.64MB 110MB / 0B 2
6b91947c4c55 traefik_wp2_1 0.00% 118.5MiB / 984MiB 12.04% 113MB / 41.6MB 2.4GB / 60.8MB 4
e9ca7b1b9714 traefik_wp1_1 0.00% 130.3MiB / 984MiB 13.24% 45MB / 20.4MB 3.03GB / 418kB 4
36a6aa19fae5 traefik_traefik_1 0.08% 16.32MiB / 984MiB 1.66% 102MB / 105MB 29.6GB / 0B 8
f7bb0fbed0fa traefik_nginx-wp2_1 0.00% 2.309MiB / 984MiB 0.23% 14MB / 16.7MB 112MB / 0B 2
ffe3addaf68c traefik_adminer_1 0.00% 4.082MiB / 984MiB 0.41% 34kB / 0B 389MB / 0B 1
8c22df7d1c59 traefik_watchtower_1 0.00% 2.898MiB / 984MiB 0.29% 4.34kB / 0B 188MB / 0B 7Note I no longer use Watchtower.
Disclaimer.. this is messing with the DB parameters
Approach with caution, especially if you have any sort of real uptime aspiration. This worked for me with two fairly lightly loaded Wordpress instances, on a 1GB server, with Docker, on Ubuntu 18.04, with my particular WP configuration. Instances that are barely production, that I can tolerate being down for a while occasionally.
New parameters for MariaDB / MySQL
Borrowing some good (but 2014) info from this blog, I took the suggested parameters and applied to a my.cnf file I extracted from the container. I took the approach that if the parameter already exists, I will modify it. If not, I will do nothing.
I'll add the whole my.cnf file to the end of the post but I would recommend you don't copy it exactly: extract a file from the latest container version and modify it. As we're putting it in an !include directory, comment out the !includes at the end to prevent recursion. This one is from MariaDB container version 10.4.7. These are the changes I made.
max_connections to 10, was 100
query_cache_size to 512K, was 64M
thread_cache_size to 0, was 128
sort_buffer_size to 32K, was 4M
bulk_insert_buffer_size to 0, was 16M
tmp_table_size to 1K, was 32M
max_heap_table_size to 16K, was 32M
key_buffer_size to 1M, was 128M
read_buffer_size to 8K, was 2M
read_rnd_buffer_size to 8K, was 1M
innodb_buffer_pool_size to 10M, was 256M
innodb_log_buffer_size to 512K, was 8MUpdate Jan 2021: Recently I performed a container image upgrade to Matomo V4. It's not Wordpress related but also uses MariaDB (MySQL) and I had this same set. I began to see database errors "1038 Out of sort memory". In response I upped sort_buffer_size to 64K, and it fixed the problem.
Attaching the new my.cnf file to the container
I move the file into the /data/mariadb1 directory. In the docker-compose.yml file I update the volume mapping for my mariadb2 service. Paths may vary with MariaDB version or if you are using the official MySQL image.
Here the new my.cnf file is being mapped into the container /etc/mysql/conf.d directory. The default my.cnf will read anything with a .cnf suffix in that directory and override any current settings.
volumes:
- /data/mariadb2:/var/lib/mysql
- /data/mariadb2/my.cnf:/etc/mysql/conf.d/my.cnfA quick docker-compose up -d later, and a revisit to docker stats tells me my mariadb1 instance has gone from 156.7MiB memory use to 48.82MiB. My overall server usage is now 570MB ish.
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
3bca4043e58a traefik_mariadb1_1 0.04% 48.82MiB / 984MiB 4.96% 676kB / 14.2MB 184MB / 9.86MBPage load performance test after the change
Eyeballing the page loads looks good, so I run it through webpagetest.org to see what I can see. Page loads are just over 5 seconds, no worse at all than before. However, recall that use of the admin panel, or say the comments system, will add uncachable load to the database. Only time will tell if the reduced settings will cause a problem. Make sure you have access to the database logs and keep tabs on it, especially under load.
Example my.cnf - MariaDB/MySQL reduced memory usage
Extracted from the container, and modified.
# MariaDB database server configuration file.
# https://techroads.org - use at your own risk
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
#user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
#max_connections = 100
max_connections = 10
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
#thread_cache_size = 128
thread_cache_size = 0
#sort_buffer_size = 4M
sort_buffer_size = 32K
#bulk_insert_buffer_size = 16M
bulk_insert_buffer_size = 0
#tmp_table_size = 32M
tmp_table_size = 1K
#max_heap_table_size = 32M
max_heap_table_size = 16K
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
#key_buffer_size = 128M
key_buffer_size = 1M
#open-files-limit = 2000
table_open_cache = 400
myisam_sort_buffer_size = 512M
concurrent_insert = 2
#read_buffer_size = 2M
read_buffer_size = 8K
#read_rnd_buffer_size = 1M
read_rnd_buffer_size = 8K
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit = 128K
#query_cache_size = 64M
query_cache_size = 512K
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type = DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
#log_warnings = 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit = 1000
#log_slow_verbosity = query_plan
#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#report_host = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
#log_bin = /var/log/mysql/mariadb-bin
#log_bin_index = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog = 1
expire_logs_days = 10
max_binlog_size = 100M
# slaves
#relay_log = /var/log/mysql/relay-bin
#relay_log_index = /var/log/mysql/relay-bin.index
#relay_log_info_file = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size = 50M
#innodb_buffer_pool_size = 256M
innodb_buffer_pool_size = 10M
#innodb_log_buffer_size = 8M
innodb_log_buffer_size = 512K
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completion
[isamchk]
key_buffer = 16M
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
# As this lives in the container's conf.d directory, the includes would start a recursive loop, so comment them out
#!include /etc/mysql/mariadb.cnf
#!includedir /etc/mysql/conf.d/Main photo courtesy of Harrison Broadbent on Unsplash.
Retrospective blog post to use BlueSky for comments: techroads.org/reducing-mem... #MariaDB
— TechRoads blog (@techroads.org) Feb 22, 2024 at 11:38 am