> > - 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.

I wasn't sure about the whole TIP page issue, but I thought there would be at least 1 IO because of it. But without knowing for sure I wasn't going to "put my foot in my mouth" -- it seems that I have been eating a lot of leather and rubber lately in my Firebird posts. ;-]

> The next transaction and next connection
> ids on the header page are going to grind holes in SSD cells unless they're
> intelligently distributed.

All modern SSD controllers use a "wear levelling" algorithm to move write to different sections of the SSD, they don't write to the exact same block over and over. So, in that way the write as distributed, but as you say the page headers writes will "grind" at the SSD media.

> > - 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).

I agree, I was trying to point out that row update have both direct IO operations but also indirect IO operations, in this case index maintenance, that must be considered when calculating the amount of disk write activity. Often, people forget about the indirect operations.


Sean