Stability issues with FreeBSD + MySQL

[Ken Ebling]

On Jun 14, 2005, at 9:14 PM, Thomas Hurst 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..?

Thomas,

Thank you for responding with such well thought out (and organized!)  
responses.  I hope I can respond as well as you did.  It's 3:45AM  
here, so I''ll try my hardest! =)

The system seemed to perform fairly well with FreeBSD 5.3/5.4, but  
these errors happened far more frequently than they do when I'm  
running FreeBSD 4.x.

There's also the fact that I never found an answer to whether I  
should be using MySQL+LinuxThreads on FreeBSD 5.4.  Do you use the  
4BSD or ULE scheduler?  Do you use MySQL 4.0.x or 4.1.x?  4.1.x was  
guaranteed to result in hundreds of error emails when I tried it.

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

A perl script on the site are hardcoded to essentially open a  
connection or die with an error and send me (and my client) an email.

Sometimes when these "hiccups" happen, I receive about 30 messages in  
a 1 minute time span, and other times I receive over 300 in a few  
minutes.

I didn't program any of the web site.  If I did, it would be PHP and  
PostgreSQL, not Perl and MySQL.   =)
I provide co-location and administration.  I suppose retry logic  
would make sense, but I'd have to get the programmer involved to take  
care of that.

>> /etc/sysctl.conf on database server:
>> kern.ipc.somaxconn=1024
>>
>
> 128 here, but I take it you're not using persistant connections?

I was very liberal with this value on both the web server and the  
database server.  The web server is set at 2048 and I've never seen  
more than 800, which were mostly mysql.  So persistent connections  
look interesting.  I never thought to look at that.  I'm used to PHP  
defaulting to persistent connections for MySQL and PostgreSQL.

>> netstat -m on web server:
>> 4294928101 mbufs in use
>>
>
> Hurray for counter bugs.

Yeah, I was excited too.  It seems to overflow after a few hours.

>> netstat -na | grep tcp | wc -l on database server:
>> 34
>>
>
> db0# netstat -na | grep tcp | wc -l
>      167
>
> :)

I'll probably need more bandwidth before I can handle the same amount  
as traffic as you. =)

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

Yeah, I wasn't sure about the high in/sys/cs.  It was lower on some  
of my other machines, but they get a lot less traffic than these  
machines.


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

I'll reboot with a new kernel in morning or evening and let you know  
if I have any results.

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

Thanks again for the great input.  I was hoping to hear from someone  
with your experience.  It is greatly appreciated.  If your insight  
helps make my systems as reliable as yours, I'd like to send you an  
Amazon gift card (or giftcertificates.com ?) to help with your  
reading habit. =)

Thanks again,

Ken Ebling