CODEWITHSUNDEEP
cassandracql
Philipp Claßen
Philipp Claßen

Reputation: 43960

select count(*) runs into timeout issues in Cassandra

Maybe it is a stupid question, but I'm not able to determine the size of a table in Cassandra.

This is what I tried:

select count(*) from articles;

It works fine if the table is small but once it fills up, I always run into timeout issues:

cqlsh:

DBeaver:

I assume that it hits some timeout and just aborts. The actual number of entries in the table is probably much higher.

I'm testing against a local Cassandra instance which is completely idle. I would not mind if it has to do a full table scan and is unresponsive during that time.

Is there a way to reliably count the number of entries in a Cassandra table?

I'm using Cassandra 2.1.13.

Upvotes: 23

Views: 32729

Answers (7)

Usman Khawaja
Usman Khawaja

Reputation: 819

You can define timeout seconds as a command line option while using Datastax cqlsh. The default value is 10.

$ cqlsh --request-timeout=3600

Upvotes: 0

Shubham
Shubham

Reputation: 317

You can use copy to avoid cassandra timeout usually happens on count(*)

use this bash

cqlsh -e "copy keyspace.table_name (first_partition_key_name) to '/dev/null'" | sed -n 5p | sed 's/ .*//'

Upvotes: 0

Kat
Kat

Reputation: 337

This is a good utility for counting rows that avoids the timeout issues that happen when running a large COUNT(*) in Cassandra:

https://github.com/brianmhess/cassandra-count

Upvotes: 4

Citrullin
Citrullin

Reputation: 2321

The reason is simple:

When you're using:

SELECT count(*) FROM articles;

it has the same effect on the database as:

SELECT * FROM articles;

You have to query over all your nodes. Cassandra simply runs into a timeout.

You can change the timeout, but it isn't a good solution. (For one time it's fine but don't use it in your regular queries.)

There's a better solution: make your client count your rows. You can create a java app where you count your rows, when you inserting them, and insert the result using a counter column in a Cassandra table.

Upvotes: 1

Oleksandr Petrenko
Oleksandr Petrenko

Reputation: 633

As far as I see you problem connected to timeout of cqlsh: OperationTimedOut: errors={}, last_host=127.0.0.1

you can simple increase it with options: 

 --connect-timeout=CONNECT_TIMEOUT
                       Specify the connection timeout in seconds (default: 5
                       seconds).
 --request-timeout=REQUEST_TIMEOUT
                       Specify the default request timeout in seconds
                       (default: 10 seconds).

Upvotes: 19

doanduyhai
doanduyhai

Reputation: 8812

Is there a way to reliably count the number of entries in a Cassandra table?

Plain answer is no. It is not a Cassandra limitation but a hard challenge for distributed systems to count unique items reliably.

That's the challenge that approximation algorithms like HyperLogLog address.

One possible solution is to use counter in Cassandra to count the number of distinct rows but even counters can miscount in some corner cases so you'll get a few % error.

Upvotes: 13

Philipp Claßen
Philipp Claßen

Reputation: 43960

Here is my current workaround:

COPY articles TO '/dev/null';
...
3568068 rows exported to 1 files in 2 minutes and 16.606 seconds.

Background: Cassandra supports to export a table to a text file, for instance:

COPY articles TO '/tmp/data.csv';
Output: 3568068 rows exported to 1 files in 2 minutes and 25.559 seconds

That also matches the number of lines in the generated file:

$ wc -l /tmp/data.csv
3568068

Upvotes: 24

Related Questions