Re: autofreeze/vacuuming - avoiding the random performance hit

Entire database. People have talked about using SSDs for data/indices and spinning disks for WAL. However I find having everything on the same disks is good for 3 reasons.

1. The SSD is simply vastly faster than the disks. That means if huge amount of WAL is being written out (e.g. tons of data inserted), WAL isn't lagging at all. Anyone arguing that WAL suits spinning disk because they write fast sequentially should acknowledge that SSDs also write fast sequentially - considerably faster.

2. By having extra 'fsync' events, IO is less bumpy. Every time wal is written out, all your buffers are getting flushed out (in principle), which helps to avoid huge IO spikes.

3. Simpler setup, less volumes to worry about in linux or disk types to manage. For example, we only need spare SSDs in the hotspare bay and on the shelf. Even a single HDD for wal requires a mirrored HDD, plus a hotspare (that's 3 bays gone from e.g. 8), plus some more on the shelf... all to get worse performance.

Our DBs have been a total dream since I put SSDs everywhere. It got rid of every throughput/latency/io spike problem. The only thing I'd do differently today is that I'd buy intel ssds instead of the ones we chose; and preferably a NVMe direct connect with software raid in place of hardware raid and sata.

Graeme Bell.

On 28 Jul 2015, at 17:51, Wei Shan <weishan(dot)ang(at)gmail(dot)com> wrote:

> Did you put your entire database on SSD or just the WAL/indexes?
>
> On 28 July 2015 at 23:39, Graeme B. Bell <graeme(dot)bell(at)nibio(dot)no> wrote:
> Some of you may have had annoying problems in the past with autofreeze or autovacuum running at unexpected moments and dropping the performance of your server randomly.
>
> On our SSD-RAID10 based system we found a 20GB table finished it's vacuum freeze in about 100 seconds. There were no noticeable interruptions to our services; maybe a tiny little bit of extra latency on the web maps, very hard to tell if it was real or imagination.
>
> If auto-stuff in postgresql has been a pain point for you in the past, I can confirm that SSD drives are a nice solution (and also for any other autovacuum/analyze type stuff) since they can handle incoming random IO very nicely while also making very fast progress with the housekeeping work.
>
> Graeme Bell
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
> Regards,
> Ang Wei Shan