OLAP

Tuned OLAP模板，针对高并行，长查询，高吞吐实例优化

Tuned OLAP模板主要针对吞吐量与计算并行度进行优化

此模板针对的机型是Dell R740 64核/400GB内存，使用PCI-E SSD的节点。您可以根据自己的实际机型进行调整。

1. # tuned configuration
2. #==============================================================#
3. # File      :   tuned.conf
4. # Mtime     :   2020-09-18
5. # Desc      :   Tune operatiing system to olap mode
6. # Path      :   /etc/tuned/olap/tuned.conf
7. # Author    :   Vonng(fengruohang@outlook.com)
8. # Copyright (C) 2018-2021 Ruohang Feng
9. #==============================================================#

11. [main]
12. summary=Optimize for PostgreSQL OLAP System
13. include=network-throughput

15. [cpu]
16. force_latency=1
17. governor=performance
18. energy_perf_bias=performance
19. min_perf_pct=100

21. [vm]
22. # disable transparent hugepages
23. transparent_hugepages=never

25. [sysctl]
26. #-------------------------------------------------------------#
27. #                           KERNEL                            #
28. #-------------------------------------------------------------#
29. # disable numa balancing
30. kernel.numa_balancing=0

32. # total shmem size in bytes： $(expr $(getconf _PHYS_PAGES) / 2 \* $(getconf PAGE_SIZE))
33. {% if param_shmall is defined and param_shmall != '' %}
34. kernel.shmall = {{ param_shmall }}
35. {% endif %}

37. # total shmem size in pages:  $(expr $(getconf _PHYS_PAGES) / 2)
38. {% if param_shmmax is defined and param_shmmax != '' %}
39. kernel.shmmax = {{ param_shmmax }}
40. {% endif %}

42. # total shmem segs 4096 -> 8192
43. kernel.shmmni=8192

45. # total msg queue number, set to mem size in MB
46. kernel.msgmni=32768

48. # max length of message queue
49. kernel.msgmnb=65536

51. # max size of message
52. kernel.msgmax=65536

54. kernel.pid_max=131072

56. # max(Sem in Set)=2048, max(Sem)=max(Sem in Set) x max(SemSet) , max(Sem per Ops)=2048, max(SemSet)=65536
57. kernel.sem=2048 134217728 2048 65536

59. # do not sched postgres process in group
60. kernel.sched_autogroup_enabled = 0

62. # total time the scheduler will consider a migrated process cache hot and, thus, less likely to be remigrated
63. # defaut = 0.5ms (500000ns), update to 5ms , depending on your typical query (e.g < 1ms)
64. kernel.sched_migration_cost_ns=5000000

66. #-------------------------------------------------------------#
67. #                             VM                              #
68. #-------------------------------------------------------------#
69. # try not using swap
70. # vm.swappiness=10

72. # disable when most mem are for file cache
73. vm.zone_reclaim_mode=0

75. # overcommit threshhold = 80%
76. vm.overcommit_memory=2
77. vm.overcommit_ratio=80

79. vm.dirty_background_ratio = 10    # throughput-performance default
80. vm.dirty_ratio=80                 # throughput-performance default 40 -> 80

82. # deny access on 0x00000 - 0x10000
83. vm.mmap_min_addr=65536

85. #-------------------------------------------------------------#
86. #                        Filesystem                           #
87. #-------------------------------------------------------------#
88. # max open files: 382589 -> 167772160
89. fs.file-max=167772160

91. # max concurrent unfinished async io, should be larger than 1M.  65536->1M
92. fs.aio-max-nr=1048576

95. #-------------------------------------------------------------#
96. #                          Network                            #
97. #-------------------------------------------------------------#
98. # max connection in listen queue (triggers retrans if full)
99. net.core.somaxconn=65535
100. net.core.netdev_max_backlog=8192
101. # tcp receive/transmit buffer default = 256KiB
102. net.core.rmem_default=262144
103. net.core.wmem_default=262144
104. # receive/transmit buffer limit = 4MiB
105. net.core.rmem_max=4194304
106. net.core.wmem_max=4194304

108. # ip options
109. net.ipv4.ip_forward=1
110. net.ipv4.ip_nonlocal_bind=1
111. net.ipv4.ip_local_port_range=32768 65000

113. # tcp options
114. net.ipv4.tcp_timestamps=1
115. net.ipv4.tcp_tw_reuse=1
116. net.ipv4.tcp_tw_recycle=0
117. net.ipv4.tcp_syncookies=0
118. net.ipv4.tcp_synack_retries=1
119. net.ipv4.tcp_syn_retries=1

121. # tcp read/write buffer
122. net.ipv4.tcp_rmem="4096 87380 16777216"
123. net.ipv4.tcp_wmem="4096 16384 16777216"
124. net.ipv4.udp_mem="3145728 4194304 16777216"

126. # tcp probe fail interval: 75s -> 20s
127. net.ipv4.tcp_keepalive_intvl=20
128. # tcp break after 3 * 20s = 1m
129. net.ipv4.tcp_keepalive_probes=3
130. # probe peroid = 1 min
131. net.ipv4.tcp_keepalive_time=60

133. net.ipv4.tcp_fin_timeout=5
134. net.ipv4.tcp_max_tw_buckets=262144
135. net.ipv4.tcp_max_syn_backlog=8192
136. net.ipv4.neigh.default.gc_thresh1=80000
137. net.ipv4.neigh.default.gc_thresh2=90000
138. net.ipv4.neigh.default.gc_thresh3=100000

140. net.bridge.bridge-nf-call-iptables=1
141. net.bridge.bridge-nf-call-ip6tables=1
142. net.bridge.bridge-nf-call-arptables=1

144. # max connection tracking number
145. net.netfilter.nf_conntrack_max=1048576

Last modified 2022-06-03: add scaffold for en docs (6a6eded)