Karol,

>

i add some more info

>

And it is interesting information.

> 1. I use Super Server 64 bit
>

Good.

> 2. DB Page Size is 16k and DefaultDBPageCache is 65536
>

Both good, I think. Very odd. I wonder if the size of the cache is
causing problems with the file system cache...

> 3. database was backed up and restored
>

What did you use for gbak restore options?

and then i do few changes to some stored procedures and some analitics

> (selects)
> and after this i delete 25% of data from db (only deletes no inserts or
> updates)
>

OK. As you know, Firebird implements a delete as an update, inserting a
new primary record (very small) indicating that the record was deleted. So
every delete temporarily increases the size of the database. When the
transaction that executed the delete commits and every transaction that
could see the original state of the database has ended, the deleted stub
and all other versions of the record are garbage collected.

What this means is that if you have a full page - particularly a full page
with small records on it - adding a deleted stub for 25% of the records can
fill the page, causing an old record to be moved off-page. That frees up
some space, but possibly not enough, so another old record will be moved.

Normally Firebird leaves space for extra versions, but that can be
suppressed at database creation or restore. It's been a long time, but my
recollection is that when space is reserved for new versions, Firebird
leaves room for one extra record header for each record on the page. With
large pages and small records, that's a lot. I wonder if, now that
Firebird is using large pages, it might make sense to have more options on
reserved space...

> 4. i disconnect from database rename it to see if it is not used and i
> restart fbserver
>
> 5. I run sweep process
>

So contention is not an issue.

>
> as you can see there are non updates to the same record and sweep was
> only attachment to database
> after 7 hour i connect to database to see what is going on in MON$ tables
> i see many writes and many reads and also the same in windows task manager
>

Right. How about indexes? Sweep will be simultaneously removing index
entries. Could that be part of the problem?

>
>
> i suppose that sweep do write for any record version removed from page not
> after all unnecessary record versions from whole page
> if e.g. i have 20 old record versions in one page then this page is saved
> to disc 20 times...
>

That should not be the case. Once again, with the exception of knowing
how to update information on the database header page, sweep is just
another application layered on the Firebird API. Pages are written

1) when a transaction commits - which sweep does not,

2) when a change would cause a cycle in which information on one page in
memory references information on another page also dirty and in memory and
information on the second page reference information on the first - could
be more pages, but anytime a cycle is about to be created, one page gets
forced to disk. But sweep should never cause cycles.

3) when the cache is full of dirty pages. Even then, the pages written are
the least recently referenced.

>
>

I don't know why you're seeing a 200:1 ratio of writes to database pages.

Best regards

Ann


[Non-text portions of this message have been removed]