Tracking slow queries
It is important to know which queries are not performing as expected so that we can further investigate to find the actual cause for the slowness. Some queries can become slow when the data grows. So, it is important to benchmark some of the critical queries over time to make sure they are getting executed within the expected time. There are three major ways to identify and debug slow queries:
- Use
EXPLAINand see if the query involves full scans and if so, see what kind of indexes you can create to avoid it. This holds true for table joins as well. - Turn on the slow query log for a specific latency threshold. In the following example, all queries whose latency is greater than or equal to 500 milliseconds will be logged in the slow query log:
> SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '500ms'; SET CLUSTER SETTING Time: 120ms total (execution 120ms / network 0ms)
- Integrate CockroachDB logging with tools such as OpenTelemetry...