Dramatic decrease of Azure Table storage performance after querying whole partition

1
votes

I use Azure Table storage as a time series database. The database is constantly extended with more rows, (approximately 20 rows per second for each partition). Every day I create new partitions for the day's data so that all partition have a similar size and never get too big.

Until now everything worked flawlessly, when I wanted to retrieve data from a specific partition it would never take more than 2.5 secs for 1000 values and on average it would take 1 sec.

When I tried to query all the data of a partition though things got really really slow, towards the middle of the procedure each query would take 30-40 sec for 1000 values.

So I cancelled the procedure just to re start it for a smaller range. But now all queries take too long. From the beginning all queries need 15-30 secs. Can that mean that data got rearranged in a non efficient way and that's why I am seeing this dramatic decrease in performance? If yes is there a way to handle such a rearrangement?

2
azureazure-table-storage
I know that you need to keep your tables "warm" in order to get optimal performance - basically, the more you query a set partition, the closer it gets to optimal speed. However, I'm not sure that is it - are you doing queries based on something other than particion key/row key that could explain the drop in performance? – Pedro G. Dias
That's what I think too, that's why I think re starting the querying would at least bring performance back to normal, but that doesn't seem to be the case, performance keeps being too bad. No, I keep querying for a specific partition key using the continuation token – LetsPlayYahtzee
I'm really curious to learn what is causing this - the performance guarantees state 2 000 entities/second (1Kb entities) per partition, and from your post, you're well within that range. – Pedro G. Dias
I am quite curious too! Now after restarting several times, there points when the performance is close to normal, close to 3 secs for 1000 values, but at other points it goes again higher than 15 secs. I will have to look closer to what is going on and I will update if I find something – LetsPlayYahtzee

2 Answers

3
votes

I would definitely recommend you to go over the links Jason pointed above. You have not given too much detail about how you generate your partition keys but from sounds of it you are falling into several anti patterns. Including by applying Append (or Prepend) and too many entities in a single partition. I would recommend you to reduce your partition size and also put either a hash or a random prefix to your partition keys so they are not in lexicographical order.

Azure storage follows a range partitioning scheme in the background, so even if the partition keys you picked up are unique, if they are sequential they will fall into the same range and potentially be served by a single partition server, which would hamper the ability of azure storage service overall to load balance and scale out your storage requests.

The other aspect you should think is how you are reading the entities back, the best recommendation is point query with partition key and row key, worst is a full table scan with no PK and RK, there in the middle you have partition scan which in your case will also be pretty bad performance due to your partition size.

2
votes

One of the challenges with time series data is that you can end up writing all your data to a single partition which prevents Table Storage from allocating additional resources to help you scale. Similarly for read operations you are constrained by potentially having all your data in a single partition which means you are limited to 2000 entities / second - whereas if you spread your data across multiple partitions you can parallelize the query and yield far greater scale.

Do you have Storage Analytics enabled? I would be interested to know if you are getting throttled at all or what other potential issues might be going on. Take a look at the Storage Monitoring, Diagnosing and Troubleshooting guide for more information.

If you still can't find the information you want please email [email protected] and we would be happy to follow up with you.

The Azure Storage Table Design Guide talks about general scalability guidance as well as patterns / anti-patterns (see the append only anti-pattern for a good overview) which is worth looking at.