Database data and log allocation on raw device

[John Guster]

using raw device for database with async driver, is there any performance concern in locating storage space for data and log? i.e. disks with DB data should not mixed with disks with DB log, is that true?
Thank you.

[TTr]

Usually you separate the data and log devices not only for performance but for safety. One important thing that is often overlooked is that these data and log devices, not only they should be separate on the host, they should stay separate all the way down to the storage systema nd the actual disk spindles.
In many cases the data and log devices are logical drives from the same array group of spindles in a storage array.

[John Guster]

can you detail the reason? it is not file system, but raw device for database.

[Jeeshan]

Hi John

Distributed with different disks can increase the performance.

and your thought is true in sense.

!!!

[TTr · Accepted solution]

> can you detail the reason?

In simple terms, your data devices are where you do the actual data I/O, the log devices is where you record each I/O as a transaction. So you do not want to do data I/O (DATA) and at the same time record the transactions (LOG) in the same raw device.

Safety comes into play when a device fails. If you have data and log in the same device that failed, you have lost data. Otherwise, if you lose the log, your data is is still there, if you lose the data, you can recovery it from backups and apply the log to make it current.

My other comment "not only they should be separate on the host, they should stay separate all the way down to the storage system" refers to a case where a DATA device and a LOG device might be logical devices on the same physical disk(s). In this case if the physical disk fails, both DATA and LOG are gone.

[John Guster]

Thanks for your responses.
for recovery reason, data disks are better not mixed with log disks. Good point.