Thanks for the response!
This is from the node that accepts almost all of the inserts in the cluster.
[mysqld]
basedir=/usr/
binlog_format=ROW
datadir=/opt/data
default_storage_engine=innodb
explicit_defaults_for_timestamp=1
innodb_autoinc_lock_mode=2
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=8264M
innodb_data_file_path = ibdata1:100M:autoextend
innodb_doublewrite=1
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_flush_method = O_DIRECT
innodb_io_capacity=800
innodb_log_buffer_size=96M
innodb_log_files_in_group=2
innodb_log_file_size=1024M
innodb_print_all_deadlocks = 1
innodb_read_io_threads=8
innodb_stats_on_metadata=0
innodb_thread_concurrency=64
innodb_write_io_threads=8
join_buffer_size = 15M
key_buffer_size = 128M
log_error=/var/log/mysql.log
log-queries-not-using-indexes
log-slow-verbosity=query_plan,explain
log_warnings=1
long_query_time = 1
lower_case_table_names=0
max_allowed_packet = 512M
max_connections=275
max_heap_table_size = 64M
memlock=0
performance_schema=ON
pid_file=mysqld.pid
port=3306
query_cache_limit = 4M
query_cache_size = 256M
query_cache_type = 1
read_buffer_size = 5M
read_rnd_buffer_size = 256M
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
socket=/var/run/mysqld/mysqld.sock
sort_buffer_size = 5M
sysdate_is_now=1
table_open_cache=700
thread_cache_size=512
tmp_table_size = 64M
user=mysql
wsrep_auto_increment_control=1
wsrep_causal_reads=0
wsrep_certify_nonPK=1
wsrep_cluster_address=gcomm://10.0.2.142,10.0.2.141,10.0.2.161
wsrep_cluster_name=“my_wsrep_cluster”
wsrep_convert_LOCK_to_trx=0
wsrep_debug=0
wsrep_drupal_282555_workaround=0
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_node_address=10.0.2.161
wsrep_node_name=10.0.2.161
wsrep_provider_options=“gcache.size=98304M; gmcast.segment=0”
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_replicate_myisam=1
wsrep_retry_autocommit=1
wsrep_slave_threads=4
wsrep_sst_auth=root:obfuscatepassword
wsrep_sst_method=xtrabackup-v2
[mysql]
socket=/var/run/mysqld/mysqld.sock
[client]
socket=/var/run/mysqld/mysqld.sock
[mysqldump]
max_allowed_packet = 512M
socket=/var/run/mysqld/mysqld.sock
[mysqld_safe]
basedir=/usr/
datadir=/opt/data
log_error=/var/log/mysql.log
pid_file=mysqld.pid
Only one of the other two nodes receives inserts at all, and only to one table. It is not exhibiting the problem. The my.cnf there is the same, except for wsrep_node_name. All three nodes used the same template for the my.cnf to start, with only the minimal changes required made locally on each node. I’m not sure we’d be so lucky as to find a smoking gun by finding differences in config.
This database began life on RDS and got migrated to this cluster. I came into the project after that and am trying to make the database more supportable. There were a lot of things present that could certainly provoke bugs. Mixture of MyISAM and InnoDB. Mix of UTF8 and latin1, sometimes in the same table…
Running values from the three nodes match what is expected:
node1> show variables like ‘%auto_increment%’;
±-----------------------------±------+
| Variable_name | Value |
±-----------------------------±------+
| auto_increment_increment | 3 |
| auto_increment_offset | 2 |
| wsrep_auto_increment_control | ON |
±-----------------------------±------+
node2> show variables like ‘%auto_increment%’;
±-----------------------------±------+
| Variable_name | Value |
±-----------------------------±------+
| auto_increment_increment | 3 |
| auto_increment_offset | 3 |
| wsrep_auto_increment_control | ON |
±-----------------------------±------+
node3> show variables like ‘%auto_increment%’;
±-----------------------------±------+
| Variable_name | Value |
±-----------------------------±------+
| auto_increment_increment | 3 |
| auto_increment_offset | 1 |
| wsrep_auto_increment_control | ON |
±-----------------------------±------+