Re: pgbench vs. wait events

On Fri, Oct 7, 2016 at 11:51 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> What happens if you turn fsync off?  Once a xlog file is fully written, it
> is immediately fsynced, even if the backend is holding WALWriteLock or
> wal_insert (or both) at the time, and even if synchrounous_commit is off.
> Assuming this machine has a BBU so that it doesn't have to wait for disk
> rotation, still fsyncs are expensive because the kernel has to find all the
> data and get it sent over to the BBU, while holding locks.

Scale factor 300, 32 clients, fsync=off:
     5  Lock            | tuple    18  LWLockTranche   | lock_manager    24  LWLockNamed     | WALWriteLock    88
LWLockTranche  | buffer_content   265  LWLockTranche   | wal_insert   373  LWLockNamed     | CLogControlLock   496
LWLockNamed    | ProcArrayLock   532  Lock            | extend   540  LWLockNamed     | XidGenLock   545  Lock
 | transactionid 27067  Client          | ClientRead 85364                  |
 

But I'm not sure you're right about the way the fsync=off code works.
I think pg_fsync()/pg_fsync_writethrough()/pg_fsync_no_writethrough()
look at enableFsync and just do nothing if it's false.

>> Second, ClientRead becomes a bigger and bigger issue as the number of
>> clients increases; by 192 clients, it appears in 45% of the samples.
>> That basically means that pgbench is increasingly unable to keep up
>> with the server; for whatever reason, it suffers more than the server
>> does from the increasing lack of CPU resources.
>
> I would be careful about that interpretation.  If you asked pgbench, it
> would probably have the opposite opinion.

Yeah, that's possible.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company