|
Hello, Are there any "generally accepted" best practices for the location of the database Transaction Log file? I've seen recommendations that it should be placed on different media from the database. 1) If this is recommended, are there any "cons" to this? 2) Also, how do I indicate to the server that the Transaction Log file is "somewhere else"? Thank you. |
|
IHMO, one disadvantage is the different location itself, i.e. you do not have the database files in the same directory anymore, which is inherently more complex and makes it possibly more difficult to copy them to another location (say, for testing). When using RAID systems, SANs or virtualized storage (where you possibly do not know about the physical location at all), I don't think it's that important to use a different location for db and log: When configured accordingly, the files may be mirrored by default, thus protecting against failure of one physical media. Additionally, when db and log are stored on different logical volumes, they still may be placed on the same physical storage in the end. - So the challenge is to find out where and how the files are "really" stored and whether the storage system itself already makes use of redundancy (different controllers, links, disks, ...). So I think that general recommendation is primarily meant for databases with storage on classical, non-mirrored, physical storage devices. For other configuration, I think "it depends". More in this similar old discussion: Is "separate physical drive" no longer important on RAID, SAN and/or NAS? 3
I recently had a discussion about this with an system engineer who's opinion I respect. We are virtualized on VMWare, connected to the SAN via iSCSI. The gist of it is that for each iSCSI LUN connection that a host has, it has a limited number of command queues. In theory, putting the data file and transaction file on different LUNs would yield a performance benefit, but neither of was convinced it would be significant.
(28 Jun '17, 08:36)
Bud Durland MRP
Replies hidden
2
It would be really good to get some expert updated advice on this whole subject from SAP. SAN configuration guidelines in particular would be very helpful. We see wildly varying performance on apparently similar set-ups. It's also not at all clear whether the calibrate IO features understand modern disk systems or not.
(30 Jun '17, 08:46)
Justin Willey
3
Yes, more advice would be helpful. In our case it seems to stem specifically from VM configuration. We install the software in the environment we are provided and as you stated, we see wildly varying performance on apparently similar setups. I am often asked what the system requirements for our software are and expected to rattle off cpus, cores, RAM, and disk numbers. The reality is that while our software's needs are quite minimal, there are definitely some VM configurations that slow it down more than others without any apparent common factor.
(30 Jun '17, 09:57)
Siger Matt
We are all Sméagol now.
(30 Jun '17, 16:47)
Breck Carter
|
The very old theory is thus: A very large table that is much bigger than will fit in RAM and is subject to a high volume of random pages being updated will benefit from the data being on one physical drive where the head is free to move randomly and the transaction log on a different physical where the head is free to step ... forward ... sequentially to store the flood of log records. Many factors conspire to make this theory suspect: automatic tuning of checkpoints (data writes), automatic tuning of commits (log writes), multiple cache levels (disk, OS, SA), not to mention RAID, SAN, VM, blah, blah, blah... not only is it impossible to predict disk performance, it is often impossible to fix, or so it feels sometimes :)