How COMMIT works in PXC-8.0.27

Hi there,
How COMMIT works in Percona XtraDB Cluster 8.0.27 .

Use case : PXC 8.0.27 - 5 node cluster

1.When DDL & DML operations are performed on NODE 1 ,would the NODE 1 waits to COMMIT at the rest of node 1 node 2,node3,node 4 & node 5.
2.If not , what would be the actual scenario for COMMIT in this case…?
3.If yes,will this impact the other transactions and delay in write process…?

Please elaborate the COMMIT scenario.

1 Like

In PXC, DML and DDL are treated differently. By default, DDLs always execute immediately and simultaneously on all nodes. For DML, when node1 does COMMIT, it waits for the transaction to be replicated to the other nodes. node1 does NOT wait for COMMIT on other nodes because COMMIT is deterministic in PXC, meaning if it will commit on node1, then it will also commit on the others.

1 Like

@AneeshBabu

DDL is handled in a different matter by PXC. Depending on wsrep_OSU_method :

  • TOI - Total Order Isolation - This means that the DDL will be propagated to all nodes and on all nodes the receiver queue (TRX waiting to be applied) has to be processed and empty, then the DDL will be applied at the same time on all nodes.
  • RSU - Rolling Schema Upgrade - In this mode it´s applied individually (by the user) on each node.

DML is different. It starts from the basis that all nodes are in sync. Then every time you commit something, it will first check if there is any potential conflict on your local node. Then it will create a signature of that trx and it will be replicated to all nodes. Each node will ensure that is doesn’t have any pending transaction on its receiver queue that can conflict (the signature is used for that) in case of no conflict them all will proceed with committing the trx (adding it to its queue and eventually processing/applying it).

So to answer you question, Node1 needs to replicate the trx to all nodes and wait for them to confirm there is no conflict, which does adds delay on each COMMIT.
That is why having small latency between each nodes is very important for write performance.

1 Like

@Marcelo_Altmann
as you stated,node 1 waits for all the nodes to confirmation.

1.Would there be any delay in write process,since each has to wait for confirm & commit… vice versa…?
2.Is Percona XtraDB Cluster WRITE scalable and PXC handle huge writes…?

1 Like

@AneeshBabu

as you stated,node 1 waits for all the nodes to COMMIT.

No, that is not what I meat. Let me try to rephrase it - Node1 does not wait for other nodes to COMMIT, it only waits for other nodes to certify.
Certification is a lightweight process that only checks the signature (you can think about this like a PK conflict) of the rows and ensure that the writeset coming in does not conflicts with anything on the applier queue (other writesets that have already been certified but not committed yet).

The COMMIT operation, meaning, persisting the entry to storage engine, binlog, … that is done separately and node-1 does not have to wait the COMMIT on all nodes.

1.Would there be any delay in write process,since each has to wait for COMIMIT … vice versa…?

Node1 needs to replicate the trx to all nodes and wait for them to confirm there is no conflict, which does adds delay on each write process.

2.Is Percona XtraDB Cluster WRITE scalable and PXC handle huge writes…?

No, PXC was not designed to scale writes, its the opposite, its designed to scale reads.
It is also not indicate to have huge transactions, although there were some improvements done on that area in 8.0 series - Streaming Replication

2 Likes