PXC on Kubernetes - ProxySQL load balancing

Hi Community,

We’re currently working on a MySQL cluster setup in order to replace our current MySQL standalone server.
We’ve setup a K8S cluster last year, and we wanted to test PXC on this cluster.

We started from PXC operator Helm chart + PXC db Helm Chart to make things easier.
DB cluster seems OK, I have 3 db pods running and synchronised, whith fault tolerance.

However, I am surprised about ProxySQL native configuration (which I enabled in replacement of HAproxy), more specificly about default configuration of load balancing.

bash-4.4$ proxysql-admin --status

mysql_galera_hostgroups row for writer-hostgroup: 11
+--------+--------+---------------+---------+--------+-------------+-----------------------+------------------+
| writer | reader | backup-writer | offline | active | max_writers | writer_is_also_reader | max_trans_behind |
+--------+--------+---------------+---------+--------+-------------+-----------------------+------------------+
| 11     | 10     | 12            | 13      | 1      | 1           | 1                     | 100              |
+--------+--------+---------------+---------+--------+-------------+-----------------------+------------------+

mysql_servers rows for this configuration
+---------------+-------+------------------------------------------------------------------------------------------+------+---------+---------+----------+---------+-----------+
| hostgroup     | hg_id | hostname                                                                                 | port | status  | weight  | max_conn | use_ssl | gtid_port |
+---------------+-------+------------------------------------------------------------------------------------------+------+---------+---------+----------+---------+-----------+
| reader        | 10    | db-cluster01-prod-pxc-pxc-0.db-cluster01-prod-pxc-pxc.db-cluster1-prod.svc.cluster.local | 3306 | ONLINE  | 1000000 | 1000     | 0       | 0         |
| reader        | 10    | db-cluster01-prod-pxc-pxc-1.db-cluster01-prod-pxc-pxc.db-cluster1-prod.svc.cluster.local | 3306 | ONLINE  | 1000    | 1000     | 0       | 0         |
| reader        | 10    | db-cluster01-prod-pxc-pxc-2.db-cluster01-prod-pxc-pxc.db-cluster1-prod.svc.cluster.local | 3306 | ONLINE  | 1000    | 1000     | 0       | 0         |
| writer        | 11    | db-cluster01-prod-pxc-pxc-0.db-cluster01-prod-pxc-pxc.db-cluster1-prod.svc.cluster.local | 3306 | ONLINE  | 1000000 | 1000     | 0       | 0         |
| writer        | 11    | db-cluster01-prod-pxc-pxc-1.db-cluster01-prod-pxc-pxc.db-cluster1-prod.svc.cluster.local | 3306 | SHUNNED | 1000    | 1000     | 0       | 0         |
| writer        | 11    | db-cluster01-prod-pxc-pxc-2.db-cluster01-prod-pxc-pxc.db-cluster1-prod.svc.cluster.local | 3306 | SHUNNED | 1000    | 1000     | 0       | 0         |
| backup-writer | 12    | db-cluster01-prod-pxc-pxc-1.db-cluster01-prod-pxc-pxc.db-cluster1-prod.svc.cluster.local | 3306 | ONLINE  | 1000    | 1000     | 0       | 0         |
| backup-writer | 12    | db-cluster01-prod-pxc-pxc-2.db-cluster01-prod-pxc-pxc.db-cluster1-prod.svc.cluster.local | 3306 | ONLINE  | 1000    | 1000     | 0       | 0         |
+---------------+-------+------------------------------------------------------------------------------------------+------+---------+---------+----------+---------+-----------+

The same pod (pxc-0) is used for both write & read request because of the weight. I can undertsand this config for writer hostgroup (to avoid conflict), but to get better performances and scalability I exepected SELECT request to be balanced on other pods.

So here are my questions:

  • Is it a bad idea to try to split writes & select ? Percona is better than me in mysql stuff, I guess we have this default config for a good reason…
  • If OK, what is the best way to perform this split ? I tried an 'UPDATE mysql_servers SET weight=‘100’ WHERE hostgroup_id=‘10’ AND hostname=‘db-cluster01-prod-pxc-pxc-0.db-cluster01-prod-pxc-pxc.db-cluster1-prod.svc.cluster.local’; but the value is instantly reset to 1000000.

I read the following page, which seems to talk about the same problem, but seems that there is no solution ? Seems strange, ProxySQL is not very usefull if not able to split SQL requests
https://jira.percona.com/browse/K8SPXC-735

Thanks :slight_smile:

– Helm chart:

allowUnsafeConfigurations: false
enableCRValidationWebhook: false
finalizers:
  - delete-pxc-pods-in-order
  - delete-proxysql-pvc
  - delete-pxc-pvc
fullnameOverride: ''
haproxy:
  enabled: false
ignoreAnnotations: null
ignoreLabels: null
initImage: ''
logcollector:
  enabled: true
  image: ''
  imagePullSecrets: []
  resources:
    limits: {}
    requests:
      cpu: 200m
      memory: 100M
nameOverride: ''
operatorImageRepository: percona/percona-xtradb-cluster-operator
pause: false
pmm:
  enabled: true
  image:
    repository: percona/pmm-client
    tag: 2.28.0
  imagePullSecrets: []
  resources:
    limits: {}
    requests:
      cpu: 300m
      memory: 150M
  serverHost: xxxxxxxxxx
  serverUser: admin
proxysql:
  affinity:
    antiAffinityTopologyKey: kubernetes.io/hostname
  annotations: {}
  enabled: true
  gracePeriod: 30
  image: ''
  imagePullSecrets: []
  labels: {}
  livenessDelaySec: 300
  nodeSelector: {}
  persistence:
    accessMode: ReadWriteOnce
    enabled: true
    size: 7Gi
  podDisruptionBudget:
    maxUnavailable: 1
  readinessDelaySec: 15
  resources:
    limits: {}
    requests:
      cpu: 800m
      memory: 2G
  sidecarPVCs: []
  sidecarResources:
    limits: {}
    requests: {}
  sidecarVolumes: []
  sidecars: []
  size: 3
  tolerations: []
  configuration: |
    datadir="/var/lib/proxysql"
    admin_variables =
    {
      admin_credentials="proxyadmin:XXXXXXXXXXXXXX"
      mysql_ifaces="0.0.0.0:6032"
      refresh_interval=2000

      cluster_username="proxyadmin"
      cluster_password="XXXXXXXXXXXX"
      checksum_admin_variables=false
      checksum_ldap_variables=false
      checksum_mysql_variables=false
      cluster_check_interval_ms=200
      cluster_check_status_frequency=100
      cluster_mysql_query_rules_save_to_disk=true
      cluster_mysql_servers_save_to_disk=true
      cluster_mysql_users_save_to_disk=true
      cluster_proxysql_servers_save_to_disk=true
      cluster_mysql_query_rules_diffs_before_sync=1
      cluster_mysql_servers_diffs_before_sync=1
      cluster_mysql_users_diffs_before_sync=1
      cluster_proxysql_servers_diffs_before_sync=1
    }

    mysql_variables=
    {
      monitor_password="XXXXXXXXXXXX"
      monitor_galera_healthcheck_interval=1000
      threads=2
      max_connections=2048
      default_query_delay=0
      default_query_timeout=600000
      poll_timeout=2000
      interfaces="0.0.0.0:3306;0.0.0.0:33062"
      default_schema="information_schema"
      stacksize=1048576
      connect_timeout_server=10000
      monitor_history=60000
      monitor_connect_interval=20000
      monitor_ping_interval=10000
      ping_timeout_server=200
      commands_stats=true
      sessions_sort=true
      have_ssl=false
      ssl_p2s_ca=""
      ssl_p2s_cert=""
      ssl_p2s_key=""
      ssl_p2s_cipher="ECDHE-RSA-AES128-GCM-SHA256"
    }
pxc:
  affinity:
    antiAffinityTopologyKey: kubernetes.io/hostname
  annotations: {}
  autoRecovery: true
  certManager: false
  disableTLS: true
  gracePeriod: 600
  image:
    repository: percona/percona-xtradb-cluster
    tag: 8.0.27-18.1
  imagePullSecrets: []
  labels: {}
  livenessDelaySec: 300
  livenessProbes:
    failureThreshold: 3
    initialDelaySeconds: 300
    periodSeconds: 10
    successThreshold: 1
    timeoutSeconds: 5
  nodeSelector: {}
  persistence:
    accessMode: ReadWriteOnce
    enabled: true
    size: 220Gi
  podDisruptionBudget:
    maxUnavailable: 1
  readinessDelaySec: 15
  readinessProbes:
    failureThreshold: 5
    initialDelaySeconds: 15
    periodSeconds: 30
    successThreshold: 1
    timeoutSeconds: 15
  resources:
    limits: {}
    requests:
      cpu: 2
      memory: 30G
  sidecarPVCs: []
  sidecarResources:
    limits: {}
    requests: {}
  sidecarVolumes: []
  sidecars: []
  size: 3
  tolerations: []
  configuration: >
    [mysqld]

     ### Specific mysql.cnf settings
secrets:
  passwords: xxxxxxxx
  tls: {}
tls: {}
updateStrategy: SmartUpdate
upgradeOptions:
  apply: 8.0-recommended
  schedule: 0 4 * * *
  versionServiceEndpoint: https://check.percona.com

I think it’s similar to my post around HG query rules: