tuning redo apply on zfs

We are running large databases on zfs filesystems. The performance is not yet optimal but sufficient to be able to work. Tuning is an ongoing process. Recently we applied the latest zfs patches and introduced some new tunables on the standby site.

Before these changes the redo apply rate was somewhere around 15MB/s. Not a speed monster but enough for our goal.

After these changes on the operating system the apply rate was 2MB/s. Not the speedboost where we were hoping for, we hoped on a similar factor in the other direction.

This meant digging and find a way to improve the situation, with only 2MB/s we could hardly keep up with production. Simple testing with dd showed that we could easily get files read with 150MB/s. During the monitoring of the redo apply we saw that to read a single archive, the database needed a few minutes. After the archive was read, it took only 10 to 20 seconds to flush the data to disk. A bit strange.

According to metalink note 387343.1

ALTER SYSTEM DUMP LOGFILE ‘’ VALIDATE;

should show the upperbound of the redo read rate. Using the tracefile we found that the file was read with a rate of 140MB/s. Not bad at all and much better than what we could see happening in the databases recovery process. The conclusion is that the database does not use the same code to read the archive during the recovery as it does when dumping the logfile.

One of the ways to control this is the filesystemio_options parameter that we had set to directIO. We changed this

alter system set filesystemio_options=setall;

The result was surprising, the redo read rate went up to 140MB/s, better than we had ever seen before. A quick test with the dump logfile showed that now the dump rate was with 40MB/s. This again shows that the metalink note is not exactly telling the truth. 40MB/s is not the upperbound of the read read rate...

equipment used:

• 32 core Sun M5000.


• Oracle version 10.2.0.4


• Solaris version 10.6 (februari 2009)


• redo file size 400MB.


• db_cache_size=4G


• dbsize=2,5T

Conclusion:
we can use zfs for large databases but need to be aware of strong reactions when tuning zfs on the OS. ZFS is getting better every patch release.