IO Sizing

[Former Member]

Hi power users,

what do you think, which is best for a database with very much insert/update operations:

1) using 4 disks as raid 10 for log and data volumes

2) using 2 disks as raid 1 for log and 4 disks as raid 1 for data volume

3) using two different raid controllers with 2 disks each as raid 1 for log and datavolume

any comments?

best regards,

Michael

[lbreddemann]

> what do you think, which is best for a database with very much insert/update operations:

Well, first of all, it's necessary to understand the I/O pattern that the data change operations apply.

Usually the most critical part here is the log I/O as this is done synchronous (that is: your database sessions need to wait for the log I/O to be completed to continue with their work).

So having a really fast log I/O write operation is very important.

The data I/O on the other hand can become critical as savepoints, that take extremely long (e.g. more than 10 minutes) to complete, block the freeing of already backed up log information from the log area.

This could in turn lead to a LOGFULL situation which basically is a stand still of the database.

So high performing write I/O is less important for the data volumes but not totally unimportant.

With this background let's review your alternatives:

> 1) using 4 disks as raid 10 for log and data volumes

Log and Data in one disk array is a clear NO-GO.

You want your log to reside on a completely separate I/O chain to ensure that it does not fail when the data volume I/O breaks apart.

Also you don't want to see concurrent I/O requests on the same channels.

> 2) using 2 disks as raid 1 for log and 4 disks as raid 1 for data volume

For the data volumes, having more disks is nearly always better.

For the log volumes raid 1 will be OK.

> 3) using two different raid controllers with 2 disks each as raid 1 for log and datavolume

Best choice among your list, except, that you should put in more disks to the data volumes and consider using raid 10 to get a I/O performance gain for read I/O operations.

Since the pages that should be written to also need to be fetched to the cache before the data is actually inserted, it's also important to have a good performing read I/O.

Of course these are all general comments, since we don't know anything about what "very much insert/update operations" mean to you.

regards,

Lars