SQL Server Log Shipping: Where is the hottest IO during RESTORE LOG: To db or to log?

Question

Using SQL Server 2008.

I have used SQL log shipping for years. Works great.

Working with a new standby server (recipient of log shipping), I am seeing very poor log restore performance, and wonder: Of the MDF and LDF, is one hit particularly harder than the other during log restore?

Background: With dedicated hardware, I have no problem with this area. (To be fair, I have always been unhappy with the ldf size on the target... but what are ya going to do with a db in restore state?)

I am now experimenting with a cloud server and block storage. So far.... I am rather underwhelmed with the IOPS of block storage. The log restore operation is really slow (our main db server puts out a log file every 4 mins, and it takes 2+ mins to restore each one on the target!).

So I am debating whether:

• get a faster block storage device (mucho $$) --- But this only makes $$ sense for either the LDF or MDF... If I have to do both, it is just too expensive (ergo my question)
• figure out another solve for SQL Server in the cloud
• Forget the cloud for SQL Server, 'cause the iops just aren't there...

Further digging around uncovers: Temporary IO boost for an MSSQL server

Which indicates that the hot IO is going to be to the LDF... and that would be the spot to optimize... (though "restore log" is not explicitly the agenda of this thread).

Are we agreed that boosing the IOPS of the LDF disk should boost RESTORE LOG performance?

Thank you!

Answer 1

The data is in:

Improving speed of the LDF storage makes a dramatic improvement in log restore time.

Thank you all for the input!

What I did:

I am using Rackspace Cloud servers. Not their "SQL Server" instances, just plain jane Win 2008 images, that I load SQL 2008 onto. For storage I use Rackspace block storage. I also evaluated Amazon EC2 with EBS (the Amazon block storage).

The core questions I was attacking:

1) Is it viable to run SQL Server on a cloud server for a 300+ GB database?

2) Is there benefit for the extra spend to get the Rackspace SSD based block storage? (It is rather pricey).

The methodology I used:

I did two kinds of testing:

i) SQL IO test tools, to measure disk performance of the block storage, and to compare to dedicated hardware (both "on the metal" and in virtual machines)

ii) An actual restore of a big database, and automated log shipping from the production database.

Answers:

a) Rackspace regular block storage is much faster than Amazon AWS (EBS). Much much.

b) Based on my measurements and experience, I would not put a production SQL Server DB onto Amazon EBS. There are just too many "pauses" and other bad performance issues. Note that SQL Server will work on EC2 w EBS -- many folks do it -- just that based on my measurements there will be IO delays (a lot of them). This will particularly impact RESTORE LOG operations, and all write-intensive operations (reads can be cached... but LDF writes are not cached...).

c) Rackspace cloud with regular block storage can absolutely be used for SQL Server. It is not mind blowing fast, but it is great for reads, and OK for writes.

d) Rackspace SSD block storage makes a dramatic difference for log restore operations, and (I extrapolate here), any write-intensive use of SQL server will get a big boost.

So the bad news is: When you are pushing around a 500gb database, you can't get the performance of a $150K SAN for $300/month.

The good news is: You can get really decent performance, that matches or beats RAID5 from a virtual machine on dedicated hardware, for about $480/month

The Data

Here is the data of the log restore operation, showing first the duration of log restore operations on the slower system, then on the faster system, and then the difference. This log restore is happening every 10 mins on both systems.

Answer 2

Yes the LDF file will be harder on IO during a log shipping restore operation. So if log shipping is a major concern give the ldf disk more IOPS.

We had an issue with log shipping a few months back. It was taking 2 hours to restore a 2mb log file. Turns out the issue was our log file had an extreme number of vlfs. We rolled the log forward and then shrunk it. After that we regrew it in 8gb chunks to the desired size. This allowed us to get a better more reasonable number of VLFs.

Answer 3

As @Brandon pointed out, high number of VLFs will drastically slow down a log file restore. I don't think throwing IOPS will resolve the issue unless you have metrics to back this. I have had a 8 disk RAID10 array trying to restore a tlog file that had too many VLFs (thousands) and IO was fine, but the internal process to open and close VLFs was a bottleneck.

If you check the restore status you'll see it stuck at 99 or 100% for a long time. Check the WAITING_TASK_DESC column in sys.dm_os_waiting_tasks to see what the wait time actually is. It'll tell you if it's IO, or something else relating to VLFs. Let's grab those metrics first. Also, what are your perfmon metrics like during a restore on the tlog drive?

This recent blog by Kevin Kline I ran across on twitter might be very useful too: http://sqlblog.com/blogs/kevin_kline/archive/2012/11/14/quick-tip-speed-a-slow-restore-from-the-transaction-log.aspx

Snippet from Kevin's post:

Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement. For example, if you set MAXTRANSFERSIZE=1048576, it'll use 1MB buffers.

If you change the MAXTRANSFERSIZE, keep an eye on the PerfMon objects for Buffer Manager and Readahead IO. You may also wish to keep an eye on LOGBUFFER wait stats.

So, let's see what your wait stats are and perfmon metrics then let's see how we can optimize this!