Transaction IO Performance on Violin 2015-04
Back in Feb, I went on a diatribe-rant against the standard SAN Vendor configuration practice. The Problem with Standard SAN Configuration IO Performance, article and accompanying post, showed IO performance metrics for a batch driven transaction processing workload on a SAN managed by a data center/cloud company. The only option offered by the service provider was to request volumes for storage. There was no consideration for special IO characteristics of transaction processing or other database workloads. No discussion. This practice is doctrine pontificated by SAN vendors, calling SAN admins on a mission to implement the "storage as a service" concept while remaining completely blind to the requirements of mission critical databases.
Ok, I am venting again. Now I have performance metrics for the same workload, except that storage is on a Violin system. The system is different in having 24 physical cores, no HT, and 256GB memory versus previous system with 16 physical cores, HT (32 logical) and 384GB.
Below are the IO characteristics. The horizontal axis time scale is 5 min per major division for 1 hour across the entire chart. Each tick is 1 minute. Data points are every 15 sec. Note that the Feb (HDD) charts were 1 min per major division, 15 min total, with data point every 5 sec.
IOPS - read (green), write (blue), log write (red)
ms/Rd or Wr
The obvious difference between the IO characteristics on Violin and the previous HDD-based storage is that checkpoints now have almost no visible impact on performance. Both CPU and transactions/sec are very steady, with slightly noticeable blips, versus the severe drops before. It is evident that checkpoint writes now have almost no impact on data reads or log write IOPS. The same is true of IO latency, in milli-seconds per read or write.
If the storage had been on HDD storage but with logs on a separate physical disks, we would expect that the checkpoint would drive up data read latency, and hence depress data read performance. But it would have no impact on log write latency, and hence no impact on log write performance. The lower data reads should have only moderately depress performance, not severely.
The difference in system processor, 24 physical cores no-HT versus 16 cores plus HT is not a factor in the IO characteristics. The difference in physical memory, 256 GB on the system Violin storage and 384 GB in the system with HDD storage is evident in the data read IOPS, starting at 7-8K IOPS then drifting down to 2-3K IOPS on the system with less memory, compare with mostly 1K IOPS on the system with more memory. Both storage systems can easily handle 20K IOPS.
The main argument here is not that SSD/Flash storage is a requirement for transaction processing databases, even though there are clear benefits. (NAND flash based SSD have both maturity and cost-structure to be very attractive for any new storage system purchases.) The point is that there is a severe problem with the SAN vendor doctrine of one common pool for all volumes.
This very severe problem can mostly and easily be mitigated simply by having separate physical disks for the log volume. So the point could and should be demonstrated by showing the IO performance on an HDD SAN with separate physical disks for logs. But this violates the SAN doctrine of ignoring user requirements, and would not be considered or allowed by the service provider under any circumstance. So the only real solution is the keep performance critical databases off a storage system administered by a team on a different mission than supporting the database.
Below are excerpts and the graphs from the Feb article.
Standard SAN Configuration IO Performance 2015-02
The chart below is CPU. The horizontal axis is time. One major division marked by the vertical line is 1 minute, and the small tick is 12 sec. The data points are 5 sec. There are 12 steps between each major division. The vertical axis is overall (system) CPU utilization in percent. Each of the stacked green lines represents an individual processor. There are 16 physical cores and 32 logical. A single logical core at 100% utilization would show a separation of 3.125% to the line below.
On the second chart, the red line is the performance monitor object: SQL Server:Databases, counter: Transactions/sec. Note that the vertical axis is log-scale, base 10. One major division is a factor of 10. Each minor tick is an integer. The first small tick up from a major tick is 2, the next is 3 and so on to 9 for the last small tick.
The third chart is IOPS. Green is data reads, blue is data writes, and red is log writes. The vertical axis is log scale.
The fourth chart is IO latency, milli-sec per IO. The same color codes applies as for IOPS. Again the vertical axis is log scale.
ms/Rd or Wr