Some essays on "20180408" MySQL cluster pxc

PXC verification does not pass only two cases

1. Snapshot too long
   • SQL statement execution time is too long, or the undo table space is too small, or the transaction volume is too large, or too frequent commits, resulting in the execution of the SQL procedure for consistent read, SQL execution after the modification of the pre-image (that is, undo data) in the Undo table space has been overwritten, can not construct a consistent read block (CR Blocks). This is the most common situation.
   • During the execution of the SQL statement, the block that is accessed is not able to determine the order of the transaction commit time of the block and the start time of the SQL execution when the delay block clears. This is rarely the case.
2. Modify the same row of data

PXC other node nodes apply application failure is generally a hardware level problem

1. General ist incremental completion after a failure
2. If ist fails, it will be clustered by T.

PXC No lock table

• The ALTER TABLE DDL operation is escalated to a global lock and the entire instance is locked. Therefore, it is best to suggest that DDL operation is not possible with the online DDL, preferably using Pt-osc or gh-ost, etc.

PXC's IST

1. When a node joins, its current UUID is the same as the existing cluster, and the missing data can be found in donor's writeset cache, the ist can be done, otherwise only all the data will be initialized, and SST
   • Gcache.size default is 128M, can be set according to the Binlog size generated in the peak period Binlog one hours
2. It is important to note that the cache required by IST is temporary, so a cluster shuts down completely, but when each node shuts down seqno inconsistent, even if the node of the newest sequo is first started, other backward nodes can not do ist, only SST. Therefore, if you need to completely shut down the entire cluster, you need to break the external link and then shut down when the cluster is relatively stationary to avoid SST.
3. There are three main ways to transfer ist and SST data between PXC:
   • Mysqldump a logical backup that locks the table.
   • There is a file lock on rsync
   • Xtrabackup is generally recommended to use this

Brain fissure

1. If two nodes have a brain fissure, then they cannot operate on the entire cluster, and any command will show "Unkown command"
   • pc.ignore_db = yes ignores brain fissure and continues operation

Limitations of PXC

1. Only works on the InnoDB engine table, so modifications to the system tables below the MySQL library cannot be replicated, but DDL operations can be replicated, so the operating system tables can be used such as create user,grant.
2. Delete operations on tables that do not have a primary key are not supported, and Select...limit also returns different values on different nodes. (Does limit return different result sets simply without a primary key?) ）
3. Unsupported actions: Lock/unlock tables,lock functions (Get_locks (), Release_lock () ...)
4. The query log log cannot be stored in the table and must be stored in a file.
5. The maximum transaction size is defined by wsrep_max_ws_rows,wrsep_max_ws_size, and LOAD DATA INFILE is committed every 10K row, and the transaction is split into several small transactions. (XtraDB Cluster Auto-split, or manual split during operation?) ）
   • Wsrep_max_ws_rows,wrsep_max_ws_size who triggers first, who splits first
6. Because the cluster is based on optimistic concurrency control (optimistic consurrency control), the case of a transaction conflict can occur when multiple nodes modify the same row of data, only one node succeeds, the failed node terminates, and return the deadlock error code error:1213 sqlstate:4001 (Er_lock_deadlock). (Is this too unstable?) What happens when a node ends up being scraped off? And how does this work? ）
7. XA transactions are not supported because XA transactions are likely to have exception rollback at commit time. (Refer to Http://www.infoq.com/cn/articles/xa-transactions-handle)
8. The throughput/performance of the entire cluster depends on the slowest node, because it needs to be certification on all nodes, and also depends on the network performance between nodes, so all nodes need the same hardware configuration, and the network, disk and other performance should be as high as possible, such as the use of SSDs.

Flow control

1. What is flow control?
   • Flow control, in short, is the flow, in the PXC although it is synchronous replication, but it is only in logic or virtual synchronous replication, in the application and commit is not synchronous but asynchronous, there are some reasons will lead to apply and commit will lag behind the other nodes in the cluster, Once the backward transaction is too much, this time will start the flow control, the whole process of flow control, the cluster is not to provide external write function, but does not affect the read.
2. Fc.limit=1
   • Control the threshold of the flow control, once node falls behind this value will initiate the flow control, this value is not fixed. Is dynamically adjusted based on the number of nodes in the cluster.
3. Fc_master_slave=no
   • Whether to turn on the threshold for dynamic adjustment of the flow control. The general default is no
4. Fc_factor = 0.0~1.0
   • Control flow when the return to normal, the general default is 0.5 is the value of Fc.limit 50% will return to normal, flow control end.

Some essays on "20180408" MySQL cluster pxc