On Thu, Aug 4, 2011 at 10:35 PM, Leyne, Sean <Sean@...>wrote:

>
>
>
> - Firebird databases generate at least (*1) 1 write operation per database
> transaction (even SELECT statements) since the current transaction number in
> the database header page must be written to disk. So, depending on the
> amount of transactions activity of the database, you can have a problem.
>

The default state of a transaction on a transaction inventory page (TIP) is
"Active", so a read-only transaction generates two writes, one on start-up
when it updates the next transaction on the header page and one to change
the state on the TIP to committed. The next transaction and next connection
ids on the header page are going to grind holes in SSD cells unless they're
intelligently distributed.

>
> - Also, the Firebird garbage collection process will also generate write IO
> operations, so you such think of each row UPDATE/DELETE operation will
> generate at least 2 disk IO write operations. 1 in the current transaction
> to write the latest version of the database row (*2). 1 later after the
> update transaction has been committed and the old record version is cleaned
> up from the database page (*3). This also needs to be factored into any
> math were you try to figure out the amount of write IO is taking place to an
> SSD.
>

Exactly right. Design parameters matter.

> (*1) I am not sure if there is another update to a TIP database page
>

The header page takes the most abuse.

>
> (*2) separate updates to index pages will also be required for each index
> which needs to be maintained
>

Firebird is relatively mild in index updates. A record id is stable until
the record is deleted, even if the values of key fields change. The index
entry changes only if the value for the index changes - so you can change
non-indexed values without changing any indexes, or you can change a value
in one index without changing other indexes as long as their key values are
unaffected. (See PostgreSQL for a counter example).

>
> (*3) Some of the old record versions can be cleaned in a single disk IO,
> but that depends on the engine version and the garbage collection
> mode/settings.
>

. Right. If all versions of a record are on a singe "page" ... sadly I
doubt that disk blocks transfer well as an idiom to SSDs ... still you've
got a very hot set of bits in the header page.

Good luck,

Ann


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