What are forced-writes?
Let’s think about what happens when we issue an insert, update or delete statement. For example:
insert into customers(id, name) values(1, "John")
Forced-Writes ON
Firebird’s default behavior is to write this new data immediately on disk. That is Forced-Writes. Firebird stores everything on disk as soon as possible.
With Forced-Writes ON each change goes straight
to persistent storage.
(Are you familiar with the term "pages"? That is the term I’ll use from now on.)
Forced-Writes OFF
When Forced-Writes is off Firebird does not write everything to disk immediately. Instead, it leaves such pages in memory for a while. During this period Firebird accumulates other pages that must also be written to disk.
For example if you issue another statement right away:
insert into customers(id, name) values(2, "Mary”)
These new pages will also go to memory along with the pages generated by the first statement. When this memory is full or a certain timeout is reached all pages will be written to disk at once.
This makes writing more efficient and much faster.
With Forced-Writes OFF each change is accumulated
in memory and written to disk all at once after some delay.
Real danger
Despite being more efficient, turning Forced-Writes off is extremely dangerous. Those pages sitting in memory are already officially part of your database. If Firebird is terminated unexpectedly, the database file will be left in an inconsistent state known as corruption. It is also likely that you will loose data in the restoration process.
This danger is exactly what must keep you away from turning Forced-Writes off. Some examples of servers terminating unexpectedly:
- Energy loss. Even if you have an UPS, the UPS may have problems. Or the energy loss may last long enough for the UPS battery to run out.
- Hardware problems
- Operating system problems
- Firebird problems
Simple example of corruption: Indices pointing to non-existent data pages.
Excess I/O
Turning Forced-Writes on undoubtedly increases the I/O load on your server. That is the cost of keeping your data safe. Turning it off indefinitely only to decrease this load is a choice from which you may regret sooner than you imagine.
To keep the safety of Forced-Writes and minimize the effects of the increased I/O load, consider the following options:
- Improve your server’s disk subsystem. It does not matter what is your current model there is always another with greater I/O capacity. Check with you hardware vendor.
- Decrease the amount of I/O originated from reads. This can be made, for example, by adjusting your page-cache parameters. The total I/O load will decrease and your server will respond better.
- Reevaluate the server architecture you are using. For example ClassicServer naturally consumes much more I/O than SuperServer. more.
Exceptions
There are certain scenarios where turning Forced-Writes off can be very useful.
For example during a large data load. It is much faster to turn Forced-Writes off while executing the load and then turning it back on to bring the database to a production state. You only have to keep in mind that the dangers still exist during the data load period.
Conclusion
Turning Forced-Writes off can be useful in some isolated cases. For the day-to-day of a production database the ideal is to turn it on and find alternatives to decrease the server’s I/O loads.
Author
My name is Douglas Tosi. I’m the person ahead of Sinática, a company created to make your software more efficient.
Our first product, Sinática Monitor for Firebird, helps you optimize Firebird SQL databases.