I'm planning to use Spider to shard my huge table across multiple backend nodes. What I'd like to do, is inserting on the Spider node(s) on the virtual big table, and Spider have it distribute it for me to the right backend node (shard), while maintaining the isolation level of a simple commit by an application. In a picture from the Spider documentation:
While reading these docs, especially the picture shown above, I assumed that Spider would split up the single commit for me into a two-phase group commit (XA 2PC), sending it to the backend nodes but only fully committing if all shards agree to commit their part.
This assumption is also supported by various other statements elsewhere like in MDEV-14090.
Internally, Spider implements COMMIT by executing XA PREPARE on each shard, and then executing XA COMMIT on each shard.
I've tried to test this "simple-COMMIT-to-XA2PC-transaction" assumption using the following setup, and guess what I saw...
- mariadb-server version 10.4.5+maria~bionic
- mariadb-plugin-spider version 1:10.4.5+maria~bionic
CREATE OR REPLACE SERVER srvA FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', PORT 3306, HOST '192.168.199.10', DATABASE 'testdb'); CREATE OR REPLACE SERVER srvB FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', PORT 3306, HOST '192.168.199.11', DATABASE 'testdb'); create table mytable ( id INT unsigned NOT NULL, some_field TEXT(100) DEFAULT NULL, PRIMARY KEY (id), ) ENGINE=spider partition by list (mod(id, 2)) ( partition p00 values in(0) comment = 'server "srvA", table "mytable_shard0"', partition p01 values in(1) comment = 'server "srvB", table "mytable_shard1"' );
Everything else on the Spider is left at the default settings.
Looking at Wireshark PCAPs, I see this in chronological packet-id order on the single bridge interface all my test nodes are connected on:
client connects to Spider node, setting connection parameters, disable autocommit, set transaction level SERIALIZABLE.
client doing insert on Spider node:
sending START TRANSACTION.
large 1000-row single-statement insert spreading shards (unknowingly about shards). awaiting response...
Spider communicates with srv1:
Spider connect to srv1, setting connection parameters, set transaction level SERIALIZABLE, etc.
Spider sends the insert statement of rows for srv1 to srv1.
Spider receiving OK from srv1, but in-transaction.
Spider communicates with srv2:
[omitted] same as with srv1.
client receiving OK, but in-transaction.
client sending COMMIT.
Spider sending COMMIT to srv1, srv1 acks commit
Spider sending COMMIT to srv2, srv2 acks commit
client receiving OK from Spider, not in-transaction.
My observations derived from the above events:
- I don't see any XA commit statements happening at all from Spider to backend servers. All seem serial regular commits. Huh!?
- All seems serially committed in one-phase shard after shard.
- Spider seems to communicate to backend servers one-after-another (3 and 4 in the observation list do not cross in time), not in parallel. Why? It seems slower and unnecessary.
(I've double-checked with Wireshark filters
mysql.query contains "prepare",
mysql.query contains "xa" that no single MySQL query packet contains this.)
How can I obtain XA-2PC as depicted in the image from the documentation shown above? I need to have strict guarantees that ALL shards have seen and committed their part of the original client-side commit, OR rollback as a group.
Also, I'd like to speed up the inserts by having Spider sending the shards their part of the inserts in parallel. Is that possible?