Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCIsR-0005x9-8b for pgsql-performance@arkaria.postgresql.org; Wed, 30 Jul 2014 01:40:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XCIsQ-0002sg-Hm for pgsql-performance@arkaria.postgresql.org; Wed, 30 Jul 2014 01:40:22 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XCIsP-0002rS-32 for pgsql-performance@postgresql.org; Wed, 30 Jul 2014 01:40:21 +0000 Received: from [2404:130:0:10::40:2] (helo=mail.catalyst.net.nz) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCIsC-0000jK-9v for pgsql-performance@postgresql.org; Wed, 30 Jul 2014 01:40:18 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.catalyst.net.nz (Postfix) with ESMTP id 4068831D49; Wed, 30 Jul 2014 13:40:04 +1200 (NZST) X-Virus-Scanned: Debian amavisd-new at catalyst.net.nz Received: from mail.catalyst.net.nz ([127.0.0.1]) by localhost (bertrand.catalyst.net.nz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id OaqbXyVhenhX; Wed, 30 Jul 2014 13:40:03 +1200 (NZST) Received: from [IPv6:2404:130:0:1000:28d2:d0f9:91fe:a093] (unknown [IPv6:2404:130:0:1000:28d2:d0f9:91fe:a093]) (Authenticated sender: mark.kirkwood) by mail.catalyst.net.nz (Postfix) with ESMTPSA id 0395D32518; Wed, 30 Jul 2014 13:40:03 +1200 (NZST) Message-ID: <53D84E16.90609@catalyst.net.nz> Date: Wed, 30 Jul 2014 13:44:54 +1200 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 To: Andres Freund CC: pgsql-performance@postgresql.org Subject: Re: 60 core performance with 9.3 References: <53ACB190.2030206@catalyst.net.nz> <53ACD6C4.9050605@catalyst.net.nz> <20140627091951.GA16680@awork2.anarazel.de> <53B283F3.7020005@catalyst.net.nz> <20140701101307.GO21422@awork2.anarazel.de> <53BF326F.2070903@catalyst.net.nz> <20140711082207.GI17261@awork2.anarazel.de> <53C711B6.5040802@catalyst.net.nz> In-Reply-To: <53C711B6.5040802@catalyst.net.nz> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Host-Lookup-Failed: Reverse DNS lookup failed for 2404:130:0:10::40:2 (failed) X-Pg-Spam-Score: 0.8 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 17/07/14 11:58, Mark Kirkwood wrote: > > Trying out with numa_balancing=0 seemed to get essentially the same > performance. Similarly wrapping postgres startup with --interleave. > > All this made me want to try with numa *really* disabled. So rebooted > the box with "numa=off" appended to the kernel cmdline. Somewhat > surprisingly (to me anyway), the numbers were essentially identical. The > profile, however is quite different: > A little more tweaking got some further improvement: rwlocks patch as before wal_buffers = 256MB checkpoint_segments = 1920 wal_sync_method = open_datasync LSI RAID adaptor disable read ahead and write cache for SSD fast path mode numa_balancing = 0 Pgbench scale 2000 again: clients | tps (prev) | tps (tweaked config) ---------+------------+--------- 6 | 8175 | 8281 12 | 14409 | 15896 24 | 17191 | 19522 48 | 23122 | 29776 96 | 22308 | 32352 192 | 23109 | 28804 Now recall we were seeing no actual tps changes with numa_balancing=0 or 1 (so the improvement above is from the other changes), but figured it might be informative to try to track down what the non-numa bottlenecks looked like. We tried profiling the entire 10 minute run which showed the stats collector as a possible source of contention: 3.86% postgres [kernel.kallsyms] [k] _raw_spin_lock_bh | --- _raw_spin_lock_bh | |--95.78%-- lock_sock_nested | udpv6_sendmsg | inet_sendmsg | sock_sendmsg | SYSC_sendto | sys_sendto | tracesys | __libc_send | | | |--99.17%-- pgstat_report_stat | | PostgresMain | | ServerLoop | | PostmasterMain | | main | | __libc_start_main | | | |--0.77%-- pgstat_send_bgwriter | | BackgroundWriterMain | | AuxiliaryProcessMain | | 0x7f08efe8d453 | | reaper | | __restore_rt | | PostmasterMain | | main | | __libc_start_main | --0.07%-- [...] | |--2.54%-- __lock_sock | | | |--91.95%-- lock_sock_nested | | udpv6_sendmsg | | inet_sendmsg | | sock_sendmsg | | SYSC_sendto | | sys_sendto | | tracesys | | __libc_send | | | | | |--99.73%-- pgstat_report_stat | | | PostgresMain | | | ServerLoop Disabling track_counts and rerunning pgbench: clients | tps (no counts) ---------+------------ 6 | 9806 12 | 18000 24 | 29281 48 | 43703 96 | 54539 192 | 36114 While these numbers look great in the middle range (12-96 clients), then benefit looks to be tailing off as client numbers increase. Also running with no stats (and hence no auto vacuum or analyze) is way too scary! Trying out less write heavy workloads shows that the stats overhead does not appear to be significant for *read* heavy cases, so this result above is perhaps more of a curiosity than anything (given that read heavy is more typical...and our real workload is more similar to read heavy). The profile for counts off looks like: 4.79% swapper [kernel.kallsyms] [k] read_hpet | --- read_hpet | |--97.10%-- ktime_get | | | |--35.24%-- clockevents_program_event | | tick_program_event | | | | | |--56.59%-- __hrtimer_start_range_ns | | | | | | | |--78.12%-- hrtimer_start_range_ns | | | | tick_nohz_restart | | | | tick_nohz_idle_exit | | | | cpu_startup_entry | | | | | | | | | |--98.84%-- start_secondary | | | | | | | | | --1.16%-- rest_init | | | | start_kernel | | | | x86_64_start_reservations | | | | x86_64_start_kernel | | | | | | | --21.88%-- hrtimer_start | | | tick_nohz_stop_sched_tick | | | __tick_nohz_idle_enter | | | | | | | |--99.89%-- tick_nohz_idle_enter | | | | cpu_startup_entry | | | | | | | | | |--98.30%-- start_secondary | | | | | | | | | --1.70%-- rest_init | | | | start_kernel | | | | x86_64_start_reservations | | | | x86_64_start_kernel | | | --0.11%-- [...] | | | | | |--40.25%-- hrtimer_force_reprogram | | | __remove_hrtimer | | | | | | | |--89.68%-- __hrtimer_start_range_ns | | | | hrtimer_start | | | | tick_nohz_stop_sched_tick | | | | __tick_nohz_idle_enter | | | | | | | | | |--99.90%-- tick_nohz_idle_enter | | | | | cpu_startup_entry | | | | | | | | | | | |--99.04%-- start_secondary | | | | | | | | | | | --0.96%-- rest_init | | | | | start_kernel | | | | | x86_64_start_reservations | | | | | x86_64_start_kernel | | | | --0.10%-- [...] | | | | Any thoughts on how to proceed further appreciated! Cheers, Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance