March 29, 2009 at 11:10 am
Hi,
I fully understand the benefits of separating log & database data on different physical arrays to avoid mixing random & sequential IO. While this is logical for a single database, how does it hold up when hosting multiple databases?
On our current server, we've got about 160 different databases. About 5 of those are pulling the bulk of the load, 30 intermediate and the rest don't really see much traffic.
If we have that many log files being written to, won't this as a result turn into mainly random writes, instead of sequential? Will it still be more beneficial to split the log data from the normal data? At the moment I can basically choose between a single 4 disk RAID10 array or two RAID1's. Unless recommended otherwise, I'm going for the dual RAID1's, but I can't stop considering whether I'll really gain much from it.
Thanks.
Mark S. Rasmussen
Blog: improve.dk
Twitter: @improvedk
Internals geek & author of OrcaMDF
March 29, 2009 at 11:46 am
mark (3/29/2009)
Hi,If we have that many log files being written to, won't this as a result turn into mainly random writes, instead of sequential?
No, It won't log files are per database. does not matter how many log files you have still the sql server will write log file sequentially
Will it still be more beneficial to split the log data from the normal data?
Yes, no doubt it that both to increase performance and also for recovery purposes.
March 29, 2009 at 11:54 am
Krishna (3/29/2009)No, It won't log files are per database. does not matter how many log files you have still the sql server will write log file sequentially
Because log files are per database, and the different databases will have log files spread out on the disk, won't that mean the disk will spend a lot of time seeking to the different log file locations before writing? I suppose the performance advantage still outweighs it.
Mark S. Rasmussen
Blog: improve.dk
Twitter: @improvedk
Internals geek & author of OrcaMDF
March 29, 2009 at 1:42 pm
No it won't. If you have enough memory that will sort out the problem. The only time transaction log will hit the performance is when there is Fragmentation and also on the huge number of VLF's.
As long as you place it on faster disks and configured properly it's a complementary to your database.
March 29, 2009 at 3:12 pm
Check out Kimberley's blog for a better management of Transaction log :
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
March 29, 2009 at 3:59 pm
Krishna (3/29/2009)
Check out Kimberley's blog for a better management of Transaction log :http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Thanks for the link. However, Kimberly's post, as well as most others, don't take the issue of having multiple databases into account. The main incentive for having a dedicated log disk is that the disk head will almost never move, since the only writes going on will be sequential compared to the last log write, since it's the same file - provided the log write was correlated to the same database.
If we have 100 databases, we will have at least 100 MDF datafiles, as well as at least (won't have more than one per DB, but technically there could be more) 100 LDF files as well. Each log will have a number of VLF's, but let's assume VLF fragmentation is not an issue.
Now, if all activity occurs in database A, all is good. The diskhead will be at a specific location inside a VLF in A's log file. All writes at this point are sequential. If database B suddenly commits a transaction, the diskhead will have to move to B's log file location, which may be a completely different location on the disk, physically. When A commits a transaction again, the diskhead will have to move back to A's log file. Multiple A & B with 50 to get 100 databases, and the sequential writes suddenly start to become more and more random due to the large amount of log files.
I do appreciate that the separate disks for log and data will still be better, since the chances of the log disk performing sequential writes decrease as activity across databases (and within the same databases, if it's data operations) increase. But am I not right in assuming that there will be diminishing returns in regards of performance, as the number of databases, and hence log files, increases?
I'd love to be corrected, I like performance 🙂
Mark S. Rasmussen
Blog: improve.dk
Twitter: @improvedk
Internals geek & author of OrcaMDF
March 29, 2009 at 4:02 pm
Krishna (3/29/2009)
No it won't. If you have enough memory that will sort out the problem. The only time transaction log will hit the performance is when there is Fragmentation and also on the huge number of VLF's.As long as you place it on faster disks and configured properly it's a complementary to your database.
How does memory help performance when it comes to writing the transaction log? I can see it helps for checkpoint performance since increased memory means larger chances of performing sequential data writes, but since log happens synchronously to disk, I can't see how memory affects it.
Mark S. Rasmussen
Blog: improve.dk
Twitter: @improvedk
Internals geek & author of OrcaMDF
March 29, 2009 at 4:26 pm
You're right Mark; what you're describing is going to perform sub-optimally because it's going to behave like a fragmented disk. If you've got the space/budget you would get the best performance if you separated those 5 busy log files off to separate physical drives.
That sort of cost probably isn't going to be popular with management, so you might want to do some stats-gathering first. I'd suggest doing some PerfMon traces, looking at the the physical I/O stats, in parallel with some sqltrace traces to track what changes are getting written and looking specifically for I/O waits. If you've got the opportunity some sort of "I/O flood" test would tell you what the maximum throughput of your disk is, but that may not be possible if you've got a 24*7 system.
Once you've got some stats, and hopefully some sort of "maximum I/O" figure, you can then use that to establish what impact the shared log drive is causing. You may find that it's not necessary to add 5 drives: adding one or two drives and "averaging out" the log files across all the available drives may be sufficient.
The other factor that needs to be weighed up is what the impact you're experiencing is. If what you have is meeting business needs you'll find it difficult to convince management of the value of the expense of adding new drives. With any reasonable sort of write caching at the controller then small inserts/updates probably won't be impacted enough to notice and it will be only large I/Os (eg. an update of all rows in a large table) that will be impacted enough for an end-user to notice any real difference.
March 29, 2009 at 4:41 pm
You're right Mark; what you're describing is going to perform sub-optimally because it's going to behave like a fragmented disk. If you've got the space/budget you would get the best performance if you separated those 5 busy log files off to separate physical drives.
At the moment budget does not allow more than four disks either in 2xRAID1 or a single RAID10. But at our current numbers, that's also plenty, I'm just looking for insight on how to plan for the future.
That sort of cost probably isn't going to be popular with management, so you might want to do some stats-gathering first. I'd suggest doing some PerfMon traces, looking at the the physical I/O stats, in parallel with some sqltrace traces to track what changes are getting written and looking specifically for I/O waits. If you've got the opportunity some sort of "I/O flood" test would tell you what the maximum throughput of your disk is, but that may not be possible if you've got a 24*7 system.
We're getting around 225IOPS from a single RAID1 array, and roughly double that for the RAID10. These are for 8kb random writes with a 1GB test file, SAN cache is about 400 megs. I don't have the numbers with me right now, but we can easily handle our current load with those measurements.
Once you've got some stats, and hopefully some sort of "maximum I/O" figure, you can then use that to establish what impact the shared log drive is causing. You may find that it's not necessary to add 5 drives: adding one or two drives and "averaging out" the log files across all the available drives may be sufficient.
I'm finding that 90% of our disk IO is for the logs, almost all data is stored in memory, and changes are relatively few so checkpoints doesn't take many resources. However, sometimes we do make some large scans for archived statistics data - and these may block our log operations and thereby slow other queries. This scenario should be avoidable by splitting up the arrays.
The other factor that needs to be weighed up is what the impact you're experiencing is. If what you have is meeting business needs you'll find it difficult to convince management of the value of the expense of adding new drives. With any reasonable sort of write caching at the controller then small inserts/updates probably won't be impacted enough to notice and it will be only large I/Os (eg. an update of all rows in a large table) that will be impacted enough for an end-user to notice any real difference.
We're a small enough business for me to both do management, DBA and storage, so there's really no management to convince - though there is a budget. Thus far we've been using local RAID1 storage. We just invested in our first single-shelf SAN, and I've only dedicated four disks for the database. Considering the very beneficial added cache, four drives versus the previous two, we're not going to have any performance problems as is at the moment. However, I'd like to postpone adding an extra shelf for as long as possible, and that's why I'd really like to make the best informed decision at this point.
Mark S. Rasmussen
Blog: improve.dk
Twitter: @improvedk
Internals geek & author of OrcaMDF
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply