Title
#users-public
d

Dario Heinisch

09/13/2022, 9:03 PM
I am running in some issues with the following table:
create table orderbooks (
  ticker SYMBOL capacity 20000 cache index,
  side SYMBOL CAPACITY 2,
  shares int,
  price int,
  tape_time TIMESTAMP
) timestamp(tape_time) PARTITION by day;
I am executing the following query:
(SELECT price, side, ticker, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0  and tape_time in '2022-09-13') LATEST on tape_time PARTITION BY price;
The table has 218,157,083 rows of data and currently only one parition the '2022-09-13' The query runs on a cold run up to 5seconds, on a hot run it takes 80MS to 120MS. What kind of settings should I look into tweaking the execution time of the query? I am currently testing everything on my macbook pro.
9:04 PM
2022-09-13T21:00:12.993261Z I i.q.c.h.p.JsonQueryProcessorState [7] exec [q='(SELECT price, side, ticker, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0  and tape_time in '2022-09-13') LATEST on tape_time PARTITION BY price']
2022-09-13T21:00:12.993312Z I i.q.c.h.p.QueryCache hit [thread=questdb-worker-8, sql=(SELECT price, side, ticker, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0  and tape_time in '2022-09-13') LATEST on tape_time PARTITION BY price]
2022-09-13T21:00:12.993334Z I i.q.c.h.p.JsonQueryProcessorState [7] execute-cached [skip: 0, stop: 1000]
2022-09-13T21:00:17.786772Z I i.q.c.h.p.JsonQueryProcessorState [7] timings [compiler: 0, count: 5542, execute: 4793500708, q=`(SELECT price, side, ticker, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0  and tape_time in '2022-09-13') LATEST on tape_time PARTITION BY price`]
2022-09-13T21:00:17.786843Z I i.q.c.h.p.JsonQueryProcessor all sent [fd=7, lastRequestBytesSent=56512, nCompletedRequests=167, totalBytesSent=7137255]
2022-09-13T21:00:17.788413Z I i.q.c.h.p.QueryCache push [thread=questdb-worker-8, sql=(SELECT price, side, ticker, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0  and tape_time in '2022-09-13') LATEST on tape_time PARTITION BY price]
Here are the logs from the query when it is cold
9:05 PM
The 3 settings I tried so far and I am running with:
shared.worker.count=5
cairo.sql.jit.mode=on
cairo.sql.latest.by.row.count=10000
9:17 AM
2022-09-14T09:13:05.558078Z I i.q.c.h.p.JsonQueryProcessorState [7] exec [q='select count(*) from 'orderbooks'']
2022-09-14T09:13:05.558153Z I i.q.c.h.p.QueryCache hit [thread=questdb-worker-6, sql=select count(*) from 'orderbooks']
2022-09-14T09:13:05.558200Z I i.q.c.h.p.JsonQueryProcessorState [7] execute-cached [skip: 0, stop: 1000]
2022-09-14T09:13:05.564861Z I i.q.c.h.p.JsonQueryProcessorState [7] timings [compiler: 0, count: 13792, execute: 6837125, q=`select count(*) from 'orderbooks'`]
2022-09-14T09:13:05.564946Z I i.q.c.h.p.JsonQueryProcessor all sent [fd=7, lastRequestBytesSent=411, nCompletedRequests=115, totalBytesSent=5570176]
2022-09-14T09:13:05.565004Z I i.q.c.h.p.QueryCache push [thread=questdb-worker-6, sql=select count(*) from 'orderbooks']
2022-09-14T09:13:08.189116Z I i.q.c.h.p.JsonQueryProcessorState [7] exec [q='(SELECT price, side, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0 and tape_time in '2022-09-14') LATEST on tape_time PARTITION BY price']
2022-09-14T09:13:08.189210Z I i.q.c.h.p.QueryCache hit [thread=questdb-worker-5, sql=(SELECT price, side, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0 and tape_time in '2022-09-14') LATEST on tape_time PARTITION BY price]
2022-09-14T09:13:08.189222Z I i.q.c.h.p.JsonQueryProcessorState [7] execute-cached [skip: 0, stop: 1000]
2022-09-14T09:13:13.554462Z I i.q.c.h.p.JsonQueryProcessorState [7] timings [compiler: 0, count: 18375, execute: 5365355083, q=`(SELECT price, side, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0 and tape_time in '2022-09-14') LATEST on tape_time PARTITION BY price`]
2022-09-14T09:13:13.554567Z I i.q.c.h.p.JsonQueryProcessor all sent [fd=7, lastRequestBytesSent=50470, nCompletedRequests=116, totalBytesSent=5570587]
2022-09-14T09:13:13.555577Z I i.q.c.h.p.QueryCache push [thread=questdb-worker-5, sql=(SELECT price, side, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0 and tape_time in '2022-09-14') LATEST on tape_time PARTITION BY price]
Just retried it but no success, here is another set of logs:
I i.q.c.h.p.JsonQueryProcessorState [7] execute-cached [skip: 0, stop: 1000]
whatever happens after this step takes 5seconds. What could be the reason and how could I maybe tweak this?
9:51 AM
I have now parititioned the table
by hour
instead of day. I noticed something interesting, one time it did take 3 seconds and it had to reopen 3 partitions as far as the logs go. Another time it took 900MS but it had to reopen every partition, not sure as why that is, here are the logs: https://dpaste.org/DCFPx
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 9:57 AM
Hi, The time may be spent reading the data from the disk, not opening partitions as such. In one case the data could be in the page cache already, in another one OS had to read it from the disk
9:58 AM
In general, if your dataset fits into RAM almost any query should run very fast after the very first query cold run
d

Dario Heinisch

09/14/2022, 10:00 AM
I have 16GB of ram on my machine and the DB seems to have the following size:
du -sh *
 80K	conf
6.8G	db
4.3M	log
4.8M	public
So that should fit into ram, no?
10:00 AM
But it seems that is not happening otherwise my queries would be faster
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 10:02 AM
Do your queries involve a full scan?
10:03 AM
If they filter based on different time intervals, they could be reading different parts of the column files
d

Dario Heinisch

09/14/2022, 10:09 AM
What do you mean by full scan? The only query I am running is:
(SELECT price, side, ticker, shares, tape_time FROM 'orderbooks' where ticker = 'SPY' and shares > 0  and tape_time in '2022-09-14') LATEST on tape_time PARTITION BY price;
And I have an index on ticker
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 10:10 AM
This means that you only access rows with ticker = 'SPY', not all of them
10:11 AM
Also, do you have rows in 2022-09-14 or some of them belong to other dates?
d

Dario Heinisch

09/14/2022, 10:15 AM
Also, do you have rows in 2022-09-14 or some of them belong to other dates?
I don't fully understand the question sorry. I have 218million rows of data for 09-14 and all of them have timestamps for the 14th partitioned by hour and I have 8K distinct tickers if that helps.
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 10:24 AM
So, all rows belong to the same day
10:26 AM
Then what you see is more or less consistent with OS page cache warm-up. Thanks to index, the database reads only a minimally necessary amount of data and the next time it needs to access rows for the same ticker, the data is in page cache
10:28 AM
For each ticket you have around 27K rows and, if they're not stored one after each other on the disk, there would be lots of random reads on the column files
10:29 AM
In this particular case, you could try dropping the index, so that at least the whole ticker column is in the page cache after the first query run
10:30 AM
If it does change much, you can always add the index back
d

Dario Heinisch

09/14/2022, 11:06 AM
Dropping the index didn't help, another thing would be to split the table up into ticker ranges to have smaller partitions and hence faster reads but not sure about that tbh. I thouhgt the whole DB could fit into memory given that I have 16GB of ram but the DB is only 6.6GB, any idea why this is not happening?
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 11:07 AM
What did you observe when you dropped the index?
d

Dario Heinisch

09/14/2022, 11:14 AM
hot queries were actually slower. Cold initital query seemed faster tho. Do you want me to test that more? Also the amount of data per ticker is not equally distributed, the most frequent ticker has 1,071,301 rows while there are also ones with less than 1,000 rows
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 11:16 AM
Yes, would be nice if you could try to compare execution times with and without index on a number of the same ticket values
d

Dario Heinisch

09/14/2022, 11:40 AM
I only did one run with index and one run without index but here are the numbers: https://dpaste.org/uGxc7
11:42 AM
Does that help?
11:45 AM
I could also upload a CSV with all the data in the table and share that with you if you would like
11:49 AM
I think my use case is very similar to: https://questdb.io/case-study/aquis/ so clearly it is possible I am just missing something 😄
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 12:45 PM
Thanks for measuring the queries. The results don't clearly show the winner, as you have mentioned
12:46 PM
Sharing the dataset is a nice idea, if that's possible. This way I would be able to look for bottlenecks
12:47 PM
I'm OOO today, but should be able to take a look tomorrow
d

Dario Heinisch

09/14/2022, 1:23 PM
okay I am uploading the data, then will share a link with you! Thanks in advance, I appreciate it!
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 1:23 PM
Great!
d

Dario Heinisch

09/14/2022, 1:55 PM
But from the top of your head, do you have any suggestions for settings I should be trying which may help?
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 1:58 PM
Hard to tell in advance as they would heavily depend on the execution plan
d

Dario Heinisch

09/14/2022, 2:04 PM
How do I get the execution plan of the query?
2:27 PM
I enabled debug logging, seeing lots of these logs:
2022-09-14T14:20:16.516710Z D i.q.s.DirectLongList resized [old=4096, new=8192]
2022-09-14T14:20:16.516830Z D i.q.s.DirectLongList resized [old=2048, new=4096]
2022-09-14T14:20:16.518028Z D i.q.s.DirectLongList resized [old=8192, new=16384]
2022-09-14T14:20:16.520121Z D i.q.s.DirectLongList resized [old=16384, new=32768]
And this seems to be the compiler plan:
2022-09-14T14:20:16.425778Z I i.q.g.SqlCompiler plan [q=`select-choose price, side, shares, tape_time from (select [price, side, shares, tape_time] from (select-choose [tape_time, price, side, shares] price, side, shares, tape_time from (select [tape_time, price, side, shares, ticker] from orderbooks timestamp (tape_time) where ticker = 'SPY' and shares > 0 and tape_time in '2022-09-14')) _xQdbA1 latest on tape_time partition by price)`, fd=7]
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 2:28 PM
List resizes are relatively cheap since they're amortized
d

Dario Heinisch

09/14/2022, 2:30 PM
Also I just realized that are the logs when I start the server:
2022-09-14T14:19:54.792723Z A server-main platform [bit=64]
2022-09-14T14:19:54.792735Z A server-main OS/Arch: apple/apple-silicon
2022-09-14T14:19:54.792744Z A server-main available CPUs: 10
2022-09-14T14:19:54.792756Z A server-main db root: /opt/homebrew/var/questdb/db
2022-09-14T14:19:54.792767Z A server-main backup root: null
2022-09-14T14:19:54.792820Z A server-main db file system magic: 0x1a [apfs] EXPERIMENTAL
2022-09-14T14:19:54.792850Z A server-main 


			*** SYSTEM IS USING UNSUPPORTED FILE SYSTEM AND COULD BE UNSTABLE ***
It doesn't say anywhere that JIT is enabled, maybe my platform does not support it yet and maybe that is an issue?
2:53 PM
Here are the full debug,info logs of a query that took 2.3seconds: https://dpaste.org/czpd4
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 6:18 PM
Are you on ARM CPU?
6:18 PM
What about your file system?
d

Dario Heinisch

09/14/2022, 6:24 PM
yeah I am on ARM, macbook pro m1
6:25 PM
what do u mean by file system? Sorry I am not too familiar with architecture stuff
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 6:26 PM
We don't have JIT support on ARM yet. But multi-threaded Java-based filter should be pretty snappy
6:28 PM
*** SYSTEM IS USING UNSUPPORTED FILE SYSTEM AND COULD BE UNSTABLE ***
- this message is shown since we didn't recognize file system magic number. Most likely what you have is APFS
6:29 PM
If so, I don't think you'll face any issues on it and it doesn't have anything to do with the query performance
d

Dario Heinisch

09/14/2022, 6:40 PM
ahhhhh okay, thanks for the explanation!
Andrey Pechkurov

Andrey Pechkurov

09/14/2022, 6:41 PM
You're welcome!
Eugene

Eugene

09/16/2022, 9:59 AM
Hi Dario Unfortunately there is no magic config option to speed up your query. QuestDB is not optimised for the ARM platform (JIT is disabled for it). What I can suggest to you is to restructure your data a bit. Divide the ticker universe into several segments and store them in different tables. Also you can try the undocumented
touch
function:
select touch (select price, side, ticker, stock, tape_time FROM "orderbooks" where tape_time is "2022-09-14");
to preload all data from disk into the page cache.
d

Dario Heinisch

09/16/2022, 10:16 AM
Hi Eugene, Yeah I am currently trying to segment the tickers into different tables. Interesting with the touch function. I noticed the data gets removed from the page cache after a certain time when I don’t query the DB, is there a way I can keep the data in the page cache forever? What determines when something goes out of the page cache or not? (I don’t know much about underlying computer architecture, hence why I ask 🙂 )
Eugene

Eugene

09/16/2022, 10:21 AM
The page cache is out of our control. It is shared for all processes in the system. Rule of thumb - the more free RAM you have, the more likely your data will not be evicted from the cache.
d

Dario Heinisch

09/16/2022, 10:24 AM
okay thanks and what determines how fast QuestDB can read data from disk into the cache & answer the cold query? Lots of CPU? Is there some setting I can take a look at? My goal is have the queries respond in 50MS or worst case cold queries 120-200MS since I expect frequently requested queries
Eugene

Eugene

09/16/2022, 11:11 AM
We cannot ignore hardware/OS. For example, for your data and "SPY" query, I've got ~3 seconds for an M1 MBP and almost 9 seconds for an Intel based MBP for a cold run, and consistently have a 2x speedup on an Intel platform for a hot run. Thus, the results of a cold query are determined by the disk and memory (page cache) subsystems. Do you have access to the Linux/amd64 platform? This is our main platform and we usually have better results on it than on MacOS. Andrei is currently experimenting with the pretouch feature for parallel filters, and on Linux he has up to 8-10x speedup for some queries. Unfortunately, this does not apply to MacOS. PS. I have your target ~50ms for a hot run on Intel MBP, but I cannot get it on M1.
d

Dario Heinisch

09/16/2022, 11:12 AM
Target would not be MacOs, that is just what I am testing right now on since that is my dev machine but good point. I will see to spin up a server and test it there!
11:12 AM
Thx for the fast response Eugene!