Quick Links

Re: 60 core performance with 9.3

From:	Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To:	Andres Freund <andres(at)2ndquadrant(dot)com>
Cc:	pgsql-performance(at)postgresql(dot)org
Subject:	Re: 60 core performance with 9.3
Date:	2014-07-11 00:40:15
Message-ID:	53BF326F.2070903@catalyst.net.nz
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-performance

On 01/07/14 22:13, Andres Freund wrote:
> On 2014-07-01 21:48:35 +1200, Mark Kirkwood wrote:
>> - cherry picking the last 5 commits into 9.4 branch and building a package
>> from that and retesting:
>>
>> Clients | 9.4 tps 60 cores (rwlock)
>> --------+--------------------------
>> 6 | 70189
>> 12 | 128894
>> 24 | 233542
>> 48 | 422754
>> 96 | 590796
>> 192 | 630672
>>
>> Wow - that is more like it! Andres that is some nice work, we definitely owe
>> you some beers for that :-) I am aware that I need to retest with an
>> unpatched 9.4 src - as it is not clear from this data how much is due to
>> Andres's patches and how much to the steady stream of 9.4 development. I'll
>> post an update on that later, but figured this was interesting enough to
>> note for now.
>
> Cool. That's what I like (and expect) to see :). I don't think unpatched
> 9.4 will show significantly different results than 9.3, but it'd be good
> to validate that. If you do so, could you post the results in the
> -hackers thread I just CCed you on? That'll help the work to get into
> 9.5.

So we seem to have nailed read only performance. Going back and
revisiting read write performance finds:

Postgres 9.4 beta
rwlock patch
pgbench scale = 2000

max_connections = 200;
shared_buffers = "10GB";
maintenance_work_mem = "1GB";
effective_io_concurrency = 10;
wal_buffers = "32MB";
checkpoint_segments = 192;
checkpoint_completion_target = 0.8;

clients | tps (32 cores) | tps
---------+----------------+---------
6 | 8313 | 8175
12 | 11012 | 14409
24 | 16151 | 17191
48 | 21153 | 23122
96 | 21977 | 22308
192 | 22917 | 23109

So we are back to not doing significantly better than 32 cores. Hmmm.
Doing quite a few more tweaks gets some better numbers:

kernel.sched_autogroup_enabled=0
kernel.sched_migration_cost_ns=5000000
net.core.somaxconn=1024
/sys/kernel/mm/transparent_hugepage/enabled [never]

+checkpoint_segments = 1920
+wal_buffers = "256MB";

clients | tps
---------+---------
6 | 8366
12 | 15988
24 | 19828
48 | 30315
96 | 31649
192 | 29497

One more:

+wal__sync_method = "open_datasync"

clients | tps
---------+---------
6 | 9566
12 | 17129
24 | 22962
48 | 34564
96 | 32584
192 | 28367

So this looks better - however I suspect 32 core performance would
improve with these as well!

The problem does *not* look to be connected with IO (I will include some
iostat below). So time to get the profiler out (192 clients for 1 minute):

Full report http://paste.ubuntu.com/7777886/

# ========
# captured on: Fri Jul 11 03:09:06 2014
# hostname : ncel-prod-db3
# os release : 3.13.0-24-generic
# perf version : 3.13.9
# arch : x86_64
# nrcpus online : 60
# nrcpus avail : 60
# cpudesc : Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz
# cpuid : GenuineIntel,6,62,7
# total memory : 1056692116 kB
# cmdline : /usr/lib/linux-tools-3.13.0-24/perf record -ag
# event : name = cycles, type = 0, config = 0x0, config1 = 0x0, config2
= 0x0, excl_usr = 0, excl_kern = 0, excl_host = 0, excl_guest = 1,
precise_ip = 0, attr_mmap2 = 0, attr_mmap = 1, attr_mmap_data = 0
# HEADER_CPU_TOPOLOGY info available, use -I to display
# HEADER_NUMA_TOPOLOGY info available, use -I to display
# pmu mappings: cpu = 4, uncore_cbox_10 = 17, uncore_cbox_11 = 18,
uncore_cbox_12 = 19, uncore_cbox_13 = 20, uncore_cbox_14 = 21, software
= 1, uncore_irp = 33, uncore_pcu = 22, tracepoint = 2, uncore_imc_0 =
25, uncore_imc_1 = 26, uncore_imc_2 = 27, uncore_imc_3 = 28,
uncore_imc_4 = 29, uncore_imc_5 = 30, uncore_imc_6 = 31, uncore_imc_7 =
32, uncore_qpi_0 = 34, uncore_qpi_1 = 35, uncore_qpi_2 = 36,
uncore_cbox_0 = 7, uncore_cbox_1 = 8, uncore_cbox_2 = 9, uncore_cbox_3 =
10, uncore_cbox_4 = 11, uncore_cbox_5 = 12, uncore_cbox_6 = 13,
uncore_cbox_7 = 14, uncore_cbox_8 = 15, uncore_cbox_9 = 16,
uncore_r2pcie = 37, uncore_r3qpi_0 = 38, uncore_r3qpi_1 = 39, breakpoint
= 5, uncore_ha_0 = 23, uncore_ha_1 = 24, uncore_ubox = 6
# ========
#
# Samples: 1M of event 'cycles'
# Event count (approx.): 359906321606
#
# Overhead Command Shared Object
Symbol
# ........ .............. .......................
.....................................................
#
8.82% postgres [kernel.kallsyms] [k]
_raw_spin_lock_irqsave
|
--- _raw_spin_lock_irqsave
|
|--75.69%-- pagevec_lru_move_fn
| __lru_cache_add
| lru_cache_add
| putback_lru_page
| migrate_pages
| migrate_misplaced_page
| do_numa_page
| handle_mm_fault
| __do_page_fault
| do_page_fault
| page_fault
| |
| |--31.07%-- PinBuffer
| | |
| | --100.00%-- ReadBuffer_common
| | |
| | --100.00%--
ReadBufferExtended
| | |

With respect to IO, here are typical iostat outputs:

sda HW RAID 10 array SAS SSD [data]
md0 SW RAID 10 of nvme[0-3]n1 PCie SSD [xlog]

Non Checkpoint

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s
avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 15.00 0.00 3.00 0.00 0.07
50.67 0.00 0.00 0.00 0.00 0.00 0.00
nvme0n1 0.00 0.00 0.00 4198.00 0.00 146.50
71.47 0.18 0.05 0.00 0.05 0.04 18.40
nvme1n1 0.00 0.00 0.00 4198.00 0.00 146.50
71.47 0.18 0.04 0.00 0.04 0.04 17.20
nvme2n1 0.00 0.00 0.00 4126.00 0.00 146.08
72.51 0.15 0.04 0.00 0.04 0.03 14.00
nvme3n1 0.00 0.00 0.00 4125.00 0.00 146.03
72.50 0.15 0.04 0.00 0.04 0.03 14.40
md0 0.00 0.00 0.00 16022.00 0.00 292.53
37.39 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 18.00 0.00 0.07
8.44 0.00 0.00 0.00 0.00 0.00 0.00
dm-4 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00

Checkpoint

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s
avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.00 29.00 1.00 96795.00 0.00 1074.52
22.73 133.13 1.38 4.00 1.38 0.01 100.00
nvme0n1 0.00 0.00 0.00 3564.00 0.00 56.71
32.59 0.12 0.03 0.00 0.03 0.03 11.60
nvme1n1 0.00 0.00 0.00 3564.00 0.00 56.71
32.59 0.12 0.03 0.00 0.03 0.03 12.00
nvme2n1 0.00 0.00 0.00 3884.00 0.00 59.12
31.17 0.14 0.04 0.00 0.04 0.04 13.60
nvme3n1 0.00 0.00 0.00 3884.00 0.00 59.12
31.17 0.13 0.03 0.00 0.03 0.03 12.80
md0 0.00 0.00 0.00 14779.00 0.00 115.80
16.05 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 3.00 0.00 0.01
8.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 1.00 96830.00 0.00 1074.83
22.73 134.79 1.38 4.00 1.38 0.01 100.00
dm-4 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00

Thanks for your patience if you have read this far!

Regards

Mark

In response to

Re: 60 core performance with 9.3 at 2014-07-01 10:13:07 from Andres Freund

Responses

Re: 60 core performance with 9.3 at 2014-07-11 08:22:07 from Andres Freund

Browse pgsql-performance by date

	From	Date	Subject
Next Message	Huang, Suya	2014-07-11 04:14:38	GIN index not used
Previous Message	Josh Berkus	2014-07-09 00:06:03	Re: PGSQL 9.3 - billion rows