Excessive memory usage?

[em...@tot-consult.com]

I am filling a small Firebird database with mostly filesystem data with a remote php script. The final size of the database is about 16 MB, but while I am filling it, Firebird's memory consumption quickly increases and finally maxes the 4GB virtual machine it is working on.

The database is nothing special, 4 tables, no more than 90 thousand records per table, several varchar(128) and varchar(256) fields.

Statements are mostly 'insert into' with  verbatim values (no prepared / parametrized statements). Single remote connection, multiple transactions.

Server is 5.0.3, running on a Rocky 10 VM, firebird.conf is empty but for the 'server mode'. Tried super and superclassic, does not make real difference. When I look into MON$statements and MON$compiled_statements I see tens of thousands records, likely all statements coming from the remote connection.

What is happeining here? Is this expected? What is all this RAM used for - statements that have been already executed?

[Dimitry Sibiryakov]

em...@tot-consult.com wrote 24.01.2026 10:56:
> What is happeining here? Is this expected? What is all this RAM used for -
> statements that have been already executed?

You use ad-hoc queries instead of parametrised ones and perhaps use them with
prepare but don't explicitly free them, so yes, these statements are alive until
disconnect and memory consumption is expected.
The right way to execute ad-hoc queries is to use
isql_dsql_exec_immediately() so they are freed immediately after execution.
Alternatively the full set of call must be used: isc_dsql_alloc_statement() +
isc_dsql_prepare() + isq_dsql_execute() + isc_dsql_free_statement().

It looks like either bug in your script of PHP driver.

--
WBR, SD.

[em...@tot-consult.com]

Thanks a lot, that clarifies the situation.

I am using php_interbase. The equivalents there seem to be:

ibase_prepare -> ibase_execute -> ibase_free_query

and

ibase_query -> ibase_free_result.

I am using the second, but maybe free_result does not do the correct thing when there is no result set.

Anyway I should probably be taking this to the php-firebird list.

Thanks again!

[Mark Rotteveel]

Or use one allocated statement handle, instead of freeing and allocating
a new one.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 24/01/2026 11:56, em...@tot-consult.com wrote:
> Thanks a lot, that clarifies the situation.
> I am using php_interbase. The equivalents there seem to be:
>
> ibase_prepare -> ibase_execute -> ibase_free_query
> and
> ibase_query -> ibase_free_result.
>
> I am using the second, but maybe free_result does not do the correct
> thing when there is no result set.

Free result probably only releases the cursor, not the statement (but I
don't program PHP nor use the firebird-php/ibase driver).

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 24.01.2026 12:10:
> Or use one allocated statement handle, instead of freeing and allocating a new one.

I'm not sure if "unprepare" is supported in PHP.

--
WBR, SD.

[Mark Rotteveel]

You don't need to explicitly unprepare before preparing a new statement
on a handle.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 24.01.2026 12:15:
> You don't need to explicitly unprepare before preparing a new statement on a
> handle.

IIRC in this case you'll get either error or handle leak.

--
WBR, SD.

[Mark Rotteveel]

No, you use the same handle, so the handle doesn't leak. And unprepare
was introduced very late (Firebird 2.5), and it has always been possible
to prepare on an already prepared handle. Simply preparing *if* there is
no open cursor is enough (and I'm not even 100% sure about that
limitation, it might just be a intentional limitation in Jaybird).

Jaybird does this all the time in its `java.sql.Statement`
implementation, and has done so for the past 25 years.

As far as I'm aware the unprepare option is only there to be able to
easily release metadata locks.

Mark
--
Mark Rotteveel

[em...@tot-consult.com]

Just to finalize this thread: I took the time to rewrite the script, preparing queries once and then executing them thousands times with parameters. It now runs much faster and without visible effect on the server's memory usage.

While you can get away with immediate queries in a web script that performs a couple of quesries and exits, for this type of "data pump" application you have to do it the right way. Lesson learned.