GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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
    Hi josuablirup,

    I'll ask the presenter, Percona support engineer Marcos Albe, to try and help here. In the meantime, here's a link to that webinar where you can also replay it in it's entirety.

    http://www.percona.com/resources/mys...right-tool-job
    Is this an emergency? Get immediate assistance from Percona Support 24/7. Click here.

    Comment


    • #3
      Hey Tom

      Has there been any update regarding this?

      Best regards
      Josua

      Comment


      • #4
        I've checked again with him, Josua - I think he was on vacation the first time I sent him an email on it. Hopefully he'll be able to point you in the right direction.
        Is this an emergency? Get immediate assistance from Percona Support 24/7. Click here.

        Comment


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


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