Buy Percona ServicesBuy Now!

Configuring ProxySQL with Percona Xtradb Cluster Operator

  • Filter
  • Time
  • Show
Clear All
new posts

  • Configuring ProxySQL with Percona Xtradb Cluster Operator


    I would like to configure the mysql-default_query_timeout setting for all of my proxysql instances.

    The PXC Operator documentation describes how to add additional MySQL server configuration here, and has an example link here too.

    That same page describes options available for the proxysql instances, but there seems to be no option for configuration.

    Proxysql's documentation shows how these values can be changed, but states that it prefers using its tool for configuration. How do I provide these settings via the PXC operator custom resource?

    Many thanks,

  • #2
    Hello martinfoot thanks for your question. I checked in with the team and we have an answer for you. A similar issue was reported in our Jira system (bugs and features requests), so I'll copy and paste from there but here's the original link :

    please use proxysql admin interface for configuring settings. You can connect into in via the following command
    kubectl exec -it some-name-proxysql-0 -- mysql -h127.0.0.1 -P6032 -uproxyadmin -padmin_password
    please put proxysql pod name instead of "some-name-proxysql-0" (you can get it from "kubectl get pods") and actual proxyadmin password. You can get proxysql admin password from
    kubectl get secrets $(kubectl get pxc -o yaml | grep secretsName: | awk '{print$2}' | xargs echo) -o yaml | grep proxyadmin: | awk '{print$2}' | base64 -D
    Last edited by lorraine.pocklington; 10-31-2019, 05:44 AM.


    • #3
      Thanks for the fast response!

      Question 1:

      please use proxysql admin interface for configuring settings.
      please put proxysql pod name
      If I have N proxysql pods for my cluster of X database instance pods, does this mean I need to make the API call to each of the N pods in the cluster to configure this, or does the operator provision clustering for each of the proxysql pods?

      Question 2:

      Having to make an API call to configure something is not a very declarative way of configuring my cluster, something that's not a great idea for "cloud native" application design principles.

      To do this automatically and declaratively I would need to configure a k8s job to run when each XtraDB cluster comes up (or each proxysql, depending on whether they share config in a clustered fashion). Since I only get control of the custom resource and the operator makes the cluster, I don't see any nice way that I can create this job this automatically. I could enforce that clusters are only made via a helm chart that we publish which does the job of scheduling the API-calling-job in Kubernetes, but it's not ideal. If the configuration is per-proxysql-pod, I'd also need to have that job get run whenever somebody changed the number of proxysql instances. The problem gets worse if we wanted to auto-scale the number of proxysql pods for any reason.

      To me it seems like a better solution would be to allow us to configure ProxySQL in the same way that we can configure the XtraDB cluster with a free text configuration field. This would let kubernetes handle updating config and restarting pods which is something it's really good at.

      Does this sound like something Percona agrees with and would consider implementing?


      • #4
        Hi again.

        It may well be, however we don't handle feature requests via the community forum. If you would like to see this, I'd encourage you to create a ticket on our Jira system and record your request there. In that way, it gets properly assessed and considered by our software engineering teams. It has the added advantage of you having the satisfaction of their considered response and if the feature is accepted for implementation, you'll be notified of progress. You can also follow other requests there.

        The URL is and the operator product requests are filed under the Cloud Dev (code CLOUD) project

        The Forum is more intended for the kind of questions you have in the first part of that last response, and for other community members to chime in on posts when they have their own experiences and ideas to share. I will, though, see if I can get a response from the team on that question.


        • #5

          Thanks for the response. I'll take a look through the code to see if it's something we could contribute back. I can see that the operator is mounting the config as a volume from a configmap in the database instances and I expect the implementation for proxysql configuration may be very similar.

          A few more questions (sorry!):

          1) Is there more information generally available about the Percona Cloud Native Autonomous Database Initiative? The only information I've found is on the page introducing the operator. I do see that one of the Percona Live 2019 talks covers "the current state of the Operator, a brief demonstration of its capabilities, and a preview of the roadmap for the remainder of the year". Is any information like this available in written form?
          2) Is there a plan to have an operator-specific section of the forum, or is it OK to continue posting in the XtraDB Cluster section (I'm aware many people may be using XtraDB Cluster without Kubernetes, and some of those using Kubernetes might be using the community helm chart.
          3) Does Percona have any connection with the community helm chart? I like everything I'm seeing with the operator, and I see the value (as does the community, since more and more projects adopt the operator pattern), but I don't have a good sense of who's using it in production / there's a limited amount of e.g. talks about it online. The community chart on the other hand has some advantages but also some disadvantages;
          - The chart seems to be much more configurable
          - The chart is actively updated
          - It's easier for us to make custom tweaks in the chart than to request (or contribute!) features in the operator
          - I don't have any sense of release schedules for the operator
          - The chart is a community chart so we likely can't get commercial support, and we don't necessarily know that the choices the community makes are the best ones for performance/resilience etc
          - The chart does not include ProxySQL at time of writing
          - The operator provides a nicer interface for scheduling and taking backups as well as doing scaling of the cluster

          Thanks a lot,


          • #6
            (Is it more appropriate to make separate threads for these?)

            Is it possible to configure the state transfer process for new joiners in the cluster? I see the description here, but I was wondering if I could configure e.g. more threads or other things that may have a performance hit but would result in a faster join time?

            Thanks a lot,


            • #7
              Hi martinfoot thanks for all the above. Just so you know, I am going to try to get the right person to chat with you and bring them into the conversation, Might be tomorrow now, bear with me, and I'll see what I can do.


              • #8
                Howdy martinfoot

                Thanks for your questions. My name is Tyler Duzan, and I'm the Product Manager for our MySQL software and our Cloud initiatives, including the Kubernetes Operators. Lorraine reached out to me to ask me to chime in. I'm going to try to address your questions here if I can.

                * ProxySQL Configuration in the Operator: Yes, we should likely implement a ConfigMap for this. Currently it only allows for configuration via the ProxySQL admin interface, and we configure ProxySQL Native Clustering by default, so any configuration changes will automatically be replicated between the ProxySQL Pods. As we have some required configuration for ProxySQL, any ConfigMap support would need to be implemented in a way which allows us to enforce some configuration options, similar to what we did for the server. This wasn't currently on the roadmap, but that's an oversight on my part and can be added. We'd be happy to take any community contributions via Github and JIRA regarding this.

                * My talk at Percona Live 2019: My talk was recorded, and I believe posted on YouTube. I'm not sure how/why it's not showing up on the website for me right now but I will follow-up with the Marketing team. It should be embedded into the talk page you linked so you can view the talk in its entirety there. I've since done a talk at Percona Live Europe 2019 which updates the state of the operators with our 1.2.0 releases and the updated roadmap. The big news is that with the launch of the Percona Distribution for PostgreSQL we've also committed to producing an operator for Postgres.

                * Operator-specific forum: I think Lorraine can best addressed this, as the Community team owns the forums, however for the interim you're quite welcome to continue posting your questions in the PXC section. Generally speaking, we see the operators as a platform enablement tool and a way to improve operational simplicity for our customers, but they are based on the underlying product offerings so are related anyhow.

                * Community Helm chart: Percona does not currently have any involvement with the Helm chart. We chose the Operator pattern because we wanted to make running PXC in Kubernetes something which was production-grade. StatefulSet alone is not sufficient to ensure a positive outcome that can self-heal and handle common failure scenarios cleanly, nor does it adequately address the full lifecycle of a persistent stateful application, like backups, upgrades, and scaling. Additionally Helm charts don't provide good ways to solve many security challenges in Kubernetes in an automated fashion. The Operator pattern allows us to solve these types of issues for our customers and the community based on our established best practices and set a standard for the way that the database should be deployed and run in a containerized environment. Our commitment is to the Operator pattern, although we may offer some limited case Helm charts alongside the Operator meant to be used in conjunction with it. We strongly recommend the use of our Operator over the Helm chart for anything which would be expected to take production workloads for the reasons stated here-in. We are actively developing our operators, do regular releases, and they are in a GA, fully supported state.

                * Configuration of SST process: `wsrep_slave_threads` is a separate variable in the my.cnf, which means it can be set from the ConfigMap successfully. The only variable we put a restriction on is `wsrep_provider_options` due to us needing to enforce specific configuration to make replication encryption work correctly.

                I hope I was able to address your questions suitably. If you have an active support subscription, you're always welcome to submit a ticket which can be escalated to the engineering team for review, or if you'd like to make a feature request or contribution as a community member please open a JIRA ticket in our public JIRA at


                Tyler Duzan
                Tyler Duzan
                Product Manager for MySQL Server Software


                • #9
                  Hi Tyler,

                  Many thanks for the response!

                  Regarding the ConfigMap for ProxySQL, that's great. Is it something I should raise a Jira ticket for, or can I subscribe to an existing one? I can see myself wanting to configure several things:
                  - Query routing rules
                  - Cache configuration
                  - Query logging
                  - User management (via secrets)

                  Is it a chance that this feature is already in Jira as (There's no description)

                  We may also want to configure having a dedicated host for backups in the cluster, but that might have to be at a higher level. For example, currently I can set the number of nodes in my cluster, but does that number include the backups server? What happens when it's set to 1?

                  Regarding the talk, it is available. You just need to post details so the (unlisted youtube) link to the video gets sent. I plan on watching it this afternoon but was just wondering if there was a way to see the roadmap online somewhere?

                  I also realise I'm very off-topic now, I'm going to move unrelated things into their own threads.

                  Thanks a lot,


                  • #10
                    Hi Tyler,

                    I have encountered another issue that I believe requires extra configuration.

                    By default, it looks like the operator creates the following query routing rules:

                    HTML Code:
                      mysql> select username, match_digest, destination_hostgroup from mysql_query_rules; +--------------+---------------------+-----------------------+ | username     | match_digest        | destination_hostgroup | +--------------+---------------------+-----------------------+ | clustercheck | ^SELECT.*FOR UPDATE | 11                    | | clustercheck | ^SELECT             | 10                    | | monitor      | ^SELECT.*FOR UPDATE | 11                    | | monitor      | ^SELECT             | 10                    | | root         | ^SELECT.*FOR UPDATE | 11                    | | root         | ^SELECT             | 10                    | | xtrabackup   | ^SELECT.*FOR UPDATE | 11                    | | xtrabackup   | ^SELECT             | 10                    | +--------------+---------------------+-----------------------+ 8 rows in set (0.00 sec)
                    As per the Multiplexing use case document, there are several things that can cause ad-hoc disabling of multiplexing.

                    During running our test suite (using the default root user while experimenting), we've encountered an issue where we tear down tables using a library that disables foreign key checks, queries for a list of tables from the information_schema database, then runs truncate table on each of them. This code fails until I delete this rule.

                    As with the first issue, I assume the answer is to run an API call to configure this, but it doesn't feel very kubernetes-native. From the documentation, it's unclear to me whether or not I can configure this with a configuration file. If I can, it would be another use case for wanting to configure proxysql. If it isn't, is there a use-case where people can define a set of API calls (or SQL commands) to run when the ProxySQL pod becomes healthy?


                    • #11
                      Originally posted by martinfoot View Post
                      Regarding the talk, it is available. You just need to post details so the (unlisted youtube) link to the video gets sent.
                      I think I was incorrect here. The link on the "Building a Kubernetes Operator for Percona XtraDB Cluster" goes to the video "Building PostgreSQL as an Enterprise Grade Setup Using Open Source Tools".