ProxySQL hostgroup 10 and 11 - favouring 10 for reads/writes (XtraDB Cluster Operator)

Polyglot Projects ProxySQL
,
1

Using the Percona XtraDB Cluster Operator and looking at PMM, hostgroup 10 (writer) seems to be getting pretty much all questions/queries, with minimal/almost none going to 11 (reader).

image
image743×388 10.4 KB

Hostgroup 11:
image

Hostgroup 10 (node 0):
image

I’ve also tried to adjust the hostgroup query routing:

proxysql:
  configuration: |
    datadir="/var/lib/proxysql"

    mysql_query_rules:
    (
    {
        rule_id=1
        active=1
        match_pattern="^SELECT .* FOR UPDATE$"
        cache_ttl=5000
        apply=1
        hostgroup=10
        comment="Writer"
    },
    {
        rule_id=2
        active=1
        match_pattern="^INSERT INTO|UPDATE|DELETE"
        cache_ttl=1000
        apply=1
        hostgroup=10
        comment="Writer"
    },
    {
        rule_id=4
        active=1
        match_pattern="^SELECT"
        cache_ttl=5000
        apply=1
        hostgroup=11
    },
    {
        rule_id=5
        active=1
        match_pattern="SELECT .*"
        cache_ttl=5000
        apply=1
        hostgroup=11
    },
    {
        rule_id=6
        active=1
        match_pattern=".*"
        apply=1
        hostgroup=10
    }
    )

Am I missing something here?

2

Seems you are missing the rule to send plain SELECTs to the reader hostgroup. If the default HG for the user is 10, and you have S FOR U and I/U/D also going to 10, and no rule for anything else, then yea, everything will go to HG 10. You need a rule that routes queries to HG 11.

3

Seems I need to double check my rules.
I was going based on Percona’s defaults for 10 being writer, 11 for reader, that IS correct, right?

Also, I noticed the imbalance for queries even without my configuration for hostgroups (using the default out of the box), most, if not all read/writes were going to PXC-0 (the default primary/writer)…

4

Yes, those HGs are correct. But you need to understand that the default HG applies to the user account. So any query executed by user X will always be sent to that user’s default HG unless there is a rule that sends the query to another HG. Yes, there is an imbalance because you don’t have a rule to redirect certain queries to HG 11.

5

I don’t see any documentation on Percona’s implementation of hostgroups with ProxySQL.

I can’t even see in the out-of-the-box setup where HG 10 and 11 is setup in the ProxySQL config.
Why don’t you have read queries directed to all nodes (HG 11/10) and write to 10 by default? Instead out of the box it seems HG 10 (node 0) is getting all the read/writes by default?

I don’t get the bit about per user account, again, I haven’t seen any documentation on how it’s setup by default without specifying a configuration (like I did).

FYI, I updated my rules to:

configuration: |
    datadir="/var/lib/proxysql"

    mysql_variables=
    {
      connect_retries_on_failure=10
      default_query_timeout=36000000
    }

    mysql_query_rules:
    (
    {
        rule_id=1
        active=1
        match_pattern="^SELECT .* FOR UPDATE$"
        cache_ttl=5000
        apply=1
        hostgroup=10
        comment="Writer"
    },
    {
        rule_id=2
        active=1
        match_pattern="^INSERT INTO|UPDATE|DELETE"
        cache_ttl=1000
        apply=1
        hostgroup=10
        comment="Writer"
    },
    {
        rule_id=4
        active=1
        match_pattern="^SELECT"
        cache_ttl=5000
        apply=1
        hostgroup=11
    },
    {
        rule_id=5
        active=1
        match_pattern="SELECT .*"
        cache_ttl=5000
        apply=1
        hostgroup=11
    },
    {
        rule_id=6
        active=1
        match_pattern=".*"
        apply=1
        hostgroup=10
    },
    {
    rule_id=7
    active=1
    match_pattern="^SELECT[^()]*$"
    cache_ttl=5000
    apply=1
    hostgroup=11
    comment="Reader"
    },
   )

I am seeing a lower ACTIVE THREADS for HG 10, which I suppose, this can help distribute the load more evenly across my read replicas, which can reduce the number of active threads on my writer nodes.

image
image1483×387 57.3 KB

Also a vast change in MySQL Thread Cache:

image
image1494×377 65.4 KB

But it seems MySQL Questions for HG 11 has gone down for the new config?

image
image1512×405 64.3 KB

6

You’d probably need to go read ProxySQL’s documentation on their website for more details.

That’s because there isn’t really any “default config”. There are minimum configuration parameters you must provide, like the user, the servers in the HGs, and basic rules. Without any rules, all queries go to the users’ default HG.

You don’t need rules 2, 5, or 6. Any query NOT matching a rule will fall through to the user’s default HG.

What user are you using to connect to ProxySQL? Let’s say it’s “bob”. Well, within ProxySQL, in the mysql_users table, there exists a record for “bob” along with that account’s password. Your application is logging in to proxysql; not mysql. For the “bob” user account in proxysql, there exists a default HG, in this case, 10. All queries executed under “bob” account will route to HG 10, unless there is a query rule which routes it to a different HG.

1 Like
7

I’m still a bit confused about the ProxySQL setup with Percona.

I just checked the logs of ProxySQL and see this:

Cluster node info
±--------------±------±--------------------------------------------------±-----±--------+
| hostgroup | hg_id | hostname | port | weight |
±--------------±------±--------------------------------------------------±-----±--------+
| reader | 10 | pxc-db-pxc-1.pxc-db-pxc.percona.svc.cluster.local | 3306 | 1000 |
| reader | 10 | pxc-db-pxc-2.pxc-db-pxc.percona.svc.cluster.local | 3306 | 1000 |
| reader | 10 | pxc-db-pxc-0.pxc-db-pxc.percona.svc.cluster.local | 3306 | 1000000 |
| writer | 11 | pxc-db-pxc-1.pxc-db-pxc.percona.svc.cluster.local | 3306 | 1000 |
| writer | 11 | pxc-db-pxc-2.pxc-db-pxc.percona.svc.cluster.local | 3306 | 1000 |
| writer | 11 | pxc-db-pxc-0.pxc-db-pxc.percona.svc.cluster.local | 3306 | 1000000 |
| backup-writer | 12 | pxc-db-pxc-1.pxc-db-pxc.percona.svc.cluster.local | 3306 | 1000 |
| backup-writer | 12 | pxc-db-pxc-2.pxc-db-pxc.percona.svc.cluster.local | 3306 | 1000 |

I was under the impression that HG 10 was the writer, from this, it seems it’s 11. But from reading bits and pieces of Percona docs, I thought it was 10, and there is only ONE writer so there is less transaction conflict resolving going on with Galera…?

8

Could be 11, could be 10, could be 1334454. The number itself doesn’t mean anything; it’s just a number.

Show us the contents of runtime_mysql_galera_hostgroups / mysql_galera_hostgroups tables. That will tell you which HG is which.

Yes, there should only be 1 writer in HG11 which should be a setting in the above table.