Collecting slow queries

The 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 DSE 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.

By default, collection is enabled for statements that are issued when the query exceeds a specified time threshold.

Permanently configure slow query logs

In the dse.yaml file, uncomment and set values for cql_slow_log_options.

The location of the dse.yaml file depends on the type of installation:

Package installations and Installer-Services installations: /etc/dse/dse.yaml
Tarball installations and Installer-No Services installations: <installation_location>/resources/dse/conf/dse.yaml

cql_slow_log_options:
    enabled: true
    threshold: 200.0
    minimum_samples: 100
    ttl_seconds: 259200
    skip_writing_to_db: true
    num_slowest_queries: 5

By default, skip_writing_to_db: true stores slow query logs in memory, not in the node_slow_log database table queried in Retrieve slow query logs and Export slow query logs.

To store the slow query information in the node_slow_log table, set skip_writing_to_db: false.

Temporarily configure slow query logs

To temporarily change the cqlslowlog settings without changing dse.yaml or restarting DSE, use the dsetool perf subcommands:

Disable slow query logs:
```
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
```
Change the percentile threshold for slow query logs.

The following example collects the slowest 5 percent of queries:
```
dsetool perf cqlslowlog 95.0
```

Retrieve slow query logs

After modifying the slow query configuration and allowing queries to run, get the slow query logs.

Read from node_slow_log table

To read from the node_slow_log table, logs must be written to the database. In dse.yaml, the skip_writing_to_db setting must be false, or you must run dsetool perf cqlslowlog with the write_to_db option.

Retrieve all slow query logs from the node_slow_log table using cqlsh:
```
SELECT * FROM dse_perf.node_slow_log;
```

Retrieve recent slow queries only:

dsetool perf cqlslowlog recent_slowest_queries

Read from memory

Slow query logs are stored in memory if skip_writing_to_db is true in dse.yaml or you run dsetool perf cqlslowlog with the skip_writing_to_db option.

To read slow query logs from memory, access the MBean managed Java object named com.datastax.bdp.performance objects.CqlSlowLog using the operation retrieveRecentSlowestCqlQueries. For more information about accessing MBeans, see Monitor DataStax Enterprise (DSE) clusters.

Export slow query logs

You can export slow queries using the CQL shell COPY TO command:

COPY dse_perf.node_slow_log ( date, commands, duration )
TO 'slow_queries.csv' WITH HEADER = true;

Collecting slow queries

Permanently configure slow query logs

Temporarily configure slow query logs

Retrieve slow query logs

Export slow query logs

Was this helpful?

Give Feedback