我的书签
添加书签
移除书签OLAP
Patroni OLAP模板，针对高并行，长查询，高吞吐实例优化
Patroni OLAP模板主要针对吞吐量与计算并行度进行优化
此模板针对的机型是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 }} (olap)
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 : Analysis Database Cluster Template
11. # Doc : https://patroni.readthedocs.io/en/latest/SETTINGS.html
12. # Copyright (C) 2018-2021 Ruohang Feng
13. #==============================================================#
15. # OLTP database are optimized for throughput
16. # typical spec: 64 Core | 400 GB RAM | PCI-E SSD xTB
18. ---
19. #------------------------------------------------------------------------------
20. # identity
21. #------------------------------------------------------------------------------
22. namespace: {{ pg_namespace }}/ # namespace
23. scope: {{ pg_cluster }} # cluster name
24. name: {{ pg_instance }} # instance name
26. #------------------------------------------------------------------------------
27. # log
28. #------------------------------------------------------------------------------
29. log:
30. level: INFO # NOTEST|DEBUG|INFO|WARNING|ERROR|CRITICAL
31. dir: /pg/log/ # default log file: /pg/log/patroni.log
32. file_size: 100000000 # 100MB log triggers a log rotate
33. # format: '%(asctime)s %(levelname)s: %(message)s'
35. #------------------------------------------------------------------------------
36. # dcs
37. #------------------------------------------------------------------------------
38. consul:
39. host: 127.0.0.1:8500
40. consistency: default # default|consistent|stale
41. register_service: true
42. service_check_interval: 15s
43. service_tags:
44. - {{ pg_cluster }}
46. #------------------------------------------------------------------------------
47. # api
48. #------------------------------------------------------------------------------
49. # how to expose patroni service
50. # listen on all ipv4, connect via public ip, use same credential as dbuser_monitor
51. restapi:
52. listen: 0.0.0.0:{{ patroni_port }}
53. connect_address: {{ inventory_hostname }}:{{ patroni_port }}
54. authentication:
55. verify_client: none # none|optional|required
56. username: {{ pg_monitor_username }}
57. password: '{{ pg_monitor_password }}'
59. #------------------------------------------------------------------------------
60. # ctl
61. #------------------------------------------------------------------------------
62. ctl:
63. optional:
64. insecure: true
65. # cacert: '/path/to/ca/cert'
66. # certfile: '/path/to/cert/file'
67. # keyfile: '/path/to/key/file'
69. #------------------------------------------------------------------------------
70. # tags
71. #------------------------------------------------------------------------------
72. tags:
73. nofailover: false
74. clonefrom: true
75. noloadbalance: false
76. nosync: false
77. {% if pg_upstream is defined %}
78. replicatefrom: {{ pg_upstream }} # clone from another replica rather than primary
79. {% endif %}
81. #------------------------------------------------------------------------------
82. # watchdog
83. #------------------------------------------------------------------------------
84. # available mode: off|automatic|required
85. watchdog:
86. mode: {{ patroni_watchdog_mode }}
87. device: /dev/watchdog
88. # safety_margin: 10s
90. #------------------------------------------------------------------------------
91. # bootstrap
92. #------------------------------------------------------------------------------
93. bootstrap:
95. #----------------------------------------------------------------------------
96. # bootstrap method
97. #----------------------------------------------------------------------------
98. method: initdb
99. # add custom bootstrap method here
101. # default bootstrap method: initdb
102. initdb:
103. - locale: C
104. - encoding: UTF8
105. # - data-checksums # enable data-checksum
108. #----------------------------------------------------------------------------
109. # bootstrap users
110. #---------------------------------------------------------------------------
111. # additional users which need to be created after initializing new cluster
112. # replication user and monitor user are required
113. users:
114. {{ pg_replication_username }}:
115. password: '{{ pg_replication_password }}'
116. {{ pg_monitor_username }}:
117. password: '{{ pg_monitor_password }}'
118. {{ pg_admin_username }}:
119. password: '{{ pg_admin_password }}'
121. # bootstrap hba, allow local and intranet password access & replication
122. # will be overwritten later
123. pg_hba:
124. - local all postgres ident
125. - local all all md5
126. - host all all 0.0.0.0/0 md5
127. - local replication postgres ident
128. - local replication all md5
129. - host replication all 0.0.0.0/0 md5
132. #----------------------------------------------------------------------------
133. # template
134. #---------------------------------------------------------------------------
135. # post_init: /pg/bin/pg-init
137. #----------------------------------------------------------------------------
138. # bootstrap config
139. #---------------------------------------------------------------------------
140. # this section will be written to /{{ pg_namespace }}/{{ pg_cluster }}/config
141. # if will NOT take any effect after cluster bootstrap
142. dcs:
144. {% if pg_role == 'primary' and pg_upstream is defined %}
145. #----------------------------------------------------------------------------
146. # standby cluster definition
147. #---------------------------------------------------------------------------
148. standby_cluster:
149. host: {{ pg_upstream }}
150. port: {{ pg_port }}
151. # primary_slot_name: patroni # must be create manually on upstream server, if specified
152. create_replica_methods:
153. - basebackup
154. {% endif %}
156. #----------------------------------------------------------------------------
157. # important parameters
158. #---------------------------------------------------------------------------
159. # constraint: ttl >: loop_wait + retry_timeout * 2
161. # the number of seconds the loop will sleep. Default value: 10
162. # this is patroni check loop interval
163. loop_wait: 10
165. # 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
166. # config this according to your network condition to avoid false-positive failover
167. ttl: 30
169. # 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
170. retry_timeout: 10
172. # the amount of time a master is allowed to recover from failures before failover is triggered (in seconds)
173. # Max RTO: 2 loop wait + master_start_timeout
174. master_start_timeout: 10
176. # import: candidate will not be promoted if replication lag is higher than this
177. # maximum RPO: 16MB (analysis tolerate more data loss)
178. maximum_lag_on_failover: 16777216
180. # The number of seconds Patroni is allowed to wait when stopping Postgres and effective only when synchronous_mode is enabled
181. master_stop_timeout: 30
183. # 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
184. # set to true for RPO mode
185. synchronous_mode: false
187. # prevents disabling synchronous replication if no synchronous replicas are available, blocking all client writes to the master
188. synchronous_mode_strict: false
191. #----------------------------------------------------------------------------
192. # postgres parameters
193. #---------------------------------------------------------------------------
194. postgresql:
195. use_slots: true
196. use_pg_rewind: true
197. remove_data_directory_on_rewind_failure: true
199. parameters:
200. #----------------------------------------------------------------------
201. # IMPORTANT PARAMETERS
202. #----------------------------------------------------------------------
203. max_connections: 400 # 100 -> 400
204. superuser_reserved_connections: 10 # reserve 10 connection for su
205. max_locks_per_transaction: 256 # 64 -> 256 (analysis)
206. max_prepared_transactions: 0 # 0 disable 2PC
207. track_commit_timestamp: on # enabled xact timestamp
208. max_worker_processes: 64 # default 8 -> 64, SET THIS ACCORDING TO YOUR CPU CORES
209. wal_level: logical # logical
210. wal_log_hints: on # wal log hints to support rewind
211. max_wal_senders: 16 # 10 -> 16
212. max_replication_slots: 16 # 10 -> 16
213. wal_keep_size: 100GB # keep at least 100GB WAL
214. password_encryption: md5 # use traditional md5 auth
216. #----------------------------------------------------------------------
217. # RESOURCE USAGE (except WAL)
218. #----------------------------------------------------------------------
219. # memory: shared_buffers and maintenance_work_mem will be dynamically set
220. shared_buffers: {{ pg_shared_buffers }}
221. maintenance_work_mem: {{ pg_maintenance_work_mem }}
222. work_mem: 128MB # 4MB -> 128MB (analysis)
223. huge_pages: try # try huge pages
224. temp_file_limit: 500GB # 0 -> 500GB (analysis)
225. vacuum_cost_delay: 2ms # wait 2ms per 10000 cost
226. vacuum_cost_limit: 10000 # 10000 cost each round
227. bgwriter_delay: 10ms # check dirty page every 10ms
228. bgwriter_lru_maxpages: 1600 # 100 -> 1600 (analysis)
229. bgwriter_lru_multiplier: 5.0 # 2.0 -> 5.0 more cushion buffer
230. max_parallel_workers: 64 # SET THIS ACCORDING TO YOUR CPU CORES
231. max_parallel_workers_per_gather: 64 # SET THIS ACCORDING TO YOUR CPU CORES
232. max_parallel_maintenance_workers: 4 # 2 -> 4
234. #----------------------------------------------------------------------
235. # WAL
236. #----------------------------------------------------------------------
237. wal_buffers: 16MB # max to 16MB
238. wal_writer_delay: 20ms # wait period
239. wal_writer_flush_after: 16MB # max allowed data loss (analysis)
240. min_wal_size: 100GB # at least 100GB WAL
241. max_wal_size: 400GB # at most 400GB WAL
242. commit_delay: 20 # 200ms -> 20ms, increase speed
243. commit_siblings: 10 # 5 -> 10
244. checkpoint_timeout: 60min # checkpoint 5min -> 1h
245. checkpoint_completion_target: 0.95 # 0.5 -> 0.95
246. archive_mode: on
247. 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'
249. #----------------------------------------------------------------------
250. # REPLICATION
251. #----------------------------------------------------------------------
252. # synchronous_standby_names: ''
253. vacuum_defer_cleanup_age: 0 # 0 (default)
254. promote_trigger_file: promote.signal # default promote trigger file path
255. max_standby_archive_delay: 10min # max delay before canceling queries when reading WAL from archive;
256. max_standby_streaming_delay: 3min # max delay before canceling queries when reading streaming WAL;
257. wal_receiver_status_interval: 1s # send replies at least this often
258. hot_standby_feedback: on # send info from standby to prevent query conflicts
259. wal_receiver_timeout: 60s # time that receiver waits for
260. max_logical_replication_workers: 8 # 4 -> 8
261. max_sync_workers_per_subscription: 8 # 4 -> 8
263. #----------------------------------------------------------------------
264. # QUERY TUNING
265. #----------------------------------------------------------------------
266. # planner
267. enable_partitionwise_join: on # enable on analysis
268. random_page_cost: 1.1 # 4 for HDD, 1.1 for SSD
269. effective_cache_size: 320GB # max mem - shared buffer
270. default_statistics_target: 1000 # stat bucket 100 -> 1000
271. jit: on # default on
272. jit_above_cost: 100000 # default jit threshold
274. #----------------------------------------------------------------------
275. # REPORTING AND LOGGING
276. #----------------------------------------------------------------------
277. log_destination: csvlog # use standard csv log
278. logging_collector: on # enable csvlog
279. log_directory: log # default log dir: /pg/data/log
280. # log_filename: 'postgresql-%a.log' # weekly auto-recycle
281. log_filename: 'postgresql-%Y-%m-%d.log' # YYYY-MM-DD full log retention
282. log_checkpoints: on # log checkpoint info
283. log_lock_waits: on # log lock wait info
284. log_replication_commands: on # log replication info
285. log_statement: ddl # log ddl change
286. log_min_duration_statement: 1000 # log slow query (>1s)
288. #----------------------------------------------------------------------
289. # STATISTICS
290. #----------------------------------------------------------------------
291. track_io_timing: on # collect io statistics
292. track_functions: all # track all functions (none|pl|all)
293. track_activity_query_size: 8192 # max query length in pg_stat_activity
295. #----------------------------------------------------------------------
296. # AUTOVACUUM
297. #----------------------------------------------------------------------
298. log_autovacuum_min_duration: 1s # log autovacuum activity take more than 1s
299. autovacuum_max_workers: 3 # default autovacuum worker 3
300. autovacuum_naptime: 1min # default autovacuum naptime 1min
301. autovacuum_vacuum_scale_factor: 0.08 # fraction of table size before vacuum 20% -> 8%
302. autovacuum_analyze_scale_factor: 0.04 # fraction of table size before analyze 10% -> 4%
303. autovacuum_vacuum_cost_delay: -1 # default vacuum cost delay: same as vacuum_cost_delay
304. autovacuum_vacuum_cost_limit: -1 # default vacuum cost limit: same as vacuum_cost_limit
305. autovacuum_freeze_max_age: 100000000 # age > 1 billion triggers force vacuum
307. #----------------------------------------------------------------------
308. # CLIENT
309. #----------------------------------------------------------------------
310. deadlock_timeout: 50ms # 50ms for deadlock
311. idle_in_transaction_session_timeout: 0 # Disable idle in xact timeout in analysis database
313. #----------------------------------------------------------------------
314. # CUSTOMIZED OPTIONS
315. #----------------------------------------------------------------------
316. # extensions
317. shared_preload_libraries: '{{ pg_shared_libraries | default("pg_stat_statements, auto_explain") }}'
319. # auto_explain
320. auto_explain.log_min_duration: 1s # auto explain query slower than 1s
321. auto_explain.log_analyze: true # explain analyze
322. auto_explain.log_verbose: true # explain verbose
323. auto_explain.log_timing: true # explain timing
324. auto_explain.log_nested_statements: true
326. # pg_stat_statements
327. pg_stat_statements.max: 10000 # 5000 -> 10000 queries
328. pg_stat_statements.track: all # track all statements (all|top|none)
329. pg_stat_statements.track_utility: off # do not track query other than CRUD
330. pg_stat_statements.track_planning: off # do not track planning metrics
333. #------------------------------------------------------------------------------
334. # postgres
335. #------------------------------------------------------------------------------
336. postgresql:
338. #----------------------------------------------------------------------------
339. # how to connect to postgres
340. #----------------------------------------------------------------------------
341. bin_dir: {{ pg_bin_dir }}
342. data_dir: {{ pg_data }}
343. config_dir: {{ pg_data }}
344. pgpass: {{ pg_dbsu_home }}/.pgpass
345. listen: {{ pg_listen }}:{{ pg_port }}
346. connect_address: {{ inventory_hostname }}:{{ pg_port }}
347. use_unix_socket: true # default: /var/run/postgresql, /tmp
349. #----------------------------------------------------------------------------
350. # who to connect to postgres
351. #----------------------------------------------------------------------------
352. authentication:
353. superuser:
354. username: {{ pg_dbsu }}
355. replication:
356. username: {{ pg_replication_username }}
357. password: '{{ pg_replication_password }}'
358. rewind:
359. username: {{ pg_replication_username }}
360. password: '{{ pg_replication_password }}'
362. #----------------------------------------------------------------------------
363. # how to react to database operations
364. #----------------------------------------------------------------------------
365. # event callback script log: /pg/log/callback.log
366. callbacks:
367. on_start: /pg/bin/pg-failover-callback
368. on_stop: /pg/bin/pg-failover-callback
369. on_reload: /pg/bin/pg-failover-callback
370. on_restart: /pg/bin/pg-failover-callback
371. on_role_change: /pg/bin/pg-failover-callback
373. # rewind policy: data checksum should be enabled before using rewind
374. use_pg_rewind: true
375. remove_data_directory_on_rewind_failure: true
376. remove_data_directory_on_diverged_timelines: false
378. #----------------------------------------------------------------------------
379. # how to create replica
380. #----------------------------------------------------------------------------
381. # create replica method: default pg_basebackup
382. create_replica_methods:
383. - basebackup
384. basebackup:
385. - max-rate: '1000M'
386. - checkpoint: fast
387. - status-interva: 1s
388. - verbose
389. - progress
391. #----------------------------------------------------------------------------
392. # ad hoc parameters (overwrite with default)
393. #----------------------------------------------------------------------------
394. # parameters:
396. #----------------------------------------------------------------------------
397. # host based authentication, overwrite default pg_hba.conf
398. #----------------------------------------------------------------------------
399. # pg_hba:
400. # - local all postgres ident
401. # - local all all md5
402. # - host all all 0.0.0.0/0 md5
403. # - local replication postgres ident
404. # - local replication all md5
405. # - host replication all 0.0.0.0/0 md5
407. ...
Last modified 2022-05-27: init commit (1e3e284)
