We are encountering an error on Node 1 of a 5-node cluster. Queries to Node 1 seem to succeed from a client perspective but are failing to insert. We are seeing a lot of autoinc errors even though autoinc shouldn't be involved in the update queries. Also, this seems to cause performance issues until a higher priority transaction occurs knocking the node offline to perform transaction replay. Below are some of the entries in error.log with debugging on and a walkthrough of setup. We are at a loss of how to troubleshoot further.
The only way to cause transactions to continue is for all the clients to drop and rebuild connection pool.
Some details of the setup:
- 5 Nodes all acting as a master to their local server
- All connected via WAN
- Node 1 also has outside SQL connections for website access
- Each node is running inside of docker on the physical machine
Here are some of the errors:
150703 5:56:27 [Note] WSREP: DUPKEY error for autoinc
THD 5041, value 133622, off 2 inc 5
150703 5:56:27 [Note] WSREP: retrying insert: INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
150703 5:56:27 [Note] WSREP: innobase_commit, abort INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
150703 5:56:27 [Note] WSREP: cleanup transaction for LOCAL_STATE: INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
150703 5:56:27 [Note] WSREP: wsrep retrying AC query: INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
150703 5:56:27 [Note] WSREP: DUPKEY error for autoinc
THD 5041, value 133627, off 2 inc 5
150703 5:56:27 [Note] WSREP: releasing retry_query: conf 0 sent 0 kill 0 errno 0 SQL INSERT INTO `server_live` (server_id, performance_30, performance_120, performance_300, performance_600, players_online, staff_online, staff_last_seen, uptime, worlds_loaded, chunks_loaded, entities_loaded, tileEntities_loaded) VALUES (79, 100, 100, 99, 99, 2, '{}', staff_last_seen, 15568, 13, 789, 384, 1101) ON DUPLICATE KEY UPDATE performance_30 = 100, performance_120 = 100, performance_300 = 99, performance_600 = 99, players_online = 2, staff_online = '{}', staff_last_seen = staff_last_seen, uptime = 15568, worlds_loaded = 13, chunks_loaded = 789, entities_loaded = 384, tileEntities_loaded = 1101
Our config
[MYSQLD]
datadir=/data
log-error=/data/error.log
query_cache_size=0
binlog_format=ROW
query_cache_type=0
bind-address=0.0.0.0
port=3304
innodb_buffer_pool_size=2048M
innodb_flush_log_at_trx_commit=0
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_io_capacity=200
innodb_doublewrite=1
innodb_log_file_size=512M
innodb_log_buffer_size=64M
innodb_buffer_pool_instances=4
innodb_log_files_in_group=2
innodb_thread_concurrency=64
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
innodb_stats_on_metadata=0
default_storage_engine=innodb
binlog_format=ROW
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
skip_name_resolve
memlock=0
sysdate_is_now=1
max_connections=512
thread_cache_size=512
query_cache_type = 0
query_cache_size = 0
table_open_cache=1024
lower_case_table_names=0
wait_timeout = 28800
explicit_defaults_for_timestamp=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=2048M; evs.keepalive_period=PT3S; evs.inactive_check_period=PT10S; evs.suspect_timeout=PT30S; evs.inactive_timeout=PT1M; evs.install_timeout=PT1M; evs.send_window=1024; evs.user_send_window=512;"
wsrep_cluster_name="<removed>"
wsrep_cluster_address="<removed>"
wsrep_slave_threads=4
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=1
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=10
wsrep_auto_increment_control=1
wsrep_replicate_myisam=1
wsrep_drupal_282555_workaround=1
wsrep_causal_reads=0
wsrep_sst_method=rsync
wsrep_log_conflicts=1
UPDATE: Per request for comments:
mysql> SHOW CREATE TABLE server_live;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| server_live | CREATE TABLE `server_live` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`server_id` int(11) NOT NULL,
`performance_30` int(11) NOT NULL,
`performance_120` int(11) NOT NULL,
`performance_300` int(11) NOT NULL,
`performance_600` int(11) NOT NULL,
`players_online` int(11) NOT NULL,
`staff_online` varchar(255) NOT NULL DEFAULT '{}',
`staff_last_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`uptime` int(11) NOT NULL,
`worlds_loaded` int(11) NOT NULL,
`chunks_loaded` int(11) NOT NULL,
`entities_loaded` int(11) NOT NULL,
`tileEntities_loaded` int(11) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `server_id_2` (`server_id`),
CONSTRAINT `server_live_ibfk_1` FOREIGN KEY (`server_id`) REFERENCES `server` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=720312 DEFAULT CHARSET=utf8 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> SHOW VARIABLES LIKE 'auto%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 3 |
| autocommit | ON |
| automatic_sp_privileges | ON |
+--------------------------+-------+
