Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

We have a FusionIO card that we use in our production database. We have both the transaction log as well as the database files on that same drive. I know the recommendation is to place the tlog files on a separate physical drive, but given that the I/O on the FusionIO card is significantly better will moving the tlogs to a disk drive create a bottleneck?

I understand that the tlogs are written to sequentially (the reason it is recommended moving them). But if IO difference is significant between the two drives as it is between traditional drives and a FusionIO card, will I still see a benefit from moving the tlogs to a different drive? Or will performance be worse?

The reason I'm asking is because we're running out of space to have both on the same drive and it would be nice if we could use a cheaper drive array for the tlogs instead of having to buy a separate FusionIO card for the tlogs.

BACKGROUND:

The database is a typical web-based workload with much more reading going on than writing - with the exception that when writes happen there is a lot of writing going on. For example, users can upload multiple files with 10,000 or more records in each of them.

share|improve this question

2 Answers

up vote 3 down vote accepted

Let's go through some of the points. You mentioned:

I understand that the tlogs are written to sequentially (the reason it is recommended moving them).

It's also for Recoverability

Moving transaction files to a different array isn't just for performance, it's also for recover-ability. Assume you take a transaction log file backup at 3:45pm and your Fudion IO disk goes bad at 3:55pm before your next transaction log backup. If you have your data and log files on the same drive you are not able to go in after the crash, take a final transaction log backup thus ensuring almost no data loss (in flight transactions would still be lost of course).

Performance

I understand that the tlogs are written to sequentially (the reason it is recommended moving them). But if IO difference is significant between the two drives as it is between traditional drives and a FusionIO card, will I still see a benefit from moving the tlogs to a different drive? Or will performance be worse?

Without proper benchmarking, you could very well lose performance by isolating your log files from a lightning fast PCI-E FusionIO card and moving them to a SAS6 RAID array with magnetic disks. Typically moving log files to a different array also increased performance, but in your case it might decrease it.

You should measure the following perfmon counters after moving the log files over:

DISK SEC/WRITE

DISK SEC/READ

If DISK SEC/WRITE on the log file goes over 5MS it's over what MS recommends. However, if your app doesn't have performance issues with writes, just keep an eye on that counter so you're not caught off guard.

Also, Transaction Logs are sequential in nature but the second you RAID them or put more than 1 accessed file on the drive the physical IO is no longer sequential thus you lose performance on traditional disks. All the FusionIO drives I benchmarked did perform better for sequential reads/writes but random IO was sufficient.

Next you mention:

The database is a typical web-based workload with much more reading going on than writing

Typically most apps are much more read intensive than write intensive, which is why common SQL Server benchmarking tools use 8K IOs @ roughly 70% read / 30% write so that part of your setup is fairly common.

For example, users can upload multiple files with 10,000 or more records in each of them. If your writes aren't slowed down by moving to the new disk then great, but if you're having scaling issues you should look into BULK LOAD. This will minimize the log activity.

Real world benchmarking in a test system is the only way I'd go at it personally. I'd reload a test system with a production workload and see how the drives handle by recording the metrics in SQL Server wait stats and perfmon, then make a decision. Good luck.

share|improve this answer
Also if you're going to move log files off the fusionIO drive, check out the disk sec read/write metrics on that drive. You might want to use the FusionIO tool to 'optimize for reads' if you need more read performance. I'm not sure if a format is required though so proceed with caution and make sure to always benchmark! – Ali Razeghi Dec 11 '12 at 17:34
1  
+1 but disagree with "the second you RAID them" comment. – Mark Storey-Smith Dec 11 '12 at 17:39
Hi Mark, thanks for replying. Let me know what you thought of the 2nd RAID comment, always good to get a different perspective. – Ali Razeghi Dec 11 '12 at 17:40
Thanks for the perfmon counters - I think that will be the most material to solving my problem. I'm confident that with our current load moving the tlogs off the FusionIO will not immediately be a problem, but I just didn't know how I'd know if/when it would become a problem. Unfortunately, I don't have the budget to build a test system to benchmark with - which is why your answer is so helpful. – Mark J Miller Dec 11 '12 at 18:13
Anytime, glad we could help. With proper perfmon and SQL Wait Stat monitoring you won't be caught off guard. Feel free to come back with more questions. Thanks. – Ali Razeghi Dec 11 '12 at 18:15
up vote 2 down vote

I have a few thoughts on this question:

1.) You should generally split out your t-logs and data files for a couple reasons. One of those is performance -They have different characteristics and write patterns/read patterns. The other reason is recoverability - If something happens to your data file, but your log file remains intact you have options for recovery while minimizing data loss that you wouldn't have if you also lost your log file. You also have options available to you if you lose your log but have your MDF file (not as neat.. and hopefully you are taking regular log backups anyway if your recovery needs demand it.)

Note on performance - As Mark Storey-Smith pointed out in his comment, the performance concern is less of a concern on the FusionIO usually. It still may be in some cases, but generally wouldn't be. The recoverability concern is still a concern for sure. This performance concern is called out because that is one of the thought processes you should be in when planning where files go and deciding on the "to split, or not to split" question about log and data files. It is more complex than that lately - for instance a lot of SAN solutions give you a slice of a ton of disks and you are on the same physical drives as everyone else. The performance question, at the end of the day, comes down to "can the storage we have handle the workload we are giving it?" If it can, than it is fine from a performance perspective. If it can't then it is insufficient.

2.) What is causing the space issues? Are your data files growing? Your log files? I trust that you are properly managing your transaction log size as this question discusses and that this is just expected data growth as you grow.

3.) Don't cheap out on your log drive. You may not need to go FusionIO for it and your system may have a lot of reads, but are you logging anything with user connections and activity? Any writes you do go through the transaction log first and that is synchronous to the commit.. In other words, your transaction isn't done until it is confirmed to be in the log file. That means you want your transaction log writes to be fast or transactions can suffer. I'd look at the big picture, and try the array, but don't cheap out on it if you need good log performance - and I've yet to meet an environment that doesn't like good log performance.

share|improve this answer
1  
+1 but disagree with the performance comment, as its FusionIO. – Mark Storey-Smith Dec 11 '12 at 17:37
Good point, Mark. I know of people who have put everything onto a fusionIO software RAID with a lot of storage space. By everything I mean tempdb,logs and data.. It was a warehouse and it performed quite well as I understand.. The recoverability concern still is there, but it was loaded in load windows and the data could be recreated, plus the FusionIO was protected in a software RAID. – Mike Walsh Dec 11 '12 at 17:39
1  
Mike, I appreciate the 3rd point as this was my biggest concern. My question is trying to understand exactly how the performance of the log file will affect me if it's slower than the FusionIO card. Just to clarify, I meant "cheaper" to be relative to a new FusionIO card. – Mark J Miller Dec 11 '12 at 18:08
Of course. I figured you meant that but wnated to be clear. As long as the metrics look good and performance is good you'll be fine. – Mike Walsh Dec 11 '12 at 18:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.