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

最后修改 2022-05-27: init commit (1e3e284)