Azure Table Storage key latency very variable

Question

We're seeing some very variable latencies when querying our Azure Table Storage data. We have a number of items each fetching time series data which is broken up by day as follows:

Partition key: {DATA_TYPE}_{YYYMMdd} - 4 different datatypes with about 2 years of data in total

Row Key: {DataObjectId} - About 3-4,000 records per day.

A record itself is a JSON encoded array of dateTime objects spread out every 15 minutes.

So I want to retrieve timeseries data for a specific object for the last few days so I constructed the following query:

string.Format("(PartitionKey ge '{0}') and (PartitionKey le '{1}') and     (RowKey eq '{2}')", lowDate, highDate, DataObjectId);

As above we have records going over 2-3 years now.

On the whole the query time is fairly speedy 600-800 ms However once or twice we get a couple of values where it seems to take a very long time to retrieve data from these partitions. i.e. one or two queries have taken 50 seconds plus to return data.

We are not aware that the system is under dramatic load. In fact frustratingly all the graphs in the portal we've found suggest no real problems.

Some suggestions that come to mind:

1.) add year component first making the partition keys immediately more selective.

However the most frustrating thing is the variation in time taken to do the queries.

The Azure storage latency in the Azure portal is averaging at about 117.2ms and the maximum reported is 294ms. I have interpreted this as Network latency.

Of course any suggestions gratefully received. The most vexing thing is that the execution time is so variable. In a very small number of cases we see our application resorting to the use of continuation tokens as the query has taken over 5 seconds to complete.

https://msdn.microsoft.com/en-us/library/azure/dd179421.aspx

Captain John Captain John · Accepted Answer · 2015-09-15T13:10:46

Have been looking at this for a while.

I've not found an answer to why querying accross partitions suffered such variable latency. I had assumed that it would work well with the indexes.

However the solution seems to be to simply request data from the 6 different partitions. Therefore all querying takes advantage of both the Partitionkey and rowkey indexing. Once this was implemented our queries began returning much faster.

Would still like to understand why querying accross partitions seemed so slow, but I can only assume the query resulted in a table scan which has variable latency.

Azure Table Storage key latency very variable

1 Answers