1

How manage partition rotating in real time on Galera Cluster?

I am catch from time to time metadata lock while dropping or adding partitions on a table. I have 3 nodes in a cluster and all application connections going to first node through load balance. If first node goes down connection switch to second node.

For partitioning I use the next SP:

CREATE DEFINER=`root`@`localhost` PROCEDURE `manage_partitions`()
BEGIN
  if @@GLOBAL.wsrep_on = 1 
    then SET wsrep_on=0; end if;
  SET sql_log_bin = 0;
  ----- > Manage partitions here
  SET sql_log_bin = 1;
  if @@GLOBAL.wsrep_on = 1 
    then SET wsrep_on=1; end if;
END

I execute this piece of code on each node on different time. And some time catch metadata lock and only kill process which create/drop partition can unfreeze server.

So, DBAs, How you manage partition rotations in MariaDB with Galera clusetr in real time? I use MariaDB version - 10.1.13.

Mike.

mgavrylov
  • 11
  • 3

1 Answers1

1

Does the problem happen if you only change sql_log_bin. That is you do not turn off all replication via wsrep_on. I feel that this should be the answer.

Meanwhile, please show us the partition management code; there could be things to do there.

Are you essentially doing an RSU? Would it work to do TOI?

What if we can speed up the management so that TOI is feasible instead of RSU?

If you are doing a "sliding time scale", it is quite possible to make the ALTER ... PARTITION commands virtually instantaneous.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • If I am not switch of wsrep_on the whole cluster can be locked. partition management code quiet simple: just read config table and create aleter table add or drop partition. Change OSU method doesn't help. Catch the same metadata lock. Alter table add partition work very fast when I run it manually. Percona support answer on my question that I should make small maintenance on a schema before I run partition management procedure on DB. – mgavrylov Dec 05 '16 at 09:13
  • 1
    If it is "fast", then remove all the flag settings and depend on TOI to 'do the right thing'. – Rick James Dec 05 '16 at 19:34