Posts

Showing posts from March, 2021

Table partitioning in MySQL NDB Cluster and what’s new (Part III)

Image
Whats new in NDB Cluster 7.5 version (Contd.) In cluster 7.5 the READ_BACKUP and FULLY_REPLICATED table features were added. These features are both designed to improve read performance and scalability, and can be set on a per-table basis. These features are fully implemented inside MySQL Cluster, and tables using these features support all of the normal MySQL Cluster features - secondary unique and ordered indexes, foreign keys, disk resident columns, replication etc. The read performance improvements do not require any special effort to take advantage of – MySQL Cluster automatically chooses the most efficient way to execute reads, whether issued over NdbApi, or via SQL, executed in MySQLD or pushed down for parallel execution in the data nodes. READ_BACKUP: Prior to 7.5, all Committed Read operations were routed to a primary replica of the table or index fragment to be read. The READ_BACKUP feature enables Committed Read operations to be routed to any replica (primary or backup role

Table Partitioning In MySQL NDB Cluster And What’s New (Part II)

Image
Whats new in NDB Cluster 7.5 version In this version, users have more flexible ways of table partitioning rather than the default way thru ldm. Now user can partition the table either by node or by ldm. There are 4 different ways of table partitioning supported, these are: FOR_RP_BY_NODE FOR_ RA_BY_NODE FOR_RP_BY_LDM ( Default) FOR_RA_BY_LDM FOR_RA_BY_LDM_X_2 FOR_RA_BY_LDM_X_3 FOR_RA_BY_LDM_X_4 From the above RA is for Read from any replica i.e either from Primary replica or backup replica and RP is for Read from Primary replica only. The above options user can give either thru create table or from alter table sql statement in the COMMENT section like below. mysql> create table t1(col1 int unsigned not null primary key AUTO_INCREMENT, col2 int unsigned not null, col3 char(255)) engine=ndbcluster COMMENT="NDB_TABLE=PARTITION_BALANCE= FOR_RP_BY_NODE "; OR mysql> alter table t1 COMMENT="NDB_TABLE=PARTITION_BALANCE= FOR_RA_BY_NODE"; The FOR_RA and FOR_RP exist be

Table Partitioning In MySQL NDB Cluster and What’s New (Part I)

Image
This blog is about table partitioning in NDB Cluster. We will see how this feature has been enhanced from version to version. Also we will see which partitioning user should use under which scenario. Here I will assume that the user has some knowledge on NDB cluster. Data distribution and table partitioning are usually coupled together. In NDB, when we talk about table partitioning, we mean ‘data distribution’ mainly as NDB doesn’t fully support RANGE, HASH or LIST partitioning. Most of the enhancements made to partitioning over the years are about ‘data distribution’ rather than supporting various partitioning schemes. The main goals of data distribution are: - Balance: Avoid premature bottlenecks of memory, storage, cpu or network - Scaling: Make use of all resources, add capacity with new resources - Efficiency: Locality of access and minimal unnecessary data transfer To demonstrate this feature, let’s create a NDB Cluster with below environment. NDB Cluster version ( e.g. 7.4.32,