[SOLVED] ScyllaDB count(*) return difference result

ScyllaDB count(*) return difference result

I have a question about query in scylladb. I want to count the rows in a table with:

SELECT COUNT(*) 
FROM tabledata;

First run returns a result of 5732 rows
Second run returns a result of 5432 rows

Always different result.

Any suggestions on how to count rows in scylla?

Solution

Consistency level?

(you can find on internet a very funny picture about eventual consistency)

IF you have RF=3

If you wrote all your rows with LOCAL_QUORUM

then I'd set CONSISTENCY LOCAL_QUORUM

and rerun the count

if you are not sure whether all your writes were properly done, use CL ALL

another option is to run a full repair and rerun the count

ALSO your table might have TTL, in such case having a different count every time is expected (and if you wrote it might be bigger, if you just read, then it will be smaller)

For efficient count look at https://github.com/scylladb/scylla-code-samples/tree/master/efficient_full_table_scan_example_code - but the same applies re consistency level (and of course this script will tell you with a timeout error that a token range couldn't queried and it means that node/shard was overloaded with other traffic, by default it doesn't retry, it's a simple script)