On Tue, Aug 27, 2013 at 2:57 AM, Дмитрий Дегтярёв <degtyaryov@xxxxxxxxx> wrote: > Hello. > > Exist 2 identical server DELL PowerEdge™ R720, CPU Dual Intel® Xeon® E5-2620 > Hexa-Core inkl, RAM 256Gb, RAID-10 8 x 600 GB SAS 6 Gb/s 15000 rpm. > > $ cat /etc/fedora-release > Fedora release 19 > > $ postgres --version > postgres (PostgreSQL) 9.2.4 > > Data ~220Gb and Indexes ~140Gb > > iowait ~0.2-0.5. Disk usage only write ~0-2 Mb/sec > > On each installed pg_bouncer. Pool size 24. > > On Master in peak load ~1200 request/sec, ~30 ms/request avg, 24 CPU ~95% - > this is no problem > $ perf top > 21,14% [kernel] [k] isolate_freepages_block > 12,27% [unknown] [.] 0x00007fc1bb303be0 > 5,93% postgres [.] hash_search_with_hash_value > 4,85% libbz2.so.1.0.6 [.] 0x000000000000a6e0 > 2,70% postgres [.] PinBuffer > 2,34% postgres [.] slot_deform_tuple > 1,92% libbz2.so.1.0.6 [.] BZ2_compressBlock > 1,85% postgres [.] LWLockAcquire > 1,69% postgres [.] heap_page_prune_opt > 1,48% postgres [.] _bt_checkkeys > 1,40% [kernel] [k] page_fault > 1,36% postgres [.] _bt_compare > 1,23% postgres [.] heap_hot_search_buffer > 1,19% [kernel] [k] get_pageblock_flags_group > 1,18% postgres [.] AllocSetAlloc > > On Slave max ~400-500 request/sec, ~200 and up 24 ms/request avg, 24 CPU > ~95% - this is problem > $ perf top > 30,10% postgres [.] s_lock > 22,90% [unknown] [.] 0x0000000000729cfe > 4,98% postgres [.] RecoveryInProgress.part.9 > 4,89% postgres [.] LWLockAcquire > 4,57% postgres [.] hash_search_with_hash_value > 3,50% postgres [.] PinBuffer > 2,31% postgres [.] heap_page_prune_opt It looks like you're hitting spinlock connection inside heap_page_prune_opt(). Which is commented: * Note: this is called quite often. It's important that it fall out quickly * if there's not any use in pruning. This in turn calls RecoveryInProgress() which spinlocks in order to get a guaranteed result. At that call site, we are told: /* * We can't write WAL in recovery mode, so there's no point trying to * clean the page. The master will likely issue a cleaning WAL record soon * anyway, so this is no particular loss. */ So ISTM it's necessary to pedantically check RecoveryInProgress on each and every call of this routine (or at least, we should be able to reduce the number of spinlocks). Hm, what if we exposed LocalRecoveryInProgress() through a function which would approximately satisfy the condition "MightRecoveryInProgress()" in the basis the condition only moves in one direction? That could lead to optimization around the spinlock in hot path cases like this where getting 'TRUE' incorrectly is mostly harmless... merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
