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. ...

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