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



## tunage (Jun 29, 2015)

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.


```
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.


```
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


```
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:


```
# 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?


----------



## wblock@ (Jun 29, 2015)

tunage said:


> There are no cronjobs on the system that have been configured.



Really?  The system /etc/crontab has some by default.  And periodic daily runs at 0301.



tunage said:


> swapon /dev/md0


Please show /etc/fstab.  Swapping to a memory disk is probably a mistake.


----------



## tunage (Jun 30, 2015)

I personally have not configured any cron jobs. Anything on the system is a default.

Here is my crontab file.

```
# 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:

```
# 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
```


----------



## tunage (Jun 30, 2015)

wblock@ said:


> Really?  The system /etc/crontab has some by default.  And periodic daily runs at 0301.
> 
> 
> Please show /etc/fstab.  Swapping to a memory disk is probably a mistake.


^^


----------



## wblock@ (Jun 30, 2015)

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).


----------



## tunage (Jun 30, 2015)

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).




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


```
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)
```


----------



## SirDice (Jun 30, 2015)

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:

```
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
```


----------



## usdmatt (Jun 30, 2015)

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



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 `find`s. I've not come across the daily maintenance causing excessive memory use though.


----------



## tunage (Jun 30, 2015)

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:
> 
> ...


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


----------



## tunage (Jun 30, 2015)

Slightly different result this morning. MySQL still blew up, but the swapfile had not been blown to smithereenze.

```
# 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%
```


----------



## SirDice (Jun 30, 2015)

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


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.


----------



## SirDice (Jun 30, 2015)

tunage said:


> slightly different result this morning. MySQL still blew up, but the swapfile had not been blown to smithereenze.
> 
> ```
> # grep -i mysql /var/log/messages
> ...


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).


----------



## tunage (Jun 30, 2015)

There is a hash map error in the logs, but it happens at many different times. I had ruled it out, but could be

```
[\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.


----------



## tunage (Jun 30, 2015)

Just ran
`make -C /etc/mail aliases`

Won't know for sure till tomorrow/later this morning.


----------



## tunage (Jul 1, 2015)

tunage said:


> Just ran
> `make -C /etc/mail aliases`
> 
> Won't know for sure till tomorrow/later this morning.



I guess that was the issue.
Still good.


----------



## SirDice (Jul 2, 2015)

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.


----------

