Date: Wed, 15 Jun 2005 04:08:46 -0400 From: Ken Ebling <ken@idealinter.net> To: Thomas Hurst <tom.hurst@clara.net> Cc: freebsd-questions@freebsd.org Subject: Re: Stability issues with FreeBSD + MySQL Message-ID: <7F9C52C0-3218-46E5-815C-E655BD822D62@idealinter.net> In-Reply-To: <20050615011442.GB29310@voi.aagh.net> References: <92276D37-99E1-4909-8283-C2A60E280B3C@idealinter.net> <20050615011442.GB29310@voi.aagh.net>
next in thread | previous in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?7F9C52C0-3218-46E5-815C-E655BD822D62>