Featured Posts

<< >>

MySQL Replication: What’s New in MySQL 5.7 and Beyond – webinar replay

Luís Soares and I recently hosted a webinar which explained the latest developments in MySQL Replication. The webinar replay is now available to download from here. Details: Continuing in the footsteps of its predecessor, MySQL 5.7 is set to be a groundbreaking release. In this webinar, the engineers behind the product provide insights into what’s

Upcoming Webinar – MySQL Replication: What’s New in MySQL 5.7 and Beyond

On Tuesday 25th November, Luís Soares and I will be hosting a webinar which explains the latest developments in MySQL Replication. As always the webinar is free but please register here. Details: Continuing in the footsteps of its predecessor, MySQL 5.7 is set to be a groundbreaking release. In this webinar, the engineers behind the

Active-Active Replication, Performance Improvements & Operational Enhancements – some of what’s available in the new MySQL Cluster 7.4.2 DMR

Oracle have just made availble the new MySQL Cluster 7.4.2 Development Milestone Release – it can be downloaded from the development release tab here. Note that this is not a GA release and so we wouldn’t recommend using it in production. This is the second DMR for MySQL 7.4; the delta between this DMR and

SQL/NoSQL and MySQL Cluster 7.4 Presentations now available

My 2 sessions from 2014’s MySQL Central at Oracle OpenWorld are now available: NoSQL and SQL: The Best of Both Worlds [CON2853] There’s a lot of excitement about NoSQL data stores, with the promise of simple access patterns, flexible schemas, scalability, and high availability. The downside comes in the form of losing ACID transactions, consistency,

My NoSQL/SQL and MySQL Cluster sessions at Oracle OpenWorld

I have 2 sessions at Oracle OpenWorld in San Francisco this Thursday (2nd October 2014); please come along if your in town and feel free to grab me after the sessions for any extra questions: NoSQL and SQL: The Best of Both Worlds [CON2853] Thursday, Oct 2, 9:30 AM – 10:15 AM – Moscone South

MySQL Replication: What’s New in MySQL 5.7 and Beyond – webinar replay

MySQL Replication LogoLuís Soares and I recently hosted a webinar which explained the latest developments in MySQL Replication.

The webinar replay is now available to download from here.

Details:

Continuing in the footsteps of its predecessor, MySQL 5.7 is set to be a groundbreaking release. In this webinar, the engineers behind the product provide insights into what’s new for MySQL replication in the latest 5.7 Development Milestone Release and review the early access features available via labs.mysql.com. The next generation of replication features cover several technical areas such as better semi-synchronous replication, an enhanced multithreaded slave (per-transaction parallelism), improved monitoring with performance schema tables, online configuration changes, options for fine-tuning replication performance, support for more-advanced topologies with multisource replication, and much more. This is also a great chance to learn about MySQL Group Replication – the next generation of active-active, update-anywhere replication for MySQL.

Seize the opportunity to learn how you will be able to leverage MySQL 5.7 replication to grow your business.





Upcoming Webinar – MySQL Replication: What’s New in MySQL 5.7 and Beyond

MySQL Replication LogoOn Tuesday 25th November, Luís Soares and I will be hosting a webinar which explains the latest developments in MySQL Replication. As always the webinar is free but please register here.

Details:

Continuing in the footsteps of its predecessor, MySQL 5.7 is set to be a groundbreaking release. In this webinar, the engineers behind the product provide insights into what’s new for MySQL replication in the latest 5.7 Development Milestone Release and review the early access features available via labs.mysql.com. The next generation of replication features cover several technical areas such as better semi-synchronous replication, an enhanced multithreaded slave (per-transaction parallelism), improved monitoring with performance schema tables, online configuration changes, options for fine-tuning replication performance, support for more-advanced topologies with multisource replication, and much more. This is also a great chance to learn about MySQL Group Replication – the next generation of active-active, update-anywhere replication for MySQL.

Seize the opportunity to learn how you will be able to leverage MySQL 5.7 replication to grow your business.

When:

  • Tue, Nov 25: 09:00 Pacific time (America)
  • Tue, Nov 25: 10:00 Mountain time (America)
  • Tue, Nov 25: 11:00 Central time (America)
  • Tue, Nov 25: 12:00 Eastern time (America)
  • Tue, Nov 25: 15:00 São Paulo time
  • Tue, Nov 25: 17:00 UTC
  • Tue, Nov 25: 17:00 Western European time
  • Tue, Nov 25: 18:00 Central European time
  • Tue, Nov 25: 19:00 Eastern European time
  • Tue, Nov 25: 22:30 India, Sri Lanka
  • Wed, Nov 26: 01:00 Singapore/Malaysia/Philippines time
  • Wed, Nov 26: 01:00 China time
  • Wed, Nov 26: 02:00 日本
  • Wed, Nov 26: 04:00 NSW, ACT, Victoria, Tasmania (Australia)

Even if you can’t join the live webinar, it’s worth registering as you’ll be emailed a link to the replay as soon as it’s available.





Active-Active Replication, Performance Improvements & Operational Enhancements – some of what’s available in the new MySQL Cluster 7.4.2 DMR

MySQL Cluster Logo

Oracle have just made availble the new MySQL Cluster 7.4.2 Development Milestone Release – it can be downloaded from the development release tab here. Note that this is not a GA release and so we wouldn’t recommend using it in production.

This is the second DMR for MySQL 7.4; the delta between this DMR and 7.4.1 can be viewed in the MySQL Cluster 7.4.2 Release Notes

There are three main focus areas for this DMR and the purpose of this post is to briefly introduce them:

  • Active-Active (Multi-Master) Replication
  • Performance
  • Operational improvements (speeding up of restarts; enhanced memory reporting)

Active-Active (Multi-Master) Replication

MySQL Cluster allows bi-directional replication between two (or more) clusters. Replication within each cluster is synchronous but between clusters it is asynchronous which means the following scenario is possible:

Conflict with asynchronous replication
Site A Replication Site B
x == 10 x == 10
x = 11 x = 20
– x=11 –> x == 11
x==20 <– x=20 –

 

In this example a value (column for a row in a table) is set to 11 on site A and the change is queued for replication to site B. In the mean time, an application sets the value to 20 on site B and that change is queued for replication to site A. Once both sites have received and applied the replicated change from the other cluster site A contains the value 20 while site B contains 11 – in other words the databases are now inconsistent.

How MySQL Cluster implements eventual consistency

There are two phases to establishing consistency between both clusters after an inconsistency has been introduced:

  1. Detect that a conflict has happened
  2. Resolve the inconsistency

The following animation illustrates how MySQL Cluster 7.2 detects that an inconsistency has been introduced by the asynchronous, active-active replication:

Detecting conflicts

While we typically consider the 2 clusters in an active-active replication configuration to be peers, in this case we designate one to be the primary and the other the secondary. Reads and writes can still be sent to either cluster but it is the responsibility of the primary to identify that a conflict has arisen and then remove the inconsistency.

A logical clock is used to identify (in relative terms) when a change is made on the primary – for those who know something of the MySQL Cluster internals, we use the index of the Global Checkpoint that the update is contained in. For all tables that have this feature turned on, an extra, hidden column is automatically added on the primary – this represents the value of the logical clock when the change was made.

Once the change has been applied on the primary, there is a “window of conflict” for the effected row(s) during which if a different change is made to the same row(s) on the secondary then there will be an inconsistency. Once the slave on the secondary has applied the change from the primary, it will send a replication event back to the slave on the primary, containing the primary’s clock value associated with the changes that have just been applied on the secondary. (Remember that the clock is actually the Global Checkpoint Index and so this feature is sometimes referred to as Reflected GCI). Once the slave on the primary has received this event, it knows that all changes tagged with a clock value no later than the reflected GCI are now safe – the window of conflict has closed.

If an application modifies this same row on the secondary before the replication event from the primary was applied then it will send an associated replication event to the slave on the primary before it reflects the new GCI. The slave on the primary will process this replication event and compare the clock value recorded with the effected rows with the latest reflected GCI; as the clock value for the conflicting row is higher the primary recognises that a conflict has occured and will launch the algorithm to resolve the inconsistency.

Options for MySQL Cluster replication conflict detection/resolution

After a conflict has been detected, you have the option of having the database simply report the conflict to the application or have it roll back just the conflicting row or the entire transaction and all subsequent transactions that were dependent on it.

So – what’s new in 7.4.1?

  • Detects conflicts between inserts and updates
  • Option to roll back entire transaction (and dependent transactions) rather than just the conflicting row
  • All conflicts are handled before switching primary – avoiding potential race conditions

As mentioned at the start of this post, this is pre-GA and there are some extra enhancements we plan on including in the final version:

  • Handle deletes which conflict with other operations
  • Roll back transactions that have read a row that had been rolled back due to a conflict

Performance

MySQL CLuster 7.4.1 Read-Write Performance
Being a scaled-out, in-memory, real-time database, MySQL Cluster performance has always been great but we continue to work on making it faster each release. In particular, we want to keep pace with the trend of having more and more cores rather than faster ones. 7.4 continues along the path of better exploiting multiple cores – as can be seen from these benchmark results.
MySQL CLuster 7.4.1 Read Performance
Just make sure that you’re using the multi-threaded data node (ndbmtd rather than ndbd) and have configured how many threads it should use.

Faster Restarts

You can restart MySQL Cluster processes (nodes) without losing database service (for example if adding extra memory to a server) and so on the face of it, the speed of the restarts isn’t that important. Having said that, while the node is restarting you’ve lost some of your high-availability which for super-critical applications can make you nervous. Additionally, faster restarts mean that you can complete maintenance activities faster – for example, a software upgrade requires a rolling restart of all of the nodes – if you have 48 data nodes then you want each of the data nodes to restart as quickly as possible.

MySQL 7.4.1 includes a number of optimisations to the restart code and so if you’re already using MySQL Cluster, it might be interesting to see how much faster it gets for your application. We also have some extra optimisations in the works that you can expect to see in later 7.4 versions.

Extra Memory Reporting

MySQL Cluster presents a lot of monitoring information through the ndbinfo database and in 7.4 we’ve added some extra information on how memory is used for individual tables.

For example; to see how much memory is being used by each data node for a particular table…

mysql> CREATE DATABASE clusterdb;USE clusterdb;
mysql> CREATE TABLE simples (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=NDB;
mysql> SELECT node_id AS node, fragment_num AS frag, \
        fixed_elem_alloc_bytes alloc_bytes, \
        fixed_elem_free_bytes AS free_bytes, \
        fixed_elem_free_rows AS spare_rows \
        FROM ndbinfo.memory_per_fragment \
        WHERE fq_name LIKE '%simples%';
+------+------+-------------+------------+------------+
| node | frag | alloc_bytes | free_bytes | spare_rows |
+------+------+-------------+------------+------------+
|    1 |    0 |      131072 |       5504 |        172 |
|    1 |    2 |      131072 |       1280 |         40 |
|    2 |    0 |      131072 |       5504 |        172 |
|    2 |    2 |      131072 |       1280 |         40 |
|    3 |    1 |      131072 |       3104 |         97 |
|    3 |    3 |      131072 |       4256 |        133 |
|    4 |    1 |      131072 |       3104 |         97 |
|    4 |    3 |      131072 |       4256 |        133 |
+------+------+-------------+------------+------------+

When you delete rows from a MySQL Cluster table, the memory is not actually freed up and so if you check the existing memoryusage table you won’t see a change. This memory will be reused when you add new rows to that same table. In MySQL Cluster 7.4, it’s possible to see how much memory is in that state for a table…

mysql> SELECT node_id AS node, fragment_num AS frag, \
        fixed_elem_alloc_bytes alloc_bytes, \
        fixed_elem_free_bytes AS free_bytes, \
        fixed_elem_free_rows AS spare_rows \
        FROM ndbinfo.memory_per_fragment \
        WHERE fq_name LIKE '%simples%';
+------+------+-------------+------------+------------+
| node | frag | alloc_bytes | free_bytes | spare_rows |
+------+------+-------------+------------+------------+
|    1 |    0 |      131072 |       5504 |        172 |
|    1 |    2 |      131072 |       1280 |         40 |
|    2 |    0 |      131072 |       5504 |        172 |
|    2 |    2 |      131072 |       1280 |         40 |
|    3 |    1 |      131072 |       3104 |         97 |
|    3 |    3 |      131072 |       4256 |        133 |
|    4 |    1 |      131072 |       3104 |         97 |
|    4 |    3 |      131072 |       4256 |        133 |
+------+------+-------------+------------+------------+
mysql> DELETE FROM clusterdb.simples LIMIT 1;
mysql> SELECT node_id AS node, fragment_num AS frag, \
        fixed_elem_alloc_bytes alloc_bytes, \
        fixed_elem_free_bytes AS free_bytes, \
        fixed_elem_free_rows AS spare_rows \
        FROM ndbinfo.memory_per_fragment \
        WHERE fq_name LIKE '%simples%';
+------+------+-------------+------------+------------+
| node | frag | alloc_bytes | free_bytes | spare_rows |
+------+------+-------------+------------+------------+
|    1 |    0 |      131072 |       5504 |        172 |
|    1 |    2 |      131072 |       1312 |         41 |
|    2 |    0 |      131072 |       5504 |        172 |
|    2 |    2 |      131072 |       1312 |         41 |
|    3 |    1 |      131072 |       3104 |         97 |
|    3 |    3 |      131072 |       4288 |        134 |
|    4 |    1 |      131072 |       3104 |         97 |
|    4 |    3 |      131072 |       4288 |        134 |
+------+------+-------------+------------+------------+

As a final example, we can check whether a table is being evenly sharded accross the data nodes (in this case a realy bad sharding key was chosen)…

mysql> CREATE TABLE simples (id INT NOT NULL AUTO_INCREMENT, \
        species VARCHAR(20) DEFAULT "Human", 
        PRIMARY KEY(id, species)) engine=ndb PARTITION BY KEY(species);

// Add some data

mysql> SELECT node_id AS node, fragment_num AS frag, \
        fixed_elem_alloc_bytes alloc_bytes, \
        fixed_elem_free_bytes AS free_bytes, \
        fixed_elem_free_rows AS spare_rows \
        FROM ndbinfo.memory_per_fragment \
        WHERE fq_name LIKE '%simples%';
+------+------+-------------+------------+------------+
| node | frag | alloc_bytes | free_bytes | spare_rows |
+------+------+-------------+------------+------------+
|    1 |    0 |           0 |          0 |          0 |
|    1 |    2 |      196608 |      11732 |        419 |
|    2 |    0 |           0 |          0 |          0 |
|    2 |    2 |      196608 |      11732 |        419 |
|    3 |    1 |           0 |          0 |          0 |
|    3 |    3 |           0 |          0 |          0 |
|    4 |    1 |           0 |          0 |          0 |
|    4 |    3 |           0 |          0 |          0 |
+------+------+-------------+------------+------------+

If you get chance to try out this new release then please let us know how you get on – either through a comment on this blog, a MySQL bug report or a post to the MySQL Cluster Forum.





SQL/NoSQL and MySQL Cluster 7.4 Presentations now available

My 2 sessions from 2014’s MySQL Central at Oracle OpenWorld are now available:

NoSQL and SQL: The Best of Both Worlds [CON2853]

There’s a lot of excitement about NoSQL data stores, with the promise of simple access patterns, flexible schemas, scalability, and high availability. The downside comes in the form of losing ACID transactions, consistency, flexible queries, and data integrity checks. What if you could have the best of both worlds? This session shows how MySQL Cluster provides simultaneous SQL and native NoSQL access to your data—whether it’s in a simple key-value API (memcached) or REST, JavaScript, Java, or C++. You will hear how the MySQL Cluster architecture delivers in-memory real-time performance; 99.999 percent availability; online maintenance; and linear, horizontal scalability through transparent autosharding.

MySQL Cluster: Dive into the Latest Developments [CON3815]

Wednesday, Oct 1, 3:30 PM – 4:15 PM – Moscone South – 250

I’ll be co-presenting this session with Bernd Ocklin – Director MySQL Cluster, Oracle

MySQL Cluster does more than scale beyond a billion transactions per minute. It’s also the in-memory database at the heart of mobile phone networks and online games. Scaling for the masses. A touch of your mobile phone’s green button likely has already gotten you in contact with MySQL Cluster. Driven by these extreme use cases, this session covers how to build business-critical scalable solutions with MySQL Cluster.





My NoSQL/SQL and MySQL Cluster sessions at Oracle OpenWorld

I have 2 sessions at Oracle OpenWorld in San Francisco this Thursday (2nd October 2014); please come along if your in town and feel free to grab me after the sessions for any extra questions:

NoSQL and SQL: The Best of Both Worlds [CON2853]

Thursday, Oct 2, 9:30 AM – 10:15 AM – Moscone South – 252

There’s a lot of excitement about NoSQL data stores, with the promise of simple access patterns, flexible schemas, scalability, and high availability. The downside comes in the form of losing ACID transactions, consistency, flexible queries, and data integrity checks. What if you could have the best of both worlds? This session shows how MySQL Cluster provides simultaneous SQL and native NoSQL access to your data—whether it’s in a simple key-value API (memcached) or REST, JavaScript, Java, or C++. You will hear how the MySQL Cluster architecture delivers in-memory real-time performance; 99.999 percent availability; online maintenance; and linear, horizontal scalability through transparent autosharding.

MySQL Cluster: Dive into the Latest Developments [CON3815]

Wednesday, Oct 1, 3:30 PM – 4:15 PM – Moscone South – 250

I’ll be co-presenting this session with Bernd Ocklin – Director MySQL Cluster, Oracle

MySQL Cluster does more than scale beyond a billion transactions per minute. It’s also the in-memory database at the heart of mobile phone networks and online games. Scaling for the masses. A touch of your mobile phone’s green button likely has already gotten you in contact with MySQL Cluster. Driven by these extreme use cases, this session covers how to build business-critical scalable solutions with MySQL Cluster.





Active-Active Replication, Performance Improvements & Operational Enhancements – some of what’s available in the new MySQL Cluster 7.4.1 DMR

MySQL Cluster Logo

Oracle have just made availble the new MySQL Cluster 7.4.1 Development Milestone Release – it can be downloaded from the development release tab here. Note that this is not a GA release and so we wouldn’t recommend using it in production.

There are three main focus areas for this DMR and the purpose of this post is to briefly introduce them:

  • Active-Active (Multi-Master) Replication
  • Performance
  • Operational improvements (speeding up of restarts; enhanced memory reporting)

Active-Active (Multi-Master) Replication

MySQL Cluster allows bi-directional replication between two (or more) clusters. Replication within each cluster is synchronous but between clusters it is asynchronous which means the following scenario is possible:

Conflict with asynchronous replication
Site A Replication Site B
x == 10 x == 10
x = 11 x = 20
– x=11 –> x == 11
x==20 <– x=20 –

 

In this example a value (column for a row in a table) is set to 11 on site A and the change is queued for replication to site B. In the mean time, an application sets the value to 20 on site B and that change is queued for replication to site A. Once both sites have received and applied the replicated change from the other cluster site A contains the value 20 while site B contains 11 – in other words the databases are now inconsistent.

How MySQL Cluster implements eventual consistency

There are two phases to establishing consistency between both clusters after an inconsistency has been introduced:

  1. Detect that a conflict has happened
  2. Resolve the inconsistency

The following animation illustrates how MySQL Cluster 7.2 detects that an inconsistency has been introduced by the asynchronous, active-active replication:

Detecting conflicts

While we typically consider the 2 clusters in an active-active replication configuration to be peers, in this case we designate one to be the primary and the other the secondary. Reads and writes can still be sent to either cluster but it is the responsibility of the primary to identify that a conflict has arisen and then remove the inconsistency.

A logical clock is used to identify (in relative terms) when a change is made on the primary – for those who know something of the MySQL Cluster internals, we use the index of the Global Checkpoint that the update is contained in. For all tables that have this feature turned on, an extra, hidden column is automatically added on the primary – this represents the value of the logical clock when the change was made.

Once the change has been applied on the primary, there is a “window of conflict” for the effected row(s) during which if a different change is made to the same row(s) on the secondary then there will be an inconsistency. Once the slave on the secondary has applied the change from the primary, it will send a replication event back to the slave on the primary, containing the primary’s clock value associated with the changes that have just been applied on the secondary. (Remember that the clock is actually the Global Checkpoint Index and so this feature is sometimes referred to as Reflected GCI). Once the slave on the primary has received this event, it knows that all changes tagged with a clock value no later than the reflected GCI are now safe – the window of conflict has closed.

If an application modifies this same row on the secondary before the replication event from the primary was applied then it will send an associated replication event to the slave on the primary before it reflects the new GCI. The slave on the primary will process this replication event and compare the clock value recorded with the effected rows with the latest reflected GCI; as the clock value for the conflicting row is higher the primary recognises that a conflict has occured and will launch the algorithm to resolve the inconsistency.

Options for MySQL Cluster replication conflict detection/resolution

After a conflict has been detected, you have the option of having the database simply report the conflict to the application or have it roll back just the conflicting row or the entire transaction and all subsequent transactions that were dependent on it.

So – what’s new in 7.4.1?

  • Detects conflicts between inserts and updates
  • Option to roll back entire transaction (and dependent transactions) rather than just the conflicting row
  • All conflicts are handled before switching primary – avoiding potential race conditions

As mentioned at the start of this post, this is pre-GA and there are some extra enhancements we plan on including in the final version:

  • Handle deletes which conflict with other operations
  • Roll back transactions that have read a row that had been rolled back due to a conflict

Performance

MySQL CLuster 7.4.1 Read-Write Performance
Being a scaled-out, in-memory, real-time database, MySQL Cluster performance has always been great but we continue to work on making it faster each release. In particular, we want to keep pace with the trend of having more and more cores rather than faster ones. 7.4 continues along the path of better exploiting multiple cores – as can be seen from these benchmark results.
MySQL CLuster 7.4.1 Read Performance
Just make sure that you’re using the multi-threaded data node (ndbmtd rather than ndbd) and have configured how many threads it should use.

Faster Restarts

You can restart MySQL Cluster processes (nodes) without losing database service (for example if adding extra memory to a server) and so on the face of it, the speed of the restarts isn’t that important. Having said that, while the node is restarting you’ve lost some of your high-availability which for super-critical applications can make you nervous. Additionally, faster restarts mean that you can complete maintenance activities faster – for example, a software upgrade requires a rolling restart of all of the nodes – if you have 48 data nodes then you want each of the data nodes to restart as quickly as possible.

MySQL 7.4.1 includes a number of optimisations to the restart code and so if you’re already using MySQL Cluster, it might be interesting to see how much faster it gets for your application. We also have some extra optimisations in the works that you can expect to see in later 7.4 versions.

Extra Memory Reporting

MySQL Cluster presents a lot of monitoring information through the ndbinfo database and in 7.4 we’ve added some extra information on how memory is used for individual tables.

For example; to see how much memory is being used by each data node for a particular table…

mysql> CREATE DATABASE clusterdb;USE clusterdb;
mysql> CREATE TABLE simples (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=NDB;
mysql> SELECT node_id AS node, fragment_num AS frag, \
        fixed_elem_alloc_bytes alloc_bytes, \
        fixed_elem_free_bytes AS free_bytes, \
        fixed_elem_free_rows AS spare_rows \
        FROM ndbinfo.memory_per_fragment \
        WHERE fq_name LIKE '%simples%';
+------+------+-------------+------------+------------+
| node | frag | alloc_bytes | free_bytes | spare_rows |
+------+------+-------------+------------+------------+
|    1 |    0 |      131072 |       5504 |        172 |
|    1 |    2 |      131072 |       1280 |         40 |
|    2 |    0 |      131072 |       5504 |        172 |
|    2 |    2 |      131072 |       1280 |         40 |
|    3 |    1 |      131072 |       3104 |         97 |
|    3 |    3 |      131072 |       4256 |        133 |
|    4 |    1 |      131072 |       3104 |         97 |
|    4 |    3 |      131072 |       4256 |        133 |
+------+------+-------------+------------+------------+

When you delete rows from a MySQL Cluster table, the memory is not actually freed up and so if you check the existing memoryusage table you won’t see a change. This memory will be reused when you add new rows to that same table. In MySQL Cluster 7.4, it’s possible to see how much memory is in that state for a table…

mysql> SELECT node_id AS node, fragment_num AS frag, \
        fixed_elem_alloc_bytes alloc_bytes, \
        fixed_elem_free_bytes AS free_bytes, \
        fixed_elem_free_rows AS spare_rows \
        FROM ndbinfo.memory_per_fragment \
        WHERE fq_name LIKE '%simples%';
+------+------+-------------+------------+------------+
| node | frag | alloc_bytes | free_bytes | spare_rows |
+------+------+-------------+------------+------------+
|    1 |    0 |      131072 |       5504 |        172 |
|    1 |    2 |      131072 |       1280 |         40 |
|    2 |    0 |      131072 |       5504 |        172 |
|    2 |    2 |      131072 |       1280 |         40 |
|    3 |    1 |      131072 |       3104 |         97 |
|    3 |    3 |      131072 |       4256 |        133 |
|    4 |    1 |      131072 |       3104 |         97 |
|    4 |    3 |      131072 |       4256 |        133 |
+------+------+-------------+------------+------------+
mysql> DELETE FROM clusterdb.simples LIMIT 1;
mysql> SELECT node_id AS node, fragment_num AS frag, \
        fixed_elem_alloc_bytes alloc_bytes, \
        fixed_elem_free_bytes AS free_bytes, \
        fixed_elem_free_rows AS spare_rows \
        FROM ndbinfo.memory_per_fragment \
        WHERE fq_name LIKE '%simples%';
+------+------+-------------+------------+------------+
| node | frag | alloc_bytes | free_bytes | spare_rows |
+------+------+-------------+------------+------------+
|    1 |    0 |      131072 |       5504 |        172 |
|    1 |    2 |      131072 |       1312 |         41 |
|    2 |    0 |      131072 |       5504 |        172 |
|    2 |    2 |      131072 |       1312 |         41 |
|    3 |    1 |      131072 |       3104 |         97 |
|    3 |    3 |      131072 |       4288 |        134 |
|    4 |    1 |      131072 |       3104 |         97 |
|    4 |    3 |      131072 |       4288 |        134 |
+------+------+-------------+------------+------------+

As a final example, we can check whether a table is being evenly sharded accross the data nodes (in this case a realy bad sharding key was chosen)…

mysql> CREATE TABLE simples (id INT NOT NULL AUTO_INCREMENT, \
        species VARCHAR(20) DEFAULT "Human", 
        PRIMARY KEY(id, species)) engine=ndb PARTITION BY KEY(species);

// Add some data

mysql> SELECT node_id AS node, fragment_num AS frag, \
        fixed_elem_alloc_bytes alloc_bytes, \
        fixed_elem_free_bytes AS free_bytes, \
        fixed_elem_free_rows AS spare_rows \
        FROM ndbinfo.memory_per_fragment \
        WHERE fq_name LIKE '%simples%';
+------+------+-------------+------------+------------+
| node | frag | alloc_bytes | free_bytes | spare_rows |
+------+------+-------------+------------+------------+
|    1 |    0 |           0 |          0 |          0 |
|    1 |    2 |      196608 |      11732 |        419 |
|    2 |    0 |           0 |          0 |          0 |
|    2 |    2 |      196608 |      11732 |        419 |
|    3 |    1 |           0 |          0 |          0 |
|    3 |    3 |           0 |          0 |          0 |
|    4 |    1 |           0 |          0 |          0 |
|    4 |    3 |           0 |          0 |          0 |
+------+------+-------------+------------+------------+

If you get chance to try out this new release then please let us know how you get on – either through a comment on this blog, a MySQL bug report or a post to the MySQL Cluster Forum.





I’ll be presenting at Oracle OpenWorld next week

I'm speaking at MySQL Central @ Oracle OpenWorld 2014
I will be presenting two sessions at MySQL Central @ Oracle OpenWorld in San Francisco next Thursday (2nd Ocotber). I hope to see as many of you there as possible and I’ll be around after the session to continue answering any questions.

NoSQL and SQL: The Best of Both Worlds [CON2853]

There’s a lot of excitement about NoSQL data stores, with the promise of simple access patterns, flexible schemas, scalability, and high availability. The downside comes in the form of losing ACID transactions, consistency, flexible queries, and data integrity checks. What if you could have the best of both worlds? This session shows how MySQL Cluster provides simultaneous SQL and native NoSQL access to your data—whether it’s in a simple key-value API (memcached) or REST, JavaScript, Java, or C++. You will hear how the MySQL Cluster architecture delivers in-memory real-time performance; 99.999 percent availability; online maintenance; and linear, horizontal scalability through transparent autosharding.

MySQL Cluster: Dive into the Latest Developments [CON3815]

MySQL Cluster is the distributed, shared-nothing version of MySQL. It’s typically used for applications that need any combination of high availability, real-time performance, and scaling of reads and writes. After a brief introduction to the technology, its uses, and the new features added in MySQL Cluster 7.3, this session focuses on the very latest developments happening in MySQL Cluster 7.4. As you’d expect from a real-time, scalable, distributed, in-memory database, performance continues to be a top priority, as do simplicity of use and robustness. Come hear firsthand what’s being done to make sure MySQL Cluster continues to dominate in mission-critical, high-performance applications.





MySQL Fabric/MySQL Utilities 1.4.4 released

MySQL Utilities & Fabric The binary and source versions of MySQL Utilities/MySQL Fabric have now been made available at http://dev.mysql.com/downloads/utilities/.

This release contains bug fixes and minor enhancements – full details can be found in the MySQL Fabric/MySQL Utilities release notes.





MySQL Cluster latest developments – webinar replay + Q&A

MySQL Cluster LogoI recently hosted hosting a webinar which explained what MySQL Clusrter is, what it can deliver and what the latest developments were. The “Discover the latest MySQL Cluster Developments” webinar is now available to view here. At the end of this article you’ll find a full transcript of the Q&A from the live session.

Details:

View this webinar to learn how MySQL Cluster 7.3, the latest GA release, enables developer agility by making it far simpler and faster to build your products and web-based applications with MySQL Cluster. You’ll also learn how MySQL Cluster and its linear scalability, 99.999% uptime, real-time responsiveness, and ability to perform over 1 BILLION Writes per Minute can help your products and applications meet the needs of the most demanding markets. MySQL Cluster combines these capabilities and the affordability of open source, making it well suited for use as an embedded database.

In this replay you’ll learn about the following MySQL Cluster capabilities, including the latest innovations in the 7.3 GA release:

  • Auto-sharding (partitioning) across commodity hardware for extreme read and write scalability
  • Cross-data center geographic synchronous and asynchronous replication
  • Online scaling and schema upgrades, now with improved Connection Thread Scalability
  • Real-time optimizations for ultra-low, predictable latency
  • Foreign Key Support for tight referential integrity
  • SQL and NoSQL interfaces, now with support for Node.js
  • Support for MySQL 5.6, allowing use of the latest InnoDB and NDB engines within one database
  • Integrated HA for 99.999% availability
  • Auto-Installer that installs, configures, provisions and tunes a production grade cluster in minutes

In addition, you will get a sneak preview of some of the new features planned in MySQL Cluster 7.4 Come and learn how MySQL Cluster can help you differentiate your products and extend their reach into new markets, as well as deliver highly demanding web-based applications, either on premises or in the cloud.

Q&A Transcript

  • When using the Memcached API, can I use my existing Memcached connector? Yes. The Memcached API actually uses the regular memcached protocol but then has a custom plugin that acesses the MySQL Cluster data nodes rather than using its local in-memory store.
  • If I’m replicating between 2 Clusters in 2 data centres and the WAN fails for a minute – what happens? Because the replication between MySQL Cluster instances is asynchronous – the application isn’t impacted (for example, there will be no extra errors or latency). The changes will be stored in the binary log of the Cluster to which they were sent and then replicated to the other site once the WAN returns.
  • Can I scale back down as well as up? It’s an online operation to reduce the number of MySQL Servers (or other application nodes) but that isn’t currently possible for the data nodes. In reality, it’s very rare that applications need to reduce the amount of data they store.
  • Are there any MySQL connectors that don’t work with MySQL Cluster? No, any connector that works with MySQL will work just as well with MySQL Cluster.
  • Do you have more details on the benchmark results? Yes – take a look at the MySQL Cluster Benchmarks page.
  • I’ve been hearing about MySQL Fabric – does that also allow queries and joins ot span multiple shards? Currently, the only option for cross-shard queries is to use MySQL Cluster or implement them at the application layer.
  • Is the data is partioned over diffrent cluster nodes or do all cluster nodes hold the full data set. Each node group stores a subset of the rows from each table. The 2 data nodes within the node group will store the exact same set of rows.
  • Where can I find a definition of those different kinds of Foreign Key constraints? The wikipedia definition for Foreign Keys is a good place to start.
  • What is the diffrence between ndbcluster and MySQL Cluster ? None – they’re one and the same. When you hear any of “Cluster”, “MySQL Cluster”, “NDB” and “NDB Cluster” the meaning is the same.
  • Do I need to have a web server installed for the Auto-Installer to work? No – the MySQL Cluster auto-installer comes with a small web server built-in.
  • Are there any dependencies to meet before installing MySQL Cluster on RHEL Liunx? It should work out of the box. My preferred way of working is to use the generic Linux tar ball for MySQL Cluster (get it from the MySQL Cluster download page) – extract it and then run the auto-installer or configure it manually.
  • Is there any guide available to migrate mysql nodes to mysql cluster? Probably the closest we have is a white paper on how to get the best out of any PoC for MySQL Cluster (as it highlights what needs to be done differently in order to get the best results)… MySQL Cluster Evaluation Guide. Note that MySQL Cluster uses a different version of the mysqld binary and so you’ll need to stop your existing MySQL Server and start up the new one. To migrate a specific table to MySQL Cluster after that is done use “ALTER TABLE my-tab ENGINE=NDB;”.
  • Does drupal support MySQL Cluster? I’ve heard of people doing it but I suspect that minor tweaks to teh Drupal code may have been needed.
  • How do the NoSQL APIs map to the SQL database schemas? It varies slightly by API – in general, you provide some annotations or meta-data to specify how tables or columns should map to keys/objects/properties. With Memcached you have the option of being schema-less and having all data stored in one, big, generic table.
  • Where can I learn more about MySQL Fabric? The MySQL Fabric page is a good starting point; for an end-to-end example, take a look at this tutorial on adding HA and then sharding using MySQL Fabric.
  • What is difference between MySQL Fabric and MySQL Cluster? MySQL Fabric provides server farm management on top of ‘regular’ MySQL Servers storing data with the InnoDB storage engine it delivers HA and sharding. MySQL Cluster works below the MySQL Server, storing data in the NDB storage engine (on the data nodes). MySQL Cluster can deliver higher levels of High Availability; better application transparency and cross-shard queries, joins and transactions but it does mean using a different storage engine which of course comes with its own limitations (see the MySQL Cluster Evaluation Guide for details of those).
  • So, if I have any full table scans, should I forget about MySQL Cluster> Note necessarily. If every one of your high running operations is a full table scan then MySQL Cluster might not be ideal. However if most operations are simpler but you have some full table scans then that could be fine. The optimisations going into MySQL Cluster 7.4 should particularly benefit table scans.




Discover the latest MySQL Cluster Developments – Upcoming webinar

MySQL Cluster LogoOn Thursday 17th July I’ll be hosting a webinar which explains what MySQL Clusrter is, what it can deliver and what the latest developments are. As always the webinar is free but please register here.

Details:

Join this technical webinar to learn how MySQL Cluster 7.3, the latest GA release, enables developer agility by making it far simpler and faster to build your products and web-based applications with MySQL Cluster. You’ll also learn how MySQL Cluster and its linear scalability, 99.999% uptime, real-time responsiveness, and ability to perform over 1 BILLION Writes per Minute can help your products and applications meet the needs of the most demanding markets. MySQL Cluster combines these capabilities and the affordability of open source, making it well suited for use as an embedded database.

In this webcast you’ll learn about the following MySQL Cluster capabilities, including the latest innovations in the 7.3 GA release:

  • Auto-sharding (partitioning) across commodity hardware for extreme read and write scalability
  • Cross-data center geographic synchronous and asynchronous replication
  • Online scaling and schema upgrades, now with improved Connection Thread Scalability
  • Real-time optimizations for ultra-low, predictable latency
  • Foreign Key Support for tight referential integrity
  • SQL and NoSQL interfaces, now with support for Node.js
  • Support for MySQL 5.6, allowing use of the latest InnoDB and NDB engines within one database
  • Integrated HA for 99.999% availability
  • Auto-Installer that installs, configures, provisions and tunes a production grade cluster in minutes

In addition, you will get a sneak preview of some of the new features planned in MySQL Cluster 7.4 Come and learn how MySQL Cluster can help you differentiate your products and extend their reach into new markets, as well as deliver highly demanding web-based applications, either on premises or in the cloud.

Even if you can’t join the live webinar, it’s worth registering as you’ll be emailed a link to the replay as soon as it’s available.