Bench marking performance or experience using Solid State Disk Drives (SSD) with postgres

Chris Barnes wrote:

Does anyone use solid state drives for postgres?

Has there been any benchmark that states whether mechanical disk
drives out perform solid state drives?

Is there any benefit, they are quite expensive.

Chris Barnes

------------------------------------------------------------------------
Windows Live: Make it easier for your friends to see what you�re up to
on Facebook. <http://go.microsoft.com/?linkid=9691811&gt;

I use a single SSD on one of our servers and it appears to be much more
responsive than a single 15K SAS drive - Thats as scientific as my
testing gets! The database I use is mostly reads.

Careful which SSD you choose because there is a wide variation in
performance.

Howard Cole
www.selestial.com

On Thu, Nov 5, 2009 at 8:46 AM, Howard Cole <howardnews@selestial.com> wrote:

I use a single SSD on one of our servers and it appears to be much more
responsive than a single 15K SAS drive - Thats as scientific as my testing
gets! The database I use is mostly reads.

Careful which SSD you choose because there is a wide variation in
performance.

I have yet to find a anyone that will assert what the write-cycles
these new devices will handle. Our DB is very high write and read
load, and I have no idea how long the SSDs would last. They are still
too expensive for me to buy them to do this experiment.

I certainly hope you have good backups for when your SSD does finally croak.

Chris Barnes wrote:

Does anyone use solid state drives for postgres?
 Has there been any benchmark that states whether mechanical disk drives out perform solid state drives?
 Is there any benefit, they are quite expensive.

I am currently running PostgreSQL 8.4.1 on a Mac Pro 2009 with a RAID
0 array consisting of 2 Intel X-25M 160G disks (50nm version).

Assuming postgresql is running properly (Snow Leopard 64 bit seems to
have created a few hassles) I get the following speed:

1) Up to around 10K random reads/second for small reads that are not CPU limited
2) Up to around 4K random writes/second for smallish writes that are
not CPU limited
3) Around 400MB/second sustained read (with less than 600 read I/Os,
and not CPU limited)
4) Around 140-160MB/second sustained writes
[These are not the drive specs, they are what I observe in "Activity Monitor"]

There is very little difference between sequential I/O and random I/O
speeds for most practical applications, which is wonderful.
Typically, in my normal environment, when postgres is busy, I see 2K
- 3K reads / second, and up to 1K writes / second, as reported by
"Activity Monitor".

However, some things in postgresql run much slower than I would expect
on my machine. pg_dump seems peak at only 60MB/s on the read side.
And "VACUUM FULL" typically grinds along at only around 1MB/s on large
tables. And each transaction seems to cause a delay. Under postgres,
I'm not very happy with my transactions per second - but I don't
really know what the bottleneck is here.

My previous system was Linux MySQL on a RAID 5 array consisting of 6 x
SCSI U320/ 15K disks.
Performance was dominated by the random seek time, which was around
4ms for that system (100us for my current system).

Most non-bulk operations are radically faster on the SSD. It could
well be 40x faster under random I/O, but I haven't benchmarked it.

And I can report that the SSD drives are radically quieter and cooler.
My old system was like a leaf-blower (14 fans, kept the room warm).
I can't even hear my SSDs :)

The specification of my SSD drives is kind of depressing:
http://www.intel.com/design/flash/nand/mainstream/technicaldocuments.htm

"The drive will have a minimum of 5 years of useful life under typical
client workloads with up to 20 GB of host writes per day."

That equates to only 40TB cumulative writes, or only 228 complete
drive rewrites. At that rate, I could wear out the drive in only 6
days of continuous, flat-out writing.

But I'm hoping that the drive will be good for say 10,000 complete
cumulative rewrites, or 1600TB per drive, as modern quality flash
memory supports much more than 10K rewrites. The cost of the drives
has already halved in the few months I have had them, so they should
be virtually free by the time they wear out and need replacement :)

So far I have encountered zero errors on the SSD drives, and SMART
status is OK. The SCSI U320 15K drives have given a few soft errors
over the past few years.

Stephen

On Thu, Nov 5, 2009 at 9:05 PM, Stephen Tyler <stephen@stephen-tyler.com> wrote:

So far I have encountered zero errors on the SSD drives, and SMART
status is OK.  The SCSI U320 15K drives have given a few soft errors
over the past few years.

Have you noticed any fall off in performance as they get re-written a
lot? I'm wondering just how much of the issues with fragmentation
have been fixed versus just putting the problem further into the
future...

On Fri, Nov 6, 2009 at 5:05 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Have you noticed any fall off in performance as they get re-written a
lot?  I'm wondering just how much of the issues with fragmentation
have been fixed versus just putting the problem further into the
future...

There has been no drop-off in sequential read speed (around 400MB/s,
or 200MB/s per drive) that I have noticed. As for write speed, I
haven't benchmarked it with a system that is sufficiently repeatable
to detect minor differences, but it still seems as fast as ever.

I've overwritten my disk many, many times (not erased - just lots of
database inserts and deletes). It should be as completely fragmented
as it is ever going to get.

Right now I'm getting 110MB/s when I copy a large file from one part
of the SSD array to another part (so simultaneous 110MB/s read and
110MB/s write, on an array that is 89% full, and one CPU core at 100%
doing something else, and a database doing around 50 reads and 10
writes per second elsewhere on the same disks). It would probably do
a little better if I could try it on a fully idle system, but I don't
think it would make much of a difference. Halve those numbers for the
speed of a single X-25M.

Stephen