Re: PostgreSQL 9.2 benchmarks

[Matthew Dillon <dillon@xxxxxxxxxxxxxxxxxxxx>]

:...
:tps = 9027.509551 (including connections establishing)
:tps = 9040.981235 (excluding connections establishing)
:Elapsed time: 64.54 seconds.
:
:-- Adaptive mutex spin
:
:Total%  Count   Time/ms          Lock                       Caller
:------ ------- --------- ---------------------- ------------------------------
: 90.69 7397761  86424.40 fffffe81301694c0       <all>
: 84.74 7397748  80756.67 fffffe81301694c0       uvm_fault_internal+62e
:  5.95      12   5667.69 fffffe81301694c0       uvm_unmap_remove+245

    I would suspect NetBSD is bottlenecking on VM faults.  No expert, but the
    symbols suggest it.  This would be the postgress servers (-j option
    to pgbench) each mapping the huge shm segment and each faulting pages in
    pretty much at the maximum rate.

    These shm faults are purely cpu-bound, and when the database is hot the
    postgres server lseek/read()'s are also purely cpu-bound.  So the whole
    mess is cpu-bound.  It's easy to calculate. 6GB = ~1.4M 4K pages x number
    of server processes (i.e. 80) = ~117 million faults over the life of
    the test, worst case.

    The postgress servers fork... their page table directory is NOT shared.
    So this also means ~12MB worth of page table pages x 80 server processes
    which comes to ~9GB worth of page table pages.  It's one of those
    horrid VM scaling issues.

    Running the tests on DFly the VM fault rate starts out ridiculously
    high... something like 2 million VM pages are faulted in per second,
    dropping to ~400K faults/sec after 15 seconds or so.

    --

    Another possibility for the stall would related to how NetBSD handles
    page table management structures.  Hence why kern.ipc.shm_use_phys
    is turned on (in FreeBSD, NetBSD, *and* DragonFly)... not sure how
    UVM works there by on FreeBSD and DragonFly that option allows the VM
    system to avoid having to allocate PV entry structures to manage the PTE's.

    However, the VM system *STILL* has to manage the page table pages.

						-Matt