separating tlog and db file when using fusionio

Question

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.

Answer 1

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.

Answer 2

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.