0

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!

2 Answers 2

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.

0

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.

3
  • I wasn't aware of the auto restart ability, that sounds like a decent workaround. Thank you!
    – twowheeler
    Jan 3, 2023 at 21:22
  • Re: "my guess is that it's when a mailing goes out?" I thought so too, and sometimes that is correct. But this morning it crashed at 5:00 AM with no civimail scheduled, and virtually no traffic. That's when I decided to post this question.
    – twowheeler
    Jan 3, 2023 at 21:27
  • I will play with the Civimail settings. The only change I have made so far was to set the cron job limit to 1. That didn't help.
    – twowheeler
    Jan 3, 2023 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.