proxysql.yaml.j2

# This configuration file is used to configure proxysql.
#
# Admin_variables: https://proxysql.com/documentation/global-variables/admin-variables
# Mysql_variables: https://proxysql.com/documentation/global-variables/mysql-variables
# Mysql_servers: https://proxysql.com/documentation/main-runtime/#mysql_servers
# Mysql_galera_hostgroups: https://proxysql.com/documentation/main-runtime/#mysql_galera_hostgroups

datadir: "/var/lib/proxysql"
errorlog: "/var/log/kolla/proxysql/proxysql.log"

admin_variables:
   admin_credentials: "{{ proxysql_admin_user }}:{{ proxysql_admin_password }}"
   mysql_ifaces: "{{ api_interface_address | put_address_in_context('url') }}:{{ proxysql_admin_port }};{{ kolla_internal_vip_address | put_address_in_context('url') }}:{{ proxysql_admin_port }};/var/lib/kolla/proxysql/admin.sock"
   stats_credentials: "{{ proxysql_stats_user }}:{{ proxysql_stats_password }}"
   restapi_enabled: "{{ enable_prometheus_proxysql_exporter | bool }}"
   restapi_port: "{{ proxysql_prometheus_exporter_port }}"
   prometheus_memory_metrics_interval: "{{ proxysql_prometheus_exporter_memory_metrics_interval }}"

mysql_variables:
   threads: {{ proxysql_workers }}
   max_connections: {{ proxysql_max_connections }}
   interfaces: "{{ kolla_internal_vip_address | put_address_in_context('url') }}:{{ database_port }}"
   connect_retries_delay: "{{ mariadb_connect_retries_delay }}"
   connect_retries_on_failure: "{{ mariadb_connect_retries_on_failure }}"
   shun_on_failures: "{{ mariadb_shun_on_failures }}"
   monitor_username: "{{ mariadb_monitor_user }}"
   monitor_password: "{{ mariadb_monitor_password }}"
   monitor_connect_interval: "{{ mariadb_monitor_connect_interval }}"
   monitor_galera_healthcheck_interval: "{{ mariadb_monitor_galera_healthcheck_interval }}"
   monitor_galera_healthcheck_timeout: "{{ mariadb_monitor_galera_healthcheck_timeout }}"
   monitor_galera_healthcheck_max_timeout_count: "{{ mariadb_monitor_galera_healthcheck_max_timeout_count }}"
   monitor_ping_interval: "{{ mariadb_monitor_ping_interval }}"
   monitor_ping_timeout: "{{ mariadb_monitor_ping_timeout }}"
   monitor_ping_max_failures: "{{ mariadb_monitor_ping_max_failures }}"
{% if mariadb_monitor_read_only_interval | length > 0 %}
   monitor_read_only_interval: {{ mariadb_monitor_read_only_interval }}
{% endif %}
   monitor_connect_timeout: 6000
   connect_timeout_client: 100000
   connect_timeout_server: 30000
   connect_timeout_server_max: 100000
{% if database_enable_tls_backend | bool %}
   ssl_p2s_ca: "/etc/proxysql/certs/root.crt"
   ssl_p2s_cert: "/etc/proxysql/certs/mariadb-cert.pem"
   ssl_p2s_key: "/etc/proxysql/certs/mariadb-key.pem"
   have_ssl: true
{% endif %}

mysql_servers:
{% for shard_id, shard in mariadb_shards_info.shards.items() %}
{% set WRITER_GROUP = shard_id | int * 10 %}
{% for host in shard.hosts %}
{% if loop.first %}
{% set WEIGHT = 100 %}
{% else %}
{% set WEIGHT = 10 %}
{% endif %}
  - address: "{{ 'api' | kolla_address(host) }}"
    port :  {{ mariadb_port }}
    hostgroup :  {{ WRITER_GROUP }}
    max_connections: {{ proxysql_backend_max_connections }}
    max_replication_lag: {{ proxysql_backend_max_replication_lag }}
    weight :  {{ WEIGHT }}
    comment :  "Writer {{ host }}"
{% if database_enable_tls_backend | bool %}
    use_ssl: 1
{% endif %}
{% endfor %}
{% endfor %}

mysql_galera_hostgroups:
{% for shard_id, shard in mariadb_shards_info.shards.items() %}
{% set WRITER_GROUP = shard_id | int * 10 %}
{% set BACKUP_WRITER_GROUP = WRITER_GROUP | int + 1 %}
{% set READER_GROUP = BACKUP_WRITER_GROUP | int + 1 %}
{% set OFFLINE_GROUP = READER_GROUP | int + 1 %}
  - writer_hostgroup: {{ WRITER_GROUP }}
    backup_writer_hostgroup: {{ BACKUP_WRITER_GROUP }}
    reader_hostgroup: {{ READER_GROUP }}
    offline_hostgroup: {{ OFFLINE_GROUP }}
    max_connections: {{ proxysql_backend_max_connections }}
    max_writers: 1
    writer_is_also_reader: 0
    comment: "Galera cluster for shard {{ shard_id }}"
{% endfor %}