Re: What is better asychronous IO or multiple db_writers?

In article <34c67f9c.88908928_at_newsserver.trl.oz.au>, Steve Adams wrote: Steve,

OK, I will concede a large degree of what you are saying. It appears you know more about filesystems than me. I still say that HP-UX filesystems are not as well implemented as several other filesystem flavours (e.g. AIX and PTX) There is, however, one proviso I will attach from my perspective.

I work for a software house.
My job is generally looking after around 50 developments databases, with only a few production systems. The production systems are at client sites and I am not in day-to-day contact with them. This is hugely problematical from a maintenance perspective as the client generally will have operators but not necessarily have DBA's (or may have Sybase but not Oracle DBA's).

Using RAW filesystems becomes more difficult the less time you have to administer the database. Even more so when for mose of the year you have a consistent transaction throughput and then is multiplies tenfold for one month before settling back (bloody tax dodgers!). Performance issues are normally SQL related and not database set-up. In the overall scheme of things, tuning your SQL is far more important than tuning the database, although I do recall a database 'sticking' during a benchmark a Sequent. We discovered that it was I/O related, shifted datafiles about, and we went from sub-100 users (simulated) sticking to over 1000 users really performing.

Hopefully our RAW war is now over.

regs

Neil Chandler

>Hi Neil,
>
>I don't want to turn this into a religious war about raw, but I will
>venture another response.
>
>When choosing a DBWR setup on HP the choice is between
>1) async on raw
>2) multiple dbwrs on filesystems
>3) mulitple dbwrs on raw
>
>None of us would recommend (3), so it is really a choice between (1)
>and (2). Because we have to choose between raw and filesystem at the
>same time that we choose between async and multiple dbwrs, we are at
>risk of confusing the two issues. I will try to keep them distinct.
>
>Async is better than multiple dbwrs because:
>1) it avoids the interprocess communication overheads for
>communication between the master and slave DBWRs
>2) it avoids the bandwidth limitation of only being able to have 50
>slave DBWRs, and only being able to do 50 concurrent I/Os
>3) it queues the writes to hp-ux as a batch, which allows the device
>driver strategy layer to order them optimally and merge them into
>larger I/O requests where possible
>4) DBWR clears all of its batch in one large operation, rather than a
>series of smaller ones, which reduces the possibility and duration of
>write complete wait events.
>
>In summary async has some slight performance advantages under all
>circumstances, but has very significant bandwidth and thus scalability
>and thus performance advantages in write intensive instances.
>
>
>The main argument for raw on HP is that it is needed for async, and
>you may need the scalability that async allows. However, there are
>other implications in choosing raw that need to be considered --
>namely performance and manageability.
>
>
>The performance issue is mainly related to the benefits of buffering
>in the hp-ux buffer cache, versus the cost of maintaining that cache,
>rather than the length of the code path (which is slightly in favour
>of raw). Firstly, note that there is no buffering advantage on writes,
>because dbwr opens the datafiles o_dsync. There can be buffering
>advantages on reads, particularly due to read-ahead. However, this is
>only significant if you do sequential access with a multiblock read
>size of less than 64K or have too few db_block_buffers. Both these
>factors are readily tunable, and so the buffering argument holds
>little weight. On the other hand, the management overheads of the
>buffer cache are significant. Mostly importantly, all readers take a
>shared lock on the datafile inode which delays dbwr from taking its
>exclusive lock on the inode to write. This can significantly impact
>dbwr performance. There is also a slight impact from dbwr on readers.
>Another aspect of filesystem buffer cache management is that the
>syncer will flush all filesystem metadata to disk every 30 seconds.
>Any attempts to modify that metadata (any buffered I/O requests) will
>block until the physical I/Os have finished. In cases where there are
>a lot of outstanding I/O requests on some disks (for example, if there
>were a lot of delayed write blocks in the cache that have just been
>flushed by syncer) the metadata flush can take in the order of
>seconds. On a busy system, these filesystem buffer cache management
>overheads are a significant factor in the outperformance of raw over
>filesystems.
>
>The management issue is uninteresting to me, but I will say that if
>you are serious about performance, you should have planned your I/O
>distribution and capacity growth with such care that the concept of
>being able to extend or add datafiles on the fly would fill you with
>horror. I am however sympathetic to those poor souls who are not given
>time for such planning, or the money for the database monitoring and
>backup management software that they need.
>
>In summary, given careful tuning raw will perform better than
>filesystem on a busy system, but there will be no significant
>difference on a lightly loaded system. Filesystems are more flexible
>than raw, but flexibility should not be used as a subtitute for proper
>planning and automation.
>
>I hope that makes my position a bit clearer.
>Fire awy! :-)
>
>Regards,
>Steve Adans
>steveadams_at_acslink.net.au
>
Received on Mon Jan 26 1998 - 00:00:00 CST