In this blog, we will answer questions from our webinar on Percona XtraDB Cluster 101.
Recently (7 Dec 2017) I presented a webinar about Percona XtraDB Cluster 101. Firstly, thanks to all the attendees: we had a great webinar with quite some interesting questions and feedback.
Through this blog, I’ll answer most of the questions that were raised during the webinar.
Q. How does the need for the acknowledgment from other nodes affect the speed of writes?
A. There are two parts to replication: delivering a transaction (including acknowledgment) and applying the transaction. Generally, the first part is pretty quick and dictated by the network latency. The second part is time-consuming, but happens asynchronously. So acknowledging a transaction from other nodes is not that time-consuming.
Q. How can geo-distributed nodes affect the speed of writes?
A. The longest node dictates cluster performance (in terms of latency). You can’t write faster than the time it takes for a packet to reach the longest node (round-trip-latency). So geo-distribution does affect write performance.
Q. Would you consider Master -> Slave replication in RDS a traditional replication of MySQL? And how easy is it replicating from PXC to RDS if its possible
A. If an application doesn’t need high availability, then a user can explore the MASTER-SLAVE replication. But I would argue that if I am going to spend time booting two servers (MASTER and SLAVE), then why not boot both as MASTER (through Percona XtraDB Cluster). This ensures HA and write-scalability. Percona XtraDB Cluster is flexible for all topologies, and can act as async-master or async-slave too.
Q. Moving forward, is there a plan to deal with version control tools like Flyway that still uses Get locks?
A. Statements like GET_LOCK that establish local locks at the said node are not cluster-safe, so they are blocked with pxc_strict_mode=ENFORCING and not recommended for use. With that said, if the application/user tries to use these statements in a non-conflicting way (with the load directed to single master) it could still work.
Q. With an ASYNC slave, can you use GTID? I know there is a bug(s) that prevent this currently from working 100% in MariaDB (though the bug is close to being fixed – MDEV-10715)?
A. Yes, you can use GTID with async-slave. MariaDB has different implementation of GTID so I am not in a position to comment on the latter part.
Q. Do tables need to have a primary key for the cluster to work?
A. Yes, all tables that you plan to use in a cluster should have a primary key ( pxc_strict_mode=ENFORCING enforces this criterion). This is mainly needed for conflict resolution, when the same conflicting workload is executed on multiple-nodes.
Q. What is the best wsrep_sst_method? (for huge database)
A. Percona XtraDB Cluster recommends using XtraBackup. It doesn’t lock the tables for the complete SST life-time, so you can continue to use the node while it is acting as DONOR.
Q. Is the “show processlist” node-specific? Is there an equivalent command to show the whole cluster process list?
A. Yes, show processlist is node specific. There is currently no way to cluster-wide-processlist.
Q. does PXC support partitioned tables?
A. Yes, using InnoDB native partitioning.
Q. These nodes (PXC-nodes) are api nodes or data nodes ?
A. Data nodes.
Q. If cluster went down then everytime it follow SST/IST?
A. It depends. If there is DONOR that has a missing write-set, then the node can rejoin through IST else SST.
Q. Around how much time it will take to join the cluster?
A. The time a node takes to join back depends on the size of the data. Generally, the time for SST is longer than IST. The good part is with 5.7.17+ we have considerably reduced the time for IST, so that a node can join faster than before.
Q. How does IST (incremental state transfer) process affect cluster performance?
A. IST is asynchronous and doesn’t emit FLOW_CONTROL, so cluster can continue to perform as normal. A small slice of DONOR bandwidth is used to send data to the JOINER, but it is not that significant to affect the overall cluster performance.
Q. How do you handle a situation when three simultaneous transactions try to insert auto_increment value?
A. Percona XtraDB Cluster has a concept of wsrep_auto_increment_control that adjust the increment size on each node based on a number of nodes in the cluster. Please check this link for more details.
Q. Imagine that a table A has a trigger on insert that inserts data into another table B. And there are two concurrent transactions: TA inserts into table A (and the trigger makes an insert into B) and TB that inserts the same data directly into B. Will such a conflict – insert from TB and from trigger – be detected?
A. Yes. A transaction can touch multiple data-objects and when the conflict resolution is done, it will check all the objects that transaction is planning to modify before certifying a transaction is safe to apply.
Q. How PXC will make sure of data integrity with parallel processing?
A. Percona XtraDB Cluster has conflict resolution protocol. This protocol is based on FIRST COMMITTER WIN principle that ensures only the first transaction (from a group of a conflicting transaction) commits to cluster.
Q. I’ve created a three node cluster and replication is working. I’d like to copy our production data to the cluster since exporting and importing from MySQL takes a long time. Should I have waited to bootstrap the cluster until the data directory is transferred?
A. If you already have a cluster in place then you are simply adding new tables to the cluster. You can start adding (LOADING) the tables and these tables are immediately replicated to the other nodes of the cluster. An alternative would be to start the first node of the cluster with the pre-loaded data that then becomes cluster state. Other joining nodes copy it over through SST.
Q. Do we have an option to autospinup the compute nodes in a cloud? If PXC will have that option or do we manually need to spinup the Instance and setup the replication?
A. You will have to manually configure it.
Q. Why does XtraBackup not work due bootstrapping but works perfectly after bootstrapping? rsync is working in both cases.
A. Not sure I get the question completely, but XtraBackup works in all scenarios. If you are facing any issue, please log it on launchpad.
Q. As per flow control, one node waits for the other node to be in sync. Won’t there be latency in writing the data?
A. The transaction originated from one node needs to get replicated on other nodes of the cluster. This is what we can call latency and is dictated by network latency. Flow-control is mainly to regulate a scenario wherein one node of the cluster falls way behind other nodes of the cluster.
Q. Can we set up PXC using AWS EC2?
A. Yes.
Once again, thanks for taking time to attend the webinar. If you have more questions, then please post them to the Percona XtraDB Cluster forum here. Also, we have a lot of blogs about Percona XtraDB Cluster. Make sure you check them out here.
I’m curious how Persona XtraDB Cluster performs in comparison to AWS Aurora.
One of the main challenge in calling reads with Mysql is replication lag, which Aurora circumvent by using redo log and sharing a disk between the master (called writer) and its slaves (called replica or readers).
XTraDB cluster could be used in similar way: Write to a single node and have reads on the others.
Could xtradb cluster handles, for example, 400 DML/sec on a node, while serving 12,000 Select /sec on the other 2 nodes?
(Using a R4.4x large).
We don’t have recent study but there is one that is bit old.
Galera Cluster beats Amazon Aurora in performance and MySQL high availability
http://galeracluster.com/2015/11/galera-cluster-beats-amazon-aurora-in-performance-and-mysql-high-availability/
Also, please check PXC performance here.
https://www.percona.com/blog/2017/04/19/performance-improvements-percona-xtradb-cluster-5-7-17/
PXC is easily able.