cockroachdb / cockroachdb/cockroach
Jobs: Slow jobs table query performance leads to cluster overload
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
When MOLT Fetch runs for a large number of partitioned tables in the case of PG -> CRDB, each partition acts as an individual table and can generate N number of IMPORT jobs based on the size of each partition. With say 300 partitions, you would potentially have anywhere from 300 job records to 300N records.
With the way Fetch operates, it issues the query to IMPORT without using detached so it does not have knowledge of the job ID, so in order to perform a look up of the import job progress it performs a query like
```
WITH x as (SHOW JOBS)
SELECT description, started, fraction_completed
FROM x
WHERE job_type='IMPORT'
AND description LIKE '%%%s.%s(%%'
AND started > '%s'
ORDER BY created DESC
```
in order to find the recent record. However, as the jobs table continues to grow in number of records, this query seems to start causing an immense amount of cluster overhead. In this example the customer was showing that node 4 handling the jobs table queries was being overloaded to 95%.

In this example, it is a comparison of running the jobs query versus not running it, and you can see the large difference in cluster utilization as the job table continues to grow.

The jobs table query for the customer took up to 50s and in my test reproduction it went as high as 25 seconds.
**To Reproduce**
Create a large number of jobs table records and try to poll the table for the status of certain jobs that are running and observe the CPU usage of the cluster spike.
**Expected behavior**
Jobs table queries should return quickly or not provide significant overload to the cluster.
**Additional data / screenshots**
Attached is a statement bundle for a query that took ~3s or so but for what the query is doing, should have been faster given the load on the cluster.
[stmt-bundle-1000509387754045441.zip](https://github.com/user-attachments/files/16949789/stmt-bundle-1000509387754045441.zip)
What was the impact?
The node handling the jobs table query became overloaded and spiked in CPU usage which can cause issues for production applications.
Jira issue: CRDB-42053
Contributor guide
Assessment
This issue has not been assessed yet.