DB connection error, which drives me crazy

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I am on database box, using mysql to connect to mysql server:

mysql -h db.opensubtitles.org -pERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system error: 54Exit 1

Server version: 5.1.34 FreeBSD

I described this error also in MYSQL forum, but no answer at all, so I am trying luck here.

This error happens occasionally - 3 times it connect, 1 time it doesn’t. Any idea what I can try (coz I tried everything, what I’ve found on google) ?

Thanks a lot

2

I assume you don’t get this problem when you go through loopback. If you have other network interfaces on your db box, I’d give those a try to make sure the same thing happens. Have you checked if your network interfaces and the nearby router are running in half-duplex or full-duplex mode? I remember reading once about some MySQL connection issues that were caused by this. If you have physical access to the machine try plugging in a different router and see if the problem persists.

3

hello,

yes, I dont have this problem, when I connect to localhost or using socket; when I type host name, I get this problem. We use single GIG switch running fullduplex.

mysql --host=db1.opensubtitles.orgERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system error: 54# perror 54OS error code 54: Connection reset by peer# sockstat -4 | wc -l 1160# netstat -lan | wc -l 11335

Any other idea ?

4

more infos:

netstat -sp tcptcp: 519922046 packets sent 139187494 data packets (67473736132 bytes) 3510 data packets (1238306 bytes) retransmitted 2022 data packets unnecessarily retransmitted 0 resends initiated by MTU discovery 343548679 ack-only packets (187475 delayed) 0 URG only packets 0 window probe packets 242949 window update packets 36938036 control packets 827116051 packets received 191707145 acks (for 67569540538 bytes) 35418374 duplicate acks 0 acks for unsent data 135921161 packets (6477387973 bytes) received in-sequence 2161 completely duplicate packets (127608 bytes) 0 old duplicate packets 164 packets with some dup. data (7037 bytes duped) 780 out-of-order packets (813099 bytes) 1 packet (0 bytes) of data after window 0 window probes 5728897 window update packets 25356 packets received after close 43 discarded for bad checksums 0 discarded for bad header offset fields 0 discarded because packet too short 0 discarded due to memory problems 83 connection requests 36945318 connection accepts 379 bad connection attempts 268952956 listen queue overflows 127 ignored RSTs in the windows 36945321 connections established (including accepts) 36935555 connections closed (including 268 drops) 36828035 connections updated cached RTT on close 36828278 connections updated cached RTT variance on close 35306610 connections updated cached ssthresh on close 80 embryonic connections dropped 191662925 segments updated rtt (of 148835644 attempts) 7146 retransmit timeouts 239 connections dropped by rexmit timeout 0 persist timeouts 0 connections dropped by persist timeout 0 Connections (fin_wait_2) dropped because of timeout 81 keepalive timeouts 1 keepalive probe sent 80 connections dropped by keepalive 5682 correct ACK header predictions 15419016 correct data packet header predictions 305898576 syncache entries added 4799 retransmitted 468 dupsyn 0 dropped 36945318 completed 112528 bucket overflow 0 cache overflow 137 reset 0 stale 268952956 aborted 0 badack 0 unreach 0 zone failures 305898576 cookies sent 112381 cookies received 101 SACK recovery episodes 220 segment rexmits in SACK recovery episodes 315864 byte rexmits in SACK recovery episodes 709 SACK options (SACK blocks) received 586 SACK options (SACK blocks) sent 0 SACK scoreboard overflow

5

I don’t know FreeBSD, but doesn’t

305898576 cookies sent

indicate your kernel considers some of your traffic as syn flooding ?

In such situtation linux kernel says something like that:

kernel: possible SYN flooding on port 3306. Sending cookies.

Do you have your mysql port opened to the world ? If yes, i’d suggest to firewall it.

6

this helps so far:

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

problem is with network - kernel just can not handle so much connections (