Every day our environment freezes at about 7:00 am, when we stop the second node the environment returns to respond normally. I believe it is a parameter problem.
Follows the configuration files.
We have two servers only.
wsrep.cnf
[mysqld]# Path to Galera librarywsrep_provider=/usr/lib64/galera3/libgalera_smm.so# Cluster connection URL contains IPs of nodes#If no IP is found, this implies that a new cluster needs to be created,#in order to do that you need to bootstrap this nodewsrep_cluster_address=gcomm://scxxp0816cld,scxxp0817cld# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB# Slave thread to usewsrep_slave_threads= 32wsrep_log_conflicts# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2# Node IP addresswsrep_node_address=10.32.253.180# Cluster namewsrep_cluster_name=pxc-cluster#If wsrep_node_name is not specified, then system hostname will be usedwsrep_node_name=pxc-01#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTERpxc_strict_mode=DISABLED# SST methodwsrep_sst_method=xtrabackup-v2#Authentication for SST methodwsrep_sst_auth=pxcsst:pxcsstpassswordsql_mode=“”
mysqld.cnf
[client]socket=/var/lib/mysql/mysql.sock
[mysqld]server-id=1datadir=/data_apps/mysql/datasocket=/var/lib/mysql/mysql.socklog-error=/data_apps/mysql/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidtmpdir=/data_apps/mysql/tmplog-binlog_slave_updatesexpire_logs_days=7innodb_file_per_table=ONsecure-file-priv = “”# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0innodb_buffer_pool_size=16Ginnodb-buffer-pool-instances=8innodb_buffer_pool_chunk_size=1Gtable_definition_cache=65536table_open_cache=10240innodb_flush_log_at_trx_commit=2sync_binlog=0wait_timeout = 7800interactive_timeout = 4800
mysql> show processlist; - Master server at the time of the problem±------±------------±------------------------±----------±--------±------±-------------------------------------------------±----------------------------------------------------------------------------------------±----------±--------------+| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |±------±------------±------------------------±----------±--------±------±-------------------------------------------------±----------------------------------------------------------------------------------------±----------±--------------+| 1 | system user | | NULL | Sleep | 68326 | wsrep: applier idle | NULL | 0 | 0 || 2 | system user | | NULL | Sleep | 68326 | wsrep: aborter idle | NULL | 0 | 0 || 3 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 4 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 5 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 6 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 7 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 8 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 9 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 10 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 11 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 12 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 13 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 14 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 15 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 16 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 17 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 18 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 19 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 20 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 21 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 22 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 23 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 24 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 25 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 26 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 28 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 29 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 30 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 31 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 32 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 33 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 34 | system user | | NULL | Sleep | 68323 | wsrep: applier idle | NULL | 0 | 0 || 33586 | reflectorw | scxxp0815cld.itau:47154 | reflector | Sleep | 6453 | | NULL | 0 | 0 || 33615 | reflectorw | scxxp0815cld.itau:47218 | reflector | Sleep | 6453 | | NULL | 0 | 0 || 33637 | reflectorw | scxxp0815cld.itau:47266 | reflector | Sleep | 6452 | | NULL | 0 | 0 || 33644 | reflectorw | scxxp0815cld.itau:47280 | reflector | Sleep | 6452 | | NULL | 0 | 0 || 33658 | reflectorw | scxxp0815cld.itau:47308 | reflector | Sleep | 6451 | | NULL | 0 | 0 || 33665 | reflectorw | scxxp0815cld.itau:47322 | reflector | Sleep | 6450 | | NULL | 0 | 0 || 33677 | reflectorw | scxxp0815cld.itau:47346 | reflector | Sleep | 6449 | | NULL | 0 | 0 || 33706 | reflectorw | scxxp0815cld.itau:47404 | reflector | Sleep | 6448 | | NULL | 0 | 0 || 33721 | reflectorw | scxxp0815cld.itau:47436 | reflector | Sleep | 6447 | | NULL | 0 | 0 || 33734 | reflectorw | scxxp0815cld.itau:47466 | reflector | Sleep | 6447 | | NULL | 0 | 0 || 33756 | reflectorw | scxxp0815cld.itau:47510 | reflector | Query | 6446 | checking permissions | DROP TABLE temp_spm_service_portfolio | 0 | 0 || 33763 | datalake | localhost | datalake | Query | 4661 | checking permissions | DROP TABLE IF EXISTS tb_lobs_desc | 0 | 0 || 33772 | datalake | localhost | datalake | Query | 4058 | wsrep: initiating replication for write set (-1) | DELETE FROM TB_DEF_JOB_SERVICE_NOW WHERE DATA_CENTER like ‘DS%’ and AMBIENTE=‘PRODUCAO’ | 0 | 479081 || 33935 | reflectorw | scxxp0813cld.itau:34800 | reflector | Query | 2763 | checking permissions | DROP TABLE temp_u_incident_details | 0 | 0 || 33947 | reflectorw | 10.92.110.82:61754 | reflector | Sleep | 165 | | NULL | 0 | 0 || 33948 | reflectorw | 10.92.110.82:61755 | reflector | Sleep | 165 | | NULL | 0 | 0 || 34021 | reflectorw | 10.92.110.82:62689 | reflector | Sleep | 367 | | NULL | 0 | 0 || 34022 | reflectorw | 10.92.110.82:62690 | reflector | Sleep | 367 | | NULL | 0 | 0 || 34035 | junitur | 10.92.96.118:55987 | reflector | Sleep | 316 | | NULL | 0 | 0 || 34036 | junitur | 10.92.96.118:55988 | reflector | Sleep | 316 | | NULL | 0 | 0 || 34037 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |±------±------------±------------------------±----------±--------±------±-------------------------------------------------±----------------------------------------------------------------------------------------±----------±--------------+54 rows in set (0.00 sec)
another information:
4639 lock struct(s), heap size 483536, 483719 row lock(s), undo log entries 129487
MySQL thread id 33772, OS thread handle 140312637523712, query id 384917 localhost datalake wsrep: initiating replication for write set (-1)
DELETE FROM TB_DEF_JOB_SERVICE_NOW WHERE DATA_CENTER like ‘DS%’ and AMBIENTE=‘PRODUCAO’
mysql> show variables like ‘%version%’;±------------------------±------------------------------------------------------------------------------------------------+| Variable_name | Value |±------------------------±------------------------------------------------------------------------------------------------+| innodb_version | 5.7.29-32 || protocol_version | 10 || slave_type_conversions | || tls_version | TLSv1,TLSv1.1,TLSv1.2 || version | 5.7.29-32-57-log || version_comment | Percona XtraDB Cluster (GPL), Release rel32, Revision 1b99a32, WSREP version 31.43, wsrep_31.43 || version_compile_machine | x86_64 || version_compile_os | Linux || version_suffix | -57-log |±------------------------±------------------------------------------------------------------------------------------------+9 rows in set (0.00 sec)
@yrmenezes DDL statements take absolute highest priority in PXC and will abort any running transactions. You should try not to delete tables so frequently.
Also, large batch deletes are BAD for Galera performance. You should upgrade to PXC8 and take advantage of ‘streaming writesets’ or make your DELETE’s smaller.