Search results matching tags 'Performance', 'san', and 'Disk I/O'

Performance impact: file fragmentation and SAN – Part V

Linchi Shea — Mon, 29 Dec 2008 21:36:00 GMT

SQL Server workloads

So far, the discussions in all the previous posts (1, 2, 3, and 4) on the performance impact of file fragmentation on a drive presented from a high-end enterprise-class disk array are related to disk I/O workloads. Ultimately, you want to know how file fragmentation may impact your SQL Server workloads.

In this post, I share with you some results from running three SQL Server workloads: (1) database backups, (2) checkpoints, and (3) table scans. These SQL Server workloads were run in the same three test scenarios as used in all the previous four posts:

· Non-fragmented. The database files were created on an empty drive,

· 2MB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 2MB in size,

· 128KB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 128KB in size,

The same database was used in all the tests. The database was about 9.5GB in size, and the table on which table scan was performed had 10 million rows and was about 4GB in size. DBCC DROPCLEANBUFFERS was run prior to each table scan.

The following chart is a summary of the SQL Server workload test results:

Clearly, file fragmentation did not have any significant performance impact on these SQL Server workloads. Because the performance of these workloads is dependent on the disk I/O throughput, we could have predicted this from the results of our disk I/O tests as reported in the previous four posts. However, it’s still comforting to see the prediction validated with real data points.

This concludes this series of posts on the performance impact of file fragmentation on a drive that is presented from a high-end enterprise class disk array. Overall, the impact was significantly less than what we have seen on a traditional directly attached disk drive. For I/O throughput, there was little to no impact. For I/O latency (or response time), file fragmentation can cause some I/O request to take much longer to complete.

In a future post(s), I’ll explore whether the same holds true with a drive presented from a lower end disk array.

Performance impact: file fragmentation and SAN – Part IV

Linchi Shea — Mon, 22 Dec 2008 16:34:00 GMT

Lies, damned lies, and statistics!

If you have read my three previous posts (1, 2, 3), you may walk away with an impression that on a drive presented from a high-end enterprise class disk array, Windows file fragmentation does not have a significant performance impact. And I’ve given you empirical data—oh yeah, statistics—to support that impression.

But that is not the whole story! No, I didn’t lie to you. The numbers I presented were solid. It’s just that the story is not yet finished.

In these previous posts on the performance impact of file fragmentation, I presented the I/O throughput data as the evidence. The arguments were valid, especially we did see file fragmentation causing the I/O throughput to degrade in a directly attached storage. But I/O throughput is but one I/O performance metric, and it is not enough to look at the I/O throughput alone.

Let me start with an analogy. So suppose we have an eight-lane super highway going from New York City to Los Angles. As we pumping (okay, driving) more cars from NYC to LA, we take measure at a checkpoint in LA to find out how many cars are crossing that checkpoint every hour, i.e. we are measuring the throughput of the super highway. Now, instead of building the eight-lane super highway straight from NYC to LA, we have it take a detour via Boston. At that same checkpoint in LA, we again measure the throughput. Everything else being equal, we should get the same throughput.

However, for a given car, the trip from NYC to LA would take a lot longer on this detoured highway.

An I/O path is similar to a super highway. While its throughput is an important measure, how long it takes for an I/O request to complete—I/O latency or response time—is also an important measure. The question is, will file fragmentation take your I/O traffic for a detour?

Indeed, empirical test data show that when a file is severely fragmented, the maximum I/O latency of large sequential reads and writes (e.g. 256KB reads and writes) can suffer significantly. The following chart shows the impact of file fragmentation on the maximum I/O latency. The data were obtained from the same tests whose throughputs were reported in Part III of this series of posts.

Clearly, when the test file was fragmented into numerous 128KB disconnected pieces, some of the 256KB reads suffered terribly latency degradation. And if your applications happen to be issuing these I/Os, you would most likely experience a performance degradation regardless whether the I/O path can maintain the same I/O throughput.

Having some valid statistics may seem to add force to an argument, which makes it so much easier to be misleading if the whole story is not told, and technically everything is valid, and nobody is lying. By the way, this is a trick often employed by the vendors, who tend to conveniently ignore the bad news, or intentionally bury it with statistics on the good news. In my book, that would be lies, damned lies, and statistics.

Performance Impact: file fragmentation and SAN – Part III

Linchi Shea — Wed, 10 Dec 2008 16:55:00 GMT

256KB Sequential Reads

In my two previous posts (1, 2), I highlighted the fact that while file fragmentation had a huge adverse performance impact on directly attached storage (DAS), it did not have much, if any, impact on the drive presented from a high end enterprise class disk array. That observation was derived from running disk I/O tests with 1KB sequential writes.

What about other disk I/O workloads? In this post, let look at the test results from running 256KB sequential reads on the same DAS and SAN drives.

To see the behavior in even more extreme, I also ran the tests with the test file fragmented into 60,000 fragments. Each fragment was 128KB in size. So I checked three test scenarios:

The 10GB file was created on a freshly formatted empty drive, thus without any fragmentation at all.
The freshly formatted drive was first filled to the full capacity with 2MB files, and then some of these 2MB files were randomly deleted to make sufficient room for the 10GB test file. In this case, the 10GB test file was fragmented into more than 3000 non-contiguous fragments.
The freshly formatted drive was first filled to the full capacity with 128KB files, and then some of these 128KB files were randomly deleted to make sufficient room for the 10GB test file. In this case the 10GB test file was fragmented into more than 60,000 non-contiguous fragments.

The following chart shows the results of many repeated tests, applying 256KB sequential reads at various load levels.

Again and clearly, as is the case with the 1KB sequential write tests, severe file fragmentation had no impact on the disk I/O performance of 256KB sequential reads on this drive presented from a high end enterprise class fibre channel disk array.

Note that the disk array had a cache that was much larger than 10GB. You may say, that’s cheating and not a fair comparison with a directly attached storage. Well, maybe, but that’s how high end disk arrays work.

Performance impact: file fragmentation and SAN – Part II

Linchi Shea — Mon, 08 Dec 2008 18:49:00 GMT

1KB Sequential Writes on DAS

There were some questions about the use 1KB sequential writes in my previous post to test the performance impact of file fragmentation on a drive presented from a high end enterprise class disk array.

There were two reasons for testing 1KB sequential writes:

· SQL Server may write to a transaction log in 1KB sequential writes.

· File fragmentation can have a devastating impact on 1KB sequential writes on a directly attached drive.

To highlight the contrast, I’m posting the results that I did a quite while ago on a RAID-1 set that consisted of two internal disks. The test setup was exactly the same as described in Performance impact: file fragmentation and SAN – Part I. The only difference was the target disks were now directly attached. The results are summarized as follows:

As the chart shows, the file fragmentation had caused the throughput to drop 33% from ~60MB/sec to ~40MB/sec on this directly attached RAID-1 set.

Nothing is new here. It is worth posting for comparison purposes only.

Performance Impact: file fragmentation and SAN -- Part I

Linchi Shea — Sun, 07 Dec 2008 21:57:00 GMT

1KB Sequential Writes

It’s well known that disk I/O performance can be severely impacted by fragmentation at the file system level. In other words, when a file is allocated space from many small fragments, its performance can be much worse than when its space is allocated from a single contiguous chunk. The impact is most pronounced with sequential I/Os.

Disk defrag tools vendors have been trying very hard to get that message across to you through their aggressive web and email ad campaigns, creating an impression that if you don’t run their file defrag tools regularly, your file system performancewould suffer greatly.

On a traditional directly attached storage, you might as well heed their advice and keep your drives defraged regularly.

However, on drives that are presented from some enterprise disk array over a SAN, the impact of file fragmentation may not be as severe, or not severe at all, and running the defrag tools constantly only serves to burn disk I/Os for nothing or not very much.

To see the impact of file fragmentation on a drive presented from a high end enterprise class disk array, 1KB sequential writes wee tested on a 10GB test file in the following two scenarios:

The 10GB file was created on a freshly formatted empty drive, thus without any fragmentation at all.
The freshly formatted drive was first filled to the full capacity with 2MB files, and then some of these 2MB files were randomly deleted to make sufficient room for the 10GB test file. In this case, the 10GB test file was extremely fragmented.

The following chart shows the results of many repeated tests, applying 1KB sequential writes at various load levels:

Clearly, on this drive, severe file fragmentation had no impact on the performance of 1KB sequential writes.

I don’t mean to suggest that file fragmentation does not have any impact on the I/O performance on any SAN/disk arrays. I simply don’t know if there is a significant impact on systems I have had no experience with. For your particular SAN environment, you have to run tests to find out yourself.

The intent of this blog post is to highlight the fact that the impact of file fragmentation may not be as universal as some defrag tools vendor may want to lead you to believe.