Not the answer you need?
Register and ask your own question!

MySQL Performance: Choosing the Right Tool for the Job" Webinar - follow up question

josuablirupjosuablirup EntrantCurrent User Role Beginner
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:
  • 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
I will be happy to provide you with any further info required

Comments

  • josuablirupjosuablirup Entrant Current User Role Beginner
    Hey Tom

    Has there been any update regarding this?

    Best regards
    Josua
  • marcos.albemarcos.albe Percona Support Engineer Percona Staff Role
    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
  • josuablirupjosuablirup Entrant Current User Role Beginner
    Hey Marcos

    This gives:



    Variable_name Value


    connect_timeout
    300


    delayed_insert_timeout
    300


    have_statement_timeout
    YES


    innodb_flush_log_at_timeout
    1


    innodb_lock_wait_timeout
    50


    innodb_rollback_on_timeout
    OFF


    interactive_timeout
    300


    lock_wait_timeout
    31536000


    net_read_timeout
    30


    net_write_timeout
    60


    rpl_stop_slave_timeout
    31536000


    slave_net_timeout
    3600


    thread_pool_idle_timeout
    60


    wait_timeout
    300





    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.