I did a analyze in stages on both.
And Yes both are compiled.
This is the configure command (change 10.6 for PG10)
./configure --prefix=/usr/local/pgsql11.2
See attached perf report. The difference seems to be all in this line, but
not sure :
+ 26.80% 0.00% 222 postmaster [kernel.kallsyms]
[k] system_call_fastpath
I am using CentOS 7
With Centos I am using this profile for tuned-adm
[root@STAGING-CMD1 ~]# tuned-adm active
Current active profile: latency-performance
Il giorno sab 2 mar 2019 alle ore 20:41 Thomas Munro <[email protected]>
ha scritto:
> On Sat, Mar 2, 2019 at 5:02 AM Ray O'Donnell <[email protected]> wrote:
> > On 01/03/2019 15:01, Nicola Contu wrote:
> > > Hello,
> > > is there any reason why I am getting worse results using pgsql11.2 in
> > > writing comparing it with pgsql 10.6?
> > >
> > > I have two Instances, both just restored, so no bloats.
> > > Running read queries I have pretty much same results, a little bit
> > > better on pg11- Running writes the difference is in favour of 10.
> >
> > Did you run ANALYZE on the databases after restoring?
>
> If you can rule out different query plans, and if you compiled them
> both with the same compiler and optimisation levels and without
> cassert enabled (it's a long shot but I mentioned that because you
> showed a path in /usr/local so perhaps you're hand-compiling 11, but
> 10 came from a package?), then the next step might be to use a
> profiler like "perf" (or something equivalent on your OS) to figure
> out where 11 is spending more time in the write test?
>
> --
> Thomas Munro
> https://enterprisedb.com
>
PGSQL11
+ 26.80% 0.00% 222 postmaster [kernel.kallsyms]
[k] system_call_fastpath
+ 13.46% 0.04% 751 postmaster [kernel.kallsyms]
[k] sys_futex
+ 13.43% 0.00% 782 postmaster [kernel.kallsyms]
[k] do_futex
+ 12.89% 0.00% 0 postmaster [unknown]
[k] 0000000000000000
+ 9.09% 0.00% 0 swapper [kernel.kallsyms]
[k] start_cpu
+ 9.09% 0.14% 2860 swapper [kernel.kallsyms]
[k] cpu_startup_entry
+ 9.09% 0.00% 0 swapper [kernel.kallsyms]
[k] start_secondary
+ 8.97% 0.00% 389 postmaster [kernel.kallsyms]
[k] page_fault
+ 8.97% 0.00% 687 postmaster [kernel.kallsyms]
[k] do_page_fault
+ 8.97% 0.73% 6471 postmaster [kernel.kallsyms]
[k] __do_page_fault
+ 8.66% 0.14% 1648 postmaster libpthread-2.17.so
[.] sem_post@@GLIBC_2.2.5
+ 8.40% 0.70% 13567 postmaster [kernel.kallsyms]
[k] get_futex_key
+ 8.08% 0.00% 0 postmaster [unknown]
[k] 0x0000000000000080
+ 8.08% 0.93% 642 postmaster libpthread-2.17.so
[.] do_futex_wait.constprop.1
+ 7.87% 0.19% 2666 postmaster [kernel.kallsyms]
[k] futex_wake
+ 7.68% 0.19% 9343 postmaster [kernel.kallsyms]
[k] handle_mm_fault
+ 7.34% 0.00% 207 swapper [kernel.kallsyms]
[k] arch_cpu_idle
+ 7.34% 0.00% 979 swapper [kernel.kallsyms]
[k] cpuidle_idle_call
+ 7.15% 0.00% 2671 postmaster [kernel.kallsyms]
[k] handle_pte_fault
+ 5.87% 0.00% 6 postmaster libc-2.17.so
[.] __GI___setsid
+ 5.87% 0.00% 9 postmaster [kernel.kallsyms]
[k] sys_setsid
+ 5.87% 0.00% 15 postmaster [kernel.kallsyms]
[k] sched_autogroup_create_attach
+ 5.41% 5.41% 48199 postmaster postgres
[.] hash_search_with_hash_value
+ 5.40% 5.40% 44439 postmaster postgres
[.] LWLockWaitListLock
+ 5.38% 0.72% 1425 postmaster [kernel.kallsyms]
[k] futex_wait
+ 5.07% 0.00% 5 postmaster [kernel.kallsyms]
[k] sched_create_group
+ 4.23% 0.59% 2253 postmaster [kernel.kallsyms]
[k] __slab_alloc
+ 4.13% 0.00% 2330 postmaster [kernel.kallsyms]
[k] kmem_cache_alloc_node_trace
+ 3.75% 0.16% 2919 postmaster [kernel.kallsyms]
[k] unlock_page
+ 3.67% 3.67% 49595 postmaster postgres
[.] LWLockAttemptLock
+ 3.64% 0.33% 2796 postmaster [kernel.kallsyms]
[k] ___slab_alloc
+ 3.59% 1.44% 4390 postmaster [kernel.kallsyms]
[k] __wake_up_bit
+ 3.56% 0.19% 1605 swapper [kernel.kallsyms]
[k] cpuidle_enter_state
+ 3.51% 0.00% 1827 postmaster [kernel.kallsyms]
[k] __alloc_pages_nodemask
+ 3.51% 0.00% 594 postmaster [kernel.kallsyms]
[k] alloc_pages_vma
+ 3.37% 0.00% 895 postmaster [kernel.kallsyms]
[k] alloc_fair_sched_group
+ 3.25% 0.00% 1313 postmaster [kernel.kallsyms]
[k] futex_wait_setup
+ 3.20% 1.11% 12224 postmaster postgres
[.] AllocSetAlloc
+ 3.19% 3.19% 179390 swapper [kernel.kallsyms]
[k] intel_idle
+ 2.90% 2.90% 15113 postmaster postgres
[.] nocachegetattr
+ 2.85% 1.86% 28742 postmaster [kernel.kallsyms]
[k] deactivate_slab
+ 2.81% 2.81% 20931 postmaster [kernel.kallsyms]
[k] clear_page_c_e
+ 2.74% 0.00% 21 postmaster [kernel.kallsyms]
[k] do_group_exit
+ 2.74% 0.00% 3 postmaster [kernel.kallsyms]
[k] sys_exit_group
+ 2.74% 0.00% 77 postmaster [kernel.kallsyms]
[k] do_exit
+ 2.41% 0.00% 30 pgbench [kernel.kallsyms]
[k] system_call_fastpath
+ 2.41% 0.00% 22 pgbench libc-2.17.so
[.] __fopen_internal
+ 2.41% 0.00% 16 pgbench libc-2.17.so
[.] __GI___libc_open
+ 2.41% 0.00% 8 pgbench [kernel.kallsyms]
[k] sys_open
+ 2.41% 0.00% 38 pgbench [kernel.kallsyms]
[k] do_sys_open
+ 2.41% 0.00% 18 pgbench [kernel.kallsyms]
[k] do_filp_open
+ 2.41% 0.00% 28 pgbench [kernel.kallsyms]
[k] path_openat
+ 2.41% 0.00% 41 pgbench [kernel.kallsyms]
[k] do_last
+ 2.41% 0.00% 5 pgbench [kernel.kallsyms]
[k] vfs_open
+ 2.41% 2.41% 50 pgbench [kernel.kallsyms]
[k] do_dentry_open
+ 2.41% 0.00% 0 pgbench [unknown]
[k] 0x6f5f656369767265
+ 2.31% 2.31% 34865 postmaster postgres
[.] LWLockRelease
+ 2.15% 0.37% 5310 postmaster [kernel.kallsyms]
[k] try_to_wake_up
+ 2.15% 0.00% 194 postmaster [kernel.kallsyms]
[k] __wake_up
+ 2.15% 0.00% 212 postmaster [kernel.kallsyms]
[k] wake_up_q
+ 2.06% 0.10% 6918 postmaster [kernel.kallsyms]
[k] _raw_spin_lock_irqsave
+ 2.02% 0.00% 15 postmaster libc-2.17.so
[.] __GI___libc_read
+ 1.97% 0.00% 80 postmaster [kernel.kallsyms]
[k] queued_spin_lock_slowpath
+ 1.97% 1.97% 18138 postmaster [kernel.kallsyms]
[k] native_queued_spin_lock_slowpath
+ 1.97% 1.11% 2401 postmaster [kernel.kallsyms]
[k] get_user_pages_fast
+ 1.93% 0.00% 0 postmaster postgres
[.] 0x00000000005925e0
+ 1.85% 1.73% 8791 postmaster postgres
[.] hash_any
+ 1.85% 0.00% 111 swapper [kernel.kallsyms]
[k] apic_timer_interrupt
+ 1.76% 1.76% 3535 postmaster postgres
[.] LockRelease
+ 1.72% 0.00% 102 swapper [kernel.kallsyms]
[k] smp_apic_timer_interrupt
PGSQL10
+ 15.10% 0.00% 238 postmaster [kernel.kallsyms]
[k] system_call_fastpath
+ 13.53% 0.15% 375 postmaster [kernel.kallsyms]
[k] page_fault
+ 13.39% 0.00% 674 postmaster [kernel.kallsyms]
[k] do_page_fault
+ 12.37% 1.41% 6390 postmaster [kernel.kallsyms]
[k] __do_page_fault
+ 11.86% 0.00% 0 postmaster [unknown]
[k] 0000000000000000
+ 11.25% 7.19% 48253 postmaster postgres
[.] hash_search_with_hash_value
+ 10.58% 0.00% 0 swapper [kernel.kallsyms]
[k] start_cpu
+ 10.58% 0.00% 2543 swapper [kernel.kallsyms]
[k] cpu_startup_entry
+ 10.58% 0.00% 0 swapper [kernel.kallsyms]
[k] start_secondary
+ 10.42% 0.99% 9089 postmaster [kernel.kallsyms]
[k] handle_mm_fault
+ 9.26% 0.11% 2810 postmaster [kernel.kallsyms]
[k] handle_pte_fault
+ 8.92% 0.00% 237 swapper [kernel.kallsyms]
[k] arch_cpu_idle
+ 8.92% 0.00% 967 swapper [kernel.kallsyms]
[k] cpuidle_idle_call
+ 7.88% 0.51% 1568 swapper [kernel.kallsyms]
[k] cpuidle_enter_state
+ 6.20% 6.20% 177644 swapper [kernel.kallsyms]
[k] intel_idle
+ 5.99% 0.00% 5 postmaster libc-2.17.so
[.] __GI___setsid
+ 5.99% 0.00% 11 postmaster [kernel.kallsyms]
[k] sys_setsid
+ 5.99% 0.00% 8 postmaster [kernel.kallsyms]
[k] sched_autogroup_create_attach
+ 5.91% 0.00% 763 postmaster [kernel.kallsyms]
[k] sys_futex
+ 5.91% 0.00% 759 postmaster [kernel.kallsyms]
[k] do_futex
+ 5.82% 0.00% 4 postmaster [kernel.kallsyms]
[k] sched_create_group
+ 5.13% 0.00% 113 postmaster [kernel.kallsyms]
[k] apic_timer_interrupt
+ 5.13% 0.00% 53 postmaster [kernel.kallsyms]
[k] smp_apic_timer_interrupt
+ 4.98% 0.00% 2221 postmaster [kernel.kallsyms]
[k] kmem_cache_alloc_node_trace
+ 4.75% 0.25% 2180 postmaster [kernel.kallsyms]
[k] __slab_alloc
+ 4.51% 0.17% 2754 postmaster [kernel.kallsyms]
[k] ___slab_alloc
+ 4.43% 0.00% 1138 postmaster libpthread-2.17.so
[.] sem_post@@GLIBC_2.2.5
+ 4.29% 0.60% 13107 postmaster [kernel.kallsyms]
[k] get_futex_key
+ 4.25% 0.00% 0 postmaster [unknown]
[.] 0x7fffffff7fffffff
+ 4.06% 0.00% 62 postmaster [kernel.kallsyms]
[k] do_softirq
+ 4.06% 0.00% 32 postmaster [kernel.kallsyms]
[k] call_softirq
+ 4.06% 0.00% 195 postmaster [kernel.kallsyms]
[k] __do_softirq
+ 4.06% 0.00% 48 postmaster [kernel.kallsyms]
[k] irq_exit
+ 4.06% 4.06% 272 postmaster [kernel.kallsyms]
[k] run_timer_softirq
+ 3.75% 0.00% 2283 postmaster [kernel.kallsyms]
[k] futex_wake
+ 3.75% 3.75% 49341 postmaster postgres
[.] LWLockAttemptLock
+ 3.58% 0.47% 1865 postmaster [kernel.kallsyms]
[k] __alloc_pages_nodemask
+ 3.58% 0.00% 656 postmaster [kernel.kallsyms]
[k] alloc_pages_vma
+ 3.51% 3.51% 62398 postmaster postgres
[.] LWLockWaitListLock
+ 3.50% 0.15% 905 postmaster [kernel.kallsyms]
[k] alloc_fair_sched_group
+ 3.13% 2.06% 28201 postmaster [kernel.kallsyms]
[k] deactivate_slab
+ 3.00% 0.00% 0 postmaster [unknown]
[k] 0x0000000000000080
+ 3.00% 0.00% 519 postmaster libpthread-2.17.so
[.] do_futex_wait.constprop.1
+ 2.97% 0.64% 1243 postmaster [kernel.kallsyms]
[k] do_read_fault.isra.60
+ 2.42% 2.42% 35004 postmaster postgres
[.] LWLockRelease
+ 2.34% 2.09% 1395 postmaster libc-2.17.so
[.] __strlen_sse42
+ 2.33% 0.43% 5459 postmaster [kernel.kallsyms]
[k] alloc_rt_sched_group
+ 2.29% 1.26% 4395 postmaster [kernel.kallsyms]
[k] get_page_from_freelist
+ 2.24% 2.24% 10417 postmaster postgres
[.] LWLockAcquire
+ 2.20% 0.00% 0 postmaster [unknown]
[.] 0x0000005900000060
+ 2.08% 0.17% 7457 postmaster libc-2.17.so
[.] __memcpy_ssse3
+ 1.89% 0.00% 808 postmaster [kernel.kallsyms]
[k] __do_fault.isra.58
+ 1.89% 0.17% 594 postmaster [kernel.kallsyms]
[k] ext4_filemap_fault
+ 1.87% 0.00% 29 postmaster [kernel.kallsyms]
[k] do_group_exit
+ 1.87% 0.00% 0 postmaster [kernel.kallsyms]
[k] sys_exit_group
+ 1.87% 0.00% 76 postmaster [kernel.kallsyms]
[k] do_exit
+ 1.82% 0.00% 1022 postmaster [kernel.kallsyms]
[k] futex_wait
+ 1.79% 1.79% 16022 postmaster postgres
[.] guc_name_compare
+ 1.78% 0.68% 6873 postmaster [kernel.kallsyms]
[k] _raw_spin_lock_irqsave
+ 1.72% 1.72% 18771 postmaster [kernel.kallsyms]
[k] __cmpxchg_double_slab.isra.34
+ 1.72% 0.00% 18 postmaster [kernel.kallsyms]
[k] mmput
+ 1.72% 0.00% 48 postmaster [kernel.kallsyms]
[k] exit_mmap
+ 1.69% 0.00% 0 postmaster [unknown]
[k] 0x000000002928574f
+ 1.65% 0.00% 1164 postmaster [kernel.kallsyms]
[k] futex_wait_setup
+ 1.61% 1.61% 4419 postmaster postgres
[.] ReadBuffer_common
+ 1.51% 0.07% 669 postmaster postgres
[.] core_yylex
+ 1.51% 1.01% 1708 postmaster postgres
[.] base_yyparse
+ 1.48% 0.00% 0 postmaster [unknown]
[.] 0x0000000001bacec0
+ 1.48% 0.81% 9570 postmaster [kernel.kallsyms]
[k] mem_cgroup_charge_common
+ 1.44% 0.13% 3006 postmaster [kernel.kallsyms]
[k] unlock_page
