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 2,27% postgres [.] LWLockRelease 1,18% postgres [.] heap_hot_search_buffer 1,03% postgres [.] AllocSetAlloc ... Slave at a much lower load than the master hangs on the function s_lock. What can be done about it? On Slave ~300 request/sec, ~5-8 ms/request avg, cpu usage ~20% - normal work by small load $ perf top 10,74% postgres [.] hash_search_with_hash_value 4,94% postgres [.] PinBuffer 4,61% postgres [.] AllocSetAlloc 3,57% postgres [.] heap_page_prune_opt 3,24% postgres [.] LWLockAcquire 2,47% postgres [.] heap_hot_search_buffer 2,11% postgres [.] SearchCatCache 1,90% postgres [.] LWLockRelease 1,87% postgres [.] _bt_compare 1,68% postgres [.] FunctionCall2Coll 1,46% postgres [.] _bt_checkkeys 1,24% postgres [.] copyObject 1,15% postgres [.] RecoveryInProgress.part.9 1,05% postgres [.] slot_deform_tuple ... Configuration Master postgres.conf listen_addresses = '*' max_connections = 100 shared_buffers = 200GB work_mem = 20MB maintenance_work_mem = 2GB effective_io_concurrency = 4 wal_level = hot_standby fsync = on synchronous_commit = off full_page_writes = on wal_writer_delay = 200ms checkpoint_segments = 100 checkpoint_timeout = 15min checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'pbzip2 -f -c %p > /opt/pg/wals/wals/%f.bz2' max_wal_senders = 3 random_page_cost = 0.5 cpu_tuple_cost = 0.02 cpu_index_tuple_cost = 0.01 cpu_operator_cost = 0.005 effective_cache_size = 40GB default_statistics_target = 300 logging_collector = on log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_min_duration_statement = 1000 log_checkpoints = on log_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) ' log_lock_waits = on track_io_timing = on track_activity_query_size = 4096 autovacuum = on log_autovacuum_min_duration = 0 autovacuum_freeze_max_age = 1500000000 datestyle = 'iso, dmy' timezone = 'Europe/Moscow' lc_messages = 'en_US.UTF-8' lc_monetary = 'ru_RU.UTF-8' lc_numeric = 'ru_RU.UTF-8' lc_time = 'ru_RU.UTF-8' default_text_search_config = 'pg_catalog.russian' shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 1000 pg_stat_statements.track = all max_locks_per_transaction = 264 Configuration Slave postgres.conf listen_addresses = '*' max_connections = 100 shared_buffers = 200GB work_mem = 20MB maintenance_work_mem = 2GB effective_io_concurrency = 4 wal_level = hot_standby fsync = on synchronous_commit = off full_page_writes = on wal_writer_delay = 200ms commit_delay = 1000 commit_siblings = 2 checkpoint_segments = 100 checkpoint_timeout = 15min checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'pbzip2 -f -c %p > /opt/pg/wals/wals/%f.bz2' max_wal_senders = 4 hot_standby = on max_standby_archive_delay = 30s max_standby_streaming_delay = 30s hot_standby_feedback = on random_page_cost = 0.5 cpu_tuple_cost = 0.02 cpu_index_tuple_cost = 0.01 cpu_operator_cost = 0.005 effective_cache_size = 40GB default_statistics_target = 300 logging_collector = on log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_min_duration_statement = 1000 log_checkpoints = on log_line_prefix = '%t %p %c-%l %x %q(%u, %d, %r, %a) ' log_lock_waits = on track_functions = none track_io_timing = on track_activity_query_size = 4096 autovacuum = on datestyle = 'iso, dmy' timezone = 'Europe/Moscow' lc_messages = 'en_US.UTF-8' lc_monetary = 'ru_RU.UTF-8' lc_numeric = 'ru_RU.UTF-8' lc_time = 'ru_RU.UTF-8' default_text_search_config = 'pg_catalog.russian' shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 1000 pg_stat_statements.track = all max_locks_per_transaction = 264 Thanks.
