Buffering Database Writes
Go Up to Database Properties
InterBase on Windows platforms implements a write-through cache by default. Every write operation to a page in cache is immediately written out to the disk I/O of the operating system, which itself might have a cache.
By contrast, a write-back cache defers flushing of the contents of a given cache page until a later time. InterBase performs multiple writes to a cache page in RAM before it writes the page out to disk. This results in better response time for the majority of write operations. Write-back cache consolidates I/O efficiently, and therefore it is much faster than write-through cache.
InterBase offers write-back cache as the default on UNIX and Linux, and as an option on Windows platforms. You can configure this at the database level using gfix -write async or by disabling forced writes for the database in IBConsole (Database Properties|General tab|Options).
The real benefit of using asynchronous writes (write-back cache) is about four times performance in the typical case. Some users have reported up to 20 times performance improvement from configuring asynchronous writes, in applications that make heavy use of write operations (INSERT, UPDATE, DELETE). The more writing an application does to the database—including write operations spawned by triggers—the more benefit the application gains.
The risk of asynchronous writes is that data in cache might be lost if the server has a power loss, or if ibserver exits abnormally for any reason. Write-through cache protects against data loss, at some performance cost. If you test your server host and client/server application thoroughly and they are not susceptible to crashes, then it is highly recommended to use asynchronous writes.
Use an uninterruptible power supply (UPS) to help protect your server against sudden power loss. A modest UPS is inexpensive relative to the cost of losing your data, and easy to install. This can allow you to gain the benefits of the asynchronous I/O mode in safety.