Make sure that you analyze wait statistics and file IO statistics to prove that IO limits on data files are actually the bottleneck before you increase the file size. In this case I’m getting 50x better performance by increasing the size of the file, but the actual improvement might vary in your workload.ĭo not immediately increase the file size if you believe that you might have performance issue on Managed Instance. This might introduce some unexpected performance issues if you have small files that require a lot of IO requests, but you can easily fix this issue by pre-allocating your files. ConclusionĪrchitecture of General Purpose tier on Managed Instance introduces dependency on file size where the bigger file sizes provide better performance. Also, it is much closer to underlying IO latency, which indicates that it don’t hit the Azure Premium disk limit on data files. Now I’m getting 2800 IOPS on the data file and write latency 10–50ms which is much lower than the previous case.
When I look at HammerDB again, I’m getting the following results: In order to try to fix this issue and add better IO characteristics to my storage, I will try to increase the size of data file: ALTER DATABASE MODIFY FILE ( NAME = N'tpcc1000', SIZE = 540 GB)ĥ40 GB is slightly bigger than 512 GB limit and it will put my data file in P30 category with (5000 IOPS and 200 MB/s).
However, the values close to the limits are indication that your have IO issues. You will never get the values that exactly match the limits in the table above because the queries are returning the average IO values in some period of time (between snapshot and the query), while Azure Premium storage measure runtime IOPS/throughput usage and throttle requests if the limit is reached. IOPS close to disk limit and high latency are indicators that Managed Instance is trying to push data to data files but it is limited by file IO performance. The difference between total latency and IO latency indicates that Managed Instance is limiting workload and queuing IO request in order to avoid IO limits. Total read and write latency is very high - around 2 seconds and much higher than underlying storage IO latency which is expected 3–4ms.This is very close to the 500 IOPS limit for the smallest files on Premium Disks (see table above). The interesting things that could notice here are: I have configured HammerDB with 100 users running on the database and I’m seeing the following results: The database has one data file smaller than 128GB, so it is getting minimal guaranteed performance characteristics (500 IOPS and 100 MB/s).
I have restored 100GB TPCC database generated by HammerDB. Query Performance Insights library for analyzing performance on Managed Instance.HammerDB for generating TPCC-like workload,.In this experiment, I’m using the following tools/libraries:
#DATABASE FOR FILE STORAGE HOW TO#
In this story, you will see how to identify that the performance issues are caused by the IO bottlenecks on Azure Premium disk due to data size and how to improve performance of your workload by increasing storage size. In this case you can easily fix the performance issues by increasing data file size. In some cases, you might experience performance issues because your data files are small and they don’t get enough IOPS/bandwidth.
Bigger files have better performance in Azure Premium disks
0 kommentar(er)
