Stability issues with FreeBSD + MySQL

[Thomas Hurst]

* Ken Ebling (ken at idealinter.net) wrote:

> The database server is running FreeBSD 4.11 (had all sorts of  
> problems with MySQL after upgrading to 5.x) with mysql-server  
> (4.0.24_1, installed from ports, compiled with LinuxThreads)

I'm running MySQL 4.1 on a 5.3-RELEASE dual Opteron (i386), with an
Adaptec 2120S and 4G of memory; it's running most of the queries for
a site several times busier than yours pretty much flawlessly, low
performance for some of the heavier queries notwithstanding.

I'd be interested to hear about your problems with MySQL and FreeBSD 5;
are they with performance, or stability, or..?

> The problem I'm having is that a few times a day, a perl script on the
> web server will fail to connect to the mysql server, and send out an
> email letting me know that the connection and whatever query it was
> going to execute have failed.
>
> Whenever this happens I check the machines out and everything looks
> okay to me, but I am obviously overlooking something.

You're sure it's the mysql_connect that's failing?  With what error?
And nothing in mysql.err?  Maybe you just need a bit of retry logic in
your script...

> /etc/sysctl.conf on database server:
> kern.ipc.somaxconn=1024

128 here, but I take it you're not using persistant connections?

> netstat -m on web server:
> 4294928101 mbufs in use

Hurray for counter bugs.

> netstat -na | grep tcp | wc -l on database server:
> 34

db0# netstat -na | grep tcp | wc -l
     167

:)

> vmstat on database server:
>  procs      memory      page                    disks      
> faults      cpu
> r b w     avm    fre  flt  re  pi  po  fr  sr aa0 md0   in   sy  cs  
> us sy id
> 1 0 0  358932 163800   40   0   0   0 309 192   0   0 1660 27525 992   
> 7  8 85

Nothing really out of the ordinary there, although in/sy/cs perhaps look
a bit high for an initial sample; I get similar with a vmstat 1 though.
Your interrupt loads look fine too.

> The database server is using the default /usr/local/share/mysql/my- 
> large.cnf config file (my-huge.cnf always makes mysql act buggy) with  
> binary logging disabled.

my-huge probably requires you to increase MAXDSIZ/DFLDSIZ; the default
per-process limit of 512MB might be too small.  We run with:

  options         MAXDSIZ=(1024UL*1024*1024)
  options         DFLDSIZ=(1024UL*1024*1024)

Although using more memory for MySQL may not help, it depends; any
unused memory will go towards filesystem caching anyway. YMMV.

> Is there any special tuning or something else I am missing that would  
> be appropriate for me to try?

I'd suggest some system monitoring; maybe you're hitting MyISAM table
locks occasionally, and blocking new "threads" from being forked, or
general load spikes are overwhelming its ability to spawn new threads,
in which case persistant connections might help; SQLRelay has a MySQL
client library replacement which you might find useful if you can't
modify your scripts to do this directly.

Not much else comes to mind right now I'm afraid; I've not used FreeBSD
4 significantly in a while, and certainly not for any production use,
but maybe something else will come to me if you can find some more hints
and I choose to reply sometime other than 2am ;)

-- 
Thomas 'Freaky' Hurst
    http://hur.st/