Heavy write to Galera cluster - table locked, cluster practically unusable
- by Joe
I set up Galera Cluster on 3 nodes. It works perfectly for reading data.
I have done simple application to make some test on the cluster. Unfortunately I have to say that the Cluster fails totally when I try to do some writing. Maybe it can be configured differently or I do sth wrong?
I have a simple stored procedure:
CREATE PROCEDURE testproc(IN p_idWorker INTEGER)
BEGIN
DECLARE t_id INT DEFAULT -1;
DECLARE t_counter INT ;
UPDATE test SET idWorker = p_idWorker WHERE counter = 0 AND idWorker IS NULL limit 1;
SELECT id FROM test WHERE idWorker = p_idWorker LIMIT 1 INTO t_id;
SELECT ABS(MAX(counter)/MIN(counter)) FROM TEST INTO t_counter;
SELECT COUNT(*) FROM test WHERE counter = 0 INTO t_counter;
IF t_id >= 0 THEN
UPDATE test SET counter = counter + 1 WHERE id = t_id;
UPDATE test SET idWorker = NULL WHERE id = t_id;
SELECT t_counter AS res;
ELSE
SELECT 'end' AS res;
END IF;
END $$
Now my simple C# application creates for example 3 MySQL clients in separate threads and each one executes the procedure every 100ms until there is no record where column 'counter' = 0.
Unfortunately - after about 10 seconds sth is going bad. On servers there is process 'query_end' that never ends. After that - you cannot make update on the test table, MySQL returns:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
. You cant even restart mysql. What you can do is to restart server, sometimes whole cluster. Is Galera Cluster so unreliable when you do massive concucurrent writing/updates? Hard to believe.
