Re: pgbench vs. wait events

On Fri, Oct 7, 2016 at 11:51 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)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