We have two instances of ProxySQL - one on version 2.4.8 and the other on 2.0.17.
They are running different workloads, but they are both configured to work with the same cluster, running PXC 5.7.34-57.
Over the past few weeks, but at separate times, each ProxySQL instance has briefly crashed and recovered. It has happened multiple times for each instance.
The ProxySQL logs show a signal 11 error. The MySQL error logs do not show corresponding signal 11 errors.
Might this be an issue with ProxySQL or with MySQL? Having trouble figuring out how to diagnose and correct the issue.
Additional details:
All PXC nodes and ProxySQL servers running Ubuntu 20.04.
hello, Does MySQL logs has something which can we relate to proxysql crash time, and do you have Percona PMM for monitoring of proxysql ? if not please configure that, If you could upload the logs and config we might be able to see something thanks
The MySQL logs do not show anything for the time of the last crash. There is actually a gap in all the MySQL logs when the proxy crash happens.
PMM is set up. When isolated to the time of the last crash, nodes 1 and 2 show a dip in available memory and a spike in CPU usage. Node 3 shows a spike in disk reads. The proxy shows a spike in CPU usage, a dip in available memory, and a spike in disk reads. This is all during normal operating hours with our usual workloads. Not sure what to make of it. Image is attached.
2023-08-11T06:45:21.773140Z 3446227 [Note] Aborted connection 3446227 to db: 'MyDB1' user: 'MyUser01' host: 'myserver1.domain.com' (Got an error reading communication packets)
2023-08-11T06:45:33.058809Z 3446237 [Note] Aborted connection 3446237 to db: 'MyDB2' user: 'MyUser02' host: 'myserver1.domain.com' (Got an error reading communication packets)
2023-08-11T06:45:33.077731Z 3446235 [Note] Aborted connection 3446235 to db: 'MyDB1' user: 'MyUser01' host: 'myserver1.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:18.834622Z 3452181 [Note] Aborted connection 3452181 to db: 'MyDB2' user: 'MyUser02' host: 'myserver2.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:25.244584Z 3452188 [Note] Aborted connection 3452188 to db: 'MyDB1' user: 'MyUser01' host: 'myserver2.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:25.245101Z 3452187 [Note] Aborted connection 3452187 to db: 'MyDB2' user: 'MyUser02' host: 'myserver2.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:25.463732Z 3452190 [Note] Aborted connection 3452190 to db: 'MyDB1' user: 'MyUser03' host: 'myserver2.domain.com' (Got an error reading communication packets)
Cluster Node 2:
2023-08-11T06:45:21.841192Z 1141690 [Note] Aborted connection 1141690 to db: 'MyDB2' user: 'MyUser02' host: 'myserver1.domain.com' (Got an error reading communication packets)
2023-08-11T06:45:21.841236Z 1141689 [Note] Aborted connection 1141689 to db: 'MyDB2' user: 'MyUser02' host: 'myserver1.domain.com' (Got an error reading communication packets)
2023-08-11T06:45:21.841238Z 1141688 [Note] Aborted connection 1141688 to db: 'MyDB2' user: 'MyUser02' host: 'myserver1.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:17.315773Z 1147645 [Note] Aborted connection 1147645 to db: 'MyDB2' user: 'MyUser02' host: 'myserver2.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:17.368643Z 1147431 [Note] Aborted connection 1147431 to db: 'MyDB2' user: 'MyUser02' host: 'myserver2.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:18.812941Z 1147648 [Note] Aborted connection 1147648 to db: 'MyDB2' user: 'MyUser02' host: 'myserver2.domain.com' (Got an error reading communication packets)
Cluster Node 3:
2023-08-11T06:45:21.840685Z 4134576 [Note] Aborted connection 4134576 to db: 'MyDB1' user: 'MyUser01' host: 'myserver1.domain.com' (Got an error reading communication packets)
2023-08-11T06:45:33.144470Z 4134584 [Note] Aborted connection 4134584 to db: 'MyDB2' user: 'MyUser02' host: 'myserver1.domain.com' (Got an error reading communication packets)
2023-08-11T06:45:41.971893Z 4134594 [Note] Aborted connection 4134594 to db: 'MyDB1' user: 'MyUser01' host: 'myserver1.domain.com' (Got an error reading communication packets)
2023-08-11T07:04:22.327107Z 3560070 [Note] Aborted connection 3560070 to db: 'MyDB3' user: 'MyUser04' host: 'myproxy1.domain.com' (Got timeout reading communication packets)
2023-08-11T09:40:18.813660Z 4140537 [Note] Aborted connection 4140537 to db: 'MyDB2' user: 'MyUser02' host: 'myserver2.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:18.815608Z 4140536 [Note] Aborted connection 4140536 to db: 'MyDB2' user: 'MyUser02' host: 'myserver2.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:18.816137Z 4140539 [Note] Aborted connection 4140539 to db: 'MyDB2' user: 'MyUser02' host: 'myserver2.domain.com' (Got an error reading communication packets)
2023-08-11T09:40:22.639334Z 4140543 [Note] Aborted connection 4140543 to db: 'MyDB2' user: 'MyUser02' host: 'myserver2.domain.com' (Got an error reading communication packets)
my.cnf is identical across all three nodes, except for server IDs:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /mydatadir
tmpdir = /mytmpdir
language = /usr/share/mysql/english
skip-external-locking = 1
show_compatibility_56 = 1
pxc-encrypt-cluster-traffic = 1
early-plugin-load = keyring_vault.so
keyring_vault_config = keyring.conf
sql_mode = NO_ENGINE_SUBSTITUTION
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 6
key_buffer_size = 128M
max_connections = 600
open-files-limit = 65536
max_allowed_packet = 16M
max_heap_table_size = 128M
tmp_table_size = 128M
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
join_buffer_size = 3M
default-storage-engine = innodb
innodb_buffer_pool_size = 200G
innodb_buffer_pool_instances=8
innodb_buffer_pool_chunk_size = 268435456
innodb_log_file_size = 1G
innodb_log_buffer_size = 4M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_format = barracuda
innodb_support_xa = 0
innodb_file_per_table = true
#########################
#### BEG REPLICATION ####
#########################
#log_bin=/var/log/mysql/mysql-bin.log
#log_bin
log-bin =/mysql_binlogs/mysql-bin.log
binlog_format=ROW
log_slave_updates=1
server-id=10
expire_logs_days = 3
max_binlog_size = 500M
#########################
#### END REPLICATION ####
#########################
#########################
#### GALERA SETTINGS ####
#########################
#server_id = 10
wsrep_provider = /usr/lib/galera3/libgalera_smm.so
wsrep-cluster-address = gcomm://172.16.12.1,172.16.12.2,172.16.12.3
wsrep_node_address = 172.16.12.1 #####CHANGE
wsrep-cluster-name = my_pxc
wsrep_data_home_dir = /mydatadir
wsrep_on = ON
wsrep-provider-options = "gcache.size=1024M;gcs.fc_limit = 32;"
wsrep_provider_options = "gcs.fc_master_slave = 1;gcache.size=50G;"
wsrep-slave-threads = 4
wsrep-sst-receive-address = AUTO
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = mysstuser:mysstpw
pxc_strict_mode = PERMISSIVE
#########################
###### END GALERA ######
#########################
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
[xtrabackup]
open-files-limit=10000
#####9/13/2021
[sst]
wsrep_debug = ON
#####END 9/13/2021
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
