The binary version for MySQL Cluster 7.1,21 has now been made available at https://www.mysql.com/downloads/cluster/7.1.html#downloads (GPL version) or https://support.oracle.com/ (commercial version).
A description of all of the changes (fixes) that have gone into MySQL Cluster 7.1.22 (compared to 7.1.21) are available from the 7.1.22 Change log.
The binary version for MySQL Cluster 7.2.6 has now been made available at http://www.mysql.com/downloads/cluster/ (GPL version) or https://support.oracle.com/ (commercial version).
A description of all of the changes (fixes) that have gone into MySQL Cluster 7.2.6 (compared to 7.2.5) are available from the 7.2.6 Change log.
There are a couple of conferences coming up where you can expect to learn about the latest developments in MySQL Cluster and MySQL Replication (as well as what else is happening in MySQL 5.6).
The first is the Oracle MySQL Innovation Day which is being held in Oracle HQ at Redwood Shores. This is an all-day event on 5th June – unfortunately I won’t be able to attend this one but there will be lots of great Cluster and replication sessions. If you can’t make it out to California then there will be a live Webcast. You can register here to attend in person or join the webcast.
The second is MySQL Connect – this runs the weekend before Oracle OpenWorld in San Francisco; it’s not until 29th September but it’s worth registering now to get the early bird pricing and save $500 (end 13th July). There are lots of great sessions lined up both from the MySQL experts within Oracle and users and community members.
MySQL 5.6 Replication - Global Transaction IDs
On Wednesday (16th May 2012), Mat Keep and I will be presenting the new replication features that are previewed as part of the latest MySQL 5.6 Development Release. If you’d like to attend then register here.
MySQL 5.6 delivers new replication capabilities which we will discuss in the webinar:
The session will wrap up with resources to get started with MySQL 5.6 and an opportunity to ask questions.
The webinar will last 45-60 minutes and will start on Wednesday, May 16, 2012 at 09:00 Pacific time (America); start times in other time zones:
As always, it’s worth registering even if you can’t make the live webcast as you’ll be emailed a link to the replay as soon as it’s available.
I’ve previously provided an example of using MySQL Cluster Manager to add nodes to a running MySQL Cluster deployment but I’ve since received a number of questions around how to do this in more complex circumstances (for example when ending up with more than 1 MySQL Server on a single host where each mysqld process should use a different port). The purpose of this post is to work through one of these more complex scenarios.
The starting point is an existing cluster made up of 3 hosts with the nodes (processes) as described in this MCM report:
mcm> SHOW STATUS -r mycluster; +--------+----------+-------------------------------+---------+-----------+---------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-------------------------------+---------+-----------+---------+ | 1 | ndbmtd | paas-23-54.osc.uk.oracle.com | running | 0 | 7_2_5 | | 2 | ndbmtd | paas-23-55.osc.uk.oracle.com | running | 0 | 7_2_5 | | 49 | ndb_mgmd | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 50 | mysqld | paas-23-54.osc.uk.oracle.com | running | | 7_2_5 | | 51 | mysqld | paas-23-55.osc.uk.oracle.com | running | | 7_2_5 | | 52 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 53 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | | +--------+----------+-------------------------------+---------+-----------+---------+ 7 rows in set (0.01 sec)
This same configuration is shown graphically in this diagram:
Original MySQL Cluster deployment
Note that the ‘ndbapi’ node isn’t actually a process but is instead a ‘slot’ that can be used by any NDB API client to access the data in the data nodes directly – this could be any of:
This Cluster is now going to be extended by adding an extra host as well as extra nodes (both processes and ndbapi slots).
The following diagram illustrates what the final Cluster will look like:
MySQL Cluster after on-line scaling
The first step is to add the new host to the configuration and make it aware of the MySQL Cluster package being used (in this example, 7.2.5). Note that you should already have started the mcmd process on this new host (if not then do that now):
mcm> ADD HOSTS --hosts=paas-23-57.osc.uk.oracle.com mysite; +--------------------------+ | Command result | +--------------------------+ | Hosts added successfully | +--------------------------+ 1 row in set (8.04 sec) mcm> ADD PACKAGE -h paas-23-57.osc.uk.oracle.com --basedir=/home/oracle/cluster_7_2_5 7_2_5; +----------------------------+ | Command result | +----------------------------+ | Package added successfully | +----------------------------+ 1 row in set (0.68 sec)
At this point the MCM agent on the new host is connected with the existing 3 but it has not become part of the Cluster – this is done by declaring which nodes should be on that host; at the same time I add some extra nodes to the existing hosts. As there will be more than one MySQL server (mysqld) running on some of the hosts, I’ll explicitly tell MCM what port number to use for some of the mysqlds (rather than just using the default of 3306).
mcm> ADD PROCESS -R ndbmtd@paas-23-54.osc.uk.oracle.com, ndbmtd@paas-23-55.osc.uk.oracle.com,mysqld@paas-23-56.osc.uk.oracle.com, ndbapi@paas-23-56.osc.uk.oracle.com,mysqld@paas-23-57.osc.uk.oracle.com, mysqld@paas-23-57.osc.uk.oracle.com,ndbapi@paas-23-57.osc.uk.oracle.com -s port:mysqld:54=3307,port:mysqld:57=3307 mycluster;
+----------------------------+ | Command result | +----------------------------+ | Process added successfully | +----------------------------+ 1 row in set (2 min 34.22 sec)
In case you’re wondering how I was able to predict the node-ids that would be allocated to the new nodes, the scheme is very simple:
If you look carefully at the results you’ll notice that the ADD PROCESS command took a while to run (2.5 minutes) – the reason for this is that behind the scenes, MCM performed a rolling restart – ensuring that all of the existing nodes pick up the new configuration without losing database service. Before starting the new processes, it makes sense to double check that the correct ports are allocated to each of the mysqlds:
mcm> GET -d port:mysqld mycluster; +------+-------+----------+---------+----------+---------+---------+---------+ | Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment | +------+-------+----------+---------+----------+---------+---------+---------+ | port | 3306 | mysqld | 50 | | | Default | | | port | 3306 | mysqld | 51 | | | Default | | | port | 3306 | mysqld | 52 | | | Default | | | port | 3307 | mysqld | 54 | | | | | | port | 3306 | mysqld | 56 | | | Default | | | port | 3307 | mysqld | 57 | | | | | +------+-------+----------+---------+----------+---------+---------+---------+ 6 rows in set (0.07 sec)
At this point the new processes can be started and then the status of all of the processes confirmed:
mcm> START PROCESS --added mycluster; +------------------------------+ | Command result | +------------------------------+ | Process started successfully | +------------------------------+ 1 row in set (26.30 sec) mcm> SHOW STATUS -r mycluster; +--------+----------+-------------------------------+---------+-----------+---------+ | NodeId | Process | Host | Status | Nodegroup | Package | +--------+----------+-------------------------------+---------+-----------+---------+ | 1 | ndbmtd | paas-23-54.osc.uk.oracle.com | running | 0 | 7_2_5 | | 2 | ndbmtd | paas-23-55.osc.uk.oracle.com | running | 0 | 7_2_5 | | 49 | ndb_mgmd | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 50 | mysqld | paas-23-54.osc.uk.oracle.com | running | | 7_2_5 | | 51 | mysqld | paas-23-55.osc.uk.oracle.com | running | | 7_2_5 | | 52 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 53 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | | | 3 | ndbmtd | paas-23-54.osc.uk.oracle.com | running | 1 | 7_2_5 | | 4 | ndbmtd | paas-23-55.osc.uk.oracle.com | running | 1 | 7_2_5 | | 54 | mysqld | paas-23-56.osc.uk.oracle.com | running | | 7_2_5 | | 55 | ndbapi | *paas-23-56.osc.uk.oracle.com | added | | | | 56 | mysqld | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 | | 57 | mysqld | paas-23-57.osc.uk.oracle.com | running | | 7_2_5 | | 58 | ndbapi | *paas-23-57.osc.uk.oracle.com | added | | | +--------+----------+-------------------------------+---------+-----------+---------+ 14 rows in set (0.08 sec)
The enlarged Cluster is now up and running but any existing MySQL Cluster tables will only be stored across the original data nodes. To remedy that, each of those existing tables should be repartitioned:
mysql> ALTER ONLINE TABLE simples REORGANIZE PARTITION; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> OPTIMIZE TABLE simples; +-------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+----------+----------+----------+ | clusterdb.simples | optimize | status | OK | +-------------------+----------+----------+----------+ 1 row in set (0.00 sec)
You can safely perform the repartitioning while the Cluster is up and running (with your application sending in reads and writes) but there is a performance impact (has been measured at 50%) and so you probably want to do this at a reasonably quiet time of day.
As always, please post feedback and questions in the comments section of this post.
Schema-free NoSQL Data
Update – the webinar replay is now available from here.
On Thursday, I’ll be presenting a webinar on NoSQL (of course with a MySQL twist!) – as always it’s free to attend but you need to register here in advance. Even if you can’t attend, it’s worth registering as you’ll be sent a link to the replay and the charts. The session will introduce the concepts and motivations behind the NoSQL movement and then go on to explain how you can get most of the same benefits with MySQL (including MySQL Cluster) while still getting the RDBMS benefits such as ACID transactions.
The official description:
The ever increasing performance demands of web-based services has generated significant interest in providing NoSQL access methods to MySQL – enabling users to maintain all of the advantages of their existing relational database infrastructure, while providing blazing fast performance for simple queries, using an API to complement regular SQL access to their data. This session looks at the existing NoSQL access methods for MySQL as well as the latest developments for both the InnoDB and MySQL Cluster storage engines. See how you can get the best of both worlds – persistence, consistency, rich queries, high availability, scalability and simple, flexible APIs and schemas for agile development.
When:
The binary version for MySQL Cluster 7.2.5 has now been made available at http://www.mysql.com/downloads/cluster/ (GPL version) or https://support.oracle.com/ (commercial version).
A description of all of the changes (fixes) that have gone into MySQL Cluster 7.2.5 (compared to 7.2.4) will appear in the 7.2.5 Change log.
There are a number of resources available to help get you up and running with MySQL Cluster so that you can start experimenting – including the quick start guides from the download page but what happens when you get further down the line and need to get the best performance from your Cluster? One useful source is the Guide to Optimizing Performance of the MySQL Cluster Database which covers how best to configure MySQL Cluster as well as what you should be doing with your application and schema to get the best performance.
Any feedback on this white paper would be greatly appreciated – whether it be a recommendation that you found gave good results, something that didn’t work or a killer tip that we missed out.
I’ll be presenting on/demoing MySQL Cluster 7.2 at this free breakfast seminar in Oracle’s London office on 25th April – starting with coffee at 9:00 and ending with lunch at 13:00 (quite a generous take on “breakfast”!). Space is limited and so if you would like to attend then register early here.
As well as MySQL Cluster there will be sessions on optimising MySQL Server for performance and scaling and Oracle’s roadmap for cloud deployment.
Full agenda:
| 09:00 | Registration and Welcome Coffee |
| 09:30 | Introduction Simon Deighton, MySQL Sales Manager |
| 09:45 | MySQL Database: Performance & Scalability Optimizations Tony Holmes, Principal PreSales Consultant |
| 10:45 | Coffee/Tea Break |
| 11:00 | Performance & Scalability with MySQL Cluster 7.2 Mat Keep, Senior Product Marketing Manager & Andrew Morgan, Senior Product Manager |
| 12:00 | The MySQL Roadmap: Discover What’s Next For On-Premise & Cloud-Based Deployments Tony Holmes, Principal PreSales Consultant |
| 12:45 | Q&A |
| 13:00 | Light lunch buffet and end of seminar |
1 Billion queries per minute with MySQL Cluster
Oracle announced the General Availability of MySQL Cluster 7.2 today. Join this live webinar to learn about what’s new in the production-ready, GA release of MySQL Cluster 7.2, enabling the latest generation of web and telecoms applications to take advantage of high write scalability, SQL and NoSQL interfaces and 99.999% availability, including:
The webinar takes place on Thursday 23rd February at 09:00 PST, 17:00 GMT, 18:00 CET. Mat Keep and I will be presenting.
As always, the webinar is free but you’ll need to register here in advance – even if you can’t make the live event, this will make sure that you get emailed a link to the recording.
