Re: PostgreSQL as a local in-memory cache

От: Bruce Momjian
Тема: Re: PostgreSQL as a local in-memory cache
Дата: ,
Msg-id: 201006291845.o5TIjMh24107@momjian.us
(см: обсуждение, исходный текст)
Ответ на: Re: PostgreSQL as a local in-memory cache  (Tom Lane)
Ответы: Re: PostgreSQL as a local in-memory cache  (Jignesh Shah)
Список: pgsql-performance

Скрыть дерево обсуждения

PostgreSQL as a local in-memory cache  ("", )
 Re: PostgreSQL as a local in-memory cache  ("", )
  Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
   Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
   Re: PostgreSQL as a local in-memory cache  (Dimitri Fontaine, )
    Re: PostgreSQL as a local in-memory cache  (Tom Lane, )
     Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
      Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
       Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
      Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
       Re: PostgreSQL as a local in-memory cache  (Dave Page, )
        Re: PostgreSQL as a local in-memory cache  (Tom Lane, )
         Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
         Re: PostgreSQL as a local in-memory cache  (Dimitri Fontaine, )
       Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
     Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
      Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
       Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
        Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
         Re: PostgreSQL as a local in-memory cache  ("Kevin Grittner", )
         Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
          Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
           Re: PostgreSQL as a local in-memory cache  (Tom Lane, )
            Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
             Re: PostgreSQL as a local in-memory cache  (Jignesh Shah, )
              Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
               Re: PostgreSQL as a local in-memory cache  (Brad Nicholson, )
                Re: PostgreSQL as a local in-memory cache  (Bruce Momjian, )
               Re: PostgreSQL as a local in-memory cache  (Jignesh Shah, )
                Re: PostgreSQL as a local in-memory cache  (Greg Smith, )
    Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
     Re: PostgreSQL as a local in-memory cache  (Matthew Wakeling, )
      Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
       Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
        Re: PostgreSQL as a local in-memory cache  (Rob Wultsch, )
         Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
          Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
           Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
            Re: PostgreSQL as a local in-memory cache  ("Joshua D. Drake", )
             Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
              Re: PostgreSQL as a local in-memory cache  ("A.M.", )
               Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
            Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
             Re: PostgreSQL as a local in-memory cache  (Pavel Stehule, )
            Re: PostgreSQL as a local in-memory cache  ("Joshua D. Drake", )
      Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
    Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
     Re: PostgreSQL as a local in-memory cache  (Tom Lane, )
   Re: PostgreSQL as a local in-memory cache  (Greg Smith, )
   Re: PostgreSQL as a local in-memory cache  (Robert Haas, )
 Re: PostgreSQL as a local in-memory cache  (Dave Crooke, )
  Re: PostgreSQL as a local in-memory cache  (Craig James, )

Tom Lane wrote:
> Bruce Momjian <> writes:
> >>> I asked on IRC and was told it is true, and looking at the C code it
> >>> looks true. ?What synchronous_commit = false does is to delay writing
> >>> the wal buffers to disk and fsyncing them, not just fsync, which is
> >>> where the commit loss due to db process crash comes from.
>
> >> Ah, I see.  Thanks.
>
> > I am personally surprised it was designed that way;  I thought we would
> > just delay fsync.
>
> That would require writing and syncing to be separable actions.  If
> you're using O_SYNC or similar, they aren't.

Ah, very good point.  I have added a C comment to clarify why this is
the current behavior;  attached and applied.

--
  Bruce Momjian  <>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +
Index: src/backend/access/transam/xact.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.291
diff -c -c -r1.291 xact.c
*** src/backend/access/transam/xact.c    13 May 2010 11:39:30 -0000    1.291
--- src/backend/access/transam/xact.c    29 Jun 2010 18:33:47 -0000
***************
*** 1028,1034 ****
      if (XactSyncCommit || forceSyncCommit || haveNonTemp)
      {
          /*
!          * Synchronous commit case.
           *
           * Sleep before flush! So we can flush more than one commit records
           * per single fsync.  (The idea is some other backend may do the
--- 1028,1034 ----
      if (XactSyncCommit || forceSyncCommit || haveNonTemp)
      {
          /*
!          * Synchronous commit case:
           *
           * Sleep before flush! So we can flush more than one commit records
           * per single fsync.  (The idea is some other backend may do the
***************
*** 1054,1060 ****
      else
      {
          /*
!          * Asynchronous commit case.
           *
           * Report the latest async commit LSN, so that the WAL writer knows to
           * flush this commit.
--- 1054,1065 ----
      else
      {
          /*
!          * Asynchronous commit case:
!          *
!          * This enables possible committed transaction loss in the case of a
!          * postmaster crash because WAL buffers are left unwritten.
!          * Ideally we could issue the WAL write without the fsync, but
!          * some wal_sync_methods do not allow separate write/fsync.
           *
           * Report the latest async commit LSN, so that the WAL writer knows to
           * flush this commit.


В списке pgsql-performance по дате сообщения:

От: "Kevin Grittner"
Дата:
Сообщение: Re: ideal storage configuration
От: Jignesh Shah
Дата:
Сообщение: Re: PostgreSQL as a local in-memory cache