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.
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)…
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.
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).
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.