Performance Testing Series
This post is part of a series of blog posts about my performance tests in Azure SQL Database. For the first post in this series (and links to all of the other posts) please see here. For a summary of all the results please see here.
For a general overview of the test architecture, test components and test types, please see here.
Sequential Select Test Overview
The Sequential Select test involves reading an entire table or tables to get a simple estimate of maximum read rates. The data is read from each table sequentially (i.e. roughly speaking SELECT * with no ORDER BY). Of course, data access at the file level may well not be sequential.
The test involves using a newly restored database. The database is sized at 4.5 GB for Web Edition, 10 GB for Business Edition, 2 GB for the Basic Tier and 36 GB for the Standard and Premier tiers.
The size of the database and the fact it is newly restored means that none of the data exists in the buffer pool and the test therefore estimates the maximum read rates from physical disk for each tier.
Results from the Sequential Select tests are shown in the two charts below. These rates represent the “read-limited” rates, i.e. when the disk read % metric was reporting 100%. In these charts, the “T” values are the number of worker threads generating requests against the database, e.g. “Business 4T” = a test against Business edition, running with 4 threads.
This result demonstrates the clear differences in performance between tiers. It is interesting to note where the new Premium tier performance levels are positioned against Web/Business, i.e. P1 is lower than Web/Business, P2 significantly higher.
In hindsight, it would have been useful to also have tested the Business Edition with a 2T test (on an A2 sized instance rather than an A1), since this might have further increased the read rate of Business Edition.
We can now also compare the read rates from disk with the read rates for data already in the buffer pool:
It is interesting to note that Basic edition seems to outperform S1. That slight difference actually equates to about 11 MB difference per minute. This difference is discussed further below.
The charts below show the performance profiles over the lifetime of the tests in the charts above. Since 30 lines is rather a lot to fit onto one chart, data for the different editions / tiers has been split across several charts.
It is also worth noting that due to the way the Azure SQL Database logging into sys.resource_stats works, data is only available at five minute intervals and the last data point in each test is not useful and so is omitted.
The large jump at minute 42 is very likely due to the entire database now being cached in the buffer pool. The disk read profile below will allow us to confirm this. Nonetheless, it suggests that there is no buffer memory limit enforced per database. Whether this behaviour will continue as the preview progresses and into GA when the service becomes more heavily used is debatable.
Basic and S1 Tiers
This is an interesting chart. At first glance it appears to show the Basic edition generally slightly outperforms S1 as shown in the average charts earlier.
However… the peak in the S1 read rate in the first five minutes suggests data read quota is allocated in blocks per unit of time. And thus, provided users stay within the expected transaction rate, read performance may be quite good. Step outside that quote however and performance is very heavily constrained. The burst read rate is about three times the sustained read rate.
Basic to S2 Tiers
Again, a higher performing burst appears to be present initially in the S2 profile, lasting for around eight minutes, after which performance becomes more variable and generally lower.
S1 to P2 Tiers
Again, slightly higher performing bursts and present in the P1 and P2 profiles. On this chart, the apparent initial bursts are quite pronounced for all tiers.
Web / Business
Another interesting chart. Business on and minute by minute basis seems slightly more consistent than the new service tiers, though as ever there is a risk of a sudden and significant drop in performance, such as around minutes 16 and 58 in the chart above.
Web / Business, P1 and P2
Plotting Web/Business and P1/P2 on the same charts allows this comparison to be made more easily.
It may be that the new tiers have two “pools” of read quota – a higher and more consistent level of performance that is allocated according to the quota of the tier – and once this has been fully consumed performance defaults back to a lower and less guaranteed performance level. As blocks of the guaranteed quota are allocated, performance rises for a period and then falls back again once that is consumed. This is of course entirely speculation but would explain the more lumpy nature of the performance profiles for the new tiers.
If read rate quota were enforced as a single hard limit then it would seem difficult for the Standard Editions to support anything remotely approaching 250GB – e.g. even for a well designed, well indexed database – just loading indexes at 0.5MB per second is going to take a long time relatively speaking. The allowance for short term bursts would seem the obvious solution to this problem.
Of course, this initial peak may be nothing more than a curious side-effect of my test apparatus, however, if that were the case, I would probably expect the same behaviour to be present in the Web/Business tests – where it is not really present.
Disk Read Utilisation
This chart shows that Web edition appears to have entirely cached the 4.5GB test database after ten minutes and Business Edition has also cached the entire 10 GB database after around twenty minutes.
As expected, this test shows all of the tests on the new service tiers were running at 100% disk read rate utilisation. The Basic Tier database has been entirely cached after around 40 minutes, consistent with the sudden increase in read rate for that database seen in the earlier charts at around the same point in time.
It is interesting to see some inconsistency in the reported read rate usage (with no apparent similar difference seen in the observed read rates), with two tests reporting nearly 100% throughout, with the remaining tests much less. These tests were all performed on the same day.
CPU quota utilisation is low across all of the newer service tiers, with the exception of the Basic tier once after around 40 minutes. At this point, since the entire database is cached, performance goes from being constrained by disk read quota to CPU quota.
Sequential Select Test Conclusions
The Sequential Select tests have illustrated how the data read rate from physical disk has been heavily constrained in the new service tiers. The tests have hinted at the possible existence of a higher performance level when running under quota that seems to exist most dramatically in the Std S1 performance level.
More specifically, for the initial burst:
|Service Tier||Phys Disk Burst Limit||Phys Disk Burst Comparison|
|Web / Business =||550 MB per minute||–|
|Basic =||39 MB per minute||7.1% of Web / Business|
|Standard S1 =||75 MB per minute||14% of Web / Business|
|Standard S2 =||210 MB per minute||38% of Web / Business|
|Premium P1 =||460 MB per minute||84% of Web / Business|
|Premium P2 =||900 MB per minute||160% of Web / Business|
For a sustained read:
|Service Tier||Phys Disk Sustained Limit||Phys Disk Sustained Comparison|
|Web / Business =||550 MB per minute||–|
|Basic =||39.3 MB per minute||7.1% of Web / Business|
|Standard S1 =||28.7 MB per minute||5.2% of Web / Business|
|Standard S2 =||143.6 MB per minute||26% of Web / Business|
|Premium P1 =||400.4 MB per minute||73% of Web / Business|
|Premium P2 =||766.2 MB per minute||140% of Web / Business|
It should be noted that the Web / Business rate given here is for a single thread reading from the database. Due to the opportunistic nature of those editions, it is possible that opening additional threads would yield a higher rate. The rates given for the newer service tiers are likely to be close to the maximum rates for those tiers due to SQL Database reporting all tests as running at 100% utilisation of available disk read quota.
Appendix – Test Configuration
|Cloud Svc Inst. Size||A1||A1||A1||A1||A1||A2||A2|
|Req. Gen. Thread Count||1||1||1||1||2||2||4|
|Initial Test Data (MB)||4.5||10||2||36||36||36||36|
Appendix – Test Results
|Configuration||Avg Rows Per Second||Avg MB Per Minute|
|Std S1 1T||1412||1229||1359||30.3||26.4||29.2|
|Std S2 2T||6661||6291||7095||143.1||135.2||152.4|
|Prem P1 2T||18767||17742||19399||403.2||381.2||416.8|
|Prem P2 4T||36151||32589||38243||776.8||700.2||821.7|
|Configuration||SQL Avg Disk Read %||SQL Avg CPU %|
|Std S1 1T||99.9||100.0||100.0||3.5||3.8||3.5|
|Std S2 2T||99.9||100.0||100.0||4.1||3.6||3.6|
|Prem P1 2T||99.9||100.0||100.0||4.5||4.5||4.6|
|Prem P2 4T||97.5||100.0||100.0||4.6||4.1||5.5|
|Configuration||Cloud Svc Avg CPU||Error Count|
|Std S1 1T||5.3||4.3||5.2||0||0||0|
|Std S2 2T||12.8||12.6||14.0||0||0||0|
|Prem P1 2T||34.7||34.5||36.6||0||0||0|
|Prem P2 4T||73.5||30.5||35.1||0||0||0|