2

How can I maximize the write performance without impacting reads?

4 Answers 4

3

A good start is to follow Oracle's SAME methodology - Stripe and Mirror Everything. This gives you a good base from which you can add further more specific improvements.

The SAME methodology is in the following PDF:

http://www.oracle.com/technology/deploy/availability/pdf/oow2000_sane.pdf

There's a good discussion on Ask Tom:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4433784236146

One of the main drivers for SAME is the ease of administration. It passes on a lot of the performance considerations to the OS and the underlying storage layer. The idea is that your tablespace files are already spread across a zillion disks in the storage array so any fiddling around you do on top of this doesn't help much. However, as ever, the devil's in the detail. It's tempting to treat the storage layer as a black box but you really do need to understand what's going on and know what sits under each of your tablespace files.

3

From a don't-want-to-touch-the-config POV - check your indexes. if you are writing to tables that are heavily indexed, remember that each time you write a row, the indexes are being changed. Fewer indexes, less physical IO.

This will - obviously - have an impact on reads.

If you have the time & capacity, I have found separating commonly written tables into specific tablespaces and making sure that/those tablespaces are stored on a separate RAID channel also helps, but this depends on the hardware you are using and adds a bunch of other considerations.

If you really do have time to ponder this, then buy & read Cary Millsaps 'Optimizing Oracle Performance' - it's dated (all depending on what version of oracle you are using) but a classic.

1

Set it up in a cluster and have your applications point to a specific node for writes; then read from the cluster itself.

1

Implementing SAME - Striping And Mirroring Everything as suggested in a previous answer - is a good default strategy. In particular if you are concerned about write performance avoids RAID5 at all costs - it creates a 4x write overhead while maintaining parity information. A RAID5 array with a large memory cache might hide that overhead for a while, but for prolonged writes the RAID 5 penalty will eventually be felt.

There's a lot of things to consider in optimizing write IO, but here are a few more things to consider:

  • Make sure there are enough disk devices in the stripe to support the IO rate. You need to place enough disks in the stripe to support the total IOs per second required, not just the number of GB to be stored. Disk provide better response time when only partly full of data – the outer parts of the disk can deliver data faster than the inner parts. So in general you want to have sparsely populated disks in your stripe.

  • It's better to avoid IO than to optimize IO. The biggest source of avoidable write IO is temporary segment IO for sorts and hash operations. Correctly sizing the PGA Aggregate Target is the best way to avoid this.

  • Make sure all your indexes are serving a valid purpose - to speed up a query or to enforce primary or foreign keys. Index maintenance is often the biggest source of write IO, so don’t have any superfluous indexes.

  • Create large and numerous redo logs. This helps avoid log switch overhead and database stalls that can occur when logs cycle before data file checkpoints complete. You might also want to put your redo logs on dedicated devices or a dedicated finely grained stripe.

  • Use asynchronous IO. If your datafiles are on file systems (e.g. not RAW or ASM) then set FILESYSTEMIO_OPTIONS to AYNCH or SETALL. SETALL implements direct IO as well as async IO which usually helps writes but may slow down reads.

  • If you are prepared to take a risk with your COMMITs, you can change COMMIT_WRITE (10g) or COMMIT_WAIT and COMMIT_LOGGING to delay or batch the IO that occurs when you issue a commit. Make sure you understand the implications, though; you could lose some transactions in the event of a database failure if you do this.

  • If you have access to the application code, make sure that array inserts are being used and - maybe – use direct path inserts using the APPEND hint.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .