Why would anyone want to put DB log on a SSD?

Question

I keep hearing about people who want to get SSD for their databases. But instead of putting their tables on SSD, quite often people want to put their DB log on SSD while leaving their tables on regular hard drives.

But why would anyone want to do that?

Log uses sequential writes. And SSD's sequential IO speed isn't any faster than your regular disks. So putting log on SSD wouldn't provide any performance gain.

The one area at which SSD is much faster than regular disks is random IO. So shouldn't the sensible thing to do is to put your tables on SSD while leave the log on regular disks?

Am I missing something?

Answer 1

Yeah, transaction logs usually* have a constant but very low throughput, when compared to the whole database. But the throughput is absolutely first priority, when it is delayed, all updates to the database have to wait. It is economically beneficial to write logs to a mirror, or even to a RAID5, but only as long as you can keep your writes sequential. If you share the disks with anything else, be it a data file, an index, or collection of porn movies, you are putting your performance at risk.

In larger environments, the thing is usually you have multiple database instances centralized on the same storage. Of course, you don't want to put the logs together on the same set of drives, as multiple sequential writes combined make in fact one big random-like write.

One option is to dedicate a separate mirror (two disks) to each log sequence. But another option - and economically viable in some cases - is to keep things simple and use single SSD for all the log files, so you have separate device and data path, with good random performance. In effect, any of multiple logs will never delay any database. I've seen such setup, but I don't recommend it (SSD's non-volatility is too fragile concept for me).

[*] - some engines mix redo logs with undo information to complicate things (e.g. MS SQL Server)

Answer 2

You always want to flush your logs out to non-volatile storage (be that platters or flash), and SSDs based on DRAM can be faster to write to than harddisks.

Transaction logs should almost always be cached in memory for performance, so read speed doesn't really come into it.

So you should be able to get a higher write throughput from putting transaction logs on SSD.

As an aside, I once experimented with putting MySQL innodb transaction logs on a tmpfs, and got a 10-20% performance boost compared to disk.