I've setup Packetbeat to sniff MySQL traffic. My problem is that for most queries, I get a response of 0 and for some an extremely large value of a few thousands. I've cross checked the lengthy queries with the API requests resulting in the supposedly long queries and they only last a few ms.
In 15 minutes I get:
total requests: ~360.000
responsetime: 0 ~350.000
responsetime: [1 TO *]: ~10.000
responsetime: [1000 TO *]: ~100
On top of that, while trying to debug the issue, I enabled send_response and for most "lengthy" requests, there is no response, although they could be a single one row SELECT statement.
Packetbeat is running on the same server with MySQL (no mirroring, although we've considered it for the future as Packetbeat is resource heavy).
Where can this behavior come from and what can I do to improve the reliability of the logs?
Are you using the af_packet sniffer type? Check also if you see any drops when you call ifconfig on the intefaces that you sniff on.
Just to make sure, note that unit is milliseconds, so 1000 means 1 second. Also, selects that return no rows tend to be slower in mysql because they have to traverse the whole index. Are you sure there are no transactions taking over a second?
The response time is given in ms. A value of 1000 is 1s response time. The value is based on the timestamps of first network packets seen by OS, for both request and response. The timestamps are set by your OS, the time the network packet passes the network stack. So responsetime includes sending complete query (request) + parse query + process query + start sending response + sending first response network packet back to client.
@tudor, @steffens Obviously there can be some slow transactions, that's why we decided to monitor them, the thing though is that I've checked the API requests resulting in the some of the reported long transactions, and while they return successfully in less than a second, the transactions seem to take 4-5 or more seconds. Also, even if there really were long transactions, that doesn't justify the fact that most transactions are reported to respond in 0ms (about 97%).
@tudor I'm using the default sniffer (pcap), changing the type to af_packet results in failing to start packetbeat with the error: "CRIT Initializing sniffer failed: Error creating sniffer: setsockopt packet_rx_ring: cannot allocate memory"
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.