We are not aware of which queries are making into the Cassandra. Is there any way by which we can find out those?
Batch time out queries which appeared in Debug.log shows queries with LIMIT 5000 but no IN statement.
Is there any way by which we can get those queries which are causing multi-partition read into the Database?
Cassandra has an Audit Logging feature which logs:
However this feature is not available in C* 3.11 and is only available in 4.0 (CASSANDRA-12151).
Since you're most concerned with high read latency, you should look at Slow query logging. It was added as a feature in Apache Cassandra 3.10 (CASSANDRA-12403). It is enabled by default in cassandra.yaml and queries which take longer than 500ms are logged at DEBUG level:
slow_query_log_timeout_in_ms: 500
As an example, set it to 2000 to only log queries which took longer than 2 seconds. Note that changes to the configuration requires a restart.
If a query is detected to be slow, the following entry will be logged in the system.log at INFO level (at most once every 5 minutes):
INFO [ScheduledTasks:1] 2020-06-06 20:35:50,209 NoSpamLogger.java:91 - Some operations were slow, details available at debug level (debug.log)
The slow queries are logged in the debug.log every 5 seconds:
DEBUG [ScheduledTasks:1] 2020-06-06 20:35:50,211 MonitoringTask.java:173 - 3 operations were slow in the last 4998 msecs: <SELECT * FROM community.users LIMIT 5000>, time 3026 msec - slow timeout 500 msec <SELECT * FROM community.users WHERE user = "jack" LIMIT 5000>, was slow 2 times: avg/min/max 330/825/835 msec - slow timeout 500 msec <SELECT * FROM community.users WHERE token(id) > 0 LIMIT 5000>, time 1449 msec - slow timeout 500 msec
CQL queries which were repeatedly detected to be slow within a 5-second window get aggregated and only logged once like the second example query above (was slow 2 times). Cheers!
5 People are following this question.
