Re: FAQ Q - Mailing list pgsql-admin

Tom Lane wrote:
> However, the big boys running on the same hardware are going to have the
> same problem.
>
> If you can figure out how to configure your drives not to report write
> complete until it's really complete, then you can feel secure with
> either Postgres or the big boys.  Or you can buy a UPS and make sure you
> are configured to shut down before the UPS runs out of steam.
>
> In no-fsync mode we report commit after pushing the data out to the
> kernel, but we don't try to force the kernel to push it out to disk.
> So you are safe against a Postgres crash, but not against kernel or
> hardware failure.  Your transaction could be lost if the system dies
> before the kernel gets around to syncing it out to disk (typically
> 30 sec max, on most Unixen).
>
> > Is the FAQ being too generous in speed and too unfair in recoverability to
> > the commercial offerings here?
>
> I didn't write the FAQ and won't take responsibility for its claims
> about commercial databases.  I really don't know what tradeoffs they
> offer in this area.

I have updated the FAQ to remove the reference to fsync, because with WAL it
isn't as much of an issue:

Performance

PostgreSQL has performance similar to other commercial and open source
databases. it is faster for some things, slower for others. In
comparison to MySQL or leaner database systems, we are slower on
inserts/updates because of transaction overhead. Of course, MySQL does
not have any of the features mentioned in the Features section above. We
are built for reliability and features, though we continue to improve
performance in every release. There is an interesting Web page comparing
PostgreSQL to MySQL at http://openacs.org/why-not-mysql.html

>
> > Sybase at least just tries to avoid writing tranlogs to disk except in
> > batches, so when you say COMMIT, it pauses until X amount of time passes
> > or Y number of committed transactions have piled up, then it writes those
> > trans to disk. But your client is blocked on the COMMIT until the
> > transaction actually makes it to disk.

Informix has two modes, buffered logging, and unbuffered logging.  The
PostgreSQL setup is unbuffered logging (from
http://nasis.nrcs.usda.gov/archive/logbuf.html#LoggingStatus):

Unbuffered Logging

An unbuffered logging status means that the database writes each
transactions result to the logical log file as soon as that transaction
completes. This mode increases the amount of data that might be
recovered during the fast recovery phase. All the transactions that make
it to disk can be recovered. It is slower because it requires more
frequent writes to the database and it takes more space because all
changes are logged.

Buffered Logging

Buffered logging does not actually reduce the amount of data that is
written to the logical log it just reduces how often changes are written
to disk. With buffered logging the Informix OnLine server waits until an
internal memory buffer is nearly filled before writing the data to disk.
This means that even though buffered logging will eventually write the
same data as unbuffered logging it will take fewer disk access. That
means that the logging should take less time. Buffered logging is not
quite as safe as unbuffered logging. As we mentioned only the
transactions on the disk will be recovered during fast recovery. Even if
a transaction has been completed it may not be on the disk with buffered
logging.


> We have that too, although it's not on by default, and probably needs
> further tuning work.

Not sure we do.  We have something that delays the transaction hoping
for another one to come along, but we don't have something that writes
out WAL after X seconds or X transactions.  I think we need something
like that.

In fact, if we had it, I think we could remove the fsync=off option
entirely.  With fsync=off, an OS crash means you have to restore from
disk.  With WAL writes/fsync every 5 seconds, at least an OS crash
brings the system back to a stable state, and the performance with
fsync=off would be nearly identical.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026