Title
#users-public
w

WCKD

10/05/2022, 1:10 PM
Hi, guys! I think there is a memory leak in the database. If I run multiple
localhost:9000/exp
queries to export multiple files of around 10M rows each, the java.exe process ram value goes up really, really high, filling up all my RAM. Shouldn't it clear itself after every query? It even stays full long after the python script finished, so it's definitely a problem on the side of questdb.
Jaromir Hamala

Jaromir Hamala

10/05/2022, 1:13 PM
hi, what questdb version you are on?
w

WCKD

10/05/2022, 1:32 PM
QuestDB 6.5.3, the latest one, Windows version with openjdk 18.0.2.1 2022-08-18
Jaromir Hamala

Jaromir Hamala

10/05/2022, 1:53 PM
what’s the scale of this consumption? small gigabytes? high gigabytes? 100s of MB? some increase is expected, there are caches, etc. if you keep querying it does the memory consumption keep going up? or is there some kind of ceiling?
1:54 PM
also: how do you measure the consumption?
w

WCKD

10/05/2022, 1:55 PM
As can be seen in the image, after exporting 84 queries(files of 10M rows each, the java.exe process eats up 16GB of RAM
1:55 PM
With each query, the java.exe RAM usage goes higher
Jaromir Hamala

Jaromir Hamala

10/05/2022, 1:56 PM
can you run
select memory_tag, bytes / (1024 * 1024) from memory_metrics() order by bytes desc;
?
w

WCKD

10/05/2022, 1:56 PM
This is what it returns outside the querying processes:
"memory_tag","column"
"TOTAL_USED",108988
"MMAP_TABLE_READER",108774
"MMAP_TABLE_WRITER",128
"NATIVE_DEFAULT",51
"NATIVE_IMPORT",16
"NATIVE_HTTP_CONN",7
"NATIVE_TREE_CHAIN",4
"MMAP_INDEX_WRITER",2
"MMAP_DEFAULT",1
"MMAP_INDEX_READER",0
"NATIVE_OFFLOAD",0
"NATIVE_REPL",0
"NATIVE_JIT",0
"NATIVE_PATH",0
"MMAP_UPDATE",0
"NATIVE_CB5",0
"NATIVE_CB1",0
"NATIVE_CB3",0
"NATIVE_CB4",0
"NATIVE_O3",0
"NATIVE_TABLE_READER",0
"NATIVE_TABLE_WRITER",0
"MMAP_O3",0
"NATIVE_RECORD_CHAIN",0
"NATIVE_COMPACT_MAP",0
"NATIVE_FAST_MAP",0
"NATIVE_FAST_MAP_LONG_LIST",0
"NATIVE_PGW_CONN",0
"MMAP_INDEX_SLIDER",0
"MMAP_BLOCK_WRITER",0
"NATIVE_SAMPLE_BY_LONG_LIST",0
"NATIVE_LATEST_BY_LONG_LIST",0
"NATIVE_JIT_LONG_LIST",0
"NATIVE_LONG_LIST",0
"NATIVE_CB2",0
"MMAP_IMPORT",0
"NATIVE_ROSTI",0
"MMAP_TABLE_WAL_READER",0
"MMAP_TABLE_WAL_WRITER",0
"MMAP_SEQUENCER",0
"MMAP_PARALLEL_IMPORT",0
"NATIVE_PARALLEL_IMPORT",0
"NATIVE_JOIN_MAP",0
Jaromir Hamala

Jaromir Hamala

10/05/2022, 1:58 PM
it it now going down even eventually? if you are no longer querying the table then eventually the readers should be released and memory reclaimed. there are some timeouts tho.
w

WCKD

10/05/2022, 1:59 PM
Yes, they are released after a while, probably after 5-10 minutes or so
2:00 PM
Actually, after 5 minutes they are released from the memory
Jaromir Hamala

Jaromir Hamala

10/05/2022, 2:01 PM
you can configure
cairo.inactive.reader.ttl
that’s reader TTL in milliseconds. I believe it’s 2 minutes by default
w

WCKD

10/05/2022, 2:02 PM
To me it's set to
#cairo.inactive.reader.ttl=-10000
at the moment
2:03 PM
It will try to set it to 1000, which should be 1s, restart the service and report back
Jaromir Hamala

Jaromir Hamala

10/05/2022, 2:04 PM
the
#
means it’s actually commented out. to overwrite you need something like this:
cairo.inactive.reader.ttl=10000
make sure the line does not start with
#
there is also
cairo.idle.check.interval
which configures how often are idle readers checked for. this property is indeed 5 minutes by default. Try to lower it to something like 30s or so. bear in mind that this can reduce performance.
w

WCKD

10/05/2022, 2:08 PM
Yup, I know it was commented out, I uncommented it and set it to
cairo.inactive.reader.ttl=1000
. However, it doesn't seem to work, it still eats up the RAM like crazy. It's like the entire queries are cached into RAM after they've been executed.
Jaromir Hamala

Jaromir Hamala

10/05/2022, 2:09 PM
did you also change
cairo.idle.check.interval
?
w

WCKD

10/05/2022, 2:09 PM
Not yet, I will in 1 minute
2:13 PM
I have set
cairo.idle.check.interval=30000
and unfortunately java.exe still eats the RAM query by query, even after 30s
2:14 PM
However, it gets cleared out faster after the query processes end, as I set it with this
cairo.inactive.reader.ttl=1000
2:27 PM
Yup, also tried setting
cairo.idle.check.interval=3000
and nothing changed, the RAM still builds up fast between queries and empties really fast only after the queries have been completed. 😶
2:31 PM
What I have also tried with no luck was disabling the http query cache
http.query.cache.enabled=false
Jaromir Hamala

Jaromir Hamala

10/05/2022, 2:41 PM
empties really fast only after the queries have been completed. 😶
This is actually expected. The properties have impact on idle readers. They won't change anything while queries are in progress
w

WCKD

10/05/2022, 2:45 PM
But the issue I have is actually that the queries seem to build up in the ram, one by one. What If I have to run 1000 queries? Then the RAM would get filled up in the first minute. Isn't there a way to clear the RAM of the previous query when the next one arises?
2:52 PM
Or maybe I didn't make myself clear: I have 80 queries like this :
requests.get(localhost:9000/exp?query="...")
and I run them one by one in a for loop:
requests.get(localhost:9000/exp?query="1")
requests.get(localhost:9000/exp?query="2")
requests.get(localhost:9000/exp?query="3")
requests.get(localhost:9000/exp?query="4")
requests.get(localhost:9000/exp?query="5")
requests.get(localhost:9000/exp?query="6")
...
requests.get(localhost:9000/exp?query="79")
requests.get(localhost:9000/exp?query="80")
Well, only after the last request, the RAM clears up, but I would like it to be cleared after each request, as this should be the default behaviour
2:58 PM
Oh, I got it, I set the
cairo.idle.check.interval
to the lowest possible
cairo.idle.check.interval = 1
, and also the
cairo.inactive.reader.ttl=1
and how it seems to clear RAM between queries, I guess it's a matter of fine-tweaking these parameters.
Jaromir Hamala

Jaromir Hamala

10/05/2022, 3:16 PM
even without fine tuning this it should not grow beyond limits. it should eventually stop growing.
3:17 PM
1ms is super aggressive, this will certainly have impact on query performance
w

WCKD

10/05/2022, 3:53 PM
Well, unless I set both to 100ms, the RAM would still climb really fast...
3:53 PM
I thought setting it to 100ms would be more reactive to clear memory in between running the requests