Title
#users-market-data
s

Shubham Jain

11/20/2022, 6:26 AM
Hi everyone, I am ingesting data using ILP golang library, but recently I am starting to see 1-2sec to delay while ingesting the data. I am ingesting ~15K rows of data in one go and then flushing it.
{"level":"info","msg":"inserting 15375 candles..."}
{"level":"info","msg":"inserted 15375candles in1.783808583s"}
{"level":"info","msg":"flushed in 56.125µs"}
• My server config - m6a.xlarge • using GP3 SSD with 3000 IOPS and confirmed only ~350 write ops are taking/sec so I am assuming its not I/O throttled • commit lag is set to server default • Using questDB official AMI for this degraded performance could writing 15k candles and then flushing it be the reason ? If so then flushing for each candle data wont that also introduce lag due to individual ack or am i missing something else here ?
Nicolas Hourcard

Nicolas Hourcard

11/20/2022, 4:00 PM
hey @Shubham Jain we’ll look at this tomorrow
4:01 PM
just as a FYI we have a new release (should be out tomorrow) with a complete rewrite around the commit lag, where we will automatically be optimizing it on your behalf. What you’re seeing there should thus be solved, but ill let engineering provide more details
s

Shubham Jain

11/21/2022, 4:11 AM
Thanks.
4:12 AM
I was also looking at the server logs looks like all my data is getting marked as O3 insertions
Nov 21 04:10:01 ip-172-31-24-116 questdb: 2022-11-21T04:10:01.877507Z I i.q.c.TableWriter o3 commit lag [table=nse_min, maxUncommittedRows=1000, o3TimestampMin=2017-06-23T03:52:00.000000Z, o3TimestampMax=2022-11-21T04:06:00.000000Z, lagUs=10000000, lagThresholdTimestamp=2022-11-21T04:05:50.000000Z, o3LagRowCount=1, srcOooMax=1000, o3RowCount=1001]
Nov 21 04:10:01 ip-172-31-24-116 questdb: 2022-11-21T04:10:01.877523Z I i.q.c.TableWriter sorted [table=nse_min, o3RowCount=1]
Nov 21 04:10:01 ip-172-31-24-116 questdb: 2022-11-21T04:10:01.877530Z I i.q.c.TableWriter o3 partition task [table=nse_min, srcOooLo=0, srcOooHi=999, srcOooMax=1000, o3RowCount=1001, o3LagRowCount=1, srcDataMax=5380555, o3TimestampMin=2017-06-23T03:52:00.000000Z, o3Timestamp=2017-06-23T03:52:00.000000Z, o3TimestampMax=2017-06-28T08:20:00.000000Z, partitionTimestamp=2017-06-01T00:00:00.000000Z, partitionIndex=112, partitionSize=5381555, maxTimestamp=2022-11-21T04:05:00.000000Z, last=false, append=false, pCount=1, flattenTimestamp=true, memUsed=19789141824]
Nov 21 04:10:02 ip-172-31-24-116 questdb: 2022-11-21T04:10:02.007408Z I i.q.c.TableWriter merged partition [table=`nse_min`, ts=2017-06-01T00:00:00.000000Z, txn=482259]
Nov 21 04:10:02 ip-172-31-24-116 questdb: 2022-11-21T04:10:02.007501Z I i.q.c.TableWriter purged [path=/var/lib/questdb/db/nse_min/2017-06.482258]
Nov 21 04:10:02 ip-172-31-24-116 questdb: 2022-11-21T04:10:02.008391Z I i.q.c.TableWriter o3 commit lag [table=nse_min, maxUncommittedRows=1000, o3TimestampMin=2017-06-28T08:21:00.000000Z, o3TimestampMax=2022-11-21T04:06:00.000000Z, lagUs=10000000, lagThresholdTimestamp=2022-11-21T04:05:50.000000Z, o3LagRowCount=1, srcOooMax=1000, o3RowCount=1001]
Nov 21 04:10:02 ip-172-31-24-116 questdb: 2022-11-21T04:10:02.008403Z I i.q.c.TableWriter sorted [table=nse_min, o3RowCount=1]
Nov 21 04:10:02 ip-172-31-24-116 questdb: 2022-11-21T04:10:02.008411Z I i.q.c.TableWriter o3 partition task [table=nse_min, srcOooLo=0, srcOooHi=840, srcOooMax=1000, o3RowCount=1001, o3LagRowCount=1, srcDataMax=5381555, o3TimestampMin=2017-06-28T08:21:00.000000Z, o3Timestamp=2017-06-28T08:21:00.000000Z, o3TimestampMax=2017-07-03T06:28:00.000000Z, partitionTimestamp=2017-06-01T00:00:00.000000Z, partitionIndex=112, partitionSize=5382396, maxTimestamp=2022-11-21T04:05:00.000000Z, last=false, append=false, pCount=1, flattenTimestamp=true, memUsed=19789141824]
Nov 21 04:10:02 ip-172-31-24-116 questdb: 2022-11-21T04:10:02.008420Z I i.q.c.TableWriter o3 partition task [table=nse_min, srcOooLo=841, srcOooHi=999, srcOooMax=1000, o3RowCount=1001, o3LagRowCount=1, srcDataMax=5215477, o3TimestampMin=2017-06-28T08:21:00.000000Z, o3Timestamp=2017-07-03T03:50:00.000000Z, o3TimestampMax=2017-07-03T06:28:00.000000Z, partitionTimestamp=2017-07-01T00:00:00.000000Z, partitionIndex=116, partitionSize=5215636, maxTimestamp=2022-11-21T04:05:00.000000Z, last=false, append=false, pCount=2, flattenTimestamp=false, memUsed=19789141824]
I am assuming this is the reason for degraded performance.
Andrey Pechkurov

Andrey Pechkurov

11/21/2022, 7:07 AM
Hi @Shubham Jain, Could you share your table schema? You can right click on the table name in Web Console and then use the Copy Schema menu option. I'd like to understand the partitioning strategy you're using.
s

Shubham Jain

11/21/2022, 7:11 AM
CREATE TABLE 'nse_min' (
  time TIMESTAMP,
  open FLOAT,
  high FLOAT,
  low FLOAT,
  close FLOAT,
  volume INT,
  token SYMBOL capacity 256 CACHE index capacity 256
) timestamp (time) PARTITION BY MONTH;
7:12 AM
just fyi although we have initialized with 256 symbols but we will be having closing to 2000 symbols as of now. But while inserting we are inserting only 1 symbol data at a time.
7:13 AM
To test the O3 issue, I also have tweaked my code to push all same month candles in a single go which will be ~7.5K rows I am still seeing high latencies close to 3-5 sec 😢
Andrey Pechkurov

Andrey Pechkurov

11/21/2022, 7:14 AM
How many rows do you have per day?
s

Shubham Jain

11/21/2022, 7:14 AM
currently we are seeding the past data only from APIs
Andrey Pechkurov

Andrey Pechkurov

11/21/2022, 7:16 AM
Are those past rows that you're inserting spread across the month or they belong to a single or a few days?
s

Shubham Jain

11/21/2022, 7:16 AM
I will be more than happy to provide all the relevant info/code snippets for more info which would be helpful for us to identify the issue
7:19 AM
I am inserting ~16K rows spread across 2 months where each tradng day contains 375 rows. Also, I am flushing after inserting 1 month data as well
Andrey Pechkurov

Andrey Pechkurov

11/21/2022, 7:26 AM
Are you appending rows to each day or the inserted rows may be somewhere in the middle of the day? Also, could you send me a more complete server log for this slow 16K rows insertion via DMs?
7:27 AM
So far it looks like you're getting commits per each 1K rows and those commits either append or re-write the monthly partition (which has around 5.2M rows)
s

Shubham Jain

11/21/2022, 7:30 AM
sure. I can share the logs file with you. Yes, we currently have 490960625 and at the end of the data seeding we would have around 1000000000 rows. Post this we will be adding 750K rows daily for this table alone
Andrey Pechkurov

Andrey Pechkurov

11/21/2022, 7:46 AM
just fyi although we have initialized with 256 symbols but we will be having closing to 2000 symbols as of now
It might be a good idea to increase the symbol column capacity (not the index capacity). We have a simple guide how to do it (in your case you would be converting SYMBOL to SYMBOL type, but with a different capacity):https://questdb.io/docs/troubleshooting/faq/#how-do-i-convert-a-string-column-to-a-symbol-or-vice-versa