It takes longer to count tables where data is being inserted

CrateDB
1

I’m using kafka connect sink data to table in cratedb, while it takes longer to count it. Is this normal ？

cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
|  3259573 |
+----------+
SELECT 1 row in set (16.699 sec)

If the table isn’t being insert or updated data, counting is fast.

cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
|  3388488 |
+----------+
SELECT 1 row in set (0.344 sec)
1 Like
2

Hi Jun Zhou,

when reading and writing at the same time, the system has to manage both workloads in one way or another, where the hardware or other kinds of low system resources may become a bottleneck.

For example, when running data I/O on a spindle disk, the disk head needs to move around heavily to serve both workloads at the same time, thus it will slow down the process.

With kind regards,
Andreas.

3

Are you only running this SELECT query on the table once in a while and no other read queries?

By default CrateDB disables the automatic refresh of tables that are only written to, but not read otherwise. So the first query might take significantly longer, as CrateDB first refreshes the table and then runs the query.

Could you try running …

ALTER TABLE ebs.rwmv_xxmtl_transactions SET ("refresh_interval" = '1000');

… to see if that improves the query speed?

3 Likes
4

Thanks a lot @proddata . There are no other queries on the table. After setting refresh_interval=1000, The query speed is significantly improved. :grinning:

cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
|  4177902 |
+----------+
SELECT 1 row in set (0.004 sec)
cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
|  4182494 |
+----------+
SELECT 1 row in set (0.002 sec)
cr> select count(*) from ebs.rwmv_xxmtl_transactions;
+----------+
| count(*) |
+----------+
|  4221167 |
+----------+
SELECT 1 row in set (0.003 sec)
1 Like