We love Microsoft Azure. It is by far the most developed cloud providers in IaaS, PaaS and even SaaS (Office 365) that you can get. It is well thought out from an administration point of view, strong API to control most aspects of it and offers as many developer oriented services as you can imagine. Much like Amazon it also offers its variety of Database as a Service which we use at ClicData extensively.
Database as a Service (DaaS)
Buying a license for database software and installing it on a server is only a small portion of the cost and effort. Since most database licenses are not cheap to begin with, you can imagine the total bill which includes items such as:
- Physical and Virtual Server or Servers
- Operating System Licenses
- Backup Devices
- Monitoring Services/Servers
- Maintenance and Administration Staff
- High Availability Replication Servers and Databases
Of course then there is the real work to make sure that the database itself is performing, secure, and replicated properly, testing restore and recovery procedures, and so forth.
Microsoft Azure currently offers a wide array of database as a service offerings, as does Amazon with their RDS offerings among others.
Why Microsoft Azure SQL Database?
ClicData runs user data exclusively on MS Azure SQL Database because it is simply the most advanced and performing database in the market. It is not the cheapest but not the most expensive one either. Some of the following features either make it unique or are definitively better implemented than the competition:
- Dynamic Memory Caches
- Columnar Store Indexes
- Transparent Data Encryption
And for our specific case, because we use mostly .NET and Azure technology, selecting this database also makes sense from a technology stack point of view since all products are Microsoft made. Finally we also wanted a Database as a Service provider that covers as many countries as possible. As each region and country starts enforcing stronger privacy and data location legislation, we want to make sure that the data resides as close as possible to our customers.
Database Performance and Sizing
Database sizing and configuration is not easy. It requires a lot of iterations of testing and monitoring to fine tune a database, with different hardware and under different workloads, schema and volume.
A query that performs well under a certain hardware configuration may not perform as well in another, even if the configuration is perceived as better.
Azure’s Database offers are even more complex to navigate. Because their revenue model, infrastructure and scaling is not transparent to the customers their offers use abstract scales to measure the performance. These scales are not easily aligned with real world examples. Basically today they offer two main modes a DTU based model and a vCore based model.
According to Microsoft, a Database Transaction Unit or DTU is a blended measure of CPU, memory, and data I/O and transaction log I/O in a ratio determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads. Doubling the DTUs by increasing the performance level of a database equates to doubling the set of resource available to that database.
Their first line of database offerings in this model are: Basic, Standard, and Premium. They are are offered under a DTU model whereby you scale the DTU higher if you need more performance. They can all be targeted for production use but what is different about them is the IO. In Basic and Standard the IOPS per DTU is 2.5 whereas in Premium it is a whopping 48 IOPS per DTU. And here lies the first issue since the DTU is defined as a measure of data and log I/O and IOPS is defined as an input/output performance measurement used to characterise computer storage devices like hard disk drives, solid state drives, and storage area networks they should be related or at least impacting one and the other.
However, looking at the pricing a Standard Database, for example a S3 is 100 DTUs and a P1 is 100 DTUs so should they perform the same or not? If the IOPS on Premium are higher we would assume that no, but since a DTU is a “blended measure that includes IO” we would assume that yes – otherwise what is the point of coming up with a scale that actually doesn’t measure performance? More on this later…
Earlier this year, Microsoft also released the vCore series. The vCore series is supposed to be better since you can select the size of the disk and the number of vCores for that database. vCore comes in 2 flavours; General Purpose and Business Critical. Additionally they have 2 generations of processors/configuration; Generation 4 and Generation 5.
General Purpose boasts 500 IOPS per vCore to a maximum 7000, while Business Critical is 5000 per vCore up to a maximum of 200,000 IOPS.
vCore seems like a much more reasonable model to size databases but has its own inconsistencies. Taking General Purpose as an example, basically after 14 cores you are at the maximum IOPS possible. Because GP is sold in steps of 8, 16, 24, 32, 40, 64 and 80 vCores, in essence it means that after 8 cores you are at the maximum IOPS possible so in what cases would you need so much parallelism in your queries that requires 80 vCores when the real bottle neck will be your disk? I am sure there are queries that can profit from that but not in many cases.
Additionally, selecting Generation 4 or Generation 5 in both General Purpose and Business Critical sometimes gives different values in both the Azure Portal as well as the Pricing Calculator.
Confused? Test. Test. Test.
So we got really confused as to what to select for our customers. We know what type of queries we run but it was difficult for us to bank on one specific series. We are still running mostly on DTU but the vCore series seems like a better fit for our dedicated customers.
We then took it upon ourselves to do our own performance testing and compare as many series and models as possible.
Remember that a configuration built for one use case may not be ideal in another use case so we ran 2 major distinct uses cases; Bulk Loading of data and Read Aggregated and Non-Aggregated Data. We don’t do a lot of UPDATES and DELETES in our databases so keep that in mind.
Also to keep in mind is that we apply Columnar Stored Indexes on almost all columns on each table and that our aggregates are software generated.
Loading: Our loading tests involved 2 tables, one with 10 Million rows exactly and 5 columns of varying types and the other with just over 18 Million rows and 15 columns, also of varying types and lengths.
We measured loading the data sets every 20 minutes and every 1 hour respectively for 2 days straight on each selected Azure configuration. One of the loads was done in concurrency with other loads to further test if additional cores or type of disk performed better under multiple data load scenarios..
Querying: For querying, we picked 3 resource intensive queries based on actual production use of ClicData and simulated them using our own test data. These queries involved calculations and aggregations at all times since that is our number one case scenario. One of them included a window function as well.
Using our own dashboards we monitored all the tests as we changed the same account to use different database configurations. The dashboard below shows a partial rendition of the monitoring dashboard, each dot representing the time take on that particular test run to perform that task. In the background you will see the database configuration in use at that time.
We then grouped all results into one summary dashboard as follows:
This dashboard was very useful to do relative comparisons between configurations and understand if our queries increased in performance or not across the different configurations. Some special notes:
1. GP versus BC and Gen4 versus Gen5
This was one of the most interesting items to compare since with GP we are able to have larger storage and with BC we get Solid State drives increasing performance 8x to 10x, on read. Take a look at the vCore 4 for BC and GP, Gen4 and Gen5 comparison in our tests.
Does the above make sense? BC costs 3-4x more than GP and yet we had identical results except for … the latest generation Gen5! Remember that this is not 1 test or 10. These are the aggregated results of 2 days of tests running every 5 minutes (queries) or every hour (loading).
Is this an outlier? More on this later.
2. DTU versus vCore
When it comes to selecting between vCore and DTU model we were interested in identifying what is the equivalent for us between one and the other. AS such we took the vCore 4 in GP and BC and selected the best approximate DTU configurations.
Roughly both BC and GP Gen4 4 Vcores perform slightly better than a P4 or a S7. However notice once more a possible outlier that S7 performs substantially better in query execution. Selecting the S4 would not be a wise decision but since there is no middle ground between S4 and S7, it is or the other.
As shown above there some inconsistent behaviours. One of the most critical ones we found was a consistent performance increase from vCore 1 to 8 but on vCore 16 it was worse than a vCore 6!
We did not understand the difference between Gen4 and Gen5 and why Gen5 (the new generation supposedly) performed worse in some cases.
We also noticed that no further performance can be gained on the same query meaning the queries could not perform better even if the configuration was increased in core or disk type. This was applicable to both DTU and vCore and makes sense even for bare metal SQL server installations.
It basically gets to a point that SQL server must synchronously perform an operation and that it can’t optimize any further.
Performance is one thing. Cost is another.
Before we finish, let’s talk about cost. If there is one negative thing about Microsoft Azure is their billing and cost monitoring. So of course we built our dashboard that breaks down as well per database what the cost per basic component is. We use this to monitor all our infrastructure across all Microsoft Data Regions.
Using a dashboard like this you could get a handle on what major areas are costing the most. Clear to us that our platform uses mostly SQL Database (green), but also storage with VMs and App Service after. If you are using Azure and would like to get a better handle on monitoring your usage, let me know, happy to share how we monitor cost and performance at ClicData.
After using Azure for over 6 years, we have learned a lot and our most recent batch of tests should be interesting to those using Microsoft SQL Azure and wanting to know more about the differences between the tiers and configurations. Hope this helps!