Advanced dashboard in ClickHouse Cloud

## Identifying issues using the advanced dashboard[​](#identifying-issues-with-the-advanced-dashboard "Direct link to Identifying issues using the advanced dashboard")

Having this real-time view of the health of your ClickHouse service greatly helps
mitigate issues before they impact your business or help solve them. Below are a
few issues you can spot using the advanced dashboard.

### Unbatched inserts[​](#unbatched-inserts "Direct link to Unbatched inserts")

As described in the [best practices documentation](/docs/best-practices/selecting-an-insert-strategy#batch-inserts-if-synchronous), it is recommended to always
bulk insert data into ClickHouse if able to do so synchronously.

A bulk insert with a reasonable batch size reduces the number of parts created
during ingestion, resulting in more efficient write-on disks and fewer merge
operations.

The key metrics to spot sub-optimized insert are **Inserted Rows/sec** and
**Max Parts for Partition**

The example above shows two spikes in **Inserted Rows/sec** and **Max Parts for Partition**
between 13h and 14h. This indicates that we ingest data at a reasonable speed.

Then we see another big spike on **Max Parts for Partition** after 16h but a
very slow **Inserted Rows/sec speed**. A lot of parts are being created with
very little data generated, which indicates that the size of the parts is
sub-optimal.

### Resource intensive query[​](#resource-intensive-query "Direct link to Resource intensive query")

It is common to run SQL queries that consume a large amount of resources, such as
CPU or memory. However, it is important to monitor these queries and understand
their impact on your deployment's overall performance.

A sudden change in resource consumption without a change in query throughput can
indicate more expensive queries being executed. Depending on the type of queries
you're running, this can be expected, but spotting them from the advanced
dashboard is good.

Below is an example of CPU usage peaking without significantly changing the
number of queries per second executed.

### Bad primary key design[​](#bad-primary-key-design "Direct link to Bad primary key design")

Another issue you can spot using the advanced dashboard is a bad primary key design.
As described in ["A practical introduction to primary indexes in ClickHouse"](/docs/guides/best-practices/sparse-primary-indexes#a-table-with-a-primary-key),
choosing the primary key to fit best your use case will greatly improve performance
by reducing the number of rows ClickHouse needs to read to execute your query.

One of the metrics you can follow to spot potential improvements in primary keys
is **Selected Rows per second**. A sudden peak in the number of selected rows can
indicate both a general increase in overall query throughput, and queries that
select a large number of rows to execute their query.

Using the timestamp as a filter, you can find the queries executed at the time
of the peak in the table `system.query_log`.

For example, running a query that shows all the queries executed between 11 am
and 11 am on a certain day to understand what queries are reading too many rows:

```
SELECT
    type,
    event_time,
    query_duration_ms,
    query,
    read_rows,
    tables
FROM system.query_log
WHERE has(databases, 'default') AND (event_time >= '2024-12-23 11:20:00') AND (event_time <= '2024-12-23 11:30:00') AND (type = 'QueryFinish')
ORDER BY query_duration_ms DESC
LIMIT 5
FORMAT VERTICAL
```

```
Row 1:
──────
type:              QueryFinish
event_time:        2024-12-23 11:22:55
query_duration_ms: 37407
query:             SELECT
    toStartOfMonth(review_date) AS month,
    any(product_title),
    avg(star_rating) AS avg_stars
FROM amazon_reviews_no_pk
WHERE
    product_category = 'Home'
GROUP BY
    month,
    product_id
ORDER BY
    month DESC,
    product_id ASC
LIMIT 20
read_rows:         150957260
tables:            ['default.amazon_reviews_no_pk']

Row 2:
──────
type:              QueryFinish
event_time:        2024-12-23 11:26:50
query_duration_ms: 7325
query:             SELECT
    toStartOfMonth(review_date) AS month,
    any(product_title),
    avg(star_rating) AS avg_stars
FROM amazon_reviews_no_pk
WHERE
    product_category = 'Home'
GROUP BY
    month,
    product_id
ORDER BY
    month DESC,
    product_id ASC
LIMIT 20
read_rows:         150957260
tables:            ['default.amazon_reviews_no_pk']

Row 3:
──────
type:              QueryFinish
event_time:        2024-12-23 11:24:10
query_duration_ms: 3270
query:             SELECT
    toStartOfMonth(review_date) AS month,
    any(product_title),
    avg(star_rating) AS avg_stars
FROM amazon_reviews_pk
WHERE
    product_category = 'Home'
GROUP BY
    month,
    product_id
ORDER BY
    month DESC,
    product_id ASC
LIMIT 20
read_rows:         6242304
tables:            ['default.amazon_reviews_pk']

Row 4:
──────
type:              QueryFinish
event_time:        2024-12-23 11:28:10
query_duration_ms: 2786
query:             SELECT
    toStartOfMonth(review_date) AS month,
    any(product_title),
    avg(star_rating) AS avg_stars
FROM amazon_reviews_pk
WHERE
    product_category = 'Home'
GROUP BY
    month,
    product_id
ORDER BY
    month DESC,
    product_id ASC
LIMIT 20
read_rows:         6242304
tables:            ['default.amazon_reviews_pk']
```

In this example, we can see the same query being executed against two
tables `amazon_reviews_no_pk` and `amazon_reviews_pk`. It can be concluded that
someone was testing a primary key option for the table `amazon_reviews`.

- [Accessing the advanced dashboard](#accessing-the-advanced-dashboard)- [Accessing the native advanced dashboard](#accessing-the-native-advanced-dashboard)- [Out-of-box visualizations](#out-of-box-visualizations)
      - [ClickHouse specific](#clickhouse-specific)- [System health specific](#system-health-specific)- [ClickHouse Cloud specific](#clickhouse-cloud-specific)- [Identifying issues using the advanced dashboard](#identifying-issues-with-the-advanced-dashboard)
          - [Unbatched inserts](#unbatched-inserts)- [Resource intensive query](#resource-intensive-query)- [Bad primary key design](#bad-primary-key-design)

Was this page helpful?