Title
w
WCKD
12/21/2022, 9:36 AMHello, I just upgraded from 6.5.5 to 6.6.1 latest version and the http csv query is about 60-80% slower per query(from 8s to about 14s). I changed absolutely nothing apart from the questdb version in Linux.
j
javier ramirez
12/21/2022, 9:40 AM😿. Is that happening consistently for different types of query or for something specific? It would be great If you could share some info about the dataset size/shape and some queries where you observed this (feel free to edit column/table names as needed if you are not a liberty to share details)
w
WCKD
12/21/2022, 9:42 AMIt's happening for the exact same query I was using for about 5 weeks until I upgraded right now.
Query looks something like this, so nothing fancy.
def get_query_base(timeframe: Timeframe):
if timeframe is Timeframe.SECOND:
return "SELECT pair, price, cast(ts/1000 as long) FROM 'klines_1s' WHERE ts >= '{}' AND ts < '{}'"
elif timeframe is Timeframe.MINUTE:
return "SELECT pair, open, high, low, close, volume, cast(ts/1000 as long) FROM 'klines_1m' WHERE ts >= '{}' AND ts < '{}'"Databases look something like this:
DB_TABLE = "klines_1s" if INTERVAL_TYPE is IntervalType.SECOND else "klines_1m"
PARTITION = "DAY" if INTERVAL_TYPE is IntervalType.SECOND else "MONTH"
SYMBOL_CAPACITY = 512
CONNECTION = "<postgresql://admin:quest@127.0.0.1:8812/qdb>"
CREATE_TABLE_QUERY = (
f"""CREATE TABLE IF NOT EXISTS {DB_TABLE} (
pair SYMBOL CAPACITY {SYMBOL_CAPACITY} CACHE,
price DOUBLE,
ts TIMESTAMP)
timestamp(ts)
PARTITION BY {PARTITION}
;"""
if INTERVAL_TYPE is IntervalType.SECOND
else f"""CREATE TABLE IF NOT EXISTS {DB_TABLE} (
pair SYMBOL CAPACITY {SYMBOL_CAPACITY} CACHE,
open DOUBLE,
high DOUBLE,
low DOUBLE,
close DOUBLE,
volume DOUBLE,
ts TIMESTAMP)
timestamp(ts)
PARTITION BY {PARTITION}
;"""
)The drop in performance is really visible in every shape of the query, no matter if it's about 3600 rows (1h of seconds) or more.
j
javier ramirez
12/21/2022, 9:46 AMAnd both for the daily and monthly partitioned tables, right?
w
WCKD
12/21/2022, 9:46 AMLet me double check that
Yup, for both partitions
Also, the server.conf file is stock on both versions
j
javier ramirez
12/21/2022, 9:48 AMThanks. Will comment with the core team to see if they can figure out what’s going on
w
WCKD
12/21/2022, 9:49 AMI think it might have to deal with the new added csv separator feature?
j
javier ramirez
12/21/2022, 9:53 AMSo this happens when using the
'exp''exec'w
WCKD
12/21/2022, 9:54 AMYup, this endpoint:
host = "<http://127.0.0.1:9000/exp>"j
javier ramirez
12/21/2022, 9:55 AMThanks!
w
WCKD
12/21/2022, 9:56 AMThank you too! Looking forward to helping you with whatever information you might need further on
a
Alex Pelagenko
12/21/2022, 10:26 AMIf you revert the QuestDB version, does timing go down? Is the timing same when you run select without exporting?
w
WCKD
12/21/2022, 10:27 AMYep, if I revert the QuestDB version back to 6.5.5 query times go down back to normal.
Frankly I haven't tried without exporting, as I need the exported csv back into python after the query executes.
j
javier ramirez
12/21/2022, 10:28 AMI created this github issue https://github.com/questdb/questdb.io/issues/1241. https://github.com/questdb/questdb/issues/2881
w
WCKD
12/21/2022, 10:29 AMThank you, @javier ramirez
a
Alex Pelagenko
12/21/2022, 10:29 AMwould be good to know the times without exporting
w
WCKD
12/21/2022, 10:29 AMHow can I test the times without exporting?
a
Alex Pelagenko
12/21/2022, 10:29 AMrun select in web console
it will print timing in status bar
w
WCKD
12/21/2022, 10:30 AMOh, ok, right away, I'll post the results here immediately
6.5.5
6.6.1
It seems noticeably slower in 6.6.1
a
Alex Pelagenko
12/21/2022, 10:36 AMthese are both 6.6.1 as per version in the corner
w
WCKD
12/21/2022, 10:37 AMThey are not, check again
One is 6.5.5 with an update to 6.6.1, one is 6.6.1
j
javier ramirez
12/21/2022, 11:01 AMI re-created the issue as I had mistakenly created it on the website repo, not the core one. Sorry. The link is this one https://github.com/questdb/questdb/issues/2881
a
Andrey Pechkurov
12/21/2022, 12:25 PMIt seems noticeably slower in 6.6.1Fluctuations within 10ms can be explained with some specifics of our threading model
w
WCKD
12/21/2022, 12:26 PMThen the issue might be related to the export
b
Bolek Ziobrowski
12/21/2022, 12:36 PMConsole only shows 1k rows so it doesn't reproduce the issue that affects export of 26 mil rows . It's probably something related to cursor iteration or serialization .
w
WCKD
12/21/2022, 12:38 PMYup, that's what I wanted to say in the first place, I know the console executes the query with LIMIT 1000
a
Andrey Pechkurov
12/21/2022, 4:59 PMThis fix might have affected export performance. It was shipped in 6.6.0. I'll check if that's the reason
https://github.com/questdb/questdb/pull/2748
w
WCKD
12/21/2022, 7:06 PMThank you, @Andrey Pechkurov. Looking forward to finding out the culprit!
a
Andrey Pechkurov
12/22/2022, 8:13 AMHi,
I've checked your queries and they're not affected by PR 2748 (they don't use parallel filter mechanism), so it must be something else.
w
WCKD
12/22/2022, 9:30 AMPR 2748 specifies something related to the limit query parameter, I am not using limit parameter in any of my queries.
a
Andrey Pechkurov
12/22/2022, 9:54 AMYes, but what's even more important, it changes behavior only for queries with non-ts filters, e.g.
select * from t where int_col != 42w
WCKD
12/22/2022, 9:55 AMOh, I understood, so it must be something else then. I suggested some PR related to added a different csv separator, maybe something's up with that
a
Andrey Pechkurov
12/22/2022, 10:20 AMI checked that one. Doesn't look like a critical change that might affect performance
w
WCKD
12/22/2022, 10:21 AMI'll stick with 6.5.5 until further updates anyways as it is significantly faster for my scenario, thank you for your extensive help!