Help with mariadb crash, out of memory error

Question

I'm trying to track down the cause of an occasional mariadb crash on my server. The system is a Linode 4GB Ubuntu 22.04 instance running Wordpress 6.1.1, apache2 2.4.52, mariadb 10.6.11, and civicrm 5.56.0.

Generally it runs fine, but once in a while it returns a 500 database connection error. Mariadb is getting killed by oom-killer at the top of the hour, when the cronjobs kick off. Restarting mariadb returns it to normal function. This is happening more frequently, maybe once or twice a week. System memory does not seem to be an issue (this is when there is not much traffic on the site):

 $ free -m
                total        used        free      shared  buff/cache   available
 Mem:            3923         829        2088         128        1006        2712
 Swap:            511          53         458

The cronjobs look like:

*/5  * * * *    cd /var/www/html/; /usr/local/bin/wp --user=cronuser --url=https://thehcpac.org --path=/var/www/html/ cron event run --due-now > /tmp/cron.log
*/10 * * * *    cd /var/www/html/; /usr/local/bin/wp --user=cronuser --url=https://thehcpac.org --path=/var/www/html/ --timezone=America/New_York civicrm api job.execute auth=0 > /tmp/civicrm.log

Which seem pretty normal.

So based on what I have read online, maybe the mariadb variables are not set correctly for this situation?

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+
| read_buffer_size | 131072 |
+------------------+--------+
| sort_buffer_size | 2097152 |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

I'm not any kind of database tuning expert, and am hoping for a pointer from someone who is. Thanks!

Answer 1

The default innodb_buffer_pool_size is a very low 134217728. I bumped it up by adding

[mysqld]
innodb_buffer_pool_size = 2G

to /etc/mysql/mariadb.conf.d/50-server.cnf

The problem has not recurred. So this was a generic mariadb setup issue, not really related to Civicrm.

Answer 2

Without addressing the MariaDB tuning directly:

Consider having the MariaDB daemon restart automatically.

I started seeing this problem after a Mosaico upgrade (2.8 maybe?). You say it's happening during times of low traffic, but my guess is that it's when a mailing goes out?

I haven't found a client who's affected enough to justify profiling this with XDebug, but you may want to try setting batch limits in the CiviMail settings to see if that alleviates your issue.