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