THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact of controller read cache: large sequential I/Os

In the next several blog posts, I’ll share with you some empirical results concerning the performance impact of configuring the read/write cache of a disk controller.

 

In the comments on Joe Chang’s blog at this site on Storage Performance for SQL Server, some statements were made concerning the performance impact of read cache in a disk controller. Joe and I agreed that read cache doesn’t really help for what matters. In fact, it may degrade the performance of large sequential reads. In this blog post, I’ll give you some supporting evidence.

 

All the data points reported in this blog post were obtained on an HP Smart Array P400 SAS controller with 512MB of cache that could be configured for either reads or writes, or a combination of reads and writes. There were three 72GB 10,000rpm SAS drives hanging off the controller, and they were configured as three separate disks (i.e. just a bunch of disks).

 

The results reported in this and the next few blog posts were from tests done on a 10GB test file on one of the three disks. The test tool was conducted primarily with sqlio.exe, and the I/O commands executed are listed at the end of this post.

 

The two charts below illustrate the performance impact of various cache settings on the throughput of 256KB sequential reads and that of 256KB sequential writes at various I/O load levels. The top chart is for 256KB sequential reads and the bottom chart is for 256KB sequential writes. Note that each data point in the charts represents the consistent results from many repeated test runs in the configured test environment.

 

 

As is evident in the top chart, the throughput of 256KB sequential reads can be degraded by the presence of a significant amount of read cache. In this case, 256KB sequential reads actually experienced a rather large drop in throughput (~20%) from ~73MB per second when no read cache was allocated to ~53MB per second when 512MB was allocated to reads.

 

The bottom chart suggests that increasing the amount of controller cache for writes did improve the performance of 256KB sequential writes. When the write cache was increased from 0MB to 512MB, the throughput of 256KB sequential writes went up from 57MB per second to 62MB per second, an increase of ~9%.

 

Now does this mean that the controller cache should be all allocate to writes? I’ll address that question in the next several posts in the context of other I/O workloads. But let me give you a hint before I get to the next post. Note that in the top chart there was no performance difference in 256KB sequential reads whether 0MB or 128MB was allocated to the read cache. Also note that in the bottom chart there was no performance difference in 256KB sequential writes whether 512MB or 384MB was allocated to the write cache. This is significant as other read I/O workloads may benefit from a small amount of read cache without harming the write I/O workloads.

 

Appendix: Batch file for large sequential I/Os

call sqlIO.exe -kW -s30 -o2   -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o8   -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o16  -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o32  -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o64  -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o128 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kW -s30 -o256 -fsequential -b256 -BH -LS -Fparam_F.txt

call sqlIO.exe -kR -s30 -o2   -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o8   -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o16  -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o32  -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o64  -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o128 -fsequential -b256 -BH -LS -Fparam_F.txt
call sqlIO.exe -kR -s30 -o256 -fsequential -b256 -BH -LS -Fparam_F.txt

 

where the content of the parameter file param_F.txt is as follows:

 

F:\testfile.dat 1 0x0 10000

Published Monday, March 24, 2008 1:14 PM by Linchi Shea
Filed under: , ,

Attachment(s): large_sequential.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jchang said:

can you test a smaller read cache?

the reputable Oracle source said 2MB read cache per LUN

test 2, 8 & 32M if you can, ie, allowed by the controller

March 24, 2008 1:58 PM
 

Linchi Shea said:

Well, if there isn't much a difference between 0MB read cache and 128MB read cache, I'd expect 2MB read cache to fall between, and therefore there shouldn't be much a difference. But I'll try it out anyway.

March 24, 2008 2:08 PM
 

Linchi Shea said:

Oh, I just realized that the only settings allowed on this controller are 0%, 25%, 75%, or 100% for read cache. Given that the total amount of cache is 512MB, the minimum non-zero read cache is 128MB. So we can't test 2MB, 8MB, or 32MB read cache. But as said before, we should be able to interpolate the throughput using the results from 0MB read cache and 128MB read cache.

March 24, 2008 4:30 PM
 

jchang said:

the other thing that also needs to be tested in small 8K random IO with read cache enabled, a transactional db is heavy on this, and a read-ahead cache can seriously degrade random performance, I would test the adaptive read-ahead. I wonder if the purpose of the super small read cache was to avoid degrading random IO

March 24, 2008 10:39 PM
 

Linchi Shea said:

I already have the 8K random I/O data (and many other data points), and will discuss them in a later post.

March 25, 2008 1:26 AM
 

TheSQLGuru said:

Very much look forward to further postings on this topic, Linchi.  Thanks for all the testing (and public findings dissemination) you do!!

March 25, 2008 11:23 AM
 

Linchi Shea said:

In my previous post on the performance impact of controller cache configurations , I presented some empirical

March 26, 2008 11:38 PM
 

Linchi Shea said:

Through empirical data presented in my two previous posts on the performance impact of controller cache

March 29, 2008 8:46 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement