Writer instance is registered as Reader hostgroup even if mysql_aws_aurora_hostgroups.writer_is_also_reader=0

[sunguck]

Using Aurora MySQL + ProxySQL 2.6.3

ProxySQL-Admin> SELECT VERSION();
+--------------------+
| version()          |
+--------------------+
| 2.6.3-107-gcdfcfdc |
+--------------------+

Writer instance is registered as Reader hostgroup even if mysql_aws_aurora_hostgroups.writer_is_also_reader=FALSE.
I have also attached ProxySQL log file

Test procedure

1. Load mysql_hostgroup_attributes by SQL first.

ProxySQL-Admin> select * from mysql_hostgroup_attributes;
+--------------+------------------------+------------+----------------------+--------------+-----------+--------------------+------------------------------+--------------------------+--------------------+--------------------------------------------------------+---------+
| hostgroup_id | max_num_online_servers | autocommit | free_connections_pct | init_connect | multiplex | connection_warming | throttle_connections_per_sec | ignore_session_variables | hostgroup_settings | servers_defaults                                       | comment |
+--------------+------------------------+------------+----------------------+--------------+-----------+--------------------+------------------------------+--------------------------+--------------------+--------------------------------------------------------+---------+
| 0            | 1000000                | -1         | 10                   |              | 1         | 1                  | 1000000                      |                          |                    | {"weight": 100, "max_connections": 7000, "use_ssl": 0} |         |
| 1            | 1000000                | -1         | 10                   |              | 1         | 1                  | 1000000                      |                          |                    | {"weight": 100, "max_connections": 7000, "use_ssl": 0} |         |
+--------------+------------------------+------------+----------------------+--------------+-----------+--------------------+------------------------------+--------------------------+--------------------+--------------------------------------------------------+---------+

2. Startup proxysql with following proxysql.cnf

datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"

admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:3309"
    web_enabled="true"
    web_port=80
}

mysql_variables=
{
    threads=2
    max_connections=50000
    connection_max_age_ms=1200000
    monitor_writer_is_also_reader=0
    default_query_delay=0
    default_query_timeout=86400000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:3306;0.0.0.0:3307"
    default_schema="proxysql"
    stacksize=1048576
    server_version="8.0"
    default_charset="utf8mb4"
    default_collation_connection="utf8mb4_0900_ai_ci"
    monitor_connect_timeout=1000
    monitor_ping_max_failures=10
    monitor_ping_timeout=1000
    connect_timeout_server=3000
    monitor_username="proxysql_monitor"
    monitor_password="passw0rd"
    monitor_history=600000
    monitor_connect_interval=2000
    monitor_ping_interval=2000
    monitor_read_only_interval=2000
    monitor_read_only_timeout=1000
    ping_interval_server_msec=12000
    ping_timeout_server=1000
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
    monitor_query_variables="SELECT * FROM PERFORMANCE_SCHEMA.GLOBAL_VARIABLES"
    monitor_query_status="SELECT * FROM PERFORMANCE_SCHEMA.GLOBAL_STATUS"
}

# defines all the MySQL servers
mysql_servers=
(
    { hostgroup=1, address="test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com"      port=3306, use_ssl=0, weight=100, max_connections=7000 },
    { hostgroup=1, address="test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com"      port=3306, use_ssl=0, weight=100, max_connections=7000 },
    { hostgroup=1, address="test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com" port=3306, use_ssl=0, weight=0, max_connections=0 }
)

# defines all the MySQL users
mysql_users=
(
    { username="proxysql", password="passw0rd", default_hostgroup=10, use_ssl=0, default_schema="proxysql" active=1, fast_forward=0, transaction_persistent=1, max_connections=50000 }
)
mysql_aws_aurora_hostgroups=
(
    { writer_hostgroup=0, reader_hostgroup=1, domain_name=".a00000000000.ap-northeast-2.rds.amazonaws.com", writer_is_also_reader=0, new_reader_weight=100 }
)

#defines MySQL Query Rules
mysql_query_rules=
(
    {rule_id=1, proxy_port=3306, destination_hostgroup=0, apply=1, multiplex=1, active=1},
    {rule_id=2, proxy_port=3307, destination_hostgroup=1, apply=1, multiplex=1, active=1}
)

Just after restart, looks autodiscovery of proxysql is working fine.

ProxySQL-Admin> select * from mysql_servers;
+--------------+--------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                                           | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 | 0         | ONLINE | 100    | 0           | 7000            | 0                   | 0       | 0              |         |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 | 0         | ONLINE | 100    | 0           | 7000            | 0                   | 0       | 0              |         |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 | 0         | ONLINE | 0      | 0           | 0               | 0                   | 0       | 0              |         |
+--------------+--------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

ProxySQL-Admin> select * from runtime_mysql_servers;
+--------------+--------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                                           | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 | 0         | ONLINE | 100    | 0           | 7000            | 0                   | 0       | 0              |         |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 | 0         | ONLINE | 100    | 0           | 7000            | 0                   | 0       | 0              |         |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 | 0         | ONLINE | 0      | 0           | 0               | 0                   | 0       | 0              |         |
+--------------+--------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

2. Run "LOAD MYSQL SERVERS TO RUNTIME" command again

But, after run "LOAD MYSQL SERVERS TO RUNTIME" command, aurora mysql writer instance is registered in reader hostgroup too.

ProxySQL-Admin> select * from runtime_mysql_servers;
+--------------+--------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                                           | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 | 0         | ONLINE | 100    | 0           | 7000            | 0                   | 0       | 0              |         |
| 1            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 | 0         | ONLINE | 100    | 0           | 7000            | 0                   | 0       | 0              |         |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 | 0         | ONLINE | 100    | 0           | 7000            | 0                   | 0       | 0              |         |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 | 0         | ONLINE | 0      | 0           | 0               | 0                   | 0       | 0              |         |
+--------------+--------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

Even if writer_is_also_reader=FALSE.

ProxySQL-Admin> select * from mysql_aws_aurora_hostgroups \G
*************************** 1. row ***************************
     writer_hostgroup: 0
     reader_hostgroup: 1
               active: 1
          aurora_port: 3306
          domain_name: .a00000000000.ap-northeast-2.rds.amazonaws.com
           max_lag_ms: 600000
    check_interval_ms: 1000
     check_timeout_ms: 1000
writer_is_also_reader: 0
    new_reader_weight: 100
           add_lag_ms: 30
           min_lag_ms: 30
       lag_num_checks: 1
              comment:

ProxySQL-Admin> select * from global_variables where variable_name='mysql-monitor_writer_is_also_reader';
+-------------------------------------+----------------+
| variable_name                       | variable_value |
+-------------------------------------+----------------+
| mysql-monitor_writer_is_also_reader | 0              |
+-------------------------------------+----------------+

Thank you!

[sunguck]

And also wondering that there's any way to remove Writer instance from Reader hostgroup.

[xykhappy]

@sunguck Have you checked this variable "aurora_max_lag_ms_only_read_from_replicas" ?

[sunguck]

Thanks @xykhappy .

Are you suggesting that I can send all read traffic to read replicas using max_lag_ms annotation on query ?
There's too many queries have to add annotation and we may miss that too.

[xykhappy]

@sunguck That's not the case. Since you're already using the "mysql_aws_aurora_hostgroups", just try to tune "mysql-aurora_max_lag_ms_only_read_from_replicas" to a small value like 0.

Are you suggesting that I can send all read traffic to read replicas using max_lag_ms annotation on query ?

I reviewed your settings once more and feel another possible reason is that you have added the writer specifically to hostgroup 1 in mysql_servers. From my experience, the aurora hostgroup is just responsible for auto discovering or replacing the backend servers, but not for removing redundant ones. Therefore, you should consider defining only hostgroup 0 in mysql_servers and let the ProxySQL do the discovery.

[sunguck]

Thanks @xykhappy

I reviewed your settings once more and feel another possible reason is that you have added the writer specifically to hostgroup 1 in mysql_servers. From my experience, the aurora hostgroup is just responsible for auto discovering or replacing the backend servers, but not for removing redundant ones. Therefore, you should consider defining only hostgroup 0 in mysql_servers and let the ProxySQL do the discovery.

The issue has gone after defining server only hostgroup=0 in mysql_servers.

One more thing is
After configuring server on hostgroup=0, do we supposed to change configuration after failover of aurora mysql cluster ?.
We are going to use proxysql on kubernetes, so proxysql does not have state, and we configure cluster information by only /etc/proxysql.cnf.

[xykhappy]

@sunguck It doesn't matter. The ProxySQL will detect the Aurora role changes and update the runtime_mysql_servers automatically. Anyway you can test this out on your local.

After configuring server on hostgroup=0, do we supposed to change configuration after failover of aurora mysql cluster ?.
We are going to use proxysql on kubernetes, so proxysql does not have state, and we configure cluster information by only /etc/proxysql.cnf.

[sunguck]

I have tested few cases as you suggested, and found no problems when I configure just single node in mysql_servers (CASE-1).
And when I configure full list of aurora mysql servers in mysql_servers, all server need to be configured as correct reader and writer hostgroup (CASE-2 vs CASE-3).

CASE-1

mysql_servers=
(
    { hostgroup=1, address="test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com"      port=3306, use_ssl=0, weight=100, max_connections=7000 }
)

ProxySQL-Admin> select * from mysql_servers;
+--------------+---------------------------------------------------------------+------+
| hostgroup_id | hostname                                                      | port |
+--------------+---------------------------------------------------------------+------+
| 1            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 |
+--------------+---------------------------------------------------------------+------+

ProxySQL-Admin> select * from runtime_mysql_servers;
+--------------+--------------------------------------------------------------------+------+
| hostgroup_id | hostname                                                           | port |
+--------------+--------------------------------------------------------------------+------+
| 0            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 |
+--------------+--------------------------------------------------------------------+------+

ProxySQL-Admin> LOAD MYSQL SERVERS TO RUNTIME;

ProxySQL-Admin> select * from runtime_mysql_servers;
+--------------+--------------------------------------------------------------------+------+
| hostgroup_id | hostname                                                           | port |
+--------------+--------------------------------------------------------------------+------+
| 0            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 |
+--------------+--------------------------------------------------------------------+------+

CASE-2

mysql_servers=
(
    { hostgroup=0, address="test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com"      port=3306, use_ssl=0, weight=100, max_connections=7000 },
    { hostgroup=1, address="test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com"      port=3306, use_ssl=0, weight=100, max_connections=7000 },
    { hostgroup=1, address="test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com" port=3306, use_ssl=0, weight=0, max_connections=0 }
)


ProxySQL-Admin> select * from mysql_servers;
+--------------+--------------------------------------------------------------------+------+
| hostgroup_id | hostname                                                           | port |
+--------------+--------------------------------------------------------------------+------+
| 0            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 |
+--------------+--------------------------------------------------------------------+------+

ProxySQL-Admin> select * from runtime_mysql_servers;
+--------------+--------------------------------------------------------------------+------+
| hostgroup_id | hostname                                                           | port |
+--------------+--------------------------------------------------------------------+------+
| 0            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 |
+--------------+--------------------------------------------------------------------+------+

ProxySQL-Admin> LOAD MYSQL SERVERS TO RUNTIME;

ProxySQL-Admin> select * from runtime_mysql_servers;
+--------------+--------------------------------------------------------------------+------+
| hostgroup_id | hostname                                                           | port |
+--------------+--------------------------------------------------------------------+------+
| 0            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 |
+--------------+--------------------------------------------------------------------+------+

CASE-3

mysql_servers=
(
    { hostgroup=1, address="test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com"      port=3306, use_ssl=0, weight=100, max_connections=7000 },
    { hostgroup=1, address="test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com"      port=3306, use_ssl=0, weight=100, max_connections=7000 },
    { hostgroup=1, address="test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com" port=3306, use_ssl=0, weight=0, max_connections=0 }
)

ProxySQL-Admin> select * from mysql_servers;
+--------------+--------------------------------------------------------------------+------+
| hostgroup_id | hostname                                                           | port |
+--------------+--------------------------------------------------------------------+------+
| 1            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 |
+--------------+--------------------------------------------------------------------+------+

ProxySQL-Admin> select * from runtime_mysql_servers;
+--------------+--------------------------------------------------------------------+------+
| hostgroup_id | hostname                                                           | port |
+--------------+--------------------------------------------------------------------+------+
| 0            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 |
+--------------+--------------------------------------------------------------------+------+

ProxySQL-Admin> LOAD MYSQL SERVERS TO RUNTIME;

ProxySQL-Admin> select * from runtime_mysql_servers;
+--------------+--------------------------------------------------------------------+------+
| hostgroup_id | hostname                                                           | port |
+--------------+--------------------------------------------------------------------+------+
| 0            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-1.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-0.a00000000000.ap-northeast-2.rds.amazonaws.com      | 3306 |
| 1            | test-auroramy-batch0.a00000000000.ap-northeast-2.rds.amazonaws.com | 3306 |
+--------------+--------------------------------------------------------------------+------+