Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-1156

LP #928919: TOI ALTER TABLE blocks all DML's for duration of ALTER TABLE in separate databases

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: Low
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      **Reported in Launchpad by Patrick Zoblisein last update 16-11-2015 10:46:58

      Hi,

      Using Percona XtraDB Cluster with Galera - 5.5.17-22.1.log and am seeing long-running ALTER TABLE statements in Node1-Database A block all writes on Node2-Database B using wsrep_OSU_method='TOI'.

      --Node 1 - Database "n126db1" - table "t1"

      CREATE TABLE `t1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `hostname` varchar(64) NOT NULL,
      `port` int(11) NOT NULL,
      `instime` datetime NOT NULL,
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=18042764 DEFAULT CHARSET=latin1;

      --Node 2 - Database "n127db2" - table "t2"

      CREATE TABLE `t2` (
      `c1` int(11) NOT NULL,
      `c2` int(11) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      Node 1 (n126db1) - ALTER TABLE t1 add column c5 int;
      Node 2 (n127db2) - insert into t2 values (1,2);

      mysql> show processlist;
      --------------------------------------------------------------------------------------------------------------------------------------

      Id User Host db Command Time State Info Rows_sent Rows_examined Rows_read

      --------------------------------------------------------------------------------------------------------------------------------------

      1 event_scheduler localhost NULL Daemon 64607 Waiting on empty queue NULL 0 0 1
      2 system user   NULL Sleep 64606 wsrep aborter idle NULL 0 0 1
      3 system user   n126db1 Sleep 44 copy to tmp table alter table t1 add column c5 int 0 0 4066970
      4 system user   NULL Sleep 566 committed 3569220 NULL 0 0 1
      5 system user   NULL Sleep 566 committed 3569199 NULL 0 0 1
      6 system user   NULL Sleep 566 committed 3569222 NULL 0 0 1
      7 system user   NULL Sleep 566 committed 3569215 NULL 0 0 1
      8 system user   NULL Sleep 566 committed 3569219 NULL 0 0 1
      9 system user   NULL Sleep 566 committed 3569211 NULL 0 0 1
      10 system user   NULL Sleep 566 committed 3569223 NULL 0 0 1
      4329 root localhost n127db2 Query 42 query end insert into t2 values (1,2) 0 0 2
      4339 root localhost NULL Query 0 sleeping show processlist 0 0 1

      --------------------------------------------------------------------------------------------------------------------------------------
      12 rows in set (0.00 sec)

      The documentation at http://www.codership.com/wiki/doku.php?id=rolling_schema_upgrade states that part of the database in the whole cluster is locked - but the above test seems to show that ALL databases on the node are locked.

      Contrast this behavior when the test is run on a single-RSU node. The ALTER TABLE takes just as long - but inserts into the other database are not blocked.

      Thanks
      Patrick

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                krunal.bauskar Krunal Bauskar
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: