Alexey Kovyazin, IBSurgeon, [email protected], 08-Jan-2019
More details about our tests with Windows power-plans can be found here.
cat /proc//limitsand pay attention to the line with the max number of open files.
Max open files 4096 4096 filesit means that the total number of connections served by the Firebird process will be limited to something around 1000.
Recommended versions of Linux: CentOS 7.x and Ubuntu 16, 18.
Unfortunately, the poor tool Windows Task Manager shows memory, which is used for file cache as «free», and some administrators try to make Firebird consume all this free memory, so they set DefaultDBCachePage parameter in firebird.conf to very high values, and it usually leads to swapping.
Always use RAMMap tool to see the actual memory usage on Windows.
The empirical rule for Windows Server (dedicated for use as Firebird server) is the following: Firebird memory (Working Set) should be less than 40% of total RAM. If the total size of working sets for all processes is more than 50%, swap can be started by Windows.
Please note: "reserve" here means not only "do not set too many page buffers in Firebird" but, also important, restrict memory usage of other software. For example, if you have MS Exchange or MSSQL on the same server with Firebird, make sure to restrict their memory appetites.
If you are interested to know details about, I've recorded the webinar devoted to memory management in Firebird:
gstat –h databaseHow to disable:
gfix -use reserve databaseAfter this command, the new data pages will be created with reserved space.
(select count(*)…. where condition11) >0Better use this construction instead
Exists(select first 1 id where condition1)If condition1 returns more than 1 record, the proposed option will be much faster, because it does not read all records, it stops after the first fetched record.
create or alter procedure NEW_PROCEDURE returns ( SUMX double precision) as declare variable _amount double precision; begin for select T1.amount from Table1 t1 where .... order by id into :_amount do begin sumx=sumx+_amount end; suspend; endCheck your PSQL code for similar situations and remove useless ORDER BY (as well as distinct and UNION).