Search

Top 60 Oracle Blogs

Recent comments

hot single range queries… troubleshooting part1

hot single range queries… troubleshooting part1

This issue has shown up in multiple customer applications with CockroachDB. This typically occurs in an environment that has a small amount of data that fits into a single range within CockroachDB. Often micro-services that poll data for dashboards or workflow applications can drive up usage with a small of data.

For this first post, I will define the symptoms, diagnosis, and treatments for "hot single range queries". A summary of all topics, tools, techniques will be kept in a github troubleshooting repository. To be as instructive as possible, I have included steps to reproduce and observe this phenomena as well. If you need help to diagnose or resolve issues with CockroachDB, please reach out to our support resources.

Symptoms

The symptoms of a single hot range include:

  • High CPU utilization on a single node
  • Spike in SQL Latency
  • Lack of workload scaling due to uneven CPU utilization

From the DB Console, we see QPS and CPU capped all on one node. This can limit scaling of the workload. So, either the workload can be driven at a higher rate to force splitting, or the split threshold can be lowered.


A summary of all topics, tools, techniques will be kept in a github troubleshooting repository. To be as instructive as possible, steps to reproduce and observe these issues will be included as well. If you need help to diagnose or resolve issues with CockroachDB, please reach out to our support resources.

Diagnosis

The symptoms can be observed via the DB console or via metrics externally. Before we can formulate a treatment plan, we need to identify which specific range is causing the high CPU utilization.

  • Check the DB console statements page to see the most highly executed queries
  • Check to see how many ranges are on the suspect table. This can be done via the DB Console or using the SHOW RANGES SQL command. For example:

    SHOW RANGES FROM TABLE hotsingle;
    
    start_key | end_key | range_id | range_size_mb | lease_holder |           lease_holder_locality           | replicas |                                                          replica_localities
    ------------+---------+----------+---------------+--------------+-------------------------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------
    NULL      | NULL    |       78 |    153.434101 |            4 | cloud=gce,region=us-east1,zone=us-east1-b | {1,2,4}  | {"cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-b"}
    (1 row)

    It is possible that these techniques might not be able to isolate the one hot range due to other statements. If this is the case, we can look at the hot ranges from the advanced endpoint on the DB Console http://hostname:26258/_status/hotranges.

  • With the range_id you can query the ranges table to determine the name of the object using the range.

    SELECT database_name, table_name, index_name
    FROM crdb_internal.ranges
    WHERE range_id = 78;
    
    database_name   | table_name | index_name
    ----------------+------------+-------------
    test            | hotsingle  |

    Finally, if the above methods do not show the range in question, you can use the hottest_ranges3.py tool to extract data from the http://hostname:25258/_status/raft endpoint and lookup the hottest ranges ordered by the highest number of QueryPerSecond.

Treatment

A small single range table can become hot for many reasons. Micro-services pooling data, dashboards, queue tables are some of the most common observed bottle-necks. To resolve, we would like to split data access across multiple ranges.

Option #1 — Adjust Auto Splitting

To disperse data access, we need to read from multiple replicas and/or split the ranges. CockroachDB can dynamically split ranges by configuring the split threshold.

Adjust Auto Splitting:

-- Adjust to Split at a lower QPS and not re-merge ranges
--
SET CLUSTER SETTING kv.range_merge.queue_enabled = false;    -- default is true
SET CLUSTER SETTING kv.range_split.by_load_enabled = true;   -- default is true
SET CLUSTER SETTING kv.range_split.load_qps_threshold = 200; -- default 2500

Monitor Range Splitting:

-- Monting Splitting of Ranges... this sample is partially finished
--
root@:26257/test> SHOW RANGES FROM TABLE hotsingle;
  start_key | end_key | range_id | range_size_mb | lease_holder |           lease_holder_locality           | replicas |                                                          replica_localities
------------+---------+----------+---------------+--------------+-------------------------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------
  NULL      | /200001 |       78 |     61.334101 |            4 | cloud=gce,region=us-east1,zone=us-east1-b | {1,4,5}  | {"cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-b"}
  /200001   | /400001 |       69 |          61.4 |            2 | cloud=gce,region=us-east1,zone=us-east1-b | {1,2,4}  | {"cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-b"}
  /400001   | NULL    |       79 |          30.7 |            2 | cloud=gce,region=us-east1,zone=us-east1-b | {1,2,4}  | {"cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-b"}
(3 rows)

QPS and CPU:

Looking at the DB Console, we can see that as the ranges SPLIT and moved, we are able to scale throughput and balance the CPU load across all nodes of the cluster.


Reproduce "hotsingle range"

A full reproduction of this topic can be run using the instructions in repo_hot_singlerange_table.md

Option #2 — Use Follower Reads

Sometimes all of the activity is querying to the same exact value from multiple clients. This is typically in as dashboard or polling application. If the application logic allows to read a slightly older copy of the data, you can use the follower_read_timestamp() function which reads from the lease-holder as well as the replica copies of data. This allows CPU to be spread over multiple nodes.

The num_replicas defines how many replicant ranges exist on each object:

SHOW ZONE CONFIGURATION FOR TABLE hotsingle;

     target     |              raw_config_sql
----------------+-------------------------------------------
  RANGE default | ALTER RANGE default CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 90000,
                |     num_replicas = 3,
                |     constraints = '[]',
                |     lease_preferences = '[]'

So, just simply using as of system time follower_read_timestamp() you will be able to increase the CPU resources by 3x. If you wish to spread this further, you can increase the num_replicas by altering the table:

ALTER TABLE hotsingle CONFIGURE ZONE USING num_replicas = 5;
CONFIGURE ZONE 1

After a few minutes:

SELECT replicas FROM [SHOW RANGES FROM TABLE hotsingle];
  replicas
------------
  {1,2,3,4,5} 

DB Console with follower reads:


Notice how CPU and QPS scales up to utilize all resource on the cluster. This example you see some back-off with the 5 replica test. It was hitting the cluster hard and the system needed CPU to keep consensus.

Reproduce "single value stress"

A full reproduction of results can be run using the instructions in repo_single_hotvalue.md. It uses the same configuration as the other hotrange, but runs only one query with a single predicate value for all threads.