[转帖]PostgreSQL 参数优化设置 32GB内存(推荐) 内存参数 检查点 日志参数 自动初始化参数shell脚本

postgresql,参数,优化,设置,32gb,内存,推荐,检查点,日志,自动,初始化,shell,脚本 · 浏览次数 : 0

小编点评

# content generation # generate parameters by modifying file postgresql.conf: generate_parameters() # modify parameters by modifying file postgresql.conf: modify_parameters() # create directory (pg_log and pg_arch): create_dir() # find the path of the database restart command 'pg_ctl': find_cmd() # remind user that they need to restart database to take effect: check_restart() # generate content by combining above steps: content generation

正文

1.修改参数列表
(1)执行计划
enable_nestloop = off #默认为on
enable_seqscan = off #默认为on
enable_indexscan = on
enable_bitmapscan = on
max_connections = 1000 #默认为100
 
(2)内存相关
shared_buffers = 16GB # 默认为128MB
effective_cache_size = 24GB #默认为4GB
work_mem = 32MB
temp_buffers = 32MB
wal_buffers = 512MB
maintenance_work_mem = 2GB
 
(3)autovacuum
autovacuum = on
autovacuum_max_workers = 3 #默认为3
autovacuum_work_mem = 256MB
autovacuum_vacuum_scale_factor = 0.05 #默认为0.2
 
(4)检查点
max_wal_size = 4GB #默认为1GB
min_wal_size = 1GB #默认为80MB
checkpoint_completion_target = 0.9 #默认为0.5
checkpoint_timeout = 30min #默认为5min
 
(5)日志
log_destination = csvlog
log_directory = pg_log
logging_collector = on
log_min_duration_statement = 800
log_rotation_size = 1024MB
log_truncate_on_rotation = on
log_filename = 'xl_log_%a.log'
 
2.参数设置公式

这个shell脚本可以使用shell的关联数组对

  1. #!/bin/bash
  2. #command to execute script: su - postgres -c "./create.sh"
  3. #default value refer to standard server(cpu:6cores,memory:32G,storage:12T)
  4. #value according to the actual situation
  5. #List of parameters to be modified
  6. :<<!
  7. enable_seqscan = off
  8. enable_indexscan = on
  9. enable_bitmapscan = on
  10. max_connections = 1000
  11. shared_buffers = 16GB
  12. effective_cache_size = 24GB
  13. work_mem = 32MB
  14. temp_buffers = 32MB
  15. wal_buffers = 512MB
  16. maintenance_work_mem = 2GB
  17. autovacuum_max_workers = 3
  18. autovacuum_work_mem = 256MB
  19. checkpoint_timeout = 30min
  20. max_wal_size = 4GB
  21. min_wal_size = 1GB
  22. checkpoint_completion_target = 0.9
  23. log_destination = csvlog
  24. log_directory = pg_log
  25. logging_collector = on
  26. log_min_duration_statement = 800
  27. log_rotation_size = 1024MB
  28. log_truncate_on_rotation = on
  29. log_filename = 'xl_log_%a.log'
  30. !
  31. ERROR="\033[41;37m ERROR \033[0m"
  32. INFO="\033[42;37m INFO \033[0m"
  33. WARN="\033[43;37m WARN \033[0m"
  34. COMMON_ERROR="some error happened, specific information please see console output"
  35. # Array of parameters
  36. declare -A parameter_array
  37. parameter_array=([enable_seqscan]=off [enable_indexscan]=on [enable_bitmapscan]=on [max_connections]=1000 [shared_buffers]=16GB
  38. [effective_cache_size]=24GB [work_mem]=32MB [temp_buffers]=32MB [wal_buffers]=512MB [maintenance_work_mem]=2GB
  39. [autovacuum_max_workers]=3 [autovacuum_work_mem]=256MB [checkpoint_timeout]=30min [max_wal_size]=4GB
  40. [min_wal_size]=1GB [checkpoint_completion_target]=0.9 [log_destination]=csvlog [log_directory]=pg_log
  41. [logging_collector]=on [log_min_duration_statement]=800 [log_rotation_size]=1024MB [log_truncate_on_rotation]=on [log_filename]=viid_log_%a.log )
  42. #default value :
  43. pgctl_path=
  44. data_directory=
  45. memory=
  46. # check command exit value, 0 is success
  47. function check_fun(){
  48. status=$?
  49. error=${COMMON_ERROR}
  50. if [[ 0 -ne ${status} ]] ; then
  51. echo -e "${ERROR} ${error}"
  52. exit 1
  53. fi
  54. }
  55. # prepare conditions
  56. function prepare_conditions(){
  57. data_directory=$(psql -qtAX -c "show data_directory" | sed 's/[ ]//g')
  58. check_fun
  59. if [[ ! -d "${data_directory}" ]] ; then
  60. echo -e "${ERROR} database's data directory does not exist"
  61. exit 1
  62. fi
  63. # physical machine environment
  64. memory=$(grep MemTotal /proc/meminfo | awk '{print $2 / 1024 / 1024}' | sed 's/\.[0-9]*//' | tail -n 1)
  65. check_fun
  66. # docker environment
  67. memory_limit=$(($(awk '{print $1}' /sys/fs/cgroup/memory/memory.limit_in_bytes) / 1024 / 1024 /1024))
  68. check_fun
  69. # comparing the two, choose the smaller one.
  70. if [[ "${memory_limit}" -le "${memory}" ]] ; then
  71. memory=${memory_limit}
  72. fi
  73. }
  74. # calculate parameters
  75. function calculate_parameters(){
  76. # 50%*memory
  77. parameter_array[shared_buffers]=$((memory * 1024 / 2))"MB"
  78. # 75%*memory
  79. parameter_array[effective_cache_size]=$((memory * 1024 * 3 / 4 ))"MB"
  80. # <1%
  81. parameter_array[work_mem]=${memory}"MB"
  82. # <1%
  83. parameter_array[temp_buffers]=${memory}"MB"
  84. # 32GB => 512MB
  85. parameter_array[wal_buffers]=$((memory * 16 ))"MB"
  86. # 32GB => 2048MB
  87. parameter_array[maintenance_work_mem]=$((memory * 64 ))"MB"
  88. }
  89. # modify parameters
  90. function modify_parameters(){
  91. # modify parameters by modifying file postgresql.conf:
  92. for parameter in ${!parameter_array[*]}
  93. do
  94. #check whether the parameters have been modified
  95. # PostgreSQL's default parameter configuration example: enable_seqscan = on
  96. # viid's example: enable_seqscan='on'
  97. check_out=$(grep "^${parameter}=" "${data_directory}"/postgresql.conf | grep -v '#' | tail -n 1)
  98. # process sleep
  99. sleep 0.2s
  100. if [[ -z "${check_out}" ]] ; then
  101. echo "${parameter}='${parameter_array[${parameter}]}'" >> "${data_directory}"/postgresql.conf
  102. check_fun
  103. echo -e "${INFO} modify ${parameter} successfully"
  104. else
  105. if [[ "${check_out}" = "${parameter}='${parameter_array[${parameter}]}'" ]] ; then
  106. echo -e "${INFO} ${parameter} is already configured, then skip this step"
  107. else
  108. sed -i s!^"${check_out}"!"${parameter}='${parameter_array[${parameter}]}'"!g "${data_directory}"/postgresql.conf
  109. check_fun
  110. echo -e "${INFO} modify ${parameter} successfully"
  111. fi
  112. fi
  113. done
  114. }
  115. # create directory(pg_log and pg_arch), and set user postgres permission
  116. function create_dir(){
  117. directory_array=("pg_arch" "pg_log")
  118. for directory in ${directory_array[*]};
  119. do
  120. # process sleep
  121. sleep 0.2s
  122. if [[ ! -d "${data_directory}/${directory}" ]] ; then
  123. mkdir -p "${data_directory}"/"${directory}"
  124. echo -e "${INFO} path ${data_directory}/${directory} create successfully"
  125. else
  126. echo -e "${INFO} ${data_directory}/${directory} is already exists, then skip this step"
  127. fi
  128. # set user postgres permission
  129. chown postgres:postgres "${data_directory}"/"${directory}"
  130. done
  131. }
  132. # because the environment is different, need to find the path of the database restart command 'pg_ctl'
  133. function find_cmd(){
  134. result=$(find / -name pg_ctl 2> /dev/null | grep bin/pg_ctl$ | tail -n 1 )
  135. # check whether the path exists
  136. if [[ -z "${result}" ]] ; then
  137. echo -e "${ERROR} database restart command 'pg_ctl' not exists"
  138. echo -e "${ERROR} please check to see if the database is installed or the command directory does not have permission to access it"
  139. exit 1
  140. else
  141. echo -e "${INFO} database restart command 'pg_ctl' path: ${result}"
  142. pgctl_path=${result}
  143. fi
  144. }
  145. # user choose whether to restart or not
  146. function check_restart(){
  147. read -r -p "Is it necessary to restart database immediately?[Enter YES or NO]:" result
  148. if [[ "${result,,}" = "yes" ]] ; then
  149. echo -e "${INFO} start to restart database"
  150. ${pgctl_path} restart -D "${data_directory}" >& /dev/null
  151. if [[ 0 -ne ${status} ]] ; then
  152. echo -e "${ERROR} restart database failed"
  153. exit 12
  154. fi
  155. elif [[ "${result,,}" = "no" ]] ; then
  156. echo -e "${WARN} please restart database manually"
  157. echo -e "${WARN} if you don't restart, database may not be available"
  158. exit 11
  159. else
  160. echo -e "${ERROR} invalid input,please enter again"
  161. check_restart
  162. fi
  163. }
  164. # ******* start *******
  165. # prepare conditions:memory ,data_directory
  166. prepare_conditions
  167. # calculate parameters
  168. calculate_parameters
  169. # modify parameters
  170. modify_parameters
  171. # create directory(pg_log and pg_arch)
  172. create_dir
  173. # the path of the database restart command 'pg_ctl'
  174. find_cmd
  175. # remind user that they need to restart database to take effect
  176. # process sleep
  177. sleep 0.5s
  178. echo -e ""
  179. echo -e "*******************************************************************"
  180. echo -e "* *"
  181. echo -e "* *"
  182. echo -e "* restart database to take effect *"
  183. echo -e "* *"
  184. echo -e "* *"
  185. echo -e "*******************************************************************"
  186. # process sleep
  187. sleep 0.5s
  188. # user choose whether to restart or not
  189. # check_restart
  190. echo -e "${INFO} start to restart database"
  191. ${pgctl_path} restart -D "${data_directory}" >& /dev/null
文章知识点与官方知识档案匹配,可进一步学习相关知识
PostgreSQL技能树首页概览6104 人正在系统学习中

与[转帖]PostgreSQL 参数优化设置 32GB内存(推荐) 内存参数 检查点 日志参数 自动初始化参数shell脚本相似的内容:

[转帖]PostgreSQL 参数优化设置 32GB内存(推荐) 内存参数 检查点 日志参数 自动初始化参数shell脚本

1.修改参数列表 (1)执行计划 enable_nestloop = off #默认为on enable_seqscan = off #默认为on enable_indexscan = on enable_bitmapscan = on max_connections = 1000 #默认为100

[转帖]PostgreSQL(三) 内存参数优化和原理(work_mem)内存表 pgfincore插件使用方法

1.常用内存参数 1.1 shared_buffers shared_buffers是PostgreSQL用于共享缓冲区的内存,是由8kb大小的块所形成的数组。PostgreSQL在进行更新、查询等操作时,首先从磁盘把数据读取到内存,之后进行更新,最后将数据写回磁盘。shared_buffers可以

[转帖]PostgreSQL 参数调整(性能优化)

昨天分别在外网和无外网环境下安装PostgreSQL,有外网环境下安装的相当顺利。但是在无外网环境下就是两个不同的概念了,可谓十有八折。感兴趣的同学可以搭建一下。 PostgreSQL安装完成后第一件事便是做相关测试,然后调整参数。 /*CPU 查看CPU型号*/ cat /proc/cpuinfo

[转帖]必看!PostgreSQL参数优化

https://www.modb.pro/db/48129 前不久,一个朋友所在的公司,业务人员整天都喊慢。 朋友是搞开发的,不是很懂DB,他说他们应用的其实没什么问题,但是就是每天一到高峰期就办理特别的慢啊,各种堵塞,一堆请求无法完成。他们没有专门的DBA,想找我帮忙看看。 我下班后打开他们的数据

[转帖]必看!PostgreSQL参数优化

https://zhuanlan.zhihu.com/p/333201734 前不久,一个朋友所在的公司,业务人员整天都喊慢。 朋友是搞开发的,不是很懂DB,他说他们应用的其实没什么问题,但是就是每天一到高峰期就办理特别的慢啊,各种堵塞,一堆请求无法完成。他们没有专门的DBA,想找我帮忙看看。 我下

[转帖]PostgreSQL 的性能调优方法

https://juejin.cn/post/7119489847529570334 浅谈PostgreSQL的性能调校 PostgreSQL的性能调校是指调校数据库以提高性能和快速访问数据;我们可以通过调校查询和数据库性能相关的参数来调校PostgreSQL的数据库性能。为了提高性能,我们需要通过

[转帖]PostgreSQL 日志参数解释 常用环境日志参数配置

1.常用日志参数 logging_collector = on/off 是否将日志重定向至文件中,默认是off(该配置修改后,需要重启DB服务),启动之后查看进程ps -ef|grep postgres,会多一个logger进程。 log_directory = 'pg_log' 日志文件目录,默认

[转帖]postgresql日志参数

https://www.jianshu.com/p/407c03aaa600 postgresql日志参数 logging_collector:这个参数启用日志收集器,它是一个捕捉被发送到stderr的日志消息的后台进程,并且它会将这些消息重定向到日志文件中;默认是OFF,修改参数需要重启。 log

[转帖]PostgreSQL配置文件--WAL

2022-12-23 3.1 Settings 3.1.1 fsync 字符串 默认: fsync = on 开启后强制把数据同步更新到磁盘,可以保证数据库将在OS或者硬件崩溃的后恢复到一个一致的状态。 虽然关闭,可以提升数据库性能,但无法保证数据库崩溃后数据一致性。 通常情况下需要打开这个参数,除

[转帖]理解 postgresql.conf 的work_mem 参数配置

https://developer.aliyun.com/article/401250 简介: 主要是通过具体的实验来理解 work_mem 今天我们着重来了解 postgresql.conf 中的 work_mem 参数 官方文档描述如下: 指定在写入临时文件之前内部排序操作和散列表使用的内存量。