OLTP

Patroni OLTP模板

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

  1. #!/usr/bin/env patroni
  2. #==============================================================#
  3. # File : patroni.yml
  4. # Ctime : 2020-04-08
  5. # Mtime : 2020-12-22
  6. # Desc : patroni cluster definition for {{ pg_cluster }} (oltp)
  7. # Path : /pg/bin/patroni.yml
  8. # Real Path : /pg/conf/{{ pg_instance }}.yml
  9. # Link : /pg/bin/patroni.yml -> /pg/conf/{{ pg_instance}}.yml
  10. # Note : Transactional Database Cluster Template
  11. # Doc : https://patroni.readthedocs.io/en/latest/SETTINGS.html
  12. # Copyright (C) 2018-2021 Ruohang Feng
  13. #==============================================================#
  14. # OLTP database are optimized for performance, rt latency
  15. # typical spec: 64 Core | 400 GB RAM | PCI-E SSD xTB
  16. ---
  17. #------------------------------------------------------------------------------
  18. # identity
  19. #------------------------------------------------------------------------------
  20. namespace: {{ pg_namespace }}/ # namespace
  21. scope: {{ pg_cluster }} # cluster name
  22. name: {{ pg_instance }} # instance name
  23. #------------------------------------------------------------------------------
  24. # log
  25. #------------------------------------------------------------------------------
  26. log:
  27. level: INFO # NOTEST|DEBUG|INFO|WARNING|ERROR|CRITICAL
  28. dir: /pg/log/ # default log file: /pg/log/patroni.log
  29. file_size: 100000000 # 100MB log triggers a log rotate
  30. # format: '%(asctime)s %(levelname)s: %(message)s'
  31. #------------------------------------------------------------------------------
  32. # dcs
  33. #------------------------------------------------------------------------------
  34. consul:
  35. host: 127.0.0.1:8500
  36. consistency: default # default|consistent|stale
  37. register_service: true
  38. service_check_interval: 15s
  39. service_tags:
  40. - {{ pg_cluster }}
  41. #------------------------------------------------------------------------------
  42. # api
  43. #------------------------------------------------------------------------------
  44. # how to expose patroni service
  45. # listen on all ipv4, connect via public ip, use same credential as dbuser_monitor
  46. restapi:
  47. listen: 0.0.0.0:{{ patroni_port }}
  48. connect_address: {{ inventory_hostname }}:{{ patroni_port }}
  49. authentication:
  50. verify_client: none # none|optional|required
  51. username: {{ pg_monitor_username }}
  52. password: '{{ pg_monitor_password }}'
  53. #------------------------------------------------------------------------------
  54. # ctl
  55. #------------------------------------------------------------------------------
  56. ctl:
  57. optional:
  58. insecure: true
  59. # cacert: '/path/to/ca/cert'
  60. # certfile: '/path/to/cert/file'
  61. # keyfile: '/path/to/key/file'
  62. #------------------------------------------------------------------------------
  63. # tags
  64. #------------------------------------------------------------------------------
  65. tags:
  66. nofailover: false
  67. clonefrom: true
  68. noloadbalance: false
  69. nosync: false
  70. {% if pg_upstream is defined %}
  71. replicatefrom: {{ pg_upstream }} # clone from another replica rather than primary
  72. {% endif %}
  73. #------------------------------------------------------------------------------
  74. # watchdog
  75. #------------------------------------------------------------------------------
  76. # available mode: off|automatic|required
  77. watchdog:
  78. mode: {{ patroni_watchdog_mode }}
  79. device: /dev/watchdog
  80. # safety_margin: 10s
  81. #------------------------------------------------------------------------------
  82. # bootstrap
  83. #------------------------------------------------------------------------------
  84. bootstrap:
  85. #----------------------------------------------------------------------------
  86. # bootstrap method
  87. #----------------------------------------------------------------------------
  88. method: initdb
  89. # add custom bootstrap method here
  90. # default bootstrap method: initdb
  91. initdb:
  92. - locale: C
  93. - encoding: UTF8
  94. # - data-checksums # enable data-checksum
  95. #----------------------------------------------------------------------------
  96. # bootstrap users
  97. #---------------------------------------------------------------------------
  98. # additional users which need to be created after initializing new cluster
  99. # replication user and monitor user are required
  100. users:
  101. {{ pg_replication_username }}:
  102. password: '{{ pg_replication_password }}'
  103. {{ pg_monitor_username }}:
  104. password: '{{ pg_monitor_password }}'
  105. {{ pg_admin_username }}:
  106. password: '{{ pg_admin_password }}'
  107. # bootstrap hba, allow local and intranet password access & replication
  108. # will be overwritten later
  109. pg_hba:
  110. - local all postgres ident
  111. - local all all md5
  112. - host all all 0.0.0.0/0 md5
  113. - local replication postgres ident
  114. - local replication all md5
  115. - host replication all 0.0.0.0/0 md5
  116. #----------------------------------------------------------------------------
  117. # template
  118. #---------------------------------------------------------------------------
  119. # post_init: /pg/bin/pg-init
  120. #----------------------------------------------------------------------------
  121. # bootstrap config
  122. #---------------------------------------------------------------------------
  123. # this section will be written to /{{ pg_namespace }}/{{ pg_cluster }}/config
  124. # if will NOT take any effect after cluster bootstrap
  125. dcs:
  126. {% if pg_role == 'primary' and pg_upstream is defined %}
  127. #----------------------------------------------------------------------------
  128. # standby cluster definition
  129. #---------------------------------------------------------------------------
  130. standby_cluster:
  131. host: {{ pg_upstream }}
  132. port: {{ pg_port }}
  133. # primary_slot_name: patroni # must be create manually on upstream server, if specified
  134. create_replica_methods:
  135. - basebackup
  136. {% endif %}
  137. #----------------------------------------------------------------------------
  138. # important parameters
  139. #---------------------------------------------------------------------------
  140. # constraint: ttl >: loop_wait + retry_timeout * 2
  141. # the number of seconds the loop will sleep. Default value: 10
  142. # this is patroni check loop interval
  143. loop_wait: 10
  144. # the TTL to acquire the leader lock (in seconds). Think of it as the length of time before initiation of the automatic failover process. Default value: 30
  145. # config this according to your network condition to avoid false-positive failover
  146. ttl: 30
  147. # timeout for DCS and PostgreSQL operation retries (in seconds). DCS or network issues shorter than this will not cause Patroni to demote the leader. Default value: 10
  148. retry_timeout: 10
  149. # the amount of time a master is allowed to recover from failures before failover is triggered (in seconds)
  150. # Max RTO: 2 loop wait + master_start_timeout
  151. master_start_timeout: 10
  152. # import: candidate will not be promoted if replication lag is higher than this
  153. # maximum RPO: 1MB
  154. maximum_lag_on_failover: 1048576
  155. # The number of seconds Patroni is allowed to wait when stopping Postgres and effective only when synchronous_mode is enabled
  156. master_stop_timeout: 30
  157. # turns on synchronous replication mode. In this mode a replica will be chosen as synchronous and only the latest leader and synchronous replica are able to participate in leader election
  158. # set to true for RPO mode
  159. synchronous_mode: false
  160. # prevents disabling synchronous replication if no synchronous replicas are available, blocking all client writes to the master
  161. synchronous_mode_strict: false
  162. #----------------------------------------------------------------------------
  163. # postgres parameters
  164. #---------------------------------------------------------------------------
  165. postgresql:
  166. use_slots: true
  167. use_pg_rewind: true
  168. remove_data_directory_on_rewind_failure: true
  169. parameters:
  170. #----------------------------------------------------------------------
  171. # IMPORTANT PARAMETERS
  172. #----------------------------------------------------------------------
  173. max_connections: 400 # 100 -> 400
  174. superuser_reserved_connections: 10 # reserve 10 connection for su
  175. max_locks_per_transaction: 128 # 64 -> 128
  176. max_prepared_transactions: 0 # 0 disable 2PC
  177. track_commit_timestamp: on # enabled xact timestamp
  178. max_worker_processes: 8 # default 8, set to cpu core
  179. wal_level: logical # logical
  180. wal_log_hints: on # wal log hints to support rewind
  181. max_wal_senders: 16 # 10 -> 16
  182. max_replication_slots: 16 # 10 -> 16
  183. wal_keep_size: 100GB # keep at least 100GB WAL
  184. password_encryption: md5 # use traditional md5 auth
  185. #----------------------------------------------------------------------
  186. # RESOURCE USAGE (except WAL)
  187. #----------------------------------------------------------------------
  188. # memory: shared_buffers and maintenance_work_mem will be dynamically set
  189. shared_buffers: {{ pg_shared_buffers }}
  190. maintenance_work_mem: {{ pg_maintenance_work_mem }}
  191. work_mem: 32MB # 4MB -> 32MB
  192. huge_pages: try # try huge pages
  193. temp_file_limit: 100GB # 0 -> 100GB
  194. vacuum_cost_delay: 2ms # wait 2ms per 10000 cost
  195. vacuum_cost_limit: 10000 # 10000 cost each round
  196. bgwriter_delay: 10ms # check dirty page every 10ms
  197. bgwriter_lru_maxpages: 800 # 100 -> 800
  198. bgwriter_lru_multiplier: 5.0 # 2.0 -> 5.0 more cushion buffer
  199. #----------------------------------------------------------------------
  200. # WAL
  201. #----------------------------------------------------------------------
  202. wal_buffers: 16MB # max to 16MB
  203. wal_writer_delay: 20ms # wait period
  204. wal_writer_flush_after: 1MB # max allowed data loss
  205. min_wal_size: 100GB # at least 100GB WAL
  206. max_wal_size: 400GB # at most 400GB WAL
  207. commit_delay: 20 # 200ms -> 20ms, increase speed
  208. commit_siblings: 10 # 5 -> 10
  209. checkpoint_timeout: 60min # checkpoint 5min -> 1h
  210. checkpoint_completion_target: 0.95 # 0.5 -> 0.95
  211. archive_mode: on
  212. archive_command: 'wal_dir=/pg/arcwal; [[ $(date +%H%M) == 1200 ]] && rm -rf ${wal_dir}/$(date -d"yesterday" +%Y%m%d); /bin/mkdir -p ${wal_dir}/$(date +%Y%m%d) && /usr/bin/lz4 -q -z %p > ${wal_dir}/$(date +%Y%m%d)/%f.lz4'
  213. #----------------------------------------------------------------------
  214. # REPLICATION
  215. #----------------------------------------------------------------------
  216. # synchronous_standby_names: ''
  217. vacuum_defer_cleanup_age: 50000 # 0->50000 last 50000 xact changes will not be vacuumed
  218. promote_trigger_file: promote.signal # default promote trigger file path
  219. max_standby_archive_delay: 10min # max delay before canceling queries when reading WAL from archive;
  220. max_standby_streaming_delay: 3min # max delay before canceling queries when reading streaming WAL;
  221. wal_receiver_status_interval: 1s # send replies at least this often
  222. hot_standby_feedback: on # send info from standby to prevent query conflicts
  223. wal_receiver_timeout: 60s # time that receiver waits for
  224. max_logical_replication_workers: 8 # 4 -> 8
  225. max_sync_workers_per_subscription: 8 # 4 -> 8
  226. #----------------------------------------------------------------------
  227. # QUERY TUNING
  228. #----------------------------------------------------------------------
  229. # planner
  230. # enable_partitionwise_join: on
  231. random_page_cost: 1.1 # 4 for HDD, 1.1 for SSD
  232. effective_cache_size: 320GB # max mem - shared buffer
  233. default_statistics_target: 1000 # stat bucket 100 -> 1000
  234. #----------------------------------------------------------------------
  235. # REPORTING AND LOGGING
  236. #----------------------------------------------------------------------
  237. log_destination: csvlog # use standard csv log
  238. logging_collector: on # enable csvlog
  239. log_directory: log # default log dir: /pg/data/log
  240. # log_filename: 'postgresql-%a.log' # weekly auto-recycle
  241. log_filename: 'postgresql-%Y-%m-%d.log' # YYYY-MM-DD full log retention
  242. log_checkpoints: on # log checkpoint info
  243. log_lock_waits: on # log lock wait info
  244. log_replication_commands: on # log replication info
  245. log_statement: ddl # log ddl change
  246. log_min_duration_statement: 100 # log slow query (>100ms)
  247. #----------------------------------------------------------------------
  248. # STATISTICS
  249. #----------------------------------------------------------------------
  250. track_io_timing: on # collect io statistics
  251. track_functions: all # track all functions (none|pl|all)
  252. track_activity_query_size: 8192 # max query length in pg_stat_activity
  253. #----------------------------------------------------------------------
  254. # AUTOVACUUM
  255. #----------------------------------------------------------------------
  256. log_autovacuum_min_duration: 1s # log autovacuum activity take more than 1s
  257. autovacuum_max_workers: 3 # default autovacuum worker 3
  258. autovacuum_naptime: 1min # default autovacuum naptime 1min
  259. autovacuum_vacuum_scale_factor: 0.08 # fraction of table size before vacuum 20% -> 8%
  260. autovacuum_analyze_scale_factor: 0.04 # fraction of table size before analyze 10% -> 4%
  261. autovacuum_vacuum_cost_delay: -1 # default vacuum cost delay: same as vacuum_cost_delay
  262. autovacuum_vacuum_cost_limit: -1 # default vacuum cost limit: same as vacuum_cost_limit
  263. autovacuum_freeze_max_age: 100000000 # age > 1 billion triggers force vacuum
  264. #----------------------------------------------------------------------
  265. # CLIENT
  266. #----------------------------------------------------------------------
  267. deadlock_timeout: 50ms # 50ms for deadlock
  268. idle_in_transaction_session_timeout: 10min # 10min timeout for idle in transaction
  269. #----------------------------------------------------------------------
  270. # CUSTOMIZED OPTIONS
  271. #----------------------------------------------------------------------
  272. # extensions
  273. shared_preload_libraries: '{{ pg_shared_libraries | default("pg_stat_statements, auto_explain") }}'
  274. # auto_explain
  275. auto_explain.log_min_duration: 1s # auto explain query slower than 1s
  276. auto_explain.log_analyze: true # explain analyze
  277. auto_explain.log_verbose: true # explain verbose
  278. auto_explain.log_timing: true # explain timing
  279. auto_explain.log_nested_statements: true
  280. # pg_stat_statements
  281. pg_stat_statements.max: 10000 # 5000 -> 10000 queries
  282. pg_stat_statements.track: all # track all statements (all|top|none)
  283. pg_stat_statements.track_utility: off # do not track query other than CRUD
  284. pg_stat_statements.track_planning: off # do not track planning metrics
  285. #------------------------------------------------------------------------------
  286. # postgres
  287. #------------------------------------------------------------------------------
  288. postgresql:
  289. #----------------------------------------------------------------------------
  290. # how to connect to postgres
  291. #----------------------------------------------------------------------------
  292. bin_dir: {{ pg_bin_dir }}
  293. data_dir: {{ pg_data }}
  294. config_dir: {{ pg_data }}
  295. pgpass: {{ pg_dbsu_home }}/.pgpass
  296. listen: {{ pg_listen }}:{{ pg_port }}
  297. connect_address: {{ inventory_hostname }}:{{ pg_port }}
  298. use_unix_socket: true # default: /var/run/postgresql, /tmp
  299. #----------------------------------------------------------------------------
  300. # who to connect to postgres
  301. #----------------------------------------------------------------------------
  302. authentication:
  303. superuser:
  304. username: {{ pg_dbsu }}
  305. replication:
  306. username: {{ pg_replication_username }}
  307. password: '{{ pg_replication_password }}'
  308. rewind:
  309. username: {{ pg_replication_username }}
  310. password: '{{ pg_replication_password }}'
  311. #----------------------------------------------------------------------------
  312. # how to react to database operations
  313. #----------------------------------------------------------------------------
  314. # event callback script log: /pg/log/callback.log
  315. callbacks:
  316. on_start: /pg/bin/pg-failover-callback
  317. on_stop: /pg/bin/pg-failover-callback
  318. on_reload: /pg/bin/pg-failover-callback
  319. on_restart: /pg/bin/pg-failover-callback
  320. on_role_change: /pg/bin/pg-failover-callback
  321. # rewind policy: data checksum should be enabled before using rewind
  322. use_pg_rewind: true
  323. remove_data_directory_on_rewind_failure: true
  324. remove_data_directory_on_diverged_timelines: false
  325. #----------------------------------------------------------------------------
  326. # how to create replica
  327. #----------------------------------------------------------------------------
  328. # create replica method: default pg_basebackup
  329. create_replica_methods:
  330. - basebackup
  331. basebackup:
  332. - max-rate: '1000M'
  333. - checkpoint: fast
  334. - status-interva: 1s
  335. - verbose
  336. - progress
  337. #----------------------------------------------------------------------------
  338. # ad hoc parameters (overwrite with default)
  339. #----------------------------------------------------------------------------
  340. # parameters:
  341. #----------------------------------------------------------------------------
  342. # host based authentication, overwrite default pg_hba.conf
  343. #----------------------------------------------------------------------------
  344. # pg_hba:
  345. # - local all postgres ident
  346. # - local all all md5
  347. # - host all all 0.0.0.0/0 md5
  348. # - local replication postgres ident
  349. # - local replication all md5
  350. # - host replication all 0.0.0.0/0 md5
  351. ...

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