Re: commit performance anomaly

On Thu, 26 Mar 2009, Ed L. wrote:

> But I'm curious why 'commit' statements (as well as certain update
> statements) seem to have actually degraded (1ms vs 5ms on avg, 2ms vs
> 14ms in the 95th percentile, etc).

When you commit something, it writes information to the write-ahead log
(WAL) and then issues a fsync call to the filesystem involved to confirm
that the data has made it onto disk, which usually percolates down onto a
physical write to disk or a write cache. It sounds like your newer system
doesn't have as fast of a path between writes and a useful cache here.

Since you mentioned moving onto a SAN, I wouldn't be surprised if much of
the additional latency is just overhead moving bits over whatever its I/O
interface is. I hope you didn't drink too much SAN vendor Kool-Aid and
connect with iSCSI or something similarly slow (I doubt that because
you're only seeing medium scale degredation). Direct connected disks
always have lower latency for short writes, the only time a SAN can pull
ahead of them is when you've doing something that can utilize a lot of
disks at once.

You might be able to tune the SAN to optimize for faster write performance
though. SAN vendors seem completely incompetant at producing out of the
box tunings that work well for database use (I feel a RAID5 rant brewing).

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD