Emergency

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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

  • #2
    Hey Tom

    Has there been any update regarding this?

    Best regards
    Josua

    Comment


    • #3
      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

      Comment


      • #4
        Hey Marcos

        This gives:
        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.

        Comment

        Working...
        X