Pg query fails with global RSS memory limit exceeded

[superichmann]

To reproduce

1. Download QuestDB latest
2. Install on ubuntu Ubuntu 24.04 LTS with 32gb of memory.
3. Create a table with 400 rows - 100 symbols and 300 doubles.
4. Insert 14765249 rows into it.
5. Use ml.net DatabaseLoader to load data.
6. Construct a query as such:

   WITH Y AS ((tbl WHERE symbolCOL::iNt>6) WHERE  timestamp<'2024-04-16' AND timestamp>='2024-03-11' AND symbolCOL='9'),
   H AS ((tbl WHERE symbolCOL::iNt>6) WHERE symbolCOL='9'),
   X AS (Y) SELECT colX as 'colX',SomeSymbol as 'SomeSymbol',symbolCOL as 'symbolCOL'.............. MANY MANY MANY symbol and double COLUMNS (400) ...,
   SELECT * FROM X

7. train an ML Model
8. See the error on the .NET client:

   System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
   ---> Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
   ---> System.TimeoutException: Timeout during reading attempt

9. See the error on QuestDB server:

   C server-main unhandled error [job=io.questdb.network.IODispatcherLinux@235a0c16, ex=
   io.questdb.cairo.CairoException: [-1] global RSS memory limit exceeded [usage=29374357092, RSS_MEM_LIMIT=29681609850, size=1073741824, memoryTag=48]
   at io.questdb.cairo.CairoException.instance(CairoException.java:323)
   at io.questdb.cairo.CairoException.nonCritical(CairoException.java:124)
   at io.questdb.std.Unsafe.checkAllocLimit(Unsafe.java:317)
   at io.questdb.std.Unsafe.malloc(Unsafe.java:236)
   at io.questdb.cutlass.pgwire.PGConnectionContext.of(PGConnectionContext.java:534)
   at io.questdb.cutlass.pgwire.PGConnectionContext.of(PGConnectionContext.java:62)
   at io.questdb.network.IOContextFactoryImpl.newInstance(IOContextFactoryImpl.java:70)
   at io.questdb.network.AbstractIODispatcher.addPending(AbstractIODispatcher.java:225)
   at io.questdb.network.AbstractIODispatcher.accept(AbstractIODispatcher.java:344)
   at io.questdb.network.IODispatcherLinux.runSerially(IODispatcherLinux.java:406)
   at io.questdb.mp.SynchronizedJob.run(SynchronizedJob.java:40)
   at io.questdb.mp.Worker.run(Worker.java:151)
   ]

QuestDB version:

8.1.1

OS, in case of Docker specify Docker and the Host OS:

Ubuntu 24.04 LTS

File System, in case of Docker specify Host File System:

ext4

Full Name:

Super Richman

Affiliation:

None

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• [X] Yes, I have

Additional context

The .Net DatabaseLoader should successfully retrieve the data from QuestDB instead of stopping in the middle and raising this error. QuestDB should also not raise this error and successfully send the data back.

[superichmann]

Due to failed inserts into binary column on large binary values have enlarged these to:

pg.net.connection.rcvbuf=1024M
pg.net.connection.sndbuf=1024M

just for a test, I lowered them to

pg.net.connection.rcvbuf=200M
pg.net.connection.sndbuf=200M

It seems like I don't receive the error outlined in this bug report anymore, I will update here again if I encounter this error again.

Perhaps consider adding to the documentation the fact that pg.net.connection.rcvbuf or pg.net.connection.sndbuf counts towards the total memory consumption of QDB.

Perhaps consider adding to the documentation the fact that if binary columns are inserted with pg they are limited to the setting of pg.net.connection.rcvbuf and not as currently documented (BINARY field size is limited either by 64-Bit signed int (8388608 peta bytes) or disk size, whichever is smaller).