Read takes longer time

We have large dataset around 40Million+ records in the database (and growing). The deployment is done in multiple datacenter and all nodes are connected. Each datacenter with 3 node cluster.

The table consists of composite PK with a string (64 chars) and an integer field. The operations that happen on this table are insert and reads (update and delete are really rare). Compared to inserts, select are more in number but read time is so huge. Always SELECT query uses PK to fetch the record.

Please see below screen shots. Initially this was good and we didn’t see any problem. Able to get around 1100 reads/sec but it hardly 100 that are successfully fetched. Not sure where the issue is.

Let me know if you guys have any recommendation about this or possible cause. Version used is 19.1.4

Hi Sathya, could you provide a trace of the slow query? set tracing=on; <stmt>; set tracing=off; show trace for session; will print out a trace projected as a table instead of a tree.

Troubleshoot SQL Behavior | CockroachDB Docs might also be useful, as it provides multiple options for troubleshooting SQL behavior.

One other thing to note is that improved debugging tools are available in newer versions of CockroachDB if upgrading is an option for you.

Hi Lauren,
I replaced IPs with 1.2.3.4 and 2.3.4.5 in the trace output. Rest attached as it is.
Please suggest.

Thanks in advance

Sathya, I’m not seeing an attachment. Was there an issue uploading the trace output?

Sorry it was not allowing me to upload a file, looks like I overlooked at it when replying. Now I could, please see the attachment
show_trace_for_session_cdb.pdf (69.1 KB)

The plan for the query seems reasonable. My guess is that the contention (other writes) on the table might be the root cause of the slow SELECT queries. Unfortunately, the trace doesn’t contain any hints.

I would also encourage you to upgrade to a newer version because we’ve made significant progress over the last 2 years (since 19.1 release came out), and it’s possible you wouldn’t be encountering the same problem then.