ZFS and RAID levels

As a builder of Ingres systems, we tend to use RAID 1+0 (aka RAID10) for most of our systems. RAID5 is not recommended for databases due to the read-parity-write that is required for every write operation. Furthermore, the ability of a mirrored system to read two separate data blocks in parallel can give RAID1 a significant read performance advantage.

With the arrival of ZFS in Solaris 10, I've been reading up a little to see if this accepted wisdom changes with RAIDZ. A very interesting article (http://blogs.sun.com/roch/entry/when_to_and_not_

t o) basically explains that the read potential for a RAIDZ filesystem can be significantly less than that of a RAID10. However, for non-data filesystems (such as the checkpoint backup area), RAIDZ can provide some advantages. In these areas, disk I/O is less critical (the Ingres archiver writing data to the journals is performed asynchronously to a users query execution) and sequential write operations are handled better than random I/O, making it ideal for checkpoints and journals, but less good for data areas.

Will now be putting this theory into practice...

Ingres Performance Tuning

I'm on an Ingres Performance Tuning course this week. Just completed day 4 of 5 and it's all been quite tough. There are many ways to tune a DBMS including choosing your table structures (heap, hash, isam and btree), when to use secondary indexes, how to examine the Query Execution Plan (QEP - the way the DBMS chooses how it will execute a query), key design, application design consideration, which have all led us to the locking and logging system today. The subsystems that enforce consistency but allow concurrency. The number of options and strategies are many and an Ingres installation is extremely flexible (it needs to be when systems have hundreds of concurrent users and databases in the hundreds of gigabytes).

The whole thing is really quite amazing and I bang my head against a wall when people automatically assume "open source database" equals only MySQL or Postgres.

I admit it - I like Ingres - I'm a fan. It's a free download and you can get it up and running (on Linux or Windows) very easily. There is a JDBC driver,

.NET connector, PHP, Python and Perl interfaces. Go on, try something new... :-)