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 <thomas.munro@xxxxxxxxx> ha scritto:
On Sat, Mar 2, 2019 at 5:02 AM Ray O'Donnell <ray@xxxxxxxxxxxx> 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
