Allow to TEMPCACHELIMIT size to be set as connection parameter, to override value specified in firebird.conf [CORE3787]

[firebird-automations]

Submitted by: @pavel-zotov

Relate to CORE4328

We have above 500 connections and only 1/3 of them are those in which real work is doing by users (yet 1/3 are for service purposes like messaging between users and 1/3 - to non-production auxiliary database with some reference data). Appox. 166 connects in which users doing they job often calls huge sorts and this operations comsume server memory. Unfortunatelly, every connect should consume such amount of server memory that is defined by firebird.conf TempCacheLimit sessing (8 Mb by default for CS & SC).

It will be very good if a connect can specify TempCacheLimit for his own session time and this value will have higher priority than TempCacheLimit in firebird.conf.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Modified the subject for readability

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

summary: Allow to set 'my own' TEMPCACHELIMIT size when connecting to FB rather than use one that is specified in firebird\.conf =\> Allow to TEMPCACHELIMIT size to be set as connection parameter, to override value specified in firebird\.conf

[firebird-automations]

Commented by: @dyemanov

The temp space cache is not preallocated, so CS connections that don't need much temp space (for sorting or whatever) will never consume the configured amount of memory. So I'm not sure I understand what is the problem.

That said, I don't object to the idea in general, it just has to be justified properly.

[firebird-automations]

Commented by: @pavel-zotov

Currently any connection that sorts large data will swap on disk in case of exceeding of TempCacheLimit. Even if there is some memory on server - tempspace per connect will no dynamically added from this 'common pool'. My purpose is to minimize swap otherwise overall performance is immediatelly falls down. Unfortunatelly we can`t explore how much space of TempCache was equipped by some statement and was it really swap or no.

What if I set this parameter in my firebird.conf to very large number, say 256 Mb (note: we've moved to SuperClassic last week) ? Can any connection that calls statement with sorting free all the memory from tempspace that was needed for this to server after sorting will done ?

[firebird-automations]

Commented by: @dyemanov

All memory used by the sorting and other duties backed by the temp space cache is released to the system as soon as that sorting etc is completed. TempCacheLimit just defines the upper limit of memory that can be used without going to the disk. This "cache" is not kept "expanded" forever.

Also, SuperClassic defines this limit for all connections together, like SuperServer.

[firebird-automations]

Commented by: @AlexPeshkoff

Letting connect specify TempCacheLimit for his own session is dangerous feature - it opens a door for DoS attacks consuming too much RAM by attacking connection.

[firebird-automations]

Commented by: @dyemanov

Do you have SS in mind? Because in CS the same issue exists for the page cache as well. Also, the attacking connection will have to do anything (e.g. sorting) in order to consume the memory. If the attacking user has no SQL rights, then it becomes a hard task. Otherwise, he may have a wider choice of DoS abilities, e.g. overfill the disk with infinite inserts.

[firebird-automations]

Commented by: @AlexPeshkoff

I know about all this possibilities of attack, just do not want to add one more. SQL rights are not a problem for an attacker, that's not big problem here:

select * from rdb$relations full join rdb$relations on 0 = 0 full join rdb$relations on 0 = 0 full join rdb$relations on 0 = 0 ................ order by 1,2,3,4,...................

with large sort area can easily put server on knees.

What about page cache - missing grants to access all user tables and (in trunk) missing rights to create new one are a good showstopper for mentioned attacks. BTW, it seems to be a good idea to have in firebird.conf the following form of it:

DefaultDbCachePages = Min, Default, Max

instead current

DefaultDbCachePages = Default

[firebird-automations]

Commented by: @AlexPeshkoff

PS. And I also see no sense in suggested parameter. Inactive attachments do no consume temp memory.

[firebird-automations]

Commented by: @pavel-zotov

(dimitr) > SuperClassic defines this limit for all connections together, like SuperServer

which value is default for this parameter in *SuperClassic*, 8 Mb or 64MB ? I can`t understand this from comments in firebird.conf.

[firebird-automations]

Commented by: @dyemanov

Default value is 8MB on Windows, zero (disabled) on Unix platforms.

[firebird-automations]

Commented by: @dyemanov

Alex, regarding the usefulness of this parameter. What about somebody deciding to set up [many] OLTP connections to a small/default cache limit (in order to to avoid excessive memory consumption in unexpected cases) and set up [a few] OLAP connections that are known to sort a lot to a bigger cache limit.

As for DoS issues, we really need to have the admin-level config settings that limit any lower level ones (like the MAX setting you proposed).

[firebird-automations]

Commented by: @pavel-zotov

(dimitr) > Default value is 8MB on Windows, zero (disabled) on Unix platforms.

"zero (disabled)" - so, how it works in details (i'm about *nix) ? no temp cache at all and swap ? or this temp cache is unlimited in size ?

[firebird-automations]

Commented by: @dyemanov

Firebird writes directly to the temporary files, their caching is left up to the OS.

[firebird-automations]

Modified by: @dyemanov

Version: 3\.0 Initial \[ 10301 \] =\>

[firebird-automations]

Modified by: @dyemanov

Link: This issue relate to [CORE4328](https://github.com/FirebirdSQL/firebird/issues?q=CORE4328+in%3Atitle) \[ [CORE4328](https://github.com/FirebirdSQL/firebird/issues?q=CORE4328+in%3Atitle) \]