swap_pager_getswapspace: failed, MySQL troubles
on FreeBSD 7.2 cause and solution
Every now and then my FreeBSD router
dmesg (
/var/log/dmesg.today ) logs, gets filled with error messages
like:
pid 86369 (httpd), uid 80, was killed: out of swap
space
swap_pager_getswapspace(14): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(11): failed
swap_pager_getswapspace(12): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(14): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(8): failed
Using
swapinfo during the
swap_pager_getswapspace(16):
failed messages were logged in, I figured out that definitely
the swap memory over-use is the bottleneck for the troubles, to
find this I used the command:
freebsd# swapinfo
Device 1K-blocks Used Avail Capacity Type
/dev/ad0s1b 49712 45920 3792 92% Interleaved
After some investigation, I've figured out that the MySQL server is
causing the kernel exceeded swap troubles.
My current MySQL server version is installed from the ports tree,
whether I'm using the bsd port
/usr/ports/databases/mysql51-server/ and it appears to work
just fine.
However I have noticed that the
mysql-server is missing a my.cnf
file!, which means the mysql server is running under a mode
with some kind of default configurations.
Strangely in the system process list it appeared it is using a
default my.cnf file located in
/var/db/mysql/my.cnf
Below you see the paste from the ps command:
ps axuww freebsd# ps axuww | grep -i my.cnf | grep -v
grep
mysql 7557 0.0 0.1 3464 1268 p1 I 12:03PM 0:00.01 /bin/sh
/usr/local/bin/mysqld_safe
--defaults-extra-file=/var/db/mysql/my.cnf --user=mysql
--datadir=/var/db/mysql --pid-file=/var/db/mysql/pcfreak.pid mysql
7589 0.0 5.1 93284 52852 p1 I 12:03PM 0:59.01
/usr/local/libexec/mysqld
--defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local
--datadir=/var/db/mysql --user=mysql
--pid-file=/var/db/mysql/pcfreak.pid --port=3306
--socket=/tmp/mysql.sock
Nevertheless it appeared the sql server is running the file
/var/db/mysql/my.cnf conf was not existing! This was really
weird for me as I'm used to have the default my.cnf from my
previous experience with Linux servers!
Thus the next logical thing I did was to create my.cnf conf file in
order to be able to have a proper limiting configuration for the
sql server.
The FreeBSD
my.cnf skele files are found in
/usr/local/share/mysql/, here are the 4 files one can use as
a starting basis for further configuration of the
mysql-server.
freebsd# ls -al /usr/local/share/mysql/my-*.cnf
-r--r--r-- 1 root wheel 4948 Aug 12 2009
/usr/local/share/mysql/my-huge.cnf
-r--r--r-- 1 root wheel 20949 Aug 12 2009
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
-r--r--r-- 1 root wheel 4924 Aug 12 2009
/usr/local/share/mysql/my-large.cnf
-r--r--r-- 1 root wheel 4931 Aug 12 2009
/usr/local/share/mysql/my-medium.cnf
-r--r--r-- 1 root wheel 2502 Aug 12 2009
/usr/local/share/mysql/my-small.cnf
I have chosen to use the
my-medium.cnf as a skele to tune
up, as my server is not high iron one e.g. the host I ru the mysql
is a (simple dual core 1.2Ghz system).
Further on I copied the
/usr/local/share/mysql/my-medium.cnf
to
/var/db/mysql/my.cnf e.g.:
freebsd# cp -rpf /usr/local/share/mysql/my-medium.cnf
/var/db/mysql/my.cnf
As a next step to properly tune up the default values of the newly
copied
my.cnf to my specific server I used the
Tuning-Primer
MySQL tuning script
Using
tuning-primer.sh is really easy as all I did is
download it and launch it and follow the script suggestions to
correct some of the values already in
my.cnf
I have finally ended up with the following
my.cnf after using
tuning-primer.sh to optimize mysql server to work with my bsd
host
Now I really hope the shitty
swap_pager_getswapspace: failed
errors would not haunt me once again by crashing my server and
causing mem overheads.
Still I wonder why the port developer
Alex Dupre -
ale@FreeBSD.org choose not to provide the default
mysql51-server conf with some kind of my.cnf file? I hope he had a
good reason.