Galera 10 클러스터 노드에서 쿼리 삭제

Galera 10 클러스터 노드에서 쿼리 삭제

5 노드 클러스터의 Node 1에서 오류가 발생했습니다.노드 1에 대한 쿼리는 클라이언트의 관점에서 성공한 것처럼 보이지만 삽입에 실패했습니다.autoinc는 업데이트 쿼리에 포함되지 않아야 하는데 autoinc 오류가 많이 발생하고 있습니다.또한 이로 인해 우선순위가 높은 트랜잭션이 발생하여 노드를 오프라인으로 전환하여 트랜잭션 재생을 수행할 때까지 성능 문제가 발생할 수 있습니다.의 엔트리의 일부를 다음에 나타냅니다.error.log디버깅을 켜고 셋업에 대해 설명하겠습니다.우리는 어떻게 더 문제를 해결해야 할지 막막하다.

트랜잭션을 계속하는 유일한 방법은 모든 클라이언트가 연결 풀을 폐기하고 재구축하는 것입니다.

설정에 대한 자세한 내용은 다음과 같습니다.

• 5노드는 모두 로컬 서버의 마스터로서 기능한다.
• 모두 WAN 경유로 접속
• 노드 1에는 웹 사이트 액세스를 위한 외부 SQL 연결도 있습니다.
• 각 노드가 물리적 시스템의 도커 내에서 실행되고 있습니다.

다음은 몇 가지 오류입니다.

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

델의 설정

[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

업데이트: 코멘트 요청당:

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    |
+--------------------------+-------+

언급URL : https://stackoverflow.com/questions/31225290/galera-10-cluster-node-dropping-queries