I manage a kubernetes cluster where I just recently added a ProxySQL to handle the query routing between the Master and Read-Replica. In the beginning it seemed to work well but after a few days it crashes when it recieves high traffic. Causing more than 30mins of downtime and I do not have the expertise to figure out why this happens. The Master and Read-Replica is hosted at Google and it's a Mysql 8.0.
ProxySQL log when the crash happened:
ProxySQL log when it came up again:
The docker image I'm using: severalnines/proxysql:1.4.12 With the configuration:
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="USER:PASS"
mysql_ifaces="0.0.0.0:6032"
refresh_interval=2000
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="8.0"
connect_timeout_server=10000
monitor_history=60000
monitor_connect_interval=200000
monitor_ping_interval=200000
ping_interval_server_msec=10000
ping_timeout_server=200
commands_stats=true
sessions_sort=true
monitor_username="USER"
monitor_password="PASS"
}
mysql_replication_hostgroups =
(
{ writer_hostgroup=10, reader_hostgroup=20, comment="MySQL Replication 8.0" }
)
mysql_servers =
(
{ address="10.32.224.4", port=3306, hostgroup=10, max_connections=100 }, # MASTER
{ address="10.32.224.4", port=3306, hostgroup=20, max_connections=100 }, # MASTER
{ address="10.32.224.44", port=3306, hostgroup=20, max_connections=100, weight=10, max_replication_lag=5} # REPLICA
)
mysql_users =
(
{ username = "USER", password = "PASS", default_hostgroup = 10, active = 1 },
{ username = "USER", password = "PASS", default_hostgroup = 10, active = 1 },
{ username = "USER", password = "PASS", default_hostgroup = 10, active = 1 }
)
mysql_query_rules =
(
{
rule_id=100
active=1
match_pattern="^SELECT .* FOR UPDATE"
destination_hostgroup=10
apply=1
},
{
rule_id=200
active=1
match_pattern="^SELECT .*"
destination_hostgroup=20
apply=1
},
{
rule_id=300
active=1
match_pattern=".*"
destination_hostgroup=10
apply=1
}
)