I created a two node cluster. Haven’t added the 3rd node yet. But the cluster is not replicating to node 2. Made a big insert on node 1, assumed it would replicate to node 2 and it has not.
As @CTutte says, you are missing the most critical configuration parameter
wsrep_cluster_address=gcomm://
This is empty and tells each node to create its OWN 1-node cluster. You don’t have a 2-node cluster. You have 2 separate clusters of 1-node each. Change this parameter on BOTH nodes to something like this:
wsrep_cluster_address=gcomm://ip-node-1,ip-node-2
Then you need to boostrap node1 (to start a cluster), then start node2 normally so that it joins node1
systemctl start mysql@bootstrap (node1)
systemctl start mysql (node2)
@CTutte You don’t need this in PXC8, it’s an invalid parameter now. SST auth is handled automatically.
@danarashad
Sorry, I did not notice there was more to your pasted config file. Scroll bars were not visible so I didn’t see anything more.
If both nodes are connected, and you are in PRIMARY state (which I see is indeed the case above), then after you COMMIT a transaction on either node, it should immediately replicate to the other node. Have you tried some simple CREATE DATABASE, CREATE TABLE and verified those appear on both nodes?
How about something like this:
node1:
CREATE DATABASE foo;
CREATE TABLE foo.bar (name varchar(20));
INSERT INTO foo.bar VALUES ('Fred');
node1/2:
SELECT * FROM foo.bar; (run this on both nodes)
I created a small table and inserted some data on node 1 and replication was somewhat instant.
on node 2 when i did the select i received in the error log.
MDL conflict db=foo table=bar ticket=10 solved by abort
ran the select statement again and it worked. So there seems to be a delay. Is there something in my config that might slow down the replication process? Dropping the table took a few seconds but replication was instant.
These are quite insane values. You’re saying that a transaction can wait 41 days for another transaction to complete. You should lower all of these to more realistic values. wait_ and interactive_ should be around 5 to 10m while innodb_ should be 50s (default)
But those are not your issue. I do not see anything in your config, nor is there a parameter available, that will slow down replication. Galera replication is always immediate and no setting can change that.
That error message seems to indicate that node2 did wait for something before it was able to read the data. Can you recreate the table and add a INT-PK?
Yes I can modify those values, it was just copied over from the previous cluster which I did not setup. I can create another table I am in the middle of waiting for a giant replication to happen on node 2.
Looking at node 2 show full processlist doesn’t show node 2 doing anything. With the exception of the datastore line. Is that the replication process happening? That is the table that was altered (update/insert/delete) from my script.
| 1 | system user | | NULL | Sleep | 168403 | wsrep aborter idle | NULL | 168402923 | 0 | 0 |
| 2 | system user | | NULL | Query | 5922 | wsrep: committed TOI write set (296179) | NULL | 5922001 | 0 | 0 |
| 8 | event_scheduler | localhost | NULL | Daemon | 129388 | Waiting on empty queue | NULL | 129387980 | 0 | 0 |
| 10 | system user | | NULL | Sleep | 7514 | wsrep: committed TOI write set (296178) | NULL | 7514568 | 0 | 0 |
| 11 | system user | | NULL | Query | 49576 | wsrep: committed write set (296176) | NULL | 49061852 | 0 | 0 |
| 12 | system user | | datastore | Query | 2885 | wsrep: writing row for write-set (296183) | NULL | 1189 | 0 | 0 |
| 13 | system user | | NULL | Query | 5922 | wsrep: committed write set (296181) | NULL | 5912690 | 0 | 0 |
| 14 | system user | | NULL | Query | 5922 | wsrep: committed TOI write set (296180) | NULL | 5920212 | 0 | 0 |
| 16 | system user | | NULL | Sleep | 7549 | wsrep: committed TOI write set (296177) | NULL | 7549409 | 0 | 0 |
| 17 | system user | | NULL | Query | 49668 | wsrep: committed write set (296175) | NULL | 49096776 | 0 | 0 |
| 19 | root | localhost:59544 | cmr | Query | 0 | init | show full processlist | 0 | 0 | 0 |
While a table is being ALTERd, no other DML/DDL will be processed. If it takes 10m for your alter to complete, then replication will be blocked for 10m until the ALTER finishes. TOI = total order isolation, which means “everything else stops while this alter finishes” ALTER TABLE should be done very infrequently in a cluster environment.
CREATE DATABASE foo_rep;
CREATE TABLE foo_rep.bar (id int primary key,name varchar(20));
INSERT INTO foo_rep.bar VALUES (1,'Fred');
select * from foo_rep.bar;
ran the query and the response is the log is above.
select * from foo_rep.bar;
ERROR 1146 (42S02): Table 'foo_rep.bar' doesn't exist
select * from foo_rep.bar;
Empty set (1.01 sec)
select * from foo_rep.bar;
+----+------+
| id | name |
+----+------+
| 1 | Fred |
+----+------+
There seems to be a delay. With the large amount of data i am adding there seems to be a greater delay. I am guessing this is not normal. Is there some sort of log file I can look at to maybe figure out what is going on?
Can you stop your “large amount of data” for the purposes of these tests? I want to make sure that normally there isn’t an issue with our simple test case.
Please also make sure you’ve installed/configured Percona Monitoring and Management. I’m interested to see if you are indeed having flow control issues (visible when you have “large amounts of writes”)
I have stopped the large datasets. The simple test is giving a delay. The only transaction being run on the cluster is below.
CREATE DATABASE foo_rep;
CREATE TABLE foo_rep.bar (id int primary key,name varchar(20));
INSERT INTO foo_rep.bar VALUES (1,'Fred');
select * from foo_rep.bar;
Should the Percona Monitoring and Management tool get installed on a different server?
If that simple test is giving you lag then you have some severe fundamental problem with your setup. You should be able to do 1000/sec of those simple inserts without experiencing any lag.
You should install PMM onto another, dedicated server. 4CPU/16GB ram is plenty.
Think I got it figured out. The memory is maxed out. I am still in the process of setting the cluster up and Node 2 is a temp server (chip shortage) it only has 20GB of memory and btop is showing 99% of the memory used.