Collecting slow queries

Thenode_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 table 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.

Procedure

  1. 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.yaml file. Uncomment and define values for cql_slow_log_options as shown in the following listing. Notice the default skip_writing_to_db: true setting.

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

      The default skip_writing_to_db: true setting stores the slow query information in memory, not in the node_slow_log database table shown later in this section.

      To store the slow query information in the node_slow_log table, set skip_writing_to_db to false in the dse.yaml file.

      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.CqlSlowLog using the operation retrieveRecentSlowestCqlQueries.

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

      • 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 -e "SELECT * FROM dse_perf.node_slow_log;"
  2. You can export slow queries using the CQL COPY TO command:

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

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com