Logfile on a different partition, why?

Question

Post reply

Logfile on a different partition, why?

FreeHansje

SSChampion

Points: 11751
More actions
February 28, 2005 at 4:32 am

#92463

I hope someone can explain the mechanics to me. I am taught that it is best to seperate the datafile from the logfile. If both are placed on the same partition the dischead has to move from 1 place to another since obvious there is a distance to bridge between the 2 files. So let's put the datafile on partition D and the logfile on a different partition, E.
Let us say we have a standard RAID configuration, and I really don't know where partition D and E phisically are. Maybe they are below eachother on the array, but how am I to know? Suppose the diskunits are very large, why could both partitions not be on the same physical unit? Can you tell where a partition is created, thus ensuring more efficient use of write and read actions? Where can I read up on this issue?
TIA,
Hansje
Greetz,
Hans Brouwer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

I find this links very helpful as a starter:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_0m5g.asp

http://www.pcguide.com/ref/hdd/perf/raid/index.htm

http://www.acnc.com/04_00.html

I think your network admin should be able to answer some of your questions, too.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

You are correct in that putting data and log files on separate partitions of the same drive or drive array gains you no benefits over putting them on the same partition. You have the same drive contention.

You want to separate data and log files onto separate physical drives or drive array (if possible).

Ideally, you will want to put a high-available, high-performance database on separate controllers to separate drive arrays. That way, data file writes and log file writes can occur almost simultaneously.

If using a Raid array, you want to use ideally use Raid10. If not, then Raid1 is next best. Try to avoid Raid5 if possible, especially for an OLTP database with heavy write volumes, as the extra overhead of Raid5 writes are a performance killer.

Mark

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 3

All above are good points, and relate mostly to performance.

The primary purpose of a Log file is recovery. If the data and log are on the same physical drives, you might as well turn logging off, not really but.

If anything happened and your drive, or array went bad, your recovery is to the last full backup.

If they are on separate "Physical Drives" either can be used to recover in the event of a failure. And while drives, especially Raid'd have become extremely reliable. I wouldn't chance it.

KlK

FreeHansje SSChampion Points: 11751 More actions · Answer 4

Tnx for the info, ppl. I am aware of the several RAID configs and their pro's and con's. I'm still not sure how to physically distinguish between diskarrays. I'm not a hardwaresavvy person; I guess you must be able to create partitions on different drives, I'm still wondering how to do exactly that. I may have to delve deeper into this.

Tnx again,

Hansje

Greetz,
Hans Brouwer

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 5

I guess you must be able to create partitions on different drives, I'm still wondering how to do exactly that.

Sure you can. But it all depends on how the raid array was setup to begin with. The raid could be configured any number of ways (e.g. all disks in one array, disks 1-4 in an array, disks 5-12 in another, etc). Then, an array can have separate partitions appearing as seperate drives to the OS, or have just one partition.

Usually, on the server, there is some sort of raid utility (depending on make and model) that will show you how the raid array was configured. But if the raid array(s) are on a SAN or shared device, I'm not sure what you could check to see how they are physically structured. Not totally familiar with those types of devices, I would assume there is a utility to check and monitor those types of devices, but it may not be on all the attached servers. In that case, you probably have to check with whoever built or maintains the device.

Mark

Seeni7 SSC Enthusiast Points: 147 More actions · Answer 6

This is the best way to keep the log file away from the data file storage. Let us take this example like you have configured your server RAID 1 and 5.

The first two disks are in RAID 1 (Keep the Log file is here while you creat the database C:\Logfile)

The remaining disks, more than 2 are RAID 5 (While creat the database D:\)

Normally the database takes I/O operation more than log file. If you keep log file under the same data file drive then, it takes added I/O for the log file on the same raid configuration. This will reduce the performance.

Sally