On Mon, 2010-05-17 at 14:46 -0700, Josh Berkus wrote:
> Jan, Marko, Simon,
>
> I'm concerned that doing anything about the write overhead issue was
> discarded almost immediately in this discussion.
Only thing we can do to write overhead _on_master_ is to trade it for
transaction boundary reconstruction on slave (or special intermediate
node), effectively implementing a "logical WAL" in addition to (or as an
extension of) the current WAL.
> This is not a trivial
> issue for performance; it means that each row which is being tracked by
> the GDQ needs to be written to disk a minimum of 4 times (once to WAL,
> once to table, once to WAL for queue, once to queue).
In reality the WAL record for main table is forced to disk mosttimes in
the same WAL write as the WAL record for queue. And the actual queue
page does not reach disk at all if queue rotation is fast.
> That's at least
> one time too many, and effectively doubles the load on the master server.
It doubles the "throughput/sequential load" to fs cache but does much
less for "number of fsyncs" as all those writesare done within the same
transaction and only WAL writes need to get to disk.
In my unscientific tests with pgbench adding FK's between the pgbench
tables + adding PK to log table had bigger performance impact than
setting up replication using londiste.
> This is particularly unacceptable overhead for systems where users are
> not that interested in retaining the queue after an unexpected shutdown.
Users not needing data after unexpected shutdown should use temp tables.
If several users need the same data, then global temp tables should be
implemented / used.
> Surely there's some way around this? Some kind of special
> fsync-on-write table, for example?
This is sure to have a large negative performance impact. WAL was added
to postgreSQL for just this - to get rid of fsync-on-commit
(fsync-on-write is as bad or worse than fsync-on-commit)
> The access pattern to a queue is
> quite specialized.
A generic solution for such users would be implementing Global Temporary
Tables (which need no WAL), and then using these for non-persistent GDQ
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
