SELECT COUNT(id) FROM table is fairly slow

sql

(Derek Zhang) #1

I have a table with 132,000,000 records. “id” is the primary key.
SELECT COUNT(id) from table
took 1m27s to finish.

Is it normal? This performance looks too bad comparing to many databases. Is there a way to improve the performance of the simple query?

Thanks,
Derek


(Ron Arévalo) #2

Hey Derek,

Can you tell me a bit more about your cluster? How many nodes do you have and how are they provisioned? Where are your running the nodes?

Thanks,

Ron


(Derek Zhang) #3

The CRDB deployment has 3 pods. Each pod has 100m CPU, 4G memory and 250G disk. They are running on AWS Kubernetes cluster.
I would say this configuration is not low at all. How does the simple count query take so long.
How long would you expect for such count queries in your test environment?


(Ron Arévalo) #4

Hey @derek.z,

From our tests we have a few benchmarks and your result isn’t too far off what we would expect. But, results can vary depending on the size of the rows, the number of machines in your cluster and the distribution of ranges in the cluster.

Could you clarify what 100m CPU means? The Kubernetes documentation leads me to believe that 100m of CPU means .1 cpu, which would mean that your machine is under provisioned for running CRDB.

You could most likely verify this by on the hardware tab of the admin UI when running your query.

Thanks,

Ron


(Derek Zhang) #5

Sorry, actually the CPU is “4”.

resources:
  limits:
    cpu: "4"
    memory: 4Gi
  requests:
    cpu: "4"
    memory: 4Gi