Collecting slow queries
node_slow_log table collects information about slow queries on a node and retains query information of long-running CQL statements to help you identify slow queries on a cluster to find and tune poorly performing queries.
You can also use OpsCenter to view, troubleshoot, and trace Slow Queries.
Pending inserts into the
node_slow_log might still be processed after the service has been disabled.
You can enable and disable the service.
After the service is disabled, the logging of queries that take longer than the specified threshold is stopped.
However, disabling the logging does not flush the pending write queue, a background thread eventually processes everything.
Where is the
The location of the
dse.yaml file depends on the type of installation:
Package installations + Installer-Services installations
Tarball installations + Installer-No Services installations
By default, collection is enabled for statements that are issued when the query exceeds a specified time threshold.
To permanently enable collecting information on slow queries, edit the
dse.yamlfile. Uncomment and define values for
cql_slow_log_optionsas shown in the following listing. Notice the default
cql_slow_log_options: enabled: true threshold: 200.0 minimum_samples: 100 ttl_seconds: 259200 skip_writing_to_db: true num_slowest_queries: 5
If you keep the default
skip_writing_to_db: truesetting then the slow query information is stored in memory, not in the
node_slow_logtable shown later in this section.
To store the slow query information in the
If you must store the slow query information in memory, then the information is accessed through the MBean managed Java object named
com.datastax.bdp.performance objects.CqlSlowLogusing the operation
To temporarily change the
cqlslowlogsetting without changing
dse.yamlor restarting DSE, use the
Disable collecting information on slow queries that exceeded the threshold:
dsetool perf cqlslowlog disable
Keep slow queries in memory only:
dsetool perf cqlslowlog skip_writing_to_db
Write slow queries to the database:
dsetool perf cqlslowlog write_to_db
Set the number of slow queries to keep in memory. For example, 5 queries:
dsetool perf cqlslowlog set_num_slowest_queries 5
Retrieve the most recent slow queries:
dsetool perf cqlslowlog recent_slowest_queries
To temporarily change the threshold to collect information on 5% of the slowest queries:
dsetool perf cqlslowlog 95.0
After you collect information using this temporarily set threshold, you can run a script to view queries that took longer with this threshold than the previously set threshold. For example:
$ cqlsh cqlsh> USE dse_perf; cqlsh:dse_perf> SELECT * FROM node_slow_log; ...
You can export slow queries using the CQL
cqlsh:dse_perf> COPY node_slow_log ( date, commands, duration ) TO 'slow_queries.csv' WITH HEADER = true;