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. #==============================================================#
  15. # OLTP database are optimized for performance, rt latency
  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: 1MB
  178.     maximum_lag_on_failover: 1048576
  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
  200.       parameters:
  201.         #----------------------------------------------------------------------
  202.         # IMPORTANT PARAMETERS
  203.         #----------------------------------------------------------------------
  204.         max_connections: 400                    # 100 -> 400
  205.         superuser_reserved_connections: 10      # reserve 10 connection for su
  206.         max_locks_per_transaction: 128          # 64 -> 128
  207.         max_prepared_transactions: 0            # 0 disable 2PC
  208.         track_commit_timestamp: on              # enabled xact timestamp
  209.         max_worker_processes: 8                 # default 8, set to cpu core
  210.         wal_level: logical                      # logical
  211.         wal_log_hints: on                       # wal log hints to support rewind
  212.         max_wal_senders: 16                     # 10 -> 16
  213.         max_replication_slots: 16               # 10 -> 16
  214.         wal_keep_size: 100GB                    # keep at least 100GB WAL
  215.         password_encryption: md5                # use traditional md5 auth
  217.         #----------------------------------------------------------------------
  218.         # RESOURCE USAGE (except WAL)
  219.         #----------------------------------------------------------------------
  220.         # memory: shared_buffers and maintenance_work_mem will be dynamically set
  221.         shared_buffers: {{ pg_shared_buffers }}
  222.         maintenance_work_mem: {{ pg_maintenance_work_mem }}
  223.         work_mem: 32MB                          # 4MB -> 32MB
  224.         huge_pages: try                         # try huge pages
  225.         temp_file_limit: 100GB                  # 0 -> 100GB
  226.         vacuum_cost_delay: 2ms                  # wait 2ms per 10000 cost
  227.         vacuum_cost_limit: 10000                # 10000 cost each round
  228.         bgwriter_delay: 10ms                    # check dirty page every 10ms
  229.         bgwriter_lru_maxpages: 800              # 100 -> 800
  230.         bgwriter_lru_multiplier: 5.0            # 2.0 -> 5.0  more cushion buffer
  232.         #----------------------------------------------------------------------
  233.         # WAL
  234.         #----------------------------------------------------------------------
  235.         wal_buffers: 16MB                       # max to 16MB
  236.         wal_writer_delay: 20ms                  # wait period
  237.         wal_writer_flush_after: 1MB             # max allowed data loss
  238.         min_wal_size: 100GB                     # at least 100GB WAL
  239.         max_wal_size: 400GB                     # at most 400GB WAL
  240.         commit_delay: 20                        # 200ms -> 20ms, increase speed
  241.         commit_siblings: 10                     # 5 -> 10
  242.         checkpoint_timeout: 60min               # checkpoint 5min -> 1h
  243.         checkpoint_completion_target: 0.95      # 0.5 -> 0.95
  244.         archive_mode: on
  245.         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'
  247.         #----------------------------------------------------------------------
  248.         # REPLICATION
  249.         #----------------------------------------------------------------------
  250.         # synchronous_standby_names: ''
  251.         vacuum_defer_cleanup_age: 50000         # 0->50000 last 50000 xact changes will not be vacuumed
  252.         promote_trigger_file: promote.signal    # default promote trigger file path
  253.         max_standby_archive_delay: 10min        # max delay before canceling queries when reading WAL from archive;
  254.         max_standby_streaming_delay: 3min       # max delay before canceling queries when reading streaming WAL;
  255.         wal_receiver_status_interval: 1s        # send replies at least this often
  256.         hot_standby_feedback: on                # send info from standby to prevent query conflicts
  257.         wal_receiver_timeout: 60s               # time that receiver waits for
  258.         max_logical_replication_workers: 8      # 4 -> 8
  259.         max_sync_workers_per_subscription: 8    # 4 -> 8
  261.         #----------------------------------------------------------------------
  262.         # QUERY TUNING
  263.         #----------------------------------------------------------------------
  264.         # planner
  265.         # enable_partitionwise_join: on
  266.         random_page_cost: 1.1                   # 4 for HDD, 1.1 for SSD
  267.         effective_cache_size: 320GB             # max mem - shared buffer
  268.         default_statistics_target: 1000         # stat bucket 100 -> 1000
  270.         #----------------------------------------------------------------------
  271.         # REPORTING AND LOGGING
  272.         #----------------------------------------------------------------------
  273.         log_destination: csvlog                 # use standard csv log
  274.         logging_collector: on                   # enable csvlog
  275.         log_directory: log                      # default log dir: /pg/data/log
  276.         # log_filename: 'postgresql-%a.log'     # weekly auto-recycle
  277.         log_filename: 'postgresql-%Y-%m-%d.log' # YYYY-MM-DD full log retention
  278.         log_checkpoints: on                     # log checkpoint info
  279.         log_lock_waits: on                      # log lock wait info
  280.         log_replication_commands: on            # log replication info
  281.         log_statement: ddl                      # log ddl change
  282.         log_min_duration_statement: 100         # log slow query (>100ms)
  284.         #----------------------------------------------------------------------
  285.         # STATISTICS
  286.         #----------------------------------------------------------------------
  287.         track_io_timing: on                     # collect io statistics
  288.         track_functions: all                    # track all functions (none|pl|all)
  289.         track_activity_query_size: 8192         # max query length in pg_stat_activity
  291.         #----------------------------------------------------------------------
  292.         # AUTOVACUUM
  293.         #----------------------------------------------------------------------
  294.         log_autovacuum_min_duration: 1s         # log autovacuum activity take more than 1s
  295.         autovacuum_max_workers: 3               # default autovacuum worker 3
  296.         autovacuum_naptime: 1min                # default autovacuum naptime 1min
  297.         autovacuum_vacuum_scale_factor: 0.08    # fraction of table size before vacuum   20% -> 8%
  298.         autovacuum_analyze_scale_factor: 0.04   # fraction of table size before analyze  10% -> 4%
  299.         autovacuum_vacuum_cost_delay: -1        # default vacuum cost delay: same as vacuum_cost_delay
  300.         autovacuum_vacuum_cost_limit: -1        # default vacuum cost limit: same as vacuum_cost_limit
  301.         autovacuum_freeze_max_age: 100000000    # age > 1 billion triggers force vacuum
  303.         #----------------------------------------------------------------------
  304.         # CLIENT
  305.         #----------------------------------------------------------------------
  306.         deadlock_timeout: 50ms                  # 50ms for deadlock
  307.         idle_in_transaction_session_timeout: 10min  # 10min timeout for idle in transaction
  309.         #----------------------------------------------------------------------
  310.         # CUSTOMIZED OPTIONS
  311.         #----------------------------------------------------------------------
  312.         # extensions
  313.         shared_preload_libraries: '{{ pg_shared_libraries | default("pg_stat_statements, auto_explain") }}'
  315.         # auto_explain
  316.         auto_explain.log_min_duration: 1s       # auto explain query slower than 1s
  317.         auto_explain.log_analyze: true          # explain analyze
  318.         auto_explain.log_verbose: true          # explain verbose
  319.         auto_explain.log_timing: true           # explain timing
  320.         auto_explain.log_nested_statements: true
  322.         # pg_stat_statements
  323.         pg_stat_statements.max: 10000           # 5000 -> 10000 queries
  324.         pg_stat_statements.track: all           # track all statements (all|top|none)
  325.         pg_stat_statements.track_utility: off   # do not track query other than CRUD
  326.         pg_stat_statements.track_planning: off  # do not track planning metrics
  329. #------------------------------------------------------------------------------
  330. # postgres
  331. #------------------------------------------------------------------------------
  332. postgresql:
  334.   #----------------------------------------------------------------------------
  335.   # how to connect to postgres
  336.   #----------------------------------------------------------------------------
  337.   bin_dir: {{ pg_bin_dir }}
  338.   data_dir: {{ pg_data }}
  339.   config_dir: {{ pg_data }}
  340.   pgpass: {{ pg_dbsu_home }}/.pgpass
  341.   listen: {{ pg_listen }}:{{ pg_port }}
  342.   connect_address: {{ inventory_hostname }}:{{ pg_port }}
  343.   use_unix_socket: true # default: /var/run/postgresql, /tmp
  345.   #----------------------------------------------------------------------------
  346.   # who to connect to postgres
  347.   #----------------------------------------------------------------------------
  348.   authentication:
  349.     superuser:
  350.       username: {{ pg_dbsu }}
  351.     replication:
  352.       username: {{ pg_replication_username }}
  353.       password: '{{ pg_replication_password }}'
  354.     rewind:
  355.       username: {{ pg_replication_username }}
  356.       password: '{{ pg_replication_password }}'
  358.   #----------------------------------------------------------------------------
  359.   # how to react to database operations
  360.   #----------------------------------------------------------------------------
  361.   # event callback script log: /pg/log/callback.log
  362.   callbacks:
  363.     on_start: /pg/bin/pg-failover-callback
  364.     on_stop: /pg/bin/pg-failover-callback
  365.     on_reload: /pg/bin/pg-failover-callback
  366.     on_restart: /pg/bin/pg-failover-callback
  367.     on_role_change: /pg/bin/pg-failover-callback
  369.   # rewind policy: data checksum should be enabled before using rewind
  370.   use_pg_rewind: true
  371.   remove_data_directory_on_rewind_failure: true
  372.   remove_data_directory_on_diverged_timelines: false
  374.   #----------------------------------------------------------------------------
  375.   # how to create replica
  376.   #----------------------------------------------------------------------------
  377.   # create replica method: default pg_basebackup
  378.   create_replica_methods:
  379.     - basebackup
  380.   basebackup:
  381.     - max-rate: '1000M'
  382.     - checkpoint: fast
  383.     - status-interva: 1s
  384.     - verbose
  385.     - progress
  387.   #----------------------------------------------------------------------------
  388.   # ad hoc parameters (overwrite with default)
  389.   #----------------------------------------------------------------------------
  390.   # parameters:
  392.   #----------------------------------------------------------------------------
  393.   # host based authentication, overwrite default pg_hba.conf
  394.   #----------------------------------------------------------------------------
  395.   # pg_hba:
  396.   #   - local   all             postgres                                ident
  397.   #   - local   all             all                                     md5
  398.   #   - host    all             all            0.0.0.0/0                md5
  399.   #   - local   replication     postgres                                ident
  400.   #   - local   replication     all                                     md5
  401.   #   - host    replication     all            0.0.0.0/0                md5
  403. ...

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