We have 3-nodes setup, huge tables, heavy load. Experienced frequent "no such a transition EXECUTING -> COMMITTED" shutdown. After some experiments We've found quite simple setup: 3 nodes, only first node is used for writing, two other nodes are just "hot-swap" to handle request upon first node failure. Error happened more and more frequently until cluster could not live for 5 minutes without destruction. Writing to "user_best_times" table causes master node (first node) to fail. Repeating the same write to the second node brings second node down as well. Third node then hangs in "Unknown comand" state (desync or whatever) or (sometimes) survives.
We intensively write to this table:
CREATE TABLE `user_best_times` (
`user` int(10) unsigned NOT NULL,
`week_day` tinyint(3) unsigned NOT NULL,
`hour` tinyint(3) unsigned NOT NULL,
`hits` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`user`,`week_day`,`hour`),
KEY `fk_user_best_times_1` (`user`),
CONSTRAINT `fk_user_best_times_1` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Writes are like this:
INSERT INTO user_best_times (hits, hour, week_day, user)
VALUES ('0.75','19','1','14240291')
ON DUPLICATE KEY UPDATE hits=hits+VALUES(hits);
Table is quite huge (87000000+ rows) and under heavy concurrency (4-50 requests) Percona goes down in a minute. Linked "users" table is updated not that frequently, it has 1868675 rows.
Finally it looks like I've found a workaround: I've removed FOREIGN KEY fk_user_best_times_1, doing it's job on the application side. I see no crushes since then.
my.cnf in the attachment.
I can send logs (wsrep_debug=1) for the crush time via e-mail (don't want to publish it for everyone).
We use Percona cluster 5.5.29, Galera 23.2.2rc2(r136):
mysql> \s
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.5.29, for Linux (x86_64) using EditLine wrapper
Connection id: 400
Current database: schema_lock
Current user: fbhub@192.168.100.62
SSL: Not in use
Current pager: less
Using outfile: ''
Using delimiter: ;
Server version: 5.5.29 Source distribution, wsrep_23.7.1.rXXXX
Protocol version: 10
Connection: dbp01 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 8661
Uptime: 40 min 22 sec
Threads: 415 Questions: 257794 Slow queries: 2 Opens: 991 Flush tables: 3 Open tables: 182 Queries per second avg: 106.438
--------------
mysql> mysql> show status like 'wsrep%';
+----------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+-------------------------------------------------------------+
| wsrep_local_state_uuid | f0d4d641-7475-11e2-0800-d4e89001eedb |
| wsrep_protocol_version | 4 |
| wsrep_last_committed | 1822295 |
| wsrep_replicated | 66873 |
| wsrep_replicated_bytes | 15768877 |
| wsrep_received | 7362 |
| wsrep_received_bytes | 1640124 |
| wsrep_local_commits | 66208 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.003955 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 642.792723 |
| wsrep_apply_oooe | 0.007636 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.007636 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 764 |
| wsrep_causal_reads | 0 |
| wsrep_incoming_addresses | 192.168.100.68:8661,192.168.100.70:8661,192.168.100.69:8661 |
| wsrep_cluster_conf_id | 19 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | f0d4d641-7475-11e2-0800-d4e89001eedb |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <email address hidden> |
| wsrep_provider_version | 23.2.2rc2(r136) |
| wsrep_ready | ON |
+----------------------------+-------------------------------------------------------------+
@Dmitry,
Can you provide your error log (containing the backtrace etc.) to
check if this is a known issue - lp:1110561