Dear Percona
I recently attended the Webinar: “Optimizing MySQL Performance: Choosing the Right Tool for the Job” Webinar”
First and foremost thank you for an enlightening seminar. During the seminar I asked what to do if you encountered errors like:
(Got timeout reading communication packets)
Would you be able to provide a bit more insight into how to solve this kind of errors? I have checked two of our servers running Percona 5.6 and they are littered with these errors.
Basic info:
[LIST]
[]CloudLinux 6.5
[]Percona 5.6
[]cPanel 11.42.1
[]PHP 5.4
[]Apache 2.4
[]Xeon 12 cores
[]64GB memory
[]3TB SATA3 HDD’s
[/LIST] I will be happy to provide you with any further info required
Hey Tom
Has there been any update regarding this?
Best regards
Josua
Joshua,
I would start by checking output of netstat -s to verify network is healthy.
Also send us output here of SHOW GLOBAL VARIABLES LIKE ‘%timeout%’; so we can check the settings are appropriate and there’s not too-short timeout.
Do you keep a log in the application? What does that log shows about connections to MySQL?
Best!
Marcos
Hey Marcos
This gives: [TABLE=“class: ajax pma_table”]
[TR]
[/TR]
[TR]
Variable_name Value [TD] [/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: data not_null”]connect_timeout[/TD]
[TD=“class: data”]300[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: data not_null”]delayed_insert_timeout[/TD]
[TD=“class: data”]300[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: data not_null”]have_statement_timeout[/TD]
[TD=“class: data”]YES[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: data not_null”]innodb_flush_log_at_timeout[/TD]
[TD=“class: data”]1[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: data not_null”]innodb_lock_wait_timeout[/TD]
[TD=“class: data”]50[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: data not_null”]innodb_rollback_on_timeout[/TD]
[TD=“class: data”]OFF[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: data not_null”]interactive_timeout[/TD]
[TD=“class: data”]300[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: data not_null”]lock_wait_timeout[/TD]
[TD=“class: data”]31536000[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: data not_null”]net_read_timeout[/TD]
[TD=“class: data”]30[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: data not_null”]net_write_timeout[/TD]
[TD=“class: data”]60[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: data not_null”]rpl_stop_slave_timeout[/TD]
[TD=“class: data”]31536000[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: data not_null”]slave_net_timeout[/TD]
[TD=“class: data”]3600[/TD]
[/TR]
[TR=“class: odd”]
[TD=“class: data not_null”]thread_pool_idle_timeout[/TD]
[TD=“class: data”]60[/TD]
[/TR]
[TR=“class: even”]
[TD=“class: data not_null”]wait_timeout[/TD]
[TD=“class: data”]300
[/TD]
[/TR]
[/TABLE]
The error log shows:
2014-09-05 12:06:52 872317 [Warning] Aborted connection 290 to db: ‘leechprotect’ user: ‘leechprote ct’ host: ‘localhost’ (Got timeout reading communication packets)
2014-09-05 13:12:25 872317 [Warning] Aborted connection 6049 to db: ‘leechprotect’ user: ‘leechprot ect’ host: ‘localhost’ (Got timeout reading communication packets)
2014-09-05 13:45:01 872317 [Warning] Aborted connection 25 to db: ‘eximstats’ user: ‘eximstats’ hos t: ‘localhost’ (Got timeout reading communication packets)
2014-09-05 14:11:15 872317 [Warning] Aborted connection 10054 to db: ‘leechprotect’ user: ‘leechpro tect’ host: ‘localhost’ (Got timeout reading communication packets)
2014-09-05 15:00:01 872317 [Warning] Aborted connection 8412 to db: ‘eximstats’ user: ‘eximstats’ h ost: ‘localhost’ (Got timeout reading communication packets)
2014-09-05 15:16:40 872317 [Warning] Aborted connection 14228 to db: ‘leechprotect’ user: ‘leechpro tect’ host: ‘localhost’ (Got timeout reading communication packets)
2014-09-05 17:13:31 872317 [Warning] Aborted connection 20836 to db: ‘leechprotect’ user: ‘leechpro tect’ host: ‘localhost’ (Got timeout reading communication packets)
2014-09-05 18:15:01 872317 [Warning] Aborted connection 13632 to db: ‘eximstats’ user: ‘eximstats’ host: ‘localhost’ (Got timeout reading communication packets)
2014-09-05 19:10:20 872317 [Warning] Aborted connection 27876 to db: ‘leechprotect’ user: ‘leechpro tect’ host: ‘localhost’ (Got timeout reading communication packets)
2014-09-05 19:35:02 872317 [Warning] Aborted connection 24595 to db: ‘eximstats’ user: ‘eximstats’ host: ‘localhost’ (Got timeout reading communication packets)
Please find the netstat output below:
Ip:
30064386 total packets received
0 forwarded
0 incoming packets discarded
29941751 incoming packets delivered
29444349 requests sent out
258 outgoing packets dropped
Icmp:
24960 ICMP messages received
0 input ICMP message failed.
ICMP input histogram:
destination unreachable: 2003
timeout in transit: 21288
echo requests: 1669
33008 ICMP messages sent
0 ICMP messages failed
ICMP output histogram:
destination unreachable: 810
echo replies: 1669
IcmpMsg:
InType3: 2003
InType8: 1669
InType11: 21288
OutType0: 1669
OutType3: 810
OutType69: 30529
Tcp:
537770 active connections openings
714953 passive connection openings
3384 failed connection attempts
25225 connection resets received
20 connections established
97371547 segments received
41539609 segments send out
1364448 segments retransmited
255 bad segments received.
193640 resets sent
Udp:
353764 packets received
810 packets to unknown port received.
0 packet receive errors
357206 packets sent
UdpLite:
TcpExt:
11540 invalid SYN cookies received
3118 resets received for embryonic SYN_RECV sockets
6 packets pruned from receive queue because of socket buffer overrun
620055 TCP sockets finished time wait in fast timer
92930 delayed acks sent
615 delayed acks further delayed because of locked socket
Quick ack mode was activated 13455 times
1440914 packets directly queued to recvmsg prequeue.
1134690794 packets directly received from backlog
3302074888 packets directly received from prequeue
4676116 packets header predicted
959609 packets header predicted and directly queued to user
74011146 acknowledgments not containing data received
10857773 predicted acknowledgments
3161 times recovered from packet loss due to fast retransmit
372050 times recovered from packet loss due to SACK data
6514 bad SACKs received
Detected reordering 649 times using FACK
Detected reordering 774 times using SACK
Detected reordering 35 times using reno fast retransmit
Detected reordering 102 times using time stamp
168 congestion windows fully recovered
577 congestion windows partially recovered using Hoe heuristic
TCPDSACKUndo: 6124
6565 congestion windows recovered after partial ack
862566 TCP data loss events
TCPLostRetransmit: 58091
421 timeouts after reno fast retransmit
19019 timeouts after SACK recovery
8716 timeouts in loss state
934978 fast retransmits
65316 forward retransmits
204633 retransmits in slow start
70287 other TCP timeouts
TCPRenoRecoveryFail: 1799
28976 sack retransmits failed
5 times receiver scheduled too late for direct processing
279 packets collapsed in receive queue due to low socket buffer
13031 DSACKs sent for old packets
9 DSACKs sent for out of order packets
72059 DSACKs received
4490 DSACKs for out of order packets received
58779 connections reset due to unexpected data
6475 connections reset due to early user close
2081 connections aborted due to timeout
66 times unable to send RST due to no memory
TCPSACKDiscard: 941
TCPDSACKIgnoredOld: 846
TCPDSACKIgnoredNoUndo: 44441
TCPSpuriousRTOs: 1979
TCPSackShifted: 682405
TCPSackMerged: 1930892
TCPSackShiftFallback: 835144
TCPChallengeACK: 1376
TCPSYNChallenge: 277
IpExt:
InBcastPkts: 12
InOctets: 24545132055
OutOctets: 65760409509
InBcastOctets: 3936
As far as I can see this should not be reason for any major issues. Also the mysql server is local.
I hope this can provide some further insight into this issue.