mysql farts and then dies at 0300 every day, no cron jobs

T

tunage

I have a low memory VPS that MySQL and the swap file crash out every morning at exactly 0300. There are no cronjobs on the system that have been configured. The server is a basic LAMP development server and all settings are defaults.

Code: 
cat /var/log/messages|grep -i mysql
Jun 25 20:51:07 vader sshd[72946]: error: PAM: authentication error for mysql from 65.52.155.140
Jun 28 03:01:34 vader kernel: pid 848 (mysqld), uid 88, was killed: out of swap space
Jun 28 03:01:34 vader kernel: pid 93947 (mysqld), uid 88, was killed: out of swap space
Jun 29 03:01:32 vader kernel: pid 98578 (mysqld), uid 88, was killed: out of swap space
Jun 29 03:01:33 vader kernel: pid 2586 (mysqld), uid 88, was killed: out of swap space

My swap file is 1 gig. I tried 2 gigs, the same pattern of crashing begins after a week.

Code: 
ls -l /home/sw*
-rw------- 1 root wheel 1073741824 Jun 21 13:19 /home/swap0

Even worse, I can't re initialized the swap file without a reboot

Code: 
swapoff -a mdconfig -a -t vnode -f /home/swap0 -u 0 && swapon /dev/md0 mdconfig: ioctl(/dev/mdctl): Device busy

I don't have much memory to work with:

Code: 
# vmstat
procs memory page disks faults cpu
r b w avm fre flt re pi po fr sr vt0 md0 in sy cs us sy id
1 0 3 1709M 491M 44 0 0 0 54 29 0 0 4 135 94 0 0 100

But I shouldn't need it if MySQL was running correctly.

2 questions.

1 How do I reinitialize a swap file after it crashes so I don't have to reboot (I would just like to know because everything I find on Google fails)

2 How do I stabilize MySQL so that it doesn't burp at 0300 for massive amounts of memory?
 
I personally have not configured any cron jobs. Anything on the system is a default.

Here is my crontab file.
Code: 
# cat /etc/crontab
# /etc/crontab - root's crontab for FreeBSD
#
# $FreeBSD: releng/10.1/etc/crontab 194170 2009-06-14 06:37:19Z brian $
#
SHELL=/bin/sh
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin
#
#minute hour  mday  month  wday  who  command
#
*/5  *  *  *  *  root  /usr/libexec/atrun
#
# Save some entropy so that /dev/random can re-seed on boot.
*/11  *  *  *  *  operator /usr/libexec/save-entropy
#
# Rotate log files every hour, if necessary.
0  *  *  *  *  root  newsyslog
#
# Perform daily/weekly/monthly maintenance.
1  3  *  *  *  root  periodic daily
15  4  *  *  6  root  periodic weekly
30  5  1  *  *  root  periodic monthly
#
# Adjust the time zone if the CMOS clock keeps local time, as opposed to
# UTC time.  See adjkerntz(8) for details.
1,31  0-5  *  *  *  root  adjkerntz -a
Here is my fstab:
Code: 
# cat /etc/fstab
/dev/vtbd0p2 / ufs rw 0 0
linproc  /compat/linux/proc  linprocfs rw  0  0
md0  none  swap  sw,file=/home/swap0  0  0
fdesc  /dev/fd  fdescfs  rw  0  0
 
Okay, you are using a swap file. The error is probably due to the md0 device still being in use. It would have to be "detached" before being reused. See mdconfig(8).

That is a default /etc/crontab. Periodic system jobs run at night. Depending on your system, you might be able to reduce what is run. See periodic.conf(5).
 
wblock@ said:
Okay, you are using a swap file. The error is probably due to the md0 device still being in use. It would have to be "detached" before being reused. See mdconfig(8).

That is a default /etc/crontab. Periodic system jobs run at night. Depending on your system, you might be able to reduce what is run. See periodic.conf(5).
Click to expand...


cron seems to run the same 3 scripts all the time, why would they crash on only at 0300?

Code: 
Jun 29 02:50:00 vader /usr/sbin/cron[1356]: (root) CMD (/usr/libexec/atrun)
Jun 29 02:55:00 vader /usr/sbin/cron[1743]: (operator) CMD (/usr/libexec/save-entropy)
Jun 29 02:55:00 vader /usr/sbin/cron[1744]: (root) CMD (/usr/libexec/atrun)
Jun 29 03:00:00 vader /usr/sbin/cron[2140]: (root) CMD (newsyslog)
Jun 29 03:00:00 vader /usr/sbin/cron[2141]: (operator) CMD (/usr/libexec/save-entropy)
Jun 29 03:00:00 vader /usr/sbin/cron[2142]: (root) CMD (/usr/libexec/atrun)
Jun 29 03:01:00 vader /usr/sbin/cron[2230]: (root) CMD (adjkerntz -a)
Jun 29 03:01:00 vader /usr/sbin/cron[2231]: (root) CMD (periodic daily)
Jun 29 03:05:00 vader /usr/sbin/cron[2865]: (root) CMD (/usr/libexec/atrun)
 
Can you install and run databases/tuning-primer? I'm interested in knowing if MySQL is configured correctly and not using too much memory.

This is the most important bit:
Code: 
MEMORY USAGE
Max Memory Ever Allocated : 2.18 G
Configured Max Per-thread Buffers : 187 M
Configured Max Global Buffers : 2.08 G
Configured Max Memory Limit : 2.26 G
Physical Memory : 3.24 G
Max memory limit seem to be within acceptable norms
 
cron seems to run the same 3 scripts all the time. why would they crash on only at 0300?
Click to expand...

The scripts that run every few minutes do very little and will put practically no load on the system. atrun could, but only if you actually give it something to do using the at() command. I highly doubt the issue has anything to do with those cron jobs that run throughout the day.

Considering it happens at 03:01 is does really point to periodic daily being involved. The daily periodic script is known to cause problems for people with large ZFS pools as it does some fairly big finds. I've not come across the daily maintenance causing excessive memory use though.
 
SirDice said:
Can you install and run databases/tuning-primer? I'm interested in knowing if MySQL is configured correctly and not using too much memory.

This is the most important bit:
Code: 
MEMORY USAGE
Max Memory Ever Allocated : 2.18 G
Configured Max Per-thread Buffers : 187 M
Configured Max Global Buffers : 2.08 G
Configured Max Memory Limit : 2.26 G
Physical Memory : 3.24 G
Max memory limit seem to be within acceptable norms
Click to expand...
I am not sure if I did this correctly, but based on the output, there are a few settings that could be adjusted from the defaults. But I also can not be certain the script read my actual my.cnf or if it made up it's own: http://paste.ee/p/o5jzX
 
Slightly different result this morning. MySQL still blew up, but the swapfile had not been blown to smithereenze.
Code: 
# grep -i mysql /var/log/messages
Jun 28 03:01:34 vader kernel: pid 848 (mysqld), uid 88, was killed: out of swap space
Jun 28 03:01:34 vader kernel: pid 93947 (mysqld), uid 88, was killed: out of swap space
Jun 29 03:01:32 vader kernel: pid 98578 (mysqld), uid 88, was killed: out of swap space
Jun 29 03:01:33 vader kernel: pid 2586 (mysqld), uid 88, was killed: out of swap space
Jun 30 03:01:33 vader kernel: pid 1252 (mysqld), uid 88, was killed: out of swap space
Jun 30 03:01:34 vader kernel: pid 3083 (mysqld), uid 88, was killed: out of swap space

[\u@vader:/usr/ports/databases/tuning-primer] # swapinfo -k
Device  1K-blocks  Used  Avail Capacity
/dev/md0  1048576  27880  1020696  3%
 
tunage said:
I am not sure if I did this correctly, but based on the output, there are a few settings that could be adjusted from the defaults. But I also can not be certain the script read my actual my.cnf or if it made up it's own http://paste.ee/p/o5jzX
Click to expand...
The script looks at the current settings and usage. MySQL has to be running for at least 48 hours before the values are any good. But a quick look shows at least it's not using too much memory.

To elaborate, I recently fixed a badly performing MySQL database. It was configured to use 1200 GB (!) of memory and the machine only had 24 GB. Some dork had been "tuning" it without actually knowing how.
 
tunage said:
slightly different result this morning. MySQL still blew up, but the swapfile had not been blown to smithereenze.
Code: 
# grep -i mysql /var/log/messages
Jun 28 03:01:34 vader kernel: pid 848 (mysqld), uid 88, was killed: out of swap space
Jun 28 03:01:34 vader kernel: pid 93947 (mysqld), uid 88, was killed: out of swap space
Jun 29 03:01:32 vader kernel: pid 98578 (mysqld), uid 88, was killed: out of swap space
Jun 29 03:01:33 vader kernel: pid 2586 (mysqld), uid 88, was killed: out of swap space
Jun 30 03:01:33 vader kernel: pid 1252 (mysqld), uid 88, was killed: out of swap space
Jun 30 03:01:34 vader kernel: pid 3083 (mysqld), uid 88, was killed: out of swap space
Click to expand...
This info, while very concerning, isn't really relevant. This is just the result of your machine running out of memory. You need to find out why that happens. It's much more interesting to know what happened before this. And it doesn't necessariy have to be MySQL that's causing it (and looking at the data you posted it's unlikely the cause).
 
There is a hash map error in the logs, but it happens at many different times. I had ruled it out, but could be
Code: 
[\u@vader:/usr/ports/databases/tuning-primer] # grep -i 'Jun 30 03:0' /var/log/messages
Jun 30 03:01:29 vader sm-mta[3052]: t5U31TW6003052: SYSERR(root): hash map "Alias0": missing map file /etc/mail/aliases.db: No such file or directory
Jun 30 03:01:29 vader sm-mta[3073]: t5U31TW6003052: SYSERR(root): hash map "Alias0": missing map file /etc/mail/aliases.db: No such file or directory
Jun 30 03:01:33 vader kernel: pid 1252 (mysqld), uid 88, was killed: out of swap space
Jun 30 03:01:34 vader kernel: pid 3083 (mysqld), uid 88, was killed: out of swap space
Jun 30 03:01:36 vader sm-mta[3092]: t5U31aKY003092: SYSERR(root): hash map "Alias0": missing map file /etc/mail/aliases.db: No such file or directory
Jun 30 03:01:36 vader sm-mta[3093]: t5U31aKY003092: SYSERR(root): hash map "Alias0": missing map file /etc/mail/aliases.db: No such file or directory


no other error of relevance in the logs.
 
I doubt that had anything to do with the crashes. All the command does is rebuild the alias database. After any change to /etc/aliases you need to run newaliases(1) (shortcut for make -C /etc/mail aliases). I have noticed this isn't always done after an update.
 
You must log in or register to reply here.
Back
Top