Articles How to make Firebird consume a lot of memory? by IBSurgeon Team

[emailx45]

How to make Firebird consume a lot of memory?

Спойлер: Content

[SHOWTOGROUPS=4,20]
Sometimes we got cases when the Firebird process consumes a lot of RAM.
Here are 3 main reasons why it happens. Please note - these are NOT recommendations Please don't run them on the production!

1. Set page buffers too high

For example, for SuperServer in firebird.conf

DefaultDbCachePages = 500000M      # pages

Restart Firebird and try to connect – the result will be error message "Unable to allocate memory from the operating system":

isql -user SYSDBA -pass masterkey  localhost:e:\temp\blob30.fdb
Statement failed, SQLSTATE = HY001
unable to allocate memory from operating system
-IProvider::attachDatabase failed when loading mapping cache
Use CONNECT or CREATE DATABASE to specify a database
SQL>

The same result will be in case of many connections with SuperClassic or Classic with a relatively high number of page buffers, according to the formula:
Page Buffers X Number of Connections X Page Size.

2. Firebird process size can grow due to the massive operations with temporary BLOBs
Try to run the following script (not on the production):

Спойлер: Code

create generator g1;
set generator g1 to 0;

execute block
as
 declare variable blb BLOB;
 declare variable icnt integer;
begin
 icnt=0;
while (icnt <100000000) DO
begin
select cast('1234567890qwertyuiopasdfghjklzxcvbnm' as BLOB)  from rdb$database into :blb;
select gen_id(g1,1) from rdb$database into :icnt;
end
end;
  Or, another variation of the idea
create or alter procedure BLOB_LOOP returns ( ATEXT blob sub_type text )
as begin
while (true) do begin
atext = '123';
suspend;
end
end

And the following query will eat all the memory (don't run it on the production!)

select count(*) from blob_loop

3. For Classic and SuperClassic it is possible to specify the big number of page buffers in the connection string of isql.exe, and it will be allocated for this connection

If Page Buffers is not set explicitly in the header page (i.e., equal to 0)

Спойлер: Code

C:\FB\30Cs>echo set list on; set stat on; select mon$page_buffers from mon$database; | isql -c 98765 /:e30 -user foo -pas bar
Database: /:e30, User: FOO
SQL>
MON$PAGE_BUFFERS                98765

Current memory = 869314176
Delta memory = 75760
Max memory = 869334512
Elapsed time= 0.001 sec
Buffers = 98765
Reads = 3
Writes = 0
Fetches = 42
SQL>

======
set bail on;
set term ^;
execute block as
    declare n int = 10;
    declare c int;
    declare my_password varchar(20) = 'bar';
begin
    while (n>0) do
    begin
        execute statement 'select 1 from rdb$database'
            on external 'localhost:' || rdb$get_context('SYSTEM', 'DB_NAME')
            as user current_user password my_password
                role lpad('', 20, replace(uuid_to_char(gen_uuid()),'-',''))
        into c;
        n = n - 1;
  end
end
^
set term ;^

set list on;

select count(distinct(t.mon$attachment_id)) as num_of_attachments, sum(t.mon$memory_used) as sum_memo_used, sum(t.mon$memory_allocated) as sum_memo_allocated
from (
    select
        a.mon$attachment_id
      ,m.*
    from mon$attachments a
    join mon$memory_usage m on a.mon$stat_id = m.mon$stat_id
    where mon$user = current_user
) t;

[/SHOWTOGROUPS]