Databases Reference
In-Depth Information
The only way to increase random read throughput is to buy faster disks or add more
memory to the controller for its read cache and hope that you get a lot of cache hits,
although even with a massive read cache, you still want to know what the cost of a cold
(cache miss) random read is versus the costofahotread(readingfromthecache).
Another way you might see an increase in random read performance that is really
cheating is when you increase the block size to the extent that you do a single random
read to the head of a massive block and then get sequential access to the rest of the
block. I don't really count this as meaningful random read performance, but some folks
selling disks might try this on you to sell their disks/disk array by trying to persuade
you that their system has better random read performance.
RandomWrite Performance
Given this information about Random Read performance, why then do random writes
increase as you add disks? The reason for this is because as you add more disks there is
a greater chance that you can spread your writes across multiple disks, so the controller
can parallelize writes to all disks in the array when there are enough writes. The other
way that writes benefit is that random writes will benefit greatly from even a small
cache on the controller. This allows the controller to turn a seemingly random stream
of writes into blocks of sequential writes to each disk.
WARNING!
This can be seriously dangerous to the health of your SQL data if you allow write
caching when the controller doesn't have battery backup to ensure the writes are writ-
ten in the event of a power failure.
If in doubt turn off write caching and suffer slower performance, but have the benefit
of knowing your data is secure.
ComparingI/OResults
In the previous pages you have looked at the performance characteristics of several different test config-
urations using just one of many tests that are available in the suite of tests provided with the code for
this topic.
One question you should ask yourself as you read this is how you can use these tests to compare dif-
ferent storage configurations. Table 12-8 showed how you can compare thruput from two different disk
subsystems. In this case, the results from Test E were used (the test where block size was varied). If your
target system has very specific I/O characteristics, then you should consider creating a customized test
to exercise the I/O characteristics of your target system.
If you're not really sure what your target system's I/O characteristics will be, then a set of general
purpose tests like those provided here is a great place to start.
Run the tests on each configuration, then examine the test results looking for the best throughput for each
I/O type: Random Read (R-R), Random Write (R-W), Sequential Read (S-R), and Sequential Write (S-W).
Search WWH ::
Custom Search