Reaching the limits of Azure Table storage…or trying to
Cloud — for most people just an accumulation of water droplets in our atmosphere, for others an accumulation of various computing services in order to build a powerful IT infrastructure. (FYI: I am talking about the second thing here.)
One of the big cloud platforms is Azure, which is created by Microsoft.
On the Azure platform, there are many different storage solutions, such as various kinds of database solutions, Data Lake Storage (mainly targeting data science applications), queue storage, managed disks, blob storage, and a lot more.
Contents
- Introduction to Azure Table storage
- Deleting billions of entities
- Testing the code
- Alternative approach
Introduction to Azure Table storage
In this article, I will focus on Azure Table storage, which is a relatively low-priced, scalable NoSQL storage suitable for storing large amounts of data while keeping costs low.
Table storage is one of the four standard storage types that is very simple to set up in the Azure portal. The other three types are Blob storage, File storage and Queue storage, which I will not introduce any further here.
There is a REST API that makes it simple to interact with Azure Storage, and there are SDKs available in a lot of different programming languages, including .NET, Java, Node.js, Python, PHP, etc.
Azure Table storage (as well as the other standard storage types) are organized under a so-called storage account. Under a storage account, you can have multiple tables (see image below). If you are familiar with SQL databases, this concept sounds familiar. Similar to a row in a relational database, we have entities in the Table storage, which is limited to 1 MB in size. Further, each entity is made up of up to 255 properties. A property is basically a key-value pair. See Table storage concepts.
Differences to a relational database
You may wonder why you should even consider using an SQL database instead, if the table storage is so much cheaper and highly scalable. Of course, Azure Table storage is not all roses.
A difference to a relational model is that you have three mandatory properties for each entity:
- PartitionKey,
- RowKey,
- Timestamp.
The former two values are indexed and the combination of PartitionKey and RowKey are used to identify an entity in a given table and must be unique. This allows fast queries for these two properties. However, these are the only two indexed properties, i.e. queries regarding other properties will be much slower.
This requires designing performant table storage solutions a little different than relational database tables. I found the Azure Storage Table Design Guide worth reading for that matter.
Deleting billions of entities
Deleting billions of entities sounds like an exaggeration. Actually, it is not all. An issue we had with deleting literally billions of entities gave us the idea to write this article.
It was an IoT project with thousands of devices sending tens of thousands of messages each day. These messages were all stored in a single Azure storage table. And by using basic mathematics, we know that this adds up quickly and we have billions of messages in the table. We calculated with around three billion new messages each day at full load. Even though each of the messages had less than 1 kB, this is not a negligible amount.
With a retention period of 180 days, there can be over 500 billion entities in the storage table. No big deal for Azure, the table storage can easily handle that. However, despite the relatively low costs of Azure Table storage, the costs add up quickly.
500 billion entities (1 KB each) would cost around 32,000 € (data center in West Europe) simply storing the data, without performing any operations.
Therefore, after these 180 days, we wanted to delete the old messages. And there, the problems arose.
As the messages were already there, we first thought about a solution with the given setup and table design. The messages’ PartitionKey was the ID of the IoT device and the RowKey contained the time when the messages were created. So far so good: we could iterate through each partition/device and then query by the RowKey for messages older than 180 days. And table service provides batch operations, that allow operations of up to 100 entities in the same partition in one go. Here, I have the .NET code for that operation:
This method gives me a list of entities, that should be deleted for one particular partition. The reason I have to query the list of messages before deleting them is, that I can only delete a message, if I know both the PartitionKey and RowKey. In our case, the RowKey for all the messages is unknown.
The following method takes the created list, splits it into batches of hundreds and then deletes them with the above-mentioned batch operation.
As this deletes the messages only for one of the devices, putting that into a loop for each device would finally delete all the old messages. But we found out that this takes ages. We have to perform this deletion every day and one execution would take much longer than a single day. Therefore, this solution needed to be optimized.
Instead of consecutively (and slowly) deleting messages of one partition after the other, we decided to parallelize the whole thing. We defined one task for each partition that undertakes the querying and deletion. Of course, we limited the number of simultaneous tasks, to avoid performance issues due to managing too many resources.
Testing the code
For testing the code described above, we used three different Azure VMs:
· B1s with 1 core and 1 GB memory
· B4ms with 4 cores and 16 GB memory
· F16s with 16 cores and 32 GB memory
What we are mainly looking at, is the load on the CPUs as well as the network throughput.
Experiment #1
VM Size B1s, 500 partitions, 5000 entities per partition
For the first experiment, the smallest of the three VMs was tested. 2,500,000 entities were deleted in about 230 seconds, equaling 10854 entities per second.
Now, let’s have a look at the interesting part, the CPU usage.
As you can see in the diagram, the CPU was basically working under full load. At the same time, the network throughput was quite constant at about 9 MB/s on average (Beware the unit is MB/s, not the commonly used Mbit/s).
As it is required to first retrieve all the entities’ RowKey in order to iterate through them and request to delete them, the CPU has quite a bit of work to do. And we can see here that the CPU is the bottleneck. So, it is interesting to see what happens with a more powerful VM.
Experiment #2
Vm Size B4ms, 1000 partitions, 5000 entities per partition
The fastest run was a bit less than 200 seconds for deleting 5,000,000 entities, or 25056 entities per second.
The following chart shows the load on the CPU. The average load was 70%, so the CPU was engaged but not working to full capacity.
The network throughput seemed to be the limiting factor here. As you can see in the following chart, it went up and down averaging at about 20 MB/s.
But what was limiting the throughput to such a low number? Don’t get me wrong, it’s 160 Mbit/s, which is not bad. But considering that the Azure VMs can have data rates of multiple Gbit/s, it is pretty disappointing.
Before investigating this question, a third experiment was conducted, with the most powerful VMs out of the three at hand, the F16s with 16 cores and presumably up to 12 Gbit/s network throughput (cf. here).
Experiment #3
VM Size F16s, 1000 partitions, 5000 entities per partition
Deleting the 5,000,000 entities in the table storage took about 185 seconds, which equals 27027 entities per second.
As it is obvious from the following chart, there was not much load on the CPU. The maximum load (averaged over all cores) was never above 30% and the average was only around 16%.
The average network throughput was with slightly over 22 MB/s a little bit higher than in the previous experiment.
This brings up the question again: What is limiting the network data rate if it’s not the virtual machine itself?
Maybe it is the Azure table storage?
After digging in the documentation for a while, an article about scalability and performance of storage accounts came up. It lists several limits of a storage account and of the different storage types. You can find it here. The following image shows the limits of the Azure table storage.
Highlighted in red, you can see that for one storage account, the requests are limited to 20,000 transactions per second. But what does that actually mean? What is one transaction?
According to this Microsoft blog post, one transaction is one REST call to the Azure storage account. Also stated in the article, is that a batch operation — which we are performing to process 100 entities together — results in a single REST call and therefore counts as a single transaction
So actually, we should be able to process many more than 20,000 entities per second, theoretically up to 2,000,000 if all batches contain 100 entities. However, the additional comment “assuming 1 KiB entity size” indicates that the limit may also depend on the size of the batch that is processed. However, we could not find any further evidence about that assumption.
Alternative approach
This whole approach of designing the table may seem totally logical, especially if you have an SQL background. However, the Table storage design guide suggests that designing tables in Azure table storage should be approached a bit differently to the design of SQL tables and it often makes sense to create multiple tables for the same type of data.
In our case, we have an archive of IoT messages that should retain messages of the last 180 days. And for deleting so many entities, we have to fetch all of them first in order to delete them. Obviously, this is very time-consuming. Another approach would be to split the data into multiple tables, basically further partitioning the data. Then, there is no need to fetch entities first but the entire table can be deleted.
One approach is to create a new table for each day. After 180 days, this table simply has to be deleted. A downside of this approach would be when requesting data from multiple days, data from multiple tables has to be retrieved.
Conclusion
To sum up, the main problem is to potentially delete billions of messages each day, which simply takes too much time.
The bottleneck is most likely the Azure Table storage, which seems to not only limit the number of transactions, but also the data rate. We reached a maximum data rate of approximately 20 MB/s.
Our biggest takeaway is to consider the entire life cycle of the stored data when designing a solution with Azure Table storage (and most likely other storage solutions). Sometimes, it’s not only important to take into account how the data can be accessed in a performant way, but also deleted in an efficient manner. As always, more time spend on planning beforehand can save you some time in retrospect.
If there are any questions popping up in your head, don’t hesitate to leave a comment or contact us!
