root@dbhost [20180314 16:23:44]:/home/user$ lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 48 On-line CPU(s) list: 0-47 Thread(s) per core: 2 Core(s) per socket: 12 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 63 Model name: Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz Stepping: 2 CPU MHz: 2900.000 BogoMIPS: 4998.18 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 30720K NUMA node0 CPU(s): 0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46 NUMA node1 CPU(s): 1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47 root@dbhost [20180314 17:06:47]:/home/user$ numactl --hardware available: 2 nodes (0-1) node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 42 44 46 node 0 size: 130978 MB node 0 free: 7194 MB node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 node 1 size: 65536 MB node 1 free: 642 MB node distances: node 0 1 0: 10 21 1: 21 10 root@dbhost [20180314 16:23:46]:/home/user$ numastat -m Per-node system memory usage (in MBs): Node 0 Node 1 Total --------------- --------------- --------------- MemTotal 130978.34 65536.00 196514.34 MemFree 5897.14 426.93 6324.06 MemUsed 125081.20 65109.07 190190.28 Active 44828.92 16888.00 61716.92 Inactive 42311.52 12586.01 54897.53 Active(anon) 6113.09 4818.25 10931.33 Inactive(anon) 154.44 281.88 436.32 Active(file) 38715.84 12069.75 50785.59 Inactive(file) 42157.08 12304.12 54461.21 Unevictable 0.00 0.00 0.00 Mlocked 0.00 0.00 0.00 Dirty 6.55 0.30 6.85 Writeback 0.00 0.00 0.00 FilePages 81428.38 25263.89 106692.27 Mapped 58.56 26.13 84.69 AnonPages 5767.31 4265.87 10033.18 Shmem 499.60 834.26 1333.86 KernelStack 17.30 5.94 23.23 PageTables 40.75 36.96 77.71 NFS_Unstable 0.00 0.00 0.00 Bounce 0.00 0.00 0.00 WritebackTmp 0.00 0.00 0.00 Slab 1721.68 1056.38 2778.06 SReclaimable 1542.00 864.26 2406.26 SUnreclaim 179.68 192.12 371.80 AnonHugePages 62.00 28.00 90.00 HugePages_Total 33000.00 33000.00 66000.00 HugePages_Free 4450.00 4454.00 8904.00 HugePages_Surp 0.00 0.00 0.00 root@dbhost [20180314 16:25:36]:/home/user$ cat /etc/sysctl.conf kernel.shmmax=64424509440 kernel.shmall=4294967296 # kernel.sem= kernel.sem = 1024 32767 128 16384 fs.aio-max-nr=3145728 fs.file-max = 6815744 net.core.rmem_default=262144 net.core.rmem_max=4194304 net.core.wmem_default=262144 net.core.wmem_max=1048586 vm.nr_hugepages=33000 vm.dirty_background_bytes=67108864 vm.dirty_bytes=536870912 vm.min_free_kbytes=1048576 root@dbhost [20180314 16:25:49]:/home/user$ free -m total used free shared buff/cache available Mem: 193166 77128 6139 1333 109899 110458 Swap: 0 0 0 root@dbhost [20180314 16:28:07]:/home/user$ omreport storage controller Controller PERC H730 Mini(Embedded) Controller ID : 0 Status : Ok Name : PERC H730 Mini Slot ID : Embedded State : Ready Firmware Version : 25.5.0.0018 Minimum Required Firmware Version : Not Applicable Driver Version : 06.807.10.00-rh1 Minimum Required Driver Version : Not Applicable Storport Driver Version : Not Applicable Minimum Required Storport Driver Version : Not Applicable Number of Connectors : 2 Rebuild Rate : 30% BGI Rate : 30% Check Consistency Rate : 30% Reconstruct Rate : 30% Alarm State : Not Applicable Cluster Mode : Not Applicable SCSI Initiator ID : Not Applicable Cache Memory Size : 1024 MB Patrol Read Mode : Auto Patrol Read State : Active Patrol Read Rate : 30% Patrol Read Iterations : 117 Abort Check Consistency on Error : Disabled Allow Revertible Hot Spare and Replace Member : Enabled Load Balance : Not Applicable Auto Replace Member on Predictive Failure : Disabled Redundant Path view : Not Applicable CacheCade Capable : Not Applicable Persistent Hot Spare : Disabled Encryption Capable : Yes Encryption Key Present : No Encryption Mode : None Preserved Cache : Not Applicable Spin Down Unconfigured Drives : Disabled Spin Down Hot Spares : Disabled Spin Down Configured Drives : Disabled Automatic Disk Power Saving (Idle C) : Disabled Start Time (HH:MM) : Not Applicable Time Interval for Spin Up (in Hours) : Not Applicable T10 Protection Information Capable : No Non-RAID HDD Disk Cache Policy : Unchanged Current Controller Mode : RAID root@dbhost [20180314 16:39:01]:/home/user$ omreport storage vdisk controller=0 Virtual Disk 0 on Controller PERC H730 Mini (Embedded) Controller PERC H730 Mini (Embedded) ID : 0 Status : Ok Name : GenericR6_0 State : Ready Hot Spare Policy violated : Not Assigned Encrypted : No Layout : RAID-6 Size : 6,703.50 GB (7197828317184 bytes) T10 Protection Information Status : No Associated Fluid Cache State : Not Applicable Device Name : /dev/sda Bus Protocol : SAS Media : HDD Read Policy : No Read Ahead Write Policy : Write Back Cache Policy : Not Applicable Stripe Element Size : 64 KB Disk Cache Policy : Unchanged root@dbhost [20180314 16:31:29]:/home/user$ grep ^Huge /proc/meminfo HugePages_Total: 33000 HugePages_Free: 4452 HugePages_Rsvd: 2066 HugePages_Surp: 0 Hugepagesize: 2048 kB ===================== postgresql.conf max_connections = 2048 shared_buffers = 48GB temp_buffers = 128MB work_mem = 256MB maintenance_work_mem = 512MB dynamic_shared_memory_type = posix wal_level = hot_standby max_wal_senders = 5 wal_keep_segments = 2000 max_replication_slots = 5 min_wal_size = 4GB max_wal_size = 32GB archive_mode = on autovacuum_vacuum_threshold = 100 autovacuum_analyze_threshold = 100 autovacuum_vacuum_scale_factor = 0.02 autovacuum_analyze_scale_factor = 0.01 autovacuum_vacuum_cost_delay = 14 autovacuum_vacuum_cost_limit = 5000 autovacuum_max_workers = 4