Troubleshooting a Slow Cluster with a Hot Node
Tags: cockroachdb, troubleshooting, testing, devops
Published: 2021-01-08 14:41:32

This blog is about the process of troubleshooting a problematic cluster.

## Understanding the Problem

Your cluster is experiencing high latency and spike in CPU usage for some nodes during the load test.
You need to lower latencies and improve CPU utilization to achieve higher throughput.

Take a look at the cluster to gather:

- the environment configuration: CPUs, MEM, Storage, Networking, node count, node location, CockroachDB version, etc.
- the database schemas.
- the SQL queries run as part of the load test.

## The Environment

The UAT environment runs on 12 nodes across 4 AZs in 2 regions, US East and US West.

The cluster is on the latest CockroachDB version, on 4 vCPUs/16GB Mem instances with standard storage.

![localities](https://dev-to-uploads.s3.amazonaws.com/i/n5j019p4xg8pruhoesq3.png)

Verify that the latency is minimal (less than 1ms) within zones of the same region.

![network-latency](https://dev-to-uploads.s3.amazonaws.com/i/p7kbn45wpqq03katffqa.png)

## Database Schemas

Here's the schema

```sql
CREATE TABLE credits (
    id INT2 NOT NULL,
    code UUID NOT NULL,
    channel STRING(1) NOT NULL,
    pid INT4 NOT NULL,
    end_date DATE NOT NULL,
    status STRING(1) NOT NULL,
    start_date DATE NOT NULL,
    CONSTRAINT "primary" PRIMARY KEY (id ASC, code ASC),
    INDEX credits_pid_idx (pid ASC),
    INDEX credits_code_id_idx (code ASC, id ASC) STORING (channel, status, end_date, start_date),
    FAMILY "primary" (id, code, channel, pid, end_date, status, start_date)
);

CREATE TABLE offers (
    id INT4 NOT NULL,
    code UUID NOT NULL,
    token UUID NOT NULL,
    start_date DATE,
    end_date DATE,
    CONSTRAINT "primary" PRIMARY KEY (id ASC, code ASC, token ASC),
    INDEX offers_token_idx (token ASC),
    FAMILY "primary" (id, code, token)
);
```

Check how the data is distributed into ranges

```sql
SHOW RANGES FROM TABLE credits;
```

```text
                        start_key                       |                        end_key                        | range_id | range_size_mb | lease_holder |           lease_holder_locality           | replicas |                                                          replica_localities
--------------------------------------------------------+-------------------------------------------------------+----------+---------------+--------------+-------------------------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------
  NULL                                                  | /1/"\x00\x05\x16\x80\xf7\xcbL䣵w\x81\x1a\x1d\xd6\xf6" |       38 |      0.000728 |            3 | cloud=gce,region=us-east1,zone=us-east1-b | {3,4,8}  | {"cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-c","cloud=gce,region=us-west1,zone=us-west1-b"}
  /1/"\x00\x05\x16\x80\xf7\xcbL䣵w\x81\x1a\x1d\xd6\xf6" | /15/"\x15\xd3\xe7\xb3_\xa9A\"\xb5p\xa2\xf5\xb9Ba'"    |       40 |    225.468243 |            3 | cloud=gce,region=us-east1,zone=us-east1-b | {3,4,10} | {"cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-c","cloud=gce,region=us-west1,zone=us-west1-c"}
  /15/"\x15\xd3\xe7\xb3_\xa9A\"\xb5p\xa2\xf5\xb9Ba'"    | NULL                                                  |       41 |    222.721298 |            8 | cloud=gce,region=us-west1,zone=us-west1-b | {3,4,8}  | {"cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-c","cloud=gce,region=us-west1,zone=us-west1-b"}
```

Note that `credits` has 2 secondary indexes. Notice how the leaseholder of the ranges are spread across both regions (check the `lease_holder_locality` column).

```sql
SHOW RANGES FROM TABLE offers;
```

```text
  start_key | end_key | range_id | range_size_mb | lease_holder |           lease_holder_locality           | replicas |                                                          replica_localities
------------+---------+----------+---------------+--------------+-------------------------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------
  NULL      | NULL    |       37 |             0 |            3 | cloud=gce,region=us-east1,zone=us-east1-b | {3,4,12} | {"cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-c","cloud=gce,region=us-west1,zone=us-west1-c"}
(1 row)
```

Notice how table `offers` has 1 secondary index, and the table is empty (`range_size_mb` is 0).

## SQL Queries

Here is the SQL query

```sql
SELECT DISTINCT c.id, c.code, c.channel, c.status, c.end_date, c.start_date
FROM credits AS c
WHERE c.status = 'A'
  AND c.end_date >= '2020-11-20'
  AND c.start_date <= '2020-11-20'
  AND c.pid = '000000'

UNION

SELECT c.id, c.code, c.channel, c.status, c.end_date, c.start_date
FROM credits AS c, offers AS o
WHERE c.id = o.id
  AND c.code = o.code
  AND c.status = 'A'
  AND c.end_date >= '2020-11-20'
  AND c.start_date <= '2020-11-20'
  AND o.token = 'c744250a-1377-4cdf-a1f4-5b85a4d29aaa';
```

## Run the load test

You start the app load test.
While it runs, check the Metrics in the DB Console. Open the **Hardware** dashboard to see if you can replicate the spike in high CPU usage.

![cpu](https://dev-to-uploads.s3.amazonaws.com/i/ig4f32ad56t4ok3ofdo2.png)

Notice how 2 nodes have very high CPU usage compared to all other nodes. Take notice in the **Summary** of the values for QPS - 4046 - and P99 latency - 402ms -, too.

Check the **Service Latency** charts in the **SQL** dashboard for a better understanding.

![sql-p99](https://dev-to-uploads.s3.amazonaws.com/i/yguq0gi5a3tgm83jkgx0.png)

Stop the workload now, indeed there are high CPU spikes and high latency.

## Analyze the Queries

Let's break the query down into 2 parts, and let's pull the query plan for the 1st part. Again, the value `000000` is a placeholder for a value passed by the application.

```sql
EXPLAIN (VERBOSE) SELECT DISTINCT c.id, c.code, c.channel, c.status, c.end_date, c.start_date
FROM credits AS c
WHERE c.status = 'A'
  AND c.end_date >= '2020-11-20'
  AND c.start_date <= '2020-11-20'
  AND c.pid = '000000';
```

```text
          tree         |        field        |                                   description                                    |                        columns                         | ordering
-----------------------+---------------------+----------------------------------------------------------------------------------+--------------------------------------------------------+-----------
                       | distribution        | local                                                                            |                                                        |
                       | vectorized          | false                                                                            |                                                        |
  project              |                     |                                                                                  | (id, code, channel, status, end_date, start_date)      |
   │                   | estimated row count | 0                                                                                |                                                        |
   └── filter          |                     |                                                                                  | (id, code, channel, pid, end_date, status, start_date) |
        │              | estimated row count | 0                                                                                |                                                        |
        │              | filter              | ((status = 'A') AND (end_date >= '2020-11-20')) AND (start_date <= '2020-11-20') |                                                        |
        └── index join |                     |                                                                                  | (id, code, channel, pid, end_date, status, start_date) |
             │         | estimated row count | 0                                                                                |                                                        |
             │         | table               | credits@primary                                                                  |                                                        |
             │         | key columns         | id, code                                                                         |                                                        |
             └── scan  |                     |                                                                                  | (id, code, pid)                                        |
                       | estimated row count | 0                                                                                |                                                        |
                       | table               | credits@credits_pid_idx                                                          |                                                        |
                       | spans               | /0-/1                                                                            |                                                        |
(15 rows)

Time: 77ms total (execution 77ms / network 0ms)
```

So the optimizer is leveraging index `credits@credits_pid_idx` to filter rows that have that specific `pid`, but then it has to do a join with `primary` to fetch `status`, `end_date` and `start_date` to finish the rest of the `WHERE`, and `SELECT`, clauses.

Wouldn’t it be better if it didn’t have to do this join and instead access a single index?

Let's now pull the plan for the second part

```sql
EXPLAIN (VERBOSE) SELECT c.id, c.code, c.channel, c.status, c.end_date, c.start_date
FROM credits AS c, offers AS o
WHERE c.id = o.id
  AND c.code = o.code
  AND c.status = 'A'
  AND c.end_date >= '2020-11-20'
  AND c.start_date <= '2020-11-20'
  AND o.token = 'c744250a-1377-4cdf-a1f4-5b85a4d29aaa';
```

```text
            tree           |         field         |                                     description                                     |                              columns                               | ordering
---------------------------+-----------------------+-------------------------------------------------------------------------------------+--------------------------------------------------------------------+-----------
                           | distribution          | full                                                                                |                                                                    |
                           | vectorized            | false                                                                               |                                                                    |
  project                  |                       |                                                                                     | (id, code, channel, status, end_date, start_date)                  |
   │                       | estimated row count   | 0                                                                                   |                                                                    |
   └── lookup join (inner) |                       |                                                                                     | (id, code, token, id, code, channel, end_date, status, start_date) |
        │                  | estimated row count   | 0                                                                                   |                                                                    |
        │                  | table                 | credits@credits_code_id_idx                                                         |                                                                    |
        │                  | equality              | (code, id) = (code,id)                                                              |                                                                    |
        │                  | equality cols are key |                                                                                     |                                                                    |
        │                  | pred                  | ((status = 'A') AND (end_date >= '2020-11-20')) AND (start_date <= '2020-11-20')    |                                                                    |
        └── scan           |                       |                                                                                     | (id, code, token)                                                  |
                           | estimated row count   | 1                                                                                   |                                                                    |
                           | table                 | offers@offers_token_idx                                                             |                                                                    |
                           | spans                 | /"\xc7D%\n\x13wLߡ\xf4[\x85\xa4Қ\xaa"-/"\xc7D%\n\x13wLߡ\xf4[\x85\xa4Қ\xaa"/PrefixEnd |                                                                    |
```

Here we see that the optimizer is choosing an index to filter from the `offers` table and join with `credits`, which is fine.

## Addressing the Hotspot

Let's tackle the high CPU usage issue first. Why is it so, why is a node, n3 in this case, using all the CPU?

You restart you workload and execute [hot.py](https://github.com/cockroachlabs/workshop_labs/blob/master/troubleshooting/hot.py) to find the hottest range.

```bash
$ python3 hot.py --numtop 10 --host myhost.mydomain.com --adminport 26258 --dbport 26257  
rank  rangeId          QPS           Nodes       leaseHolder    DBname, TableName, IndexName
  1:       37   2006.722472     [4, 3, 12]                 3    ['defaultdb', 'offers', '']
  2:       39   857.900812       [5, 2, 8]                 5    ['defaultdb', 'credits', 'credits_pid_idx']
  3:        6    48.688882      [1, 6, 3, 11, 9]                   9    ['', '', '']
  4:       26    17.644921      [1, 4, 11, 12, 7]                  4    ['system', 'namespace2', '']
  5:        4    15.409775      [1, 9, 12]                12    ['', '', '']
  6:       35    12.764951      [4, 3, 9, 8, 10]                  10    ['system', 'sqlliveness', '']
  7:       11     3.369730      [6, 2, 9, 12, 10]                  2    ['system', 'jobs', '']
  8:        2     2.697347      [1, 5, 3, 12, 7]                  12    ['', '', '']
  9:        3     1.976292      [6, 4, 3, 11, 7]                   6    ['', '', '']
 10:        7     1.373621      [1, 2, 4, 12, 7]                   4    ['system', 'lease', '']
```

So it looks like Range ID 37 on **n3** is hot. What's in that range, why that range?

Back to your SQL terminal, show the ranges for `offers@offers_token_idx`, since the query plan showed it's using this index

```sql
SHOW RANGES FROM INDEX offers@offers_token_idx;
```

```text
  start_key | end_key | range_id | range_size_mb | lease_holder |           lease_holder_locality           | replicas |                                                          replica_localities
------------+---------+----------+---------------+--------------+-------------------------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------
  NULL      | NULL    |       37 |             0 |            3 | cloud=gce,region=us-east1,zone=us-east1-b | {3,4,12} | {"cloud=gce,region=us-east1,zone=us-east1-b","cloud=gce,region=us-east1,zone=us-east1-c","cloud=gce,region=us-west1,zone=us-west1-c"}
(1 row)

Time: 2.517s total (execution 2.516s / network 0.000s)
```

Bingo! We found Range ID 37.

As `offers` is empty, so is index `offers@offers_token_idx`, and thus there is just one range for that table.  If you have a join operation going on, inevitably CockroachDB will always want to access that range to do the join, causing the hotspot.

We need to ask our DBA:

- Why is a join operation sent against an empty table?
- Why is the table empty?

There are 4 possible ways to resolve a hot range situation:

1. Add data to the table, so that more ranges are created.

2. Use the [Follower Reads](https://www.cockroachlabs.com/docs/stable/topology-follower-reads.html) pattern, where you relax the requirement to always go to the leaseholder to access the data and instead can read from any of the replicas. Range replicas are spread across the cluster, balancing the load to other nodes.

3. Use the [Duplicate Indexes](https://www.cockroachlabs.com/docs/stable/topology-duplicate-indexes.html) pattern, where you duplicate the table/index and thus its ranges, resulting in multiple ranges that can serve the data.

4. Use the [`CONFIGURE ZONE` command](https://www.cockroachlabs.com/docs/v20.2/configure-zone.html) to decrease the range size from 512MB to something smaller, 64MB for example. This will create many, small, scattered ranges that help balance the load.

5. You can insert some junk data in the table or keep the old, obsolete data, so to keep enough data that the table results in multiple ranges.

In this case, you add a lot of data to the table. As a result, 4 ranges have been created and these will be spread across the nodes.

Re-run the workload, then check the **Hardware** dashboard again

![cpu-even](https://dev-to-uploads.s3.amazonaws.com/i/ska6hab5o6fz41y6j23y.png)

Much better, good job! Let's see how the ranges for the index are spread out:

```sql
SELECT lease_holder, lease_holder_locality 
FROM [
  SHOW RANGES 
  FROM INDEX offers@offers_token_idx
];
```

```text
  lease_holder |           lease_holder_locality
---------------+--------------------------------------------
             5 | cloud=gce,region=us-east1,zone=us-east1-c
             4 | cloud=gce,region=us-east1,zone=us-east1-c
             3 | cloud=gce,region=us-east1,zone=us-east1-b
             2 | cloud=gce,region=us-east1,zone=us-east1-b
```

Better! On average we can expect the load to be spread across 4 ranges in 4 different nodes.

## Addressing the Latency

### Understanding where the latency comes from

On the SQL terminal, you run a few queries to verify the Response Time.

Show my locality first

```sql
SHOW LOCALITY;
```

```text
                  locality
---------------------------------------------
  cloud=gce,region=us-east1,zone=us-east1-b
(1 row)

Time: 2ms total (execution 1ms / network 0ms)
```

Ok, I'm in US East. Let's run the first part of the query using a randomly picked valid `c.pid`.

```sql
SELECT DISTINCT c.id, c.code, c.channel, c.status, c.end_date, c.start_date
FROM credits AS c
WHERE c.status = 'A'
  AND c.end_date >= '2020-11-20'
  AND c.start_date <= '2020-11-20'
  AND c.pid = '1109619';
```

```text
  id |                 code                 | channel | status |         end_date          |        start_date
-----+--------------------------------------+---------+--------+---------------------------+----------------------------
   9 | f99e6553-18fb-475b-910e-eae4287e7ffa | O       | A      | 2020-12-19 00:00:00+00:00 | 2020-05-04 00:00:00+00:00
(1 row)

Time: 67ms total (execution 67ms / network 0ms)
```

Response Time is 69ms, a little too much. Why is it so? Let's check where the range that has this row is located.

From the query plan we pulled above, we see that it's using index `credits_pid_idx`. Find the key of the index

```sql
SHOW INDEX FROM credits;
```

```text
  table_name |     index_name      | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+---------------------+------------+--------------+-------------+-----------+---------+-----------
[...]

  credits    | credits_pid_idx     |    true    |            1 | pid         | ASC       |  false  |  false
  credits    | credits_pid_idx     |    true    |            2 | id          | ASC       |  false  |   true
  credits    | credits_pid_idx     |    true    |            3 | code        | ASC       |  false  |   true
  
[...]
```

Cool, for `credits@credits_pid_idx` the key is `pid id code`.
Let's pull the correct range

```sql
SELECT lease_holder_locality
FROM [
  SHOW RANGE FROM INDEX credits@credits_pid_idx 
  FOR ROW(1109619, 9, 'f99e6553-18fb-475b-910e-eae4287e7ffa')
];
```

```text
            lease_holder_locality
---------------------------------------------
  cloud=gce,region=us-east1,zone=us-east1-b
```

Ok, the range is local (us-east-1), this should only take 1ms. From the query plan we see that there is a join with `credits@primary` to fetch the other columns.

Let's see how long that takes

```sql
SELECT *
FROM credits@primary
WHERE id = 9 AND code = 'f99e6553-18fb-475b-910e-eae4287e7ffa';
```

```text
  id |                 code                 | channel |   pid   |         end_date          | status |        start_date
-----+--------------------------------------+---------+---------+---------------------------+--------+----------------------------
   9 | f99e6553-18fb-475b-910e-eae4287e7ffa | O       | 1109619 | 2020-12-19 00:00:00+00:00 | A      | 2020-05-04 00:00:00+00:00
(1 row)

Time: 66ms total (execution 66ms / network 0ms)
```

66ms! Let's do the same exercise as before and find out where this range is located.

```sql
SELECT lease_holder_locality
FROM [
  SHOW RANGE 
  FROM TABLE credits 
  FOR ROW(9, 'f99e6553-18fb-475b-910e-eae4287e7ffa')
];
```

```text
           lease_holder_locality           
-------------------------------------------
 cloud=gce,region=us-west1,zone=us-west1-c 
```

A-ha! This table is in US West, so we're paying the latency price to go to the other region to fetch the data.

The problem is twofold: sub-optimal tables/indexes and cross-regional reads.

### Part 1 - Optimize the table primary and secondary indexes

Let's optimize the top part of the query by removing the need to join with `credits@primary`.

We need to create an index similar to `credits@credits_pid_idx` that stores the fields required by the query.

Also, index `credits@credits_code_id_idx` seems to be redundant, so we drop it.

```sql
DROP INDEX credits@credits_code_id_idx;
DROP INDEX credits@credits_pid_idx;
CREATE INDEX credits_pid_idx
ON credits(pid ASC)
STORING (channel, end_date, status, start_date);
```

Pull the query plan to confirm no join is required

```sql
EXPLAIN (VERBOSE) SELECT DISTINCT c.id, c.code, c.channel, c.status, c.end_date, c.start_date
FROM credits AS c
WHERE c.status = 'A'
  AND c.end_date >= '2020-11-20'
  AND c.start_date <= '2020-11-20'
  AND c.pid = '12';
```

```text
       tree      |        field        |                                   description                                    |                        columns                         | ordering
-----------------+---------------------+----------------------------------------------------------------------------------+--------------------------------------------------------+-----------
                 | distribution        | local                                                                            |                                                        |
                 | vectorized          | false                                                                            |                                                        |
  project        |                     |                                                                                  | (id, code, channel, status, end_date, start_date)      |
   │             | estimated row count | 0                                                                                |                                                        |
   └── filter    |                     |                                                                                  | (id, code, channel, pid, end_date, status, start_date) |
        │        | estimated row count | 0                                                                                |                                                        |
        │        | filter              | ((status = 'A') AND (end_date >= '2020-11-20')) AND (start_date <= '2020-11-20') |                                                        |
        └── scan |                     |                                                                                  | (id, code, channel, pid, end_date, status, start_date) |
                 | estimated row count | 0                                                                                |                                                        |
                 | table               | credits@credits_pid_idx                                                          |                                                        |
                 | spans               | /12-/13                                                                          |                                                        |
```

Good stuff, we eliminated a join operation!

As per the second part, we see that the optimizer is never using index `offers@primary`. Let's alter the primary key of that table to make it similar to index `offers@offers_token_idx`.

```sql
DROP INDEX offers_token_idx;

-- this will take some time as we're basically rewriting the entire table
BEGIN;
  ALTER TABLE offers DROP CONSTRAINT "primary";
  ALTER TABLE offers ADD CONSTRAINT "primary" PRIMARY KEY (token, id, code);
COMMIT;
```

Check the [docs](https://www.cockroachlabs.com/docs/v20.2/primary-key) for how to change the primary key while still keeping the database online.

Review the schema after these changes.

```sql
SHOW CREATE TABLE offers;
```

```text
  table_name |                          create_statement
-------------+----------------------------------------------------------------------
  offers     | CREATE TABLE public.offers (
             |     id INT4 NOT NULL,
             |     code UUID NOT NULL,
             |     token UUID NOT NULL,
             |     start_date DATE NULL,
             |     end_date DATE NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (token ASC, id ASC, code ASC),
             |     FAMILY "primary" (id, code, token, start_date, end_date)
             | )
```

Awesome, much more efficient!

### Part 2 - Create duplicate indexes and pin to region

Now that we have our tables well organized, we need to resolve the latency issue.

We need our read latency to be the same regardless of where the query originates.

Implementing the Follower Reads pattern requires an App modification, so while ideal the Follower Reads pattern  is unfortunately not available.

The best solution thus is to follow the Duplicate Index pattern: we create a copy of each index and table.

Then, we pin a copy to US West and another to US East.

We create the indexes first

```sql
-- copy of credits@primary
CREATE INDEX primary_copy
ON credits(id ASC, code ASC)
STORING (channel, pid, end_date, status, start_date);

-- copy of credits_pid_idx
CREATE INDEX credits_pid_idx_copy
ON credits(pid ASC)
STORING (channel, end_date, status, start_date);

-- copy of offers@primary
CREATE INDEX primary_copy
ON offers(token ASC, id ASC, code ASC)
STORING (start_date, end_date);
```

Excellent, we have now a copy of each index (`primary` included)!

Next, pin a copy to East, and another to West.

```sql
-- credits
--   pin to East
ALTER TABLE credits CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=us-east1: 1}',
  lease_preferences = '[[+region=us-east1]]';

ALTER INDEX credits@credits_pid_idx CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=us-east1: 1}',
  lease_preferences = '[[+region=us-east1]]';

--   pin to West
ALTER INDEX credits@primary_copy CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=us-west1: 1}',
  lease_preferences = '[[+region=us-west1]]';

ALTER INDEX credits@credits_pid_idx_copy CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=us-west1: 1}',
  lease_preferences = '[[+region=us-west1]]';

-- offers
--    pin to East
ALTER TABLE offers CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=us-east1: 1}',
  lease_preferences = '[[+region=us-east1]]';

--    pin to West
ALTER INDEX offers@primary_copy CONFIGURE ZONE USING
  num_replicas = 3,
  constraints = '{+region=us-west1: 1}',
  lease_preferences = '[[+region=us-west1]]';
```

### Part 3 - Validate the theory

Re-run the first part of the query from both regions. Check the query plan using `EXPLAIN (VERBOSE)`.

From **n1** (US East region):

```sql
SELECT DISTINCT c.id, c.code, c.channel, c.status, c.end_date, c.start_date
FROM credits AS c
WHERE c.status = 'A'
  AND c.end_date >= '2020-11-20'
  AND c.start_date <= '2020-11-20'
  AND c.pid = '12';
```

```text
  id |                 code                 | channel | status |         end_date          |        start_date
-----+--------------------------------------+---------+--------+---------------------------+----------------------------
  19 | 468750f4-cb58-4707-9fd3-bd5f99111855 | O       | A      | 2020-12-18 00:00:00+00:00 | 2020-09-21 00:00:00+00:00
(1 row)

Time: 1ms total (execution 1ms / network 0ms)
```

```sql
EXPLAIN (VERBOSE) SELECT DISTINCT c.id, c.code, c.channel, c.status, c.end_date, c.start_date
FROM credits AS c
WHERE c.status = 'A'
  AND c.end_date >= '2020-11-20'
  AND c.start_date <= '2020-11-20'
  AND c.pid = '12';
```

```text
       tree      |        field        |                                      description                                      |                        columns                         | ordering
-----------------+---------------------+---------------------------------------------------------------------------------------+--------------------------------------------------------+-----------
                 | distribution        | local                                                                                 |                                                        |
                 | vectorized          | false                                                                                 |                                                        |
  project        |                     |                                                                                       | (id, code, channel, status, end_date, start_date)      |
   │             | estimated row count | 0                                                                                     |                                                        |
   └── filter    |                     |                                                                                       | (id, code, channel, pid, end_date, status, start_date) |
        │        | estimated row count | 0                                                                                     |                                                        |
        │        | filter              | ((status = 'A') AND (end_date >= '2020-11-20')) AND (start_date <= '2020-11-20') |                                                        |
        └── scan |                     |                                                                                       | (id, code, channel, pid, end_date, status, start_date) |
                 | estimated row count | 0                                                                                     |                                                        |
                 | table               | credits@credits_pid_idx                                                               |                                                        |
                 | spans               | /3124791208-/3124791209                                                               |                                                        |                                                        |                                                        |
```

Same queries above run on **n12** (US West region):

```text
  id |                 code                 | channel | status |         end_date          |        start_date
-----+--------------------------------------+---------+--------+---------------------------+----------------------------
  19 | 468750f4-cb58-4707-9fd3-bd5f99111855 | O       | A      | 2020-12-18 00:00:00+00:00 | 2020-09-21 00:00:00+00:00
(1 row)

Time: 2ms total (execution 1ms / network 0ms)
```

```text
       tree      |        field        |                                      description                                      |                        columns                         | ordering
-----------------+---------------------+---------------------------------------------------------------------------------------+--------------------------------------------------------+-----------
                 | distribution        | local                                                                                 |                                                        |
                 | vectorized          | false                                                                                 |                                                        |
  project        |                     |                                                                                       | (id, code, channel, status, end_date, start_date)      |
   │             | estimated row count | 1                                                                                     |                                                        |
   └── filter    |                     |                                                                                       | (id, code, channel, pid, end_date, status, start_date) |
        │        | estimated row count | 1                                                                                     |                                                        |
        │        | filter              | ((status = 'A') AND (end_date >= '2020-11-19')) AND (start_date <= '2020-11-19') |                                                        |
        └── scan |                     |                                                                                       | (id, code, channel, pid, end_date, status, start_date) |
                 | estimated row count | 2                                                                                     |                                                        |
                 | table               | credits@credits_pid_idx_copy                                                          |                                                        |
                 | spans               | /3124791208-/3124791209                                                               |                                                        |
```

Perfect, we've low latency from both regions! Now start the workload again and let's measure the overall latency.

Compare to the initial result: A huge improvement in performance! We doubled the QPS and halved the Latency!

![final](https://dev-to-uploads.s3.amazonaws.com/i/n1kv78l4cie27ww6vlgw.png)

Congratulations, your cluster is performing much better now!
We hope you have now a better understanding on the process of troubleshooting an underperforming cluster.

## References

Official CockroachDB Documentation

- [Troubleshooting Overview](https://www.cockroachlabs.com/docs/stable/troubleshooting-overview.html)
- [Troubleshooting Slow Query](https://www.cockroachlabs.com/docs/stable/query-behavior-troubleshooting.html#using-the-slow-query-log)
- [Troubleshooting Hot Node/Range](https://www.cockroachlabs.com/docs/stable/query-behavior-troubleshooting.html#single-hot-node)
- [DB Console Overview](https://www.cockroachlabs.com/docs/stable/ui-overview.html)
- [DB Console Hardware Dashboard](https://www.cockroachlabs.com/docs/stable/ui-hardware-dashboard.html)
- [DB Console SQL Dashboard](https://www.cockroachlabs.com/docs/stable/ui-sql-dashboard.html)


Blogs

- [Glenn Fawcett: CockroachDB troubleshooting series: define the process](https://glennfawcett.wpcomstaging.com/2020/12/21/cockroachdb-troubleshooting-series-define-the-process/)
- [Glenn Fawcett: hot single range queries troubleshooting part 1](https://glennfawcett.wpcomstaging.com/2020/12/22/hot-single-range-queries-troubleshooting-part1/)