Re: Large Databases redux

On Thu, Mar 22, 2012 at 00:20, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> That, and a good RAID controller with BBU cache will go a long way to
> relieving the pain of fsync.

Well a BBU cache RAID is helpful, but fsyncs are a minor problem in
data warehouse workloads, since inserts are done in large bulks and
commits are rare. And you can run with synchronous_commit=off, since
it's always possible to reload the last batch after a power failure.

On Wed, Mar 21, 2012 at 23:18, Jason Herr <jaherr(at)gmail(dot)com> wrote:
> Single selects on tables need to be 3ms

You've set yourself an impossible target, that's below the average
seek time of 15kRPM disks. For indexed single-row selects on
non-cached data, expect at least a few index page fetches and a heap
fetch, and potentially file system block map lookups. 20ms seems a
more plausible target. But with competing I/O activity, especially
other OLAP/DW queries and bulk data loads, that's still quite
optimistic.

If you have a high cached access correlation and lots of RAM, it might
be possible to keep the *average* below 3ms, but I don't know if you
can bet on that with 2TB of storage.

Regards,
Marti