CrateDB number of shards and performance

Question

so i have this table :

CREATE TABLE DATA.history (
modemId text PRIMARY KEY,            
generationDate timestamp PRIMARY KEY, 
eventId integer PRIMARY KEY,   
speed double,     
altitude double INDEX OFF,
latitude Double INDEX OFF,
longitude Double INDEX OFF,   
odometer Double,
month timestamp with time zone GENERATED ALWAYS AS date_trunc('month', generationDate) PRIMARY KEY) PARTITIONED BY (month) clustered into 10 shards;

with data from gps devices in a 3 nodes cluster(8 cpu, 32gb ram).

I have a few doubts about it :

1.- Im not sure about the number of shardings (im asuming that the numbers of shards count per partition, in this case for a 2 months data i'd have 2 x 10 shards, right?), what do you think is a good number here? queries will be for modemid and generationdate ranges.

2._ How can i check the performance of the queries, i know there is a EXPLAIN command like mysql, but im not sure what to look in it.

thank you !

score 0 · Answer 1 · answered Jun 23 '20 at 09:57

To partially answer your question, look at the docs and general info on shards here. Advice is that there should be at least as many shards for a table than there are CPUs in the cluster.

Your second question is probably a little too subjective to answer.

CrateDB number of shards and performance

1 Answers1