fabianch@... wrote:

> I've noticed the following strange behavior on Firebird 3 latest
> release, 64 bits, on Windows 2012, 24 processor server, 200 GB ram, magnetic HDDs (no SSDs):

> 1) After executing and commiting a massive delete on a table, any
> select or insert takes time to execute, as if FB was not reclaiming
> the deleted "space" on the table, and had to read each deleted
> record to filter and find the response.

Well, that's not quite the process but it's certainly the result.
Nothing happens, of course, until the transaction commits and the
engine finishes writing the delete stubs for those records.
"Reclaiming the deleted space" doesn't happen at all until a garbage
collection process has been through and flagged them all. The timing
of that depends on how GCPolicy is configured: background or
cooperative. For background, which applies only to Servermode Super, a
GC thread starts up periodically to do that flagging. For cooperative,
the first transaction that hits that table after the bulk delete
transaction commits gets the load.

If the Servermode is Super then by default GCPolicy is combined, i.e.,
the engine will try both ways - useful with short transactions, not so
much with bulk updates and deletes. For Classic and SC modes, only
cooperative is possible, i.e., background and combined are ignored.

The quickest way to clear the decks after a bulk delete is to do a
SELECT COUNT(*) FROM <table name>, which will touch every record in
the table and flag the delete stubs for GC. If the bulk delete is
deleting ALL of the rows, then dropping and recreating the table is
faster.

> 2) While a big transaction is occurring (inserting into a table the
> results of a big select via an SP), another DB on the same server
> becomes unresponsive for about a minute. I thought each database had
> it's own "deamon / process / thread" assigned and the locks were
> exclusive to each DB, however it appears there is some interaction
> between the two DB performance other than just the shared OS/HDDs/Memory/Processors.

Again, the locking behaviour depends on the server mode. Super is one
single engine process with connections running in threads of the main
process. Lock files are not shared across databases, though. Classic
is your one-process-per-connection mode, with each connection having
its own lock file. SC is a listener process that starts a thread for
each connection, each having its own lock file.

The reason for the slowdown in the second database probably has to do
with the availability of resources on the machine while your bulk
delete in the other database is either still under way or is being
cleaned up by a GC process.

> And a question for the experts would it be possible to install
> multiple instances of FB 3 on the same OS instance, to ensure a
> better level of independence between the DBs response time? This was
> possible on FB2.5

The mechanism is the same in both versions. Only Superserver (Fb3
Servermode Super) has shared resources. If you were running two
instances of SS in 2.5 and you observed some benefit from doing so,
there's no reason, architecturally, for SS on 3.0 to behave
differently. Still talking about SS, if you have plenty of RAM, do
you have pages caches in both databases that are big enough to keep
things flowing? Or too big to be accommodated in RAM while you have
something big going on in one or both databases?

> however I believe on FB 3 it did not make sense
> because FB3 assigns a processor per "request / connection",
> perhaps I got that wrong?

Both v.2.5 and v.3.0 will assign a connection to an available CPU,
if CpuAffinityMask (in SS) is set to zero. SC and Classic don't care
about CPUAffinityMask.
Neither version assigns CPU at request level (i.e., requests within a
connection).

There's probably more to be said - that's just a few things I can
think of now.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com