Not the answer you need?
Register and ask your own question!

Question about proxysql weighting

ThaylinThaylin Current User Role Novice

I jsut setup a dev instance running mariadb 10.5 galera cluster and using proxysql 2.0.14 on rhel 8. I setup the system in the mysql_galera_hostgroups setup.


Hostgroup 1 I made the writer, 2 the reader, 3 the backup writer. I also enabled writer is a reader.

I then added machines to them

1) servera

2) serverb,serverc

weights 1,1

3) serverb,serverc

weights 10000,1000


The issue I am having is when I look at the runtime_mysql_servers I am seeing all the weights based off the backup writer hostgroup weights and not the reader weights,

Did me adding a hostgroup 3 override hostgroup 2?


Admin> SELECT * FROM mysql_galera_hostgroups;                       

+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |

+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

| 1        | 3            | 2        | 4         | 1   | 1      | 1           | 0            | NULL  |

+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

1 row in set (0.000 sec)


Admin> select hostgroup_id, comment, hostname, status, weight from runtime_mysql_servers;

+--------------+---------+--------------+---------+---------+

| hostgroup_id | comment | hostname   | status | weight |

+--------------+---------+--------------+---------+---------+

| 1      |     | 10.76.10.222 | SHUNNED | 1000  |

| 3      |     | 10.76.10.221 | ONLINE | 10000  |

| 3      |     | 10.76.10.222 | ONLINE | 1000  |

| 2      |     | 10.76.10.221 | ONLINE | 10000  |

| 2      |     | 10.76.10.220 | ONLINE | 1000000 |

| 2      |     | 10.76.10.222 | ONLINE | 1000  |

| 1      |     | 10.76.10.221 | SHUNNED | 10000  |

| 1      |     | 10.76.10.220 | ONLINE | 1000000 |

+--------------+---------+--------------+---------+---------+

8 rows in set (0.003 sec)


 proxy_mysql_server { '10.76.10.220:3306-1':

  hostname   => '10.76.10.220',

  port     => 3306,

  hostgroup_id => 1,

  weight => 1000000,   

 }  

 proxy_mysql_server { '10.76.10.221:3306-2':

  hostname   => '10.76.10.221',

  port     => 3306,

  hostgroup_id => 2,

  weight => 1,

 }

 proxy_mysql_server { '10.76.10.222:3306-2':

  hostname   => '10.76.10.222',

  port     => 3306,

  hostgroup_id => 2,

  weight => 1,   

 }

 proxy_mysql_server { '10.76.10.221:3306-3':

  hostname   => '10.76.10.221',

  port     => 3306,

  hostgroup_id => 3,

  weight => 10000,

 }

 proxy_mysql_server { '10.76.10.222:3306-3':

  hostname   => '10.76.10.222',

  port     => 3306,

  hostgroup_id => 3,

  weight => 1000,

 }

Best Answer

Answers

  • ThaylinThaylin Current User Role Novice

    Should also point out when I pull hostgroup 1 and 2 only from mysql_servers it shows the correct weight in there:


    +--------------+--------------+--------+---------+---------+

    | hostgroup_id | hostname   | status | weight | comment |

    +--------------+--------------+--------+---------+---------+

    | 1      | 10.76.10.220 | ONLINE | 1000000 |     |

    | 2      | 10.76.10.220 | ONLINE | 1    |     |

    | 2      | 10.76.10.221 | ONLINE | 1    |     |

    | 2      | 10.76.10.222 | ONLINE | 1    |     |

    +--------------+--------------+--------+---------+---------+

  • ThaylinThaylin Current User Role Novice

    Sorry for the formatting. So I should set all the weights to 1? I assumed I needed to weight the writers.

  • matthewbmatthewb Senior [email protected] Percona Staff Role

    mysql_servers will always show you what you inserted. That data is not affected by the running config. runtime_mysql_servers will get modified by proxysql as things change. ProxySQL will never select more than 1 writer at a time in this mode so I do not think it is necessary to weight the writers.

  • ThaylinThaylin Current User Role Novice

    thank you

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.