dsaunders1971 wrote:
I have a question about why Firebird writes / or changes so much data in the database file when you run a simple select query. What data is it changing in the database? Can this behaviour be modified /turned off via configuration? When I run the select statement in IB Expert it tells me nothing has been written to disk. When I look at the firebird process I can see it writing data for every select query I run.
Unfortunately our application runs a select query every 3 seconds to update a reservation chart.
On our production server when I look at how many bytes of data has been written over long period it has written gigabytes of data even though the databases are only megabytes in size.
Ann W. Harrison answers:
Firebird doesn't write anything to disk for a Select, unless cooperative garbage collection is on and there are old record versions to remove.
I suspect from your other message that you are creating a new transaction for each select. Every time you start a transaction, Firebird changes the "next transaction" value on the database header page. That change must be flushed to disk before any changes made by the transaction and before the transaction commits. Every time you commit or rollback a transaction, the final state of the transaction must be recorded on a transaction inventory page and that change must be written to disk. There's a slight possibility that recording the transaction's final state will require allocating a new transaction inventory page, which causes a few other writes.
The way to avoid all this, assuming that your query is read only and the only statement in the transaction, is to set the transaction state and isolation to READ_ONLY and READ_COMMITTED. In that mode, the transaction does not get a unique transaction identifier so it avoids writing the header and transaction inventory pages.