Title
#users-public
Newskooler

Newskooler

09/12/2022, 9:40 PM
Hi QDB team, I have a weird error which I suspect is o3 based. I am using QDB version 6.4.2 I am writing data to a monthly partitioned table with maxUncommitedRows set to 100k and commitLag set to 10seconds. The data is trades and I add them 6h blocks at a time. First I populate old trades and then new new trades in order to avoid o3. When I initially start this process, all works well, until at some point it gets stuck. The logs show something like repeating every ~10s (which I guess is the commit lag).
TableWriter closing last parition ...
TableWriter merged parition
TableWriter switched parition
TableWriter purged
TableWriter sorting o3
TableWriter sorted
Then it saves ~150 rows only. What I suspect is the issue is that: • I save data ahead of time (due to the commitLag/maxUncommitedRows • then it needs to save o3 data, but the commitLag is triggered, so it takes a ton of time My questions are: • Is this normal to see? • How can I speed this up - maybe increase commitLag or maxUncommitedRows? • Of course, committing no o3 will be best, right? • What if I change partition to DAY and not MONTH?
Andrey Pechkurov

Andrey Pechkurov

09/13/2022, 9:01 AM
Hi Stelian, Let me try to answer the questions in no particular order and ask you something.
What if I change partition to DAY and not MONTH?
Then partition rewrites would become much cheaper, so such a change may improve things for you. The way to approach this decision is to check Prometheus metrics:
questdb_physically_written_rows_total
and
questdb_committed_rows_total
. If the first metric is significantly higher than the second one, you need to do something with O3 writes: try to tweak commit lag, partitioning strategy and so on. As for 150 rows only being saved, might it be the case that most of the rows you're writing are within the last 10 seconds? If so, they would be committed only when some time pass
Newskooler

Newskooler

09/13/2022, 9:05 AM
Thanks. Regarding your last note:
As for 150 rows only being saved, might it be the case that most of the rows you’re writing are within the last 10 seconds? If so, they would be committed only when some time pass
This is not it, since it’s historical data - i.e. I get like 1M rows and then need to save them. Usually it saves them in the maxUncommitedRows batch ~100k at a time, but now it started to save about 150 only (maxing out the CommitLag). So a 6h period is taking horus to save.
9:05 AM
I will do as you suggested and see what results this yields.
9:05 AM
and most likely write back.
9:05 AM
Question: • Besides tweaking o3 and table parameters, what else can I do? • Would increasing QDB version help?
Andrey Pechkurov

Andrey Pechkurov

09/13/2022, 9:15 AM
How big are you partititions? I mean size in bytes, on disk
9:16 AM
Using 6.5.2 is certainly a good idea since there were some important bugfixes since 6.4.2. But I don't think the upgrade would improve performance on its own for you
Newskooler

Newskooler

09/13/2022, 9:17 AM
How can I check the partition size? ls the table folder does not help.
Andrey Pechkurov

Andrey Pechkurov

09/13/2022, 9:17 AM
Check the table dir: it should contain partition dirs
Newskooler

Newskooler

09/13/2022, 9:18 AM
yes, but each partition is the same size. Only the column files are different sizes
Andrey Pechkurov

Andrey Pechkurov

09/13/2022, 9:18 AM
And what's the size of each partition?
Newskooler

Newskooler

09/13/2022, 9:19 AM
never mind - I found how to see. They are between 100MB and 1GB - daily partitions.
Andrey Pechkurov

Andrey Pechkurov

09/13/2022, 9:19 AM
Not much
Newskooler

Newskooler

09/13/2022, 9:19 AM
Indeed.
Andrey Pechkurov

Andrey Pechkurov

09/13/2022, 9:21 AM
How many rows are you writing in this 6h batch?
Newskooler

Newskooler

09/13/2022, 9:24 AM
Depends, but they are around between 100k - 10M
9:24 AM
rarely over 2 M though
Andrey Pechkurov

Andrey Pechkurov

09/13/2022, 9:26 AM
Could you share the logs around this slowness?