dedicated mysql server dropping connections

[danger@]

Howdy guys,

I'm having some difficult time resolving an issue with mysql.
What's happening is that when I try to connect to the dedicated mysql server from a remote web server I get the following error:


root@[web1 /home/danger]# mysql --host=db1.opensubtitles.org
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 54


I don't know where to look, really. Sometimes it works fine, other time it dies with the above error. The machines are connected through a single gige switch. I tried disabling firewall (ipfw) but that didn't help. Ssh to the mysql box work w/o issues.

Anyway the mysql box is constantly pretty loaded, there's quiet a lot of connections to it.


root@[db1 ~]# sockstat -4 | wc -l
1160
root@[db1 ~]# uptime
12:36PM up 25 days, 18:44, 10 users, load averages: 7.26, 9.63, 10.18

Any idea what I should try to look at?
Thanks!

[gege]

I just want add these details:


# netstat -lan | wc -l
11335

# perror 54
OS error code 54: Connection reset by peer

# sysctl -a | grep kern.ipc.somaxconn
kern.ipc.somaxconn: 65535


thanks for any help!

[vivek]

First, make sure your user is allowed to connect to remove mysql server via your IP
mysql --host=db1.opensubtitles.org -u username -p dbname

Look at server log for the details.

Oh, load average is pretty high. Find out what is causing such high load. It may be disk I/O and memory issue. Use command such as top, vmstat and iostat to get the details about disk and memory performance. Use dtrace to get into details. You can also use mytop tool to get few more details. Usually, problem can be solved by

adding more ram

adding more hard disk to raid

Turn on mysql query cache, key buffer, table cache, max connections to improve sql read only performance

Turn on and disable flush transaction on commit,InnoDB buffer pool size, key buffer,log buffer size for write only performance

File system optimization like mount /mysql with noatime

Split mysql server for read only and write only operations using cluster and so on.

I can go on with optimization but first you need to dig out what's going on why load is so high?

HTH

[gege]

Hi Vivek,

thanks for reply. For LOAD issue:


FreeBSD 7.2-RELEASE
CPU: Intel(R) Xeon(R) CPU E5320 @ 1.86GHz (1861.92-MHz K8-class CPU)
FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs
usable memory = 8582840320 (8185 MB)


we running on this box:

mysql master
mysql slave
memcached
bind


each mysql server gets around 1000 QPS in high load. Load of this server is not such a problem, ofcourse we use almost all you've suggested, the longest query takes around 2 seconds. Problem is TCP connect:

# netstat -sp tcp | grep -i overflow
555228007 listen queue overflows
0 bucket overflow
0 cache overflow
0 SACK scoreboard overflow


some important values (set as now):

kern.ipc.somaxconn: 8192
vm.kmem_size: 1073741824
net.inet.tcp.syncache.rst_on_sock_fail: 1
net.inet.tcp.syncache.rexmtlimit: 3
net.inet.tcp.syncache.hashsize: 1024
net.inet.tcp.syncache.count: 0
net.inet.tcp.syncache.cachelimit: 102400
net.inet.tcp.syncache.bucketlimit: 100
net.inet.tcp.tcbhashsize: 1024
net.inet.ip.intr_queue_maxlen: 1024
kern.ipc.nmbclusters: 65536
kern.ipc.maxsockets: 131072


We already moved memcached to another server, but we are still getting listen queue overflows, even when server is not under big load:


# sockstat -4 | wc -l
368

# netstat -lan | wc -l
5763

# uptime
4:49AM up 9:29, 2 users, load averages: 5.54, 4.51, 4.24


Any idea what to do?

[vivek]

Last year we had same problem with Apache with same error when netstat run. It was due to "SYN flood attacks". A few suggstion

Set net.inet.tcp.keepinit to 10 or 15 seconds from default 75 seconds.

Set maximum number of entries allowable in the listen queue per socket by editing kern.ipc.somaxconn to 5000 from 128.

Other setting we use to fight against syn floods:

# limit responses to ICMP for bandwidth purposes
net.inet.icmp.icmplim=50
net.inet.icmp.maskrepl=0
net.inet.icmp.drop_redirect=1
net.inet.icmp.bmcastecho=0
net.inet.tcp.icmp_may_rst=0

# drop synfin packets
net.inet.tcp.drop_synfin=1
# up the maximum connections allowed, good for ddos's
# kern.ipc.somaxconn
# must set this in /boot/loader.conf
# kern.ipc.somaxconn="2048"
kern.ipc.somaxconn=2048
net.inet.ip.fw.one_pass=1
# adds more queue buckets for ipfw dummynet
#net.inet.ip.dummynet.hash_size=2048

# increase the size of network mbufs to allocate
# kern.ipc.nmbclusters=65536
# must be set in /boot/loader.conf
#
# kern.ipc.nmbclusers="65536"
#

# net.inet.tcp.msl defines the Maximum Segment Life -
#
net.inet.tcp.msl=7500
net.inet.ip.stealth=0

# security against stealth port scans and some DoS attacks
net.inet.tcp.blackhole=2
net.inet.udp.blackhole=1

# stops some syn flood attacks, and route cache degregation during a high-bandwidth flood
net.inet.ip.rtexpire=2
net.inet.ip.rtminexpire=2
net.inet.ip.rtmaxcache=256

# don't accept sourcerouted packets
net.inet.ip.accept_sourceroute=0
net.inet.ip.sourceroute=0


# 128MB memory reserved
kern.ipc.shmmax=134217728
kern.ipc.shmall=32768
kern.ipc.semmap=256


# update maximum files allowed for the kernel
kern.maxfiles=65536


Use TCP syn-proxy for mysql port - normally when a client initiates a TCP connection to a mysql server, PF will pass the handshake packets between the two endpoints as they arrive. PF has the ability, however, to proxy the handshake. With the handshake proxied, PF itself will complete the handshake with the client, initiate a handshake with the server, and then pass packets between the two. The benefit of this process is that no packets are sent to the server before the client completes the handshake. This eliminates the threat of spoofed TCP SYN floods affecting the server because a spoofed client connection will be unable to complete the handshake.

pass in on $ext_if proto tcp from any to $mysql_server port 3306 flags S/SA synproxy state

Increase default tcp port ranges which are limited to 49152 to 65535:
net.inet.ip.portrange.last=65535
net.inet.ip.portrange.first=10000

Also, tune tcp networking stack - http://www.psc.edu/networking/projects/tcptune/#FreeBSD

Some settings may need a server reboot.

HTH

[gege]

heya,

thanks for answer. We changed kernel values:

kern.ipc.maxsockets="131072"
vm.pmap.pg_ps_enabled=1
vm.kmem_size=1G
kern.ipc.somaxconn=16384
kern.ipc.nmbclusters=32768
security.bsd.see_other_uids=0
security.bsd.see_other_gids=0
net.inet.tcp.blackhole=2
net.inet.udp.blackhole=1
net.inet.icmp.icmplim=150
net.inet.icmp.drop_redirect=1
security.bsd.unprivileged_read_msgbuf=0
kern.maxfiles=131072
kern.maxfilesperproc=104856
kern.threads.max_threads_per_proc=4096
net.inet.tcp.keepinit=1000
net.inet.ip.portrange.last=65535
net.inet.ip.portrange.first=10000

also using TCp-SYN proxy. Thanks a lot for your answer, now are server working much better.