Stability issues with FreeBSD + MySQL

[Ken Ebling]

Hello everyone,

I've been experiencing some issues with FreeBSD + MySQL and I was  
hoping someone on this list might have some advice.

I have two servers that run a web site that receives about 650,000  
hits per day (about 15,000 unique visits/day).  Both the machines  
have dual Xeon 3.06 GHz chips with 2GB ECC REG RAM, and RAID Arrays  
(The web server has an Adaptec 2100S U160 controller, and the  
database server has an Adaptec 2120S U320 controller.)  Both machines  
have a 2-disk raid-1 array for the OS, and a raid-5 array for the data.

The web server is running FreeBSD 5.3 with Apache+mod_perl (1.3.33_2,  
installed from ports), mysql-client (4.0.24_1, installed from ports,  
compiled with LinuxThreads), and uses Postfix as an MTA.

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)

The database server isn't connected to the internet, the web server  
connects to it over a gigabit cross-over link.

Both the machines have custom kernels, that are basically GENERIC  
with all the unnecessary hardware (SCSI controllers, RAID  
controllers, NICs, USB & Firewire)  stripped out, and with SMP  
enabled.  The web server uses an Intel Pro/100 (fxp) for it's  
external interface and an Intel Pro/1000 (em) for it's connection to  
the database server, which also has an Intel Pro/1000.

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.

Here are some relevant settings on the machines:

/etc/sysctl.conf on web server:
kern.ipc.somaxconn=2048

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

/boot/loader.conf on web server:
kern.ipc.nmbclusters="65536"

/boot/loader.conf on database server:
kern.ipc.nmbclusters="32768"

netstat -m on web server:
4294928101 mbufs in use
2516/65536 mbuf clusters in use (current/max)
0/3/6656 sfbufs in use (current/peak/max)
4189537 KBytes allocated to network
0 requests for sfbufs denied
0 requests for sfbufs delayed
0 requests for I/O initiated by sendfile
137 calls to protocol drain routines

netstat -m on database server:
450/592/131072 mbufs in use (current/peak/max):
         450 mbufs allocated to data
448/570/32768 mbuf clusters in use (current/peak/max)
1288 Kbytes allocated to network (1% of mb_map in use)
0 requests for memory denied
0 requests for memory delayed
0 calls to protocol drain routines

netstat -na | grep tcp | wc -l on web server:
402

netstat -na | grep udp | wc -l on web server:
11

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

netstat -na | grep udp | wc -l on database server:
6

vmstat on webserver:
procs      memory      page                    disks     faults      cpu
r b w     avm    fre  flt  re  pi  po  fr  sr da0 da1   in   sy  cs  
us sy id
2 2 0  408776 1172772 2447   0   0   0 1958  21   0   0 3257 11668  
5447 15  5 79

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

vmstat -i on web server:
interrupt                          total       rate
irq1: atkbd0                         558          0
irq6: fdc0                             9          0
irq8: rtc                       11062204        127
irq13: npx0                            1          0
irq16: fxp0                     11129226        128
irq48: asr0                       891086         10
irq54: em0                      37098604        429
irq0: clk                       86418736        999
Total                          146600424       1696

vmstat -i on database server:
interrupt                   total       rate
em0 irq11                50726686        501
aac0 irq16                3114675         30
fxp0 irq10                      6          0
fdc0 irq6                       1          0
atkbd0 irq1                     1          0
clk irq0                101253713       1000
rtc irq8                 12961771        128
Total                   168056853       1660


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.

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

Thanks in advance,

Ken Ebling