Johan Andersson's Cluster and HA Blog

Walking on Cloud 9

2011-12-15T14:55:00.003Z

As the saying goes, we at Severalnines have been walking on several clouds this year, 9 to be precise!

Today, we are proud to say that we are on walking on Cloud 9!

And in the spirit of celebration, we would like to announce our:

Top 9 Clouds of the Year 2011 for Severalnines

Cloud 1 – releasing ClusterControl™ - our first commercial product in April!

ClusterControl™ is our flagship product. It enables developers and database administrators to Deploy, Manage, Monitor and Scale their clustered database platforms, free from the complexity and learning curves associated with database clusters. Read more ...

Cloud 2 – releasing ClusterControl™ for MySQL Replication

Designed to address issues and needs of MySQL users relying on MySQL Replication, ClusterControl™ for MySQL Replication offers a complete set of tools to assist developers and administrators of all skill levels to deploy, manage, monitor and scale their replicated MySQL databases. Read more ...

Cloud 3 - releasing ClusterControl™ for MySQL Galera together with Codership

ClusterControl™ for MySQL Galera enables customers to Deploy, Manage, Monitor and Scale a clustered MySQL database platform based on Galera Replication. MySQL Galera is a synchronous multi-master cluster for MySQL/InnoDB, and allows applications to read and write from any MySQL Server.

Developers and DBAs now have access to all of the features of Severalnines' flagship product ClusterControl™ specifically adapted to MySQL Galera. Read more ...

Cloud 4 – reaching over 7,000 installations via the Severalnines Configurator

The Severalnines Configurator allows you to generate a production class configuration for a clustered MySQL configuration. It also generates a deployment package that automates the deployment of the complete database setup. Topologies can be based on MySQL Replication, MySQL Cluster or MySQL Galera.

We have a great user base and in order to facilitate communication within our user community, we set up our Severalnines Customer Services platform with forums, online support, etc. both for paying customers as well as users. Check it out and join the conversation!

Cloud 5 – our Customers

At Severalnines, our goal is to reduce database costs, ease deployment, simplify management and increase DBA and developer productivity.

But rather than us telling you why we think we are great, we wanted to provide documented case studies on how our innovative products are helping customers meet and exceed business goals for their database operations. See what our customers have to say about us.

Cloud 6 – introducing Severalnines DataCloud™

Severalnines DataCloud™ enables DBaaS for public, private and hybrid clouds. It extends the advantages of cloud computing to the database infrastructure layer by enabling on-demand access, automated management, managed availability and elasticity of MySQL databases. This reduces cost and the complexities of deploying and managing databases. Read more ...

Cloud 7 – launching the first ever 100% European DbaaS with City Network

On November 23^rd, Severalnines and our partner City Network announced the first fully European Database as a Service (DBaaS) solution - in beta form. The City Cloud Database Service is based and operated in the European Union - offering European customers full compliance with EU Directive on Data Protection 95/46/EC and a safe haven from the reaches of the US Patriot Act and making it the first ever offering of its kind. Read more ...

Cloud 8 – being talked about at the European Commission – Severalnines in the News

Following our announcement of the City Cloud Database Service, the press took over and 20+ original articles later, we discovered that we were even being talked about at European Commission conferences. Which shows to prove that politicians do read the papers (or are well briefed by colleagues who do) and that we had hit the right spot with our announcement. Find out more and see all the press coverage in our News Center.

Cloud 9 – winning the EuroCloud Sweden & Europe Awards 2011 for Best Startup!!!

We did it! After winning the EuroCloud Award for Sweden, we won the EuroCloud Europe Award for Best Startup last week in Paris at a prestigious ceremony on the Seine. We attended the ceremony together with our friends from City Network and needless to say that we had a great night! The Awards have been widely covered by the European press in multiple languages – visit our News Center for all the details.

All in all, it's been a great year for us and we have all of you to thank for that!

So here is a BIG 'thank you' from everyone at Severalnines to all our customers, partners, friends and supporters out there.

Have a great year-end and and an even better start to the year 2012 - see you there!

Please do reach out to us with your feedback on Facebook, LinkedIn, XING or directly via these contact details for fruitful and interactive discussions on this latest release. For 'instant' communication, feel free to follow us on Twitter !

About Severalnines

Severalnines provides automation and management software for easily usable, highly available and auto-scalable cloud database platforms. ClusterControl™, the company’s flagship product, used by developers and administrators of all skill levels, addresses the full deploy-manage-monitor-scale cycle. Severalnines has enabled over 7,000 deployments to date via its popular online configurator for clustered MySQL databases.

To see who is using Severalnines today, please visit our references page.

New Euro Partnership offers safe-haven from US Patriot Act

2011-11-23T09:48:00.003Z

New Euro Partnership offers safe-haven from US Patriot Act
Severalnines and City Network launch first fully European ‘Database as a Service’

STOCKHOLM and LONDON, 23^rd November -Severalnines, provider of automation and management software for cloud database platforms, and City Network, the ‘data center in a browser’ company have announced the first fully European Database as a Service (DBaaS) solution - in beta form. The City Cloud Database Service is based and operated by companies in the European Union - offering European customers full compliance with EU Directive on Data Protection 95/46/EC and a safe haven from the reaches of the US Patriot Act.

EU customers can now benefit from the savings and flexibility enabled by cloud-based database services safe in the knowledge that they will not fall under the jurisdiction of the Patriot Act. Under the Patriot Act, data from EU users of US-owned cloud-based services can currently be shared with US law enforcement agencies without the need to tell the user.

“Small start-ups and large corporations are turning to the cloud in a bid to deploy and manage databases with as little overhead as possible - but they need to know their data is secure and that they retain the right and ability to control who accesses it,” says Vinay Joosery, chief executive officer at Severalnines.

“A fully managed database service will enable our customers to further reap the benefits of the cloud,” says Johan Christenson, Chairman at City Network. “We believe that a service owned and operated locally in the EU, and fully compliant with EU data protection laws, will be very particularly attractive for European companies. US companies with European operations will also benefit from the lower latency of a locally hosted solution.”

The MySQL based City Cloud Database Service enables companies to outsource the configuration and management of their databases and only pay for what they use. Specific benefits include:

• Web interface for easy provisioning

• Zero maintenance or administration – backups, patch management and replication handling are fully automated

• Performance Management – users have visibility of top queries and can drill-down into performance

• High Availability – failures are handled automatically to minimise downtime

• Scalability – users can add more instances at the click of a mouse

• Comprehensive user interface for administering databases; and

• Data import and export functionality to help move MySQL databases within the cloud.

The service relies on the Severalnines DataCloud platform, a database automation and management platform that helps companies run flexible databases anywhere, whether on their premises or in the cloud. It is based on City Network’s City Cloud computing platform which allows unlimited growth with a focus on stability, security and redundancy.

City Cloud Database Service is available in beta. Companies can apply for the trial by emailing dbaas@citycloud.eu. The service is available free of charge for the duration of the beta period, which will run until early 2012. More information is available on http://www.citycloud.eu/

[ENDS]

About Severalnines

With everyone at Severalnines coming from an Open Source background and with a view to supporting other start-up organisation, the company is introducing a start-up program, whereby organisations with less than 10 employees and less than one million Euro in funding will be given free access to its enterprise software for 12 months. For information, just email startup@severalnines.com.

To see who is using Severalnines today, please visit our references page: http://severalnines.com/references

About City Network

With more than 10,000 customers across Europe City Network is one of the leading European hosting companies. Core services include cloud hosting, dedicated environments as well as shared hosting and domain services. City Network also provides high end backup services via www.onlinebackup.io. City Cloud is the cloud computing brand which you can read more about at www.citycloud.eu. You can find more information about City Network at: www.citynetworkhosting.com

Severalnines releases ClusterControl™ for MySQL Galera in cooperation with Codership

2011-10-25T10:42:00.005+01:00

Stockholm – October 25^th 2011

Severalnines, provider of automation and management software for easily usable, highly available and auto-scalable cloud database platforms, today announces the release of ClusterControl™ for MySQL Galera in cooperation with Codership, the replication experts organisation that leverages the latest developments in computer science to produce fast and scalable synchronous replication solutions that "just work" for databases and similar applications.

Introducing ClusterControl™ for MySQL Galera

ClusterControl™ for MySQL Galera enables customers to Deploy, Manage, Monitor and Scale a clustered MySQL database platform based on Galera Replication.

Developers and DBAs now have access to all of the features of Severalnines' flagship product ClusterControl™ specifically adapted to MySQL Galera.

Designed to address issues and needs of MySQL users relying on MySQL Replication, ClusterControl™ for MySQL Galera offers a complete set of tools to assist developers and administrators of all skill levels to deploy, manage, monitor and scale their replicated MySQL databases.

What is MySQL Galera?

MySQL Galera is a synchronous multi-master cluster for MySQL/InnoDB or, in similar words, a synchronous multi-master replication plug-in for InnoDB. It is very different from the regular MySQL Replication and addresses a number of issues including write conflicts when writing on multiple masters, replication lag and slaves being out of sync with the master. Users do not have to know which server they can write to (the master) and which servers they can read from (the slaves). With MySQL Galera you can simply write and read from any MySQL Server.

Galera Cluster Use Cases

Galera replication works for a wide variety of use cases for high availability and scalability where you tolerate no lost of transactions or no slave lag or you look for write/read scalability Here are some common use cases where Galera can be used.

· Read Master

o Traditional MySQL master-slave topology, but with Galera all "slave" nodes are capable masters at all times, it is just the application who treats them as slaves. Galera replication can guarantee 0 slave lag for such installations and due to parallel slave applying, much better throughput for the cluster.

· Write Scalability

o Distributing writes across the cluster will harness the CPU power in slave nodes for better use to process client write transactions. Due to the row based replication method, only changes made during a client transaction will be replicated and applying such a transaction in slave applier is much faster than the processing of the original transaction. Therefore the cluster can distribute the heavy client transaction processing across many master nodes and this yields in better write transaction throughput overall.

· WAN Clustering

o Synchronous replication works fine over the WAN network. There will be a delay, which is proportional to the network round trip time (RTT), but it only affects the commit operation.

· Disaster Recovery

o Disaster recovery is a sub-class of WAN replication. Here one data center is passive and only receives replication events, but does not process any client transactions. Such a remote data center will be up to date at all times and no data loss can happen. During recovery, the spare site is just nominated as primary and application can continue as normal with a minimal fail over delay.

· Latency Eraser

o With WAN replication topology, cluster nodes can be located close to clients. Therefore all read & write operations will be super fast with the local node connection. The RTT related delay will be experienced only at commit time, and even then it can be generally accepted by end user, usually the kill-joy for end user experiences is the slow browsing response time, and read operations are as fast as they possibly can be.

Features and benefits of MySQL Galera

Galera features

· Active-active multimaster cluster

· Synchronous replication for InnoDB

· Read and write to any MySQL Server

· Automatic failover and node joining

· True Parallel replication

· Direct client connections

Galera benefits

· No slave lag or lost of data or out-of-sync situations.

o All nodes are consistent.

· Scales writes and reads

· Works synchronously over LAN/WAN/CLOUD

· No changes to applications

View all of the features here

Deploying a Galera cluster with ClusterControl™

A Galera cluster can be configured using the Severalnines Configurator

Our Online Tutorial provides an in-depth view and understanding of Galera cluster, covering the following topics:

· Introduction to Galera cluster

· Differences between MySQL Replication and Galera replication

· The pros and cons of Galera cluster

· Using Galera cluster to replicate between data centers

· Certification-based Replication

· Deploying a Galera cluster

· Failure handling with ClusterControl™ and Galera

· Adding a node to a Galera cluster

· Schema changes in Galera cluster

Try the ClusterControl™ Configurator for MySQL Galera today or book a demo to see it live in action!

· ClusterControl™ Configurator for MySQL Galera – try it today!

· Online Tutorial – ClusterControl™ for MySQL Galera

· Severalnines Knowledge Base

· Book a demo!

About Severalnines

To see who is using Severalnines today, please visit our references page.

About Codership

Codership Oy was founded in 2007 by database clustering experts to exploit the latest developments in computer science and produce fast & scalable synchronous replication solutions for databases and similar applications. Currently, Codership offers multi-master cluster for MySQL database as open source. For more information about our open source MySQL Galera software and support offering, visit www.codership.com

© 2011 Severalnines AB. Severalnines and the Severalnines logo(s) are trademarks of Severalnines AB. MySQL is a registered trademark of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Multi-master and read slaves using Severalnines

2011-10-13T08:00:00.003+01:00

This blog post shows how you can use the Severalnines Configurator for MySQL Replication to deploy a Multi-master replication setup, and install ClusterControl.
You can also watch videos showing what is described below or read an even more detailed tutorial.

When the deployment is finished you have a set of tools to manage and monitor replication, as well as to add new slaves, and to perform failover.
You can choose:

The number of slaves you wish to connect to the master->relay master.
whether you want to use MySQL 5.5.x or Percona 5.5.x
Semi-sync replication or not (semi sync really recommended)
cloud provider (on premise/EC2/Rackspace

Please note that your hosts must resolve to a valid ip. If 'hostname -i' resolves to nothing or to 127.0.0.1 it will not work.

We recommend also that you don't use bi-directional replication. It may sound like a good idea, but in practice it is challenging to get it working well.

When deployed you can easily perform tasks as:

Add new slaves
Failover
View replication health information
Stop/start replication links
Stage slaves

The Configurator for MySQL Replication is a wizard-like application and you have to enter details about the setup you want to have. When this is done you will get a tar.gz that contains the deployment and management scripts.

ClusterControl

The deployment process will also install ClusterControl which is a set of monitoring agents and functionality to manage the database installation. The agents are deployed on each server and collects host information and information from the local MySQL server a particular agent is monitoring (such as replication statistics and status information).

One server (dedicated) is denoted the ClusterControl server. It is holding a database, CMON DB, that contains data about the monitored hosts, and the reporting data from the local agents is stored in this database. On this server a Controller is running and faciliates failover etc.

Moreover, the ClusterControl server exposes a web interface that can be used to graphically see the health of the replication cluster (after the deployment is done, take a web browser and point it to http://clustercontrolserver/cmon :

From ClusterControl you can then upload a schema, and start loading in data into a database. As well as doing GRANTs etc. In the Enterprise version you an also define performance probes (to make sure you system is not degrading over time), tune queries, try out new queries before putting them in production. You also have a Query Monitor and explains at your hands.

Failover is automatic (if the master fails, the relay will become the new master). When the master comes up again, it will connect to the new master and sync up, become the relay, and the slaves will failover to the relay. We also make use of the replication features of MySQL 5.5 (such as relay recovery) and a bunch of other techniques.

You can also add slaves or remove slaves with the click of a button, as well as scheduling backups.

Installation
When you have finished the wizard you get a package that you should deploy on the ClusterControl server:

tar xvfz s9s-mysql-55.tar.gz
cd s9s-mysql-55/mysql/scripts/install
./deploy.sh

The 'deploy.sh' script will create data directories, install initd scripts, create mysql users, apply database GRANTs (those you defined in the Configurator), and install ClusterControl.

A while in 'deploy.sh' replication will be started, and it can look like this:

*******************************************************************************
*                   Starting replication                                      *
*******************************************************************************
Master hosts
------------
10.30.30.31: up
10.30.30.32: up
Slave hosts
------------
10.30.30.33: up
10.30.30.34: up
starting replication between 10.30.30.31 --> 10.30.30.32 (change master=1, reset master=1)
replication [started] 10.30.30.31 --> 10.30.30.32
starting replication between 10.30.30.32 --> 10.30.30.33 (change master=1, reset master=0)
replication [started] 10.30.30.32 --> 10.30.30.33
starting replication between 10.30.30.32 --> 10.30.30.34 (change master=1, reset master=0)
replication [started] 10.30.30.32 --> 10.30.30.34
master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
10.30.30.31 10.30.30.32 ok         binlog.000003:250 binlog.000003| 250| 250| 0
--- slaves follows ---
10.30.30.32 10.30.30.33 ok         binlog.000001:107 binlog.000001| 107| 107| 0
10.30.30.32 10.30.30.34 ok         binlog.000001:107 binlog.000001| 107| 107| 0

After this, ClusterControl will be installed. If you are using RPM based installation there can be rpm dependency problems (we install libmysqlclient.so.16), but it can conflict with what comes with your distribution. Please let us know if this happens, contact us on community@severalnines.com.

Starting/stopping Replication and basic examples

After having executed deploy.sh, you can view the status of the mysql servers:

cd s9s-mysql-55/mysql/scripts/
./status.sh  -a
Master hosts
------------
10.30.30.31: up
10.30.30.32: up
Slave hosts
------------
10.30.30.33: up
10.30.30.34: up

To view the replication status:

cd s9s-mysql-55/mysql/scripts/
./repl-status.sh -a
master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
10.30.30.31 10.30.30.32 ok         binlog.000003:250 binlog.000003| 250| 250| 0
--- slaves follows ---
10.30.30.32 10.30.30.33 ok         binlog.000001:107 binlog.000001| 107| 107| 0
10.30.30.32 10.30.30.34 ok         binlog.000001:107 binlog.000001| 107| 107| 0

To stop replication between 10.30.30.32 (relay master) to 10.0.30.33 you can do:

cd s9s-mysql-55/mysql/scripts/
./stop-repl.sh -m 10.30.30.32 -s 10.30.30.33
stopping replication between 10.30.30.32 --> 10.30.30.33
replication [stopped] 10.30.30.32 --> 10.30.30.33

To start replication again you can do:

cd s9s-mysql-55/mysql/scripts/
./start-repl.sh -m 10.30.30.32 -s 10.30.30.33
starting replication between 10.30.30.32 --> 10.30.30.33 (change master=0, reset master=0)
replication [started] 10.30.30.32 --> 10.30.30.33

Other options for 'start-repl.sh' is -r (for reset master, dangerous), -c (change master). Normally these options are not needed but you can use them in conjuction with -l (logpos) and -f (binlog file) if you want to start replication from a particular position.

And we can check the replication status:

ubuntu@ip-10-49-122-115:~/s9s-mysql-55/mysql/scripts$ ./repl-status.sh -a
serverid master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
1      10.48.207.130 10.48.139.24 ok         binlog.000005:494 binlog.000005| 494| 494| 0
2     replication not activated - you must start replication on this link.
--- slaves follows ---
3      10.48.139.24 10.49.122.56 ok         binlog.000005:485 binlog.000005| 485| 485| 0
4      10.48.139.24 10.49.110.183 ok         binlog.000005:485 binlog.000005| 485| 485| 0

Good luck and don't hesitate to contact us at feedback@severalnines.com or community@severalnines.com if you have any problems or whatever it may be. You can also book a demo this if you want to know more.

Severalnines releases ClusterControl™ for MySQL Replication v. 1.1.9

2011-10-04T08:58:00.005+01:00

Stockholm - October 4th 2011

Severalnines, provider of automation and management software for easily usable, highly available and auto-scalable cloud database platforms, today announces the latest release of its flagship product ClusterControl™ for MySQL Replication.

Introducing ClusterControl™ for MySQL Replication v.1.1.9

ClusterControl™ for MySQL Replication enables customers to Deploy, Manage, Monitor and Scale a clustered database platform based on the standard MySQL Replication.

Developers and DBAs now have access to all of the features of Severalnines' flagship product ClusterControl™ specifically adapted to MySQL Replication.

Designed to address issues and needs of MySQL users relying on MySQL Replication, ClusterControl™ for MySQL Replication offers a complete set of tools to assist developers and administrators of all skill levels to deploy, manage, monitor and scale their replicated MySQL databases.

Features and benefits

Config wizard
- Generates individual configurations based on application needs
- Creates deployment packages
- Deploys a Replication cluster in minutes
Config manager
- Sanity checks changes to configuration parameters
- Rolls out configuration changes to all servers and automates rolling restarts
Upgrade advisor
- Keeps users up-to-date by performing upgrades
- Applies minor patches or major upgrades
- Allows roll-back to previous versions
Availability manager
- Clusterware that detects process failures
- Automates MySQL master fail-over
- Performs recovery of failed database processes
Backup manager
- Schedule backups or allow users to start an immediate backup
- Monitor and browse existing backups
- Define retention policies
- No proprietary data formats
Monitor
- Granular end-to-end transaction and database operation monitoring
Query analyzer
- Analysis on cluster-wide running queries
- Global MySQL processlist
- Deep real-time and historical visibility into individual nodes
Alarms and notifications
Scale
- Add new Slaves (or remove) to the cluster without any downtime
- Easily specify hostnames of Slaves to be added

View all of the features here.

Automated fail-over with ClusterControl™

Standard MySQL Replication is easy to set up, and is probably the most widely used mechanism to provide high availability for MySQL. Unfortunately, it is somewhat fragile. There is no support for fail-over. Slaves can easily end up with different data from the master. Diverging datasets can cause replication to stop. Crashing masters can corrupt binary logs, which means replication is not possible any more.

ClusterControl™ addresses the problems associated with master fail-over and resynchronizations, and avoids data loss by taking advantage of semi-sync replication in MySQL 5.5.

ClusterControl™ automates fail-over and reconfiguration of the replication setup to keep the Replication cluster stable and running. This online tutorial explains the MySQL Replication architecture, and how different types of failures are handled.

Users can try it now by generating their deployment package.

Reources related to this release

Configurator for MySQL Replication
Tutorial: ClusterControl for MySQL Replication
Videos: Online Demos on MySQL Replication
Book a demo

Please do reach out to us with your feedback on Facebook, LinkedIn, XING or directly via these contact details for fruitful and interactive discussions on this latest release. For 'instant' communication, feel free to follow us on Twitter!

About Severalnines

Severalnines provides automation and management software for easily usable, highly available and auto-scalable cloud database platforms. ClusterControl™, the company’s flagship product, used by developers and administrators of all skill levels, addresses the full deploy-manage-monitor-scale cycle. Severalnines has enabled over 7,000 deployments to date via its popular online configurator for clustered MySQL databases.

To see who is using Severalnines today, please visit our references page.

MySQL Clusters on Amazon EC2 - verified AMIs

2011-08-23T14:30:00.011+01:00

We regularly receive questions from our user community with regards to which AMIs to use when deploying database clusters on Amazon EC2.

As part of our ongoing development work on the Severalnines Configurator and ClusterControl, we have recently done some testing on deploying MySQL Cluster on EC2 using Severalnines on three different AMIs. We thought we should share the results of these tests, hence the reason for this week's blog!

If you would like to test such a deployment yourself, feel free to use the parameters and guidelines below to do so. You can also check out these new videos to see Severalnines technology in action prior to getting started with your testing.

These are the three AMIs that we have tested the deployment on so as to have a good representative mix:

* RightImage_CentOS_5.4_x64_v5.5.9
* Amazon QuickStart AMI: amzn-ami-2011.02.1.x86_64-ebs
* Canonical AMI: ubuntu-maverick-5g-64bit

Of course, it would be great to hear from you on how your testing went and on what AMIs you are currently using yourself. Feel free to share your feedback with us on Facebook, Twitter, LinkedIn or leave a comment on this blog.

Here are the parameters that you can use to test your MySQL Cluster deployment using the Severalnines Configurator.

In all cases, the Internal IP was used as the Hostname in the Configurator and the following was used for all tests:

* Instance type: m1.larg
* Number of instances: 5 (1 for ClusterControl), 2 for data nodes, 2 for MySQL servers and management servers.

RightImage_CentOS_5.4_x64_v5.5.9 (ami-0f42a966)

* Cloud Provider [Configurator]: Amazon EC2
* OS user [Configurator]: root
* Operating System [Configurator]: Redhat/Fedora/Centos/Oracle
* Data directories [Configurator]: /mnt/data/mysqlcluster for data nodes and management servers and /mnt/data/mysql (for the mysql servers) both RPM and tar.gz installation was tested using 'deploy.sh'

Canonical AMI: ubuntu-maverick-5g-64bit (ami-ac41b7c5)

* Cloud Provider [Configurator]: Amazon EC2
* OS user [Configurator]: ubuntu
* Operating System [Configurator]: Ubuntu/Debian
* DataMemory [Configurator]: 512MB
Set it down in the Configurator - yes, it is tiny, but the default disk in this AMI is tiny, so you should add a 20GB EBS Volume if you want to test with the default suggested DataMemory
* Data directories [Configurator]: default suggested ones.
* Installation was tested using 'deploy.sh'

Amazon QuickStart AMI: amzn-ami-2011.02.1.x86_64-ebs (ami-8e1fece7)

* Cloud Provider [Configurator]: Amazon EC2
* OS user [Configurator]: ec2-user
* Operating System [Configurator]: Redhat/Fedora/Centos/Oracle
* DataMemory [Configurator]: 512MB
Set it down in the Confgurator. Yes, it is tiny, but the default disk in this AMI is tiny, so you should add a 20GB EBS Volume if you want to test with the default suggested DataMemory.
* Data directories [Configurator]: default suggested ones.
* .tar.gz installation using 'deploy.sh' no problems.
* RPM installation - dependency problems with libmysqlclient.so.16 vs libmysqlclient.so.14.
This can be fixed by editing the mysqlcluster-71-rpm/cluster/scripts/install/install-rpm.sh before running 'deploy.sh':
Change this, in the function named install_rpm_cmonmysql() :
MySQL-Cluster-gpl-shared-compat-${version}-1.${rhel}.${arch}.rpm
to
MySQL-Cluster-gpl-shared-${version}-1.${rhel}.${arch}.rpm
Now you can run ./deploy.sh

Finally, do not forget to set the EC2 Keypair in the Configurator and upload it to the ClusterControl server before running deploy.sh.

You should now be up and running with your MySQL Cluster on EC2; if you have any questions or comments, please do let us know. We welcome your feedback on Facebook, Twitter, LinkedIn or directly on this blog.

For more information on AMIs: http://aws.amazon.com/amis/
For more information on EC2: http://aws.amazon.com/ec2/

MySQL Clusters in the Cloud with Severalnines on OpenShift

2011-08-16T21:41:00.006+01:00

A blog post on how to deploy a sample JBoss application on OpenShift Flex - using a highly available, scalable backend that leverages MySQL Cluster

The Severalnines team has been busy during the summer months and as result, we have included OpenShift Flex support to our Severalnines Configurator (amongst other things). OpenShift, a Platform-as-a-Service operated by Red Hat, allows developers to develop and manage applications in the cloud. It is now possible to configure a clustered MySQL database for cloud services running on OpenShift. This brings high-availability and scalability at both the application and database layers.

As a guest contributor on Red Hat's OpenShift Community Blog, we provide a comprehensive, step-by-step walkthrough for this type of deployment. For those of you who would also like to view the process “in action”, feel free to view the video that complements this blog entry.

Read the full blog here!

Best Start-up: Severalnines - at EuroCloud Sweden Award

2011-05-26T14:53:00.005+01:00

Severalnines is proud to announce that it won the Best Startup prize at the EuroCloud Sweden Award in Stockholm this week. The company was chosen out of a number of promising startups targeting the emerging cloud computing market.

The jury noted that “Severalnines have a compelling idea to cloud-enable any customer-preferred database in mission-critical setups. Together with a simple-to-use configurator they put the user in control.”

We are quite honored and proud to receive the Best Startup award. Through our flagship product ClusterControl™, it is our objective to make complex database tasks easy and even enjoyable for our users, and above all, to make sure our users can realise the performance, high–availability and cost efficiencies that cloud computing promises.

Articles on the award can be found here:
EuroCloud Announcement (in english)
IDG article (in english)

Getting started with Cluster/J - index search and table scans

2011-04-13T23:23:00.005+01:00

This post follows the previous post, so set up the environment as described there.
In this example we will perform a full table scan (select * from my_data2) and an index scan (select * from my_data2 where userid=1).

The complete code is here from Severalnines.

Performing a full table scan (no search criteria)
Make sure you have records in the table my_data2.
To perform reads by primary key, we can just use Session::find(..), but here we will do a full table scan.

QueryBuilder qb = s.getQueryBuilder();

/*
* Run the query:
*/
List resultList = query.getResultList();
int count=0;

/*
* Loop over the results
*/

List resultList = query.getResultList();
int count=0;
for (MyData2 result: resultList) {
System.out.println(result.getFriendId());
count++;
}

That is it, if you run the example code, you will see the output.

Done reading X records in Y mssd

Performing a Index scan (search on part of the Primary Key)

The following snippet shows how to setup a EQUAL-filter:

/*
*  We want to compare userid=1 (select * from my_data2 where userid=2) 
*/
QueryBuilder qb = s.getQueryBuilder();
QueryDomainType dobj = qb.createQueryDefinition(MyData2.class);
/*
* Create a filter, we call this filter 'user_id_filter'
*/

PredicateOperand param = dobj.param("user_id_filter");
/*
* Set the column database column you want to filter on.
* Note: The setting dobj.get("userId") is really strange.
* In the table the column in the database is called userid, and in MyData2.
* The documentation of Cluster/J is very bad here, but
* the getter/setter are getUserId/setUserId, and you have to
* take the word after the get/set (in this case UserId) and change the first
* letter to lower case :(
*/
PredicateOperand column = dobj.get("userId");

/**
* Perform an equal compare
* we want to compare userid=1
*/

Predicate compare=column.equal(param);
dobj.where(compare);

Query query = s.createQuery(dobj);

/*
* Bind the search value to the filter
*/
query.setParameter("user_id_filter", new Long(1));

/*
* Run the query:
*/
List resultList = query.getResultList();
int count=0;

/*
* Loop over the results
*/
for (MyData2 result: resultList) {
System.out.println(result.getFriendId());
count++;
}

That is it for this time. I hope the clusterj documention will become better.

Getting started with Cluster/J - inserts (combined PK)

2011-04-13T22:54:00.003+01:00

This post follows the previous post, so set up the environment as described there.
In this example we will have a slightly different table with a combined PK.
The complete code is here.

Create the table

The first thing we should do is to create the table we need for this example:

CREATE TABLE `my_data2` (
`userid` bigint(20) NOT NULL DEFAULT '0',
`friendid` bigint(20) NOT NULL DEFAULT '0',
`data` varbinary(255) DEFAULT NULL,
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`userid`,`friendid`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (userid) */

Mapping table to Java Interface
An interfae describing each table is needed. You annotate the code with @PrimaryKey and @Column do denote if the names of columns and the column names.
The only difference here is that you need to annotate all primary key columns with @PrimaryKey.

import com.mysql.clusterj.annotation.Column;
import com.mysql.clusterj.annotation.Index;
import com.mysql.clusterj.annotation.PersistenceCapable;
import com.mysql.clusterj.annotation.PrimaryKey;

@PersistenceCapable(table="my_data2")
@Index(name="id")
public interface MyData2 {
   
      @PrimaryKey
      long getUserId();
      void setUserId(long i);
    
      @PrimaryKey
      long getFriendId();
      void setFriendId(long i);
   
      @Column(name = "data")
      byte[] getData();
      void setData(byte[] b);

      @Column(name = "last_updated")
      long getLastUpdated();
      void setLastUpdated(long ts);
}

Performing an insert

Populate the fields in the object, and make it persistent. Don't forget to set all primary key columns or you will get a ClusterJException.

MyData my_data=s.newInstance(MyData.class);
/**
* Set the data on the object
*/
my_data.setUserId(i);
my_data.setFriendId(i);
my_data.setData(data);
my_data.setLastUpdated(System.currentTimeMillis());
/**
* Persist the object */
s.makePersistent(my_data);

That is it, if you run the example code, you will see the output.

Done inserting 100 records in X ms

Getting started with Cluster/J - inserts

2011-04-13T18:53:00.005+01:00

Cluster/J is a the Java direct API to MySQL Cluster which means it bypasses the MySQL Server and executes requests directly on the data nodes of MySQL Cluster. This gives very good performance (typically >2x faster than SQL) and low latency.

In this blog I will present how to get started and how to do simple lookups.

The complete source code (actually a bit extended to show batching) for this example can be found at Severalnines (here). The code you will see below is just snippets.

Environment

To start with you need MySQL Cluster up and running.
For development on localhost then you can get a sandbox from Severalnines.
If you want to have a test/production environment you should you the Configurator for MySQL Cluster.

First of all you have to import the following Cluster/J jars into your project.
In Eclipse, on the project, do "Properties -> Java Build Path --> Add External JARs"
The JARs you need to import are typically located in /usr/local/mysql/share/java/ (if you have installed MySQL Cluster in this location - this is the default location used by Severalnines).

You also need to set the JVM configuration in order to point out the libndbclient library :

-Djava.library.path=/usr/local/mysql/lib/ -Xms128m -Xmx512m

Create the table

The first thing we should do is to create the table we need for this example:

CREATE TABLE `my_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data` varbinary(255) DEFAULT NULL,
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1

Mapping table to Java Interface
An interfae describing each table is needed. You annotate the code with @PrimaryKey and @Column do denote if the names of columns and the column names.

public interface MyData {     

@PrimaryKey     
long getId();
void setId(long i); 

@Column(name = "data")
byte[] getData();     
void setData(byte[] b);

@Column(name = "last_updated")
long getLastUpdated();
void setLastUpdated(long ts);
}

Setting up the connection to MySQL Cluster

Properties p = new Properties();
p.setProperty("com.mysql.clusterj.connectstring", "localhost:1186");
p.setProperty("com.mysql.clusterj.database", "test");
SessionFactory sf=ClusterJHelper.getSessionFactory(p);

Creating a Session

The SessionFactory provides Sessions. On a session instance you can do a couple of things, e.g, makePersistent, updatePersistent, and find.

/**
* Sessions are not thread safe!
*/
Session s = sf.getSession();

Performing an insert

Populate the fields in the object, and make it persistent.

MyData my_data=s.newInstance(MyData.class);
/**
* Set the data on the object
*/

my_data.setId(i);
my_data.setData(data);
my_data.setLastUpdated(System.currentTimeMillis());
/**
* Persist the object */
s.makePersistent(my_data);

That is it, if you run the example code, you will see the output.

Done inserting 1000 records in 230 ms

(UC2011) BOF - Setting up MySQL Cluster on EC2

2011-04-12T19:41:00.005+01:00

Severalnines will host a BOF at 7pm in Ballroom A, Tuesday 04/12.

After a succesful cluster tutorial we will demo how to use Severalnines Configurator to deploy MySQL Cluster on EC2 on X computers using one command (deploy.sh), complete with monitoring and management.

See you there!

Setting up Multi-master and read slaves using Severalnines

2011-04-11T14:30:00.010+01:00

Read the detailed replication tutorial instead.

This tutorial shows how you can use the Severalnines Configurator for MySQL Replication to deploy a Multi-master replication setup, and install ClusterControl.

When the deployment is finished you have a set of tools to manage and monitor replication, as well as to add new slaves, and to perform failover.
You can choose:

if you wish also setup bi-directional replication between the masters
the number of slaves you wish to connect to the master
whether you want to use MySQL 5.5.10 or Percona latest stable
Handlersocket (percona) or Semi-sync replication (MySQL 5.5)
cloud provider (on premise/EC2/Rackspace)

When deployed you can easily perform tasks as:

Add new slaves
Failover
View replication health information
Stop/start replication links
Stage slaves

tar xvfz s9s-mysql-55.tar.gz
cd s9s-mysql-55/mysql/scripts/install
./deploy.sh

The 'deploy.sh' script will create data directories, install initd scripts, create mysql users, apply database GRANTs (those you defined in the Configurator), and install ClusterControl.

Starting Replication and basic examples

After having executed deploy.sh should do:

cd s9s-mysql-55/mysql/scripts/
./list-servers.sh  -a
master     : 10.48.207.130 [  up]
master     : 10.48.139.24 [  up]
slave     : 10.49.122.56 [  up]
slave     : 10.49.110.183 [  up]

To verify the mysql servers are running and then:

cd s9s-mysql-55/mysql/scripts/
./start-repl.sh --master -i 1 -c

The command above will do a CHANGE MASTER (-c) and start replication from MySQL Server 1 to MySQL Server 2.

This step is not automated since you may want to start bi-directional replication between the masters. In that case you do:

./start-repl.sh --master -a -c

The slaves are then started with:

./start-repl.sh --slave -a -c

Which means, start all slaves (-a) and change master on those slaves.

Then you can run

./repl-status.sh  -a
serverid master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
1      10.48.207.130 10.48.139.24 ok         binlog.000005:107 binlog.000005| 107| 107| 0
2     replication not activated - you must start replication on this link.
--- slaves follows ---
3      10.48.139.24 10.49.122.56 ok         binlog.000005:107 binlog.000005| 107| 107| 0
4      10.48.139.24 10.49.110.183 ok         binlog.000005:107 binlog.000005| 107| 107| 0

We got two masters, and the master with server-id=1 replicates to server-id=2, and the master on server-id=2 replicates to the slaves (3 and 4).

Now we are ready to provision data

./mysql.sh -h 10.48.207.130 -ppassword

mysql> create table t1 (id integer auto_increment primary key, data varchar(255))engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1(data) values('hello');

And we can check the replication status:

ubuntu@ip-10-49-122-115:~/s9s-mysql-55/mysql/scripts$ ./repl-status.sh -a
serverid master_host -->slave_host status  master_status  slave_status [binlog|m_pos|exec_pos|lag]
1      10.48.207.130 10.48.139.24 ok         binlog.000005:494 binlog.000005| 494| 494| 0
2     replication not activated - you must start replication on this link.
--- slaves follows ---
3      10.48.139.24 10.49.122.56 ok         binlog.000005:485 binlog.000005| 485| 485| 0
4      10.48.139.24 10.49.110.183 ok         binlog.000005:485 binlog.000005| 485| 485| 0

Good luck and don't hesitate to contact us at feedback@severalnines.com if you have any problems or whatever it may be. You can also book a demo this if you want to know more.

Severalnines launch customer focused website

2011-04-08T00:36:00.001+01:00

I am glad to announce our brand new website today (www.severalnines.com).

The goal of the company is to make it easy for database developers to build their clustered database platforms, either on-premise or in the cloud.

We invite you to explore the new resources on the website (white papers, videos, new configurator for MySQL Replication, new cluster configurator with cloud options, etc.).

Users Conference 2011 - Cluster Tutorial

2011-03-23T07:19:00.003Z

I will be at the MySQL UC representing Severalnines AB. Together with my friends at SkySQL (Max and Joffrey) we will have have a joint tutorial on MySQL Cluster. You will learn MySQL Cluster and we will do hands-on examples and present best practices to make your deployment easier. This also provides an excellent opportunity to discuss your requirements and get input and feedback on your database design.

Also, if you are in the area and want to discuss MySQL Cluster, high availability, scalability issues, email me at johan@severalnines.com and we can organize a meeting.

Web and Telco mode Cluster Configuration

2011-02-28T09:06:00.005Z

The Core Scripts (aka Configurator) for MySQL Cluster has now been updated with a new feature that allows you to tune cluster for a particular workload.
Currently there are two types of workloads supported:

Web
Telco/Realtime

The Web workload is suitable for applications:

with long running transactions (scans, joins and/or large updates)
reading and especially writing of large chunks (BLOBs) of data where an "innodb" like behavior is wanted.

The Telco workload is suitable for realtime applications with:

requirements on response times
requirements on failover times
short but many parallel request primarily on PK

Buffers and timeouts are geared towards each workload. Some of it you can read more about here.

The Telco workload is also suitable for many kinds of applications in for example the following domains:

Finance,
Online gaming
Subscriber databases
Session management

If you don't know what is most suitable for you, please contact Severalnines and we help you.

Cluster/J - Document-oriented approach on MySQL Cluster

2011-02-25T16:06:00.001Z

In a project Severalnines is engaged in, we are developing a realtime application based on Cluster/J. To start with, I must say cluster/j is fantastic and so far I am very happy with it and beaten our expectations big time. It is quite new however and we stumbled on a couple of issues, but those were fixed very fast by the Cluster/J developers. The bugs we encountered were:

Both which were worked around, and really we never did need to have a binary or a varbinary as the PK, we used a

Performance is great - we have two data nodes (nehalem, 32GB RAM, 146GB SAS 10K disk, 2x4 core 2.4GHz (E5620) ) and two application hosts (same spec, less RAM as data nodes) and we easily push through 70-80K transactions per second (reads and writes). We could easily do more, if we added more applications on the application hosts. The data nodes are using about 50-60% of their capacity (CPU).

The graph (mysql cluster statistics) is at start quite spiky. As you can see transactions/operations goes up and down. This is because we first tested with 10M, then reloaded with 20M records, 40M, and finally 80M records and let it run overnight.

The requests are primary key based, and there is a PK followed by a VARBINARY (and some other meta fields). In the VARBINARY a tree structure is stored. The tree structure represents a parent-child relationship, and we encode it in JSON. So we are using something like a document-oriented model. By doing this we avoid complex queries to navigate a parent child structure and thereby have many round-trips between application and data nodes , and instead ask for one big packet in one network roundtrip. Writes in this model will be more costly since we write back the whole parent-child structure instead of just one of the nodes in this structure.

In the Java applications we also have a REST interface to the data.

SQL is used only for one thing and that is to create the tables.

CMON Enterprise is used for monitoring and management, and the Cluster configuration comes from Severalnines Core Scripts.

Finally, we made some fine-tuning on MySQL Cluster and got another 20% - this is not shown in the graph, and another story.

The only thing we are missing (or rather waiting for) now is the multi-connection functionality (ndb_cluster_connection pooling) in cluster/j.

CMON - Install Instructions

2011-02-17T19:40:00.007Z

CMON 1.1.4 - the Cluster Monitor and Manager has recently been released and here is a little how to about how to install from binary and source (at the end), deployment etc. If you use RPMs, you should follow the install guide here.

1. Download the cmon binary package to a monitoring host
Currently there are binaries available for Linux 64-bit and 32-bit (statically linked) and works for MySQL Cluster 7.0.9 and later 7.0.x versions and also 7.1.x.

In this case the monitoring host will be on 'ndb05' (IP address 10.0.1.5) - see 9. Suggested Deployment for a picture describing the setup.

The Monitoring machine should have installed:

a mysql server up and running that will store the cmon database - this mysql server does not need to be connected to MySQL Cluster. If you build from source you need to have the mysql cluster libraries and include files
apache (if you want to have the web interface) + php_mysql
rrdtool (if you want to have graphs in web interface)

sudo apt-get install rrdtool

The picture at the end of this post shows how you can deploy CMON.

> cd /usr/local
> wget http://www.severalnines.com/downloads/cmon/cmon-1.1.4-64bit-glibc23-mc70.tar.gz
> tar xvfz cmon-1.1.4-64bit-glibc23-mc70.tar.gz
> ln -s cmon-1.1.4-64bit-glibc23-mc70  cmon

2. Verify that you have a free API slot [mysqld] so that cmon can connect to MySQL Cluster

> ndb_mgm -e "show"
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @10.0.1.3  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)
id=4 @10.0.1.4  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1 @10.0.1.1  (mysql-5.1.39 ndb-7.0.9)
id=2 @10.0.1.2  (mysql-5.1.39 ndb-7.0.9)

[mysqld(API)] 6 node(s)
id=7 @10.0.1.1  (mysql-5.1.39 ndb-7.0.9)
id=8 @10.0.1.2  (mysql-5.1.39 ndb-7.0.9)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from 10.0.1.5)

Yes, I have two slots - if I run cmon from host 10.0.1.5, then it is fine (id=10), but in this case, I can connect cmon from any host (id=9). Point is that you must have a slot free with "any host" or from an explicit host.

You must also have MemReportFrequency=30 (or some other value, in seconds) in order to get the memory usage events sent from MySQL Cluster. Otherwize, CMON will not be able to present memory utilization information!

3. Run the cmon install script

> cd /usr/local/cmon/bin
> ./cmon_install.sh

This script will install cmon, rrd, init.d scripts, and generate SQL scripts for GRANTs
Distribution: fedora

**** MYSQL CONNECTION ****
CMON and the  RRD scripts needs a mysql connection to speak mysql server holding the cmon database.
Specify the BASEDIR where mysql is installed (default /usr/local/mysql/): /usr/local/mysql/mysql

Specify the hostname of the mysql server having the cmon database (default 'ndb05'): <return>
No hostname specified - using default 'ndb05'

Specify the port of the mysql server having the cmon database (default 3306): <return>
No port specified - using default 3306

Specify the password for the 'cmon' user (default no password): <return>
No password specified - using default (no password)

Specify the ndb-connectstring to the cluster - MUST BE COMMA SEPARATED (e.g, mgmdhost_A,mgmdhost_B):
: ndb01,ndb02
## Comment: ndb01,ndb02 are the hostnames of the two management
## servers in my cluster. YOU MUST SPECIFY THIS!

Specify cluster_id - must be unique amongst all monitored cluster (default is '1'):
<return>
Setting cluster_id = 1

Specify the name of and where to write the cmon log file. (default is /var/log/cmon.log): <return>
logfile is not specified - using default (/var/log/cmon.log)

****  WWW interface ****
The www files will be copied to /var/www/html/cmon/
## Comment: The installation scripts tries to find the default
## location for www used in your distribution
Specify the WWWROOT of your webserver (default /var/www/html):  <return>
Copying files to /var/www/html ..

Specify the owner of the WWWROOT/cmon (default user 'apache'): <return>
owner is not specified - using default user 'apache'

****  RRD ****
## Comment: If you don't have RRD installed then graphs will not be available
## from the web client, but there is no other functional impact!
Do you wish to install the rrd database  on this node (y/n)? : y
Specify the full path to 'rrdtool' (default is /usr/bin/): <return>
No path to rrd specified - using default /usr/bin/

The rrdtool stores data files in a data directory.
Specify the full path to the data directory (about 20MB free space will be neeeded).
RRD data directory (default is /var/lib/cmon/): <return>
No RRD data directory specified - using default /var/lib/cmon/
The datadir does not exists and will be created now!


**** INSTALL CRON SCRIPTS TO UPDATE/MANAGE/GRAPH RRD ****
cron schedules jobs every 5 minutes to update the rrd database and generarate graphs for the web interface.
You are recommended to install the cron jobs.
cron jobs will be installed in /etc/cron.d/cmon Do you want to install cron jobs for cmon (y/n)? : y
installed cron job in /etc/cron.d/cmon

**** INITD SCRIPTS ****
Do you want to install /etc/init.d/cmon (y/n)? : y
Specify the directory where CMON should write its pidfile (default /var/run/): <return>
Saving configuration to ../etc/init.d/cmon
Installing /etc/init.d/cmon
Done - Installed init.d scripts
Now you can start cmon with '/etc/init.d/cmon start'
Every change was written to /etc/cmon.cnf
You need to issue the following GRANTs before starting CMON:

GRANT super, replication client ON *.* TO 'cmon'@'ndb05';
GRANT select,update,insert,delete,create ON cmon.* TO 'cmon'@'ndb05';



## COMMENT: Connect a mysql client to the cmon database and do
## (actual GRANTs are subject to your particular settings):
mysql> GRANT super, replication client ON *.* TO 'cmon'@'ndb05';
mysql> GRANT select,update,insert,delete,create ON cmon.* TO 'cmon'@'ndb05';

4. Start CMON


## COMMENT: Make sure CMON database and Cluster is started (so initialization won't fail)
> /etc/init.d/cmon start
Starting cmon:  ok

The above actually means that the process started. It can still fail initializing the CMON database.
Check the cmon log:

>tail -f /var/log/cmon.log

Feb 17 21:22:28 : (INFO) Starting cmon version 1.1.4 with the following parameters:
--mysql-password=
--mysql-hostname=127.0.0.1
--mysql-port=3306
--ndb-connectstring=app01,app02
--cmon-port=50001
--cluster-id=1
--savetime-clusterlog=24 (hours)

Feb 17 21:22:28 : (INFO) If that doesn't look correct, kill cmon and restart with -? for help on the parameters, or change the params in /etc/init.d/cmon
Feb 17 21:22:28 : (INFO) You need to GRANT (and specify a password if you wish) the following on mysql on 127.0.0.1:
Feb 17 21:22:28 : (INFO) GRANT create,select,update,insert,delete on cmon.* to 'cmon'@'127.0.0.1';
Feb 17 21:22:28 : (INFO) GRANT super on *.* to 'cmon'@'127.0.0.1';
Feb 17 21:22:28 : (INFO) Also GRANT (if you have CMON and the MYSQLD on the same host):
Feb 17 21:22:28 : (INFO) GRANT create,select,update,insert,delete on cmon.* to 'cmon'@'127.0.0.1';
Feb 17 21:22:28 : (INFO) GRANT super on *.* to 'cmon'@'127.0.0.1';
Feb 17 21:22:28 : (INFO) and start cmon with --mysql-hostname=127.0.0.1
Feb 17 21:22:28 : (INFO) Testing connection to mysqld..
Feb 17 21:22:28 : (INFO) Trying to connect to management servers...
Feb 17 21:22:28 : (INFO) Connection ok..
Feb 17 21:22:28 : (INFO)
Feb 17 21:22:28 : (INFO) Please wait while cmon is starting up..
...
Feb 17 21:22:30 : (INFO) MonitorThread: Cluster STARTED   - all nodes are started  - running 1
Feb 17 21:22:30 : (INFO) MonitorThread: Number of Nodes = 12
Feb 17 21:22:30 : (INFO) MonitorThread: MGM node id = 1
Feb 17 21:22:30 : (INFO) MonitorThread: MGM node id = 2
Feb 17 21:22:30 : (INFO) MonitorThread: NDBD node 3 is STARTED
Feb 17 21:22:30 : (INFO) MonitorThread: NDBD node 4 is STARTED
...

## COMMENT: When you see it has picked up the nodes you have in your cluster, then
## the initialization is good.
## If this does not work, check GRANTs, connectstrings, and paths in /etc/cmon.cnf

5. Web interface

Requires that you have a webserver with php-mysql installed.
Simply connect your web browser to e.g 127.0.0.1:8080/cmon or 127.0.0.1/cmon

With the Web Interface it is easy to track performance problems and keep track on resource utilization problems, perhaps you have GC problems in you Java Application Servers (the picture actually show what happened with the transaction load when more worker threads were enabled)?

6. Access the Cluster status from SQL

## COMMENT: connect a mysql client to the mysql server holding the cmon database.
mysql> use cmon;

Database changed

mysql> show tables;
+-------------------------+
| Tables_in_cmon          |
+-------------------------+
| alarm                   |
| alarm_log               |
| backup                  |
| backup_log              |
| cluster                 |
| cluster_log             |
| cluster_state           |
| cluster_statistics      |
| configurator_nodemap    |
| diskdata                |
| email_notification      |
| mailserver              |
| memory_usage            |
| mysql_global_statistics |
| mysql_master_status     |
| mysql_server            |
| mysql_slave_status      |
| mysql_statistics        |
| mysql_variables         |
| node_state              |
| node_statistics         |
| restore                 |
| restore_log             |
| schema_object           |
+-------------------------+
24 rows in set (0.00 sec)

mysql> select status from cluster_state;
+---------+
| status  |
+---------+
| STARTED |
+---------+
1 row in set (0.00 sec)

mysql> select * from node_state;
+-----+--------+------------+-----------+-----------+---------------+---------+-------------+-----------------+---------------------+
| cid | nodeid | status     | node_type | nodegroup | host          | version | disconnects | last_disconnect | report_ts           |
+-----+--------+------------+-----------+-----------+---------------+---------+-------------+-----------------+---------------------+
|   1 |      1 | CONNECTED  | NDB_MGMD  |      NULL | 10.0.1.1      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      2 | CONNECTED  | NDB_MGMD  |      NULL | 10.0.1.2      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      3 | STARTED    | NDBD      |         0 | 10.0.1.3      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      4 | STARTED    | NDBD      |         0 | 10.0.1.4      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      7 | CONNECTED  | API       |      NULL | 10.0.1.1      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      8 | CONNECTED  | API       |      NULL | 10.0.1.2      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |     13 | NO_CONTACT | API       |      NULL | 0.0.0.0       | NULL    |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |     12 | NO_CONTACT | API       |      NULL | 0.0.0.0       | NULL    |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |     11 | NO_CONTACT | API       |      NULL | 0.0.0.0       | NULL    |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |     10 | NO_CONTACT | API       |      NULL | 0.0.0.0       | NULL    |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      9 | CONNECTED  | API       |      NULL | 10.0.1.5      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
+-----+--------+------------+-----------+-----------+---------------+---------+-------------+-----------------+---------------------+

7. Add MySQL Servers to be monitored

As MySQL Cluster is not able to distinguish between an API or a MySQL Server, we need to tell CMON where our mysql servers are!

> cd /usr/local/cmon/bin
> ./cmon_add_mysqld.sh -ndb01 -p3306
Using default config file path ../etc/cmon.conf
Added MySQL Server

You need to execute the following GRANTs on mysqld@ndb01 for CMON agent to be able to connect:

GRANT REPLICATION SLAVE, SUPER ON *.* TO 'cmon'@'ndb05';
GRANT SELECT ON mysql.* TO 'cmon'@'ndb05';

And so on, you need to add every mysql server you want to monitor.

8. Scripts

All information is stored in SQL tables so it is easy to write scripts around it. Here you can find some example scripts.

## COMMENT: Put the scripts in /usr/local/cmon/bin
> cd /usr/local/cmon/bin
> sh get_cluster_status.sh
STARTED

> sh get_node_status.sh -n 4
STARTED

9. Suggested Deployment
Recommendation: If you have the 'mysqld' on 10.10.1.5, connect it also to Cluster and you can use it for administration!

10. Building from source

If you prefer to build from source then you have to have:

MySQL Cluster binaries/libs/includes installed on the build machine
gcc/g++/make
mysql_config and ndb_config on the PATH

> which mysql_config
/usr/local/mysql/mysql/bin/mysql_config
> which ndb_config
/usr/local/mysql/mysql/bin/ndb_config

## COMMENT: If 'mysql_config' or 'ndb_config' is not on the PATH,
## then you must make sure it is!

> wget http://www.severalnines.com/downloads/cmon/cmon-1.0.2.tar.gz
> tar xvfz  cmon-1.0.2.tar.gz
> cd cmon-1.0.2
> ./configure --prefix=/usr/local/cmon/
## COMMENT: end the prefix with 'cmon' and life will be easier.
> make
> sudo make install
> cd /usr/local/cmon/
Proceed at section 2 above.

11. Bugs and problems

I would be very happy if you file bugs on lauchpad! If you don't want to file a bug report, you can send an email to 'support (at) severalnines (dot) com
Also, if you have suggestions for improvements, let me know!

Semi-sync replication (5.5)

2010-12-16T14:07:00.003Z

A bit unorthodox, I normally just write about MySQL Cluster here, but I just wanted to understand how much latency semi-sync replication adds.

The test was very simple:

two mysql servers, interconnected (same switch) on a 1 Gig-E network
one table (see below)
comparing insert performance (one thread) with 'no replication at all' and 'semi sync replication enabled'.
bencher (had to hack it a bit to make it work with vanilla mysql) running one thread, inserting 4B+128B+4B = 136B of data

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` varchar(255) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `ts` (`ts`)
) ENGINE=InnoDB

Test 1 - no replication

src/bencher -t1 -r 30   -q "insert into t1(b) values('012345678901234567890123456789001234567890123456789012345678900123456789012345678901234567890012345678901567890123456789001234567')" \
          -i "truncate table t1"  -o no_replication

Summary:
--------------------------
Average Throughput = 7451.26 tps (stdev=969.24)
Average Latency (us)=134.31 (stdev=2904.26)
95th Percentile (us)=110.00

(I haven't looked into why the stdev is so high, but it looks like some requests takes really long time, but this i have not studied why).

Test 2 - Semi-sync replication

src/bencher -t1 -r 30   -q "insert into t1(b) values('012345678901234567890123456789001234567890123456789012345678900123456789012345678901234567890012345678901567890123456789001234567')" \
          -i "truncate table t1"  -o no_replication

Summary:
--------------------------
Average Throughput = 2377.10 tps (stdev=210.42)
Average Latency (us)=421.46  (stdev=2644.61)
95th Percentile (us)=362.67

From this simpe test the increased latency when using semi sync replication is not surprising at all, since the semi-sync adds another network hop to the slave mysql server, and the difference in latency between 'no replication' and 'semi-sync' is very much expected network overhead, (similar factor as if you do a read in mysql cluster vs an write (which requires 2PC and network communication between the data nodes) ).

So the next step now is to compare this to synchronous replication (cluster), but this will be another time.

innodb config for the record

innodb_buffer_pool_size=2048M
innodb_log_file_size=256M
innodb_log_files_in_group=3
innodb_file_format=barracuda
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=2

DiskPageBufferMemory tuning and disk data statistics in MySQL Cluster 7.1.9

2010-11-03T14:00:00.010Z

From MySQL Cluster 7.1.9 (not yet released) it is possible to get better stats on disk data tables. In fact, the statistics makes it possible to tune the DiskPageBufferMemory parameter (similar to innodb_bufferpool), in order to avoid disk seeks. It is much (understatement) faster to fetch data from the DiskPageBufferMemory than disk.

Here is an example/tutorial how to use this information and how to check the hit ratio of the DiskPageBufferMemory. Next time, I will explain about other counters you can get from ndbinfo.diskpagebuffer.

Finally, no more educated guesswork is needed.

Let's take an example.

I have a table t1 with 650000 record

CREATE TABLE `t1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data1` varchar(512) DEFAULT NULL,
`data2` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster TABLESPACE ts_1 STORAGE DISK  ENGINE=ndbcluster

data1 and data2 is non-indexed columns so they will be placed on disk.
Then doing Random reads on this table with bencher:

 src/bencher -r 100000 -t 1 -q \
        "SET @x=FLOOR(1 + (RAND() * 650000));  SELECT * FROM t1 WHERE id=@x"

DiskPageBufferMemory=64M
The initial performance after 340 seconds into the run:

Thread 0 - 274 tps (average tps measured after 335 secs)
Thread 0 - 273 tps (average tps measured after 340 secs)

and the hit ratio:

mysql> SELECT
node_id,
100*(sum(page_requests_direct_return)/
(sum(page_requests_direct_return)+sum(page_requests_wait_io))) as hit_ratio_pct
FROM diskpagebuffer GROUP BY node_id;
+---------+---------------+
| node_id | hit_ratio_pct |
+---------+---------------+
|       3 |       93.3577 |
|       4 |       93.6565 |
+---------+---------------+

2 rows in set (0.01 sec

A hit ratio of ~93% - not so great.

So let's increase the DiskPageBufferMemory ( i am using the configurator scripts for this)

# change DiskPageBufferMemory to 512M in config.ini
vi ../config/config.ini
# do a rolling restart of the data nodes
./rolling-restart.sh --layer=storage
## the sit back and wait for the data nodes to be rolled..
hostx: (324s elapsed) Node 3: starting (Last completed phase 100) (mysql-5.1.51 ndb-7.1.9)
hostx: Data node 3 is STARTED
## then wait for node 4..
hosty: (98s elapsed) Node 4: starting (Last completed phase 4) (mysql-5.1.51 ndb-7.1.9)

Increasing DiskPageBufferMemory=512M
Of course, while doing the rolling restart, the same traffic is still happening on the cluster. Let's check what the hit_ratio is now (while restarting node 3).

695 seconds into the run we have:

mysql>  "see above"
+---------+---------------+
| node_id | hit_ratio_pct |
+---------+---------------+
|       4 |       91.2639 |
+---------+---------------+
1 row in set (0.00 sec)

and perf is...

Thread 0 - 200 tps (average tps measured after 690 secs)
Thread 0 - 200 tps (average tps measured after 695 secs)

not good at all.

After increase to DiskPageBufferMemory=512M
When the rolling restart has completed the ./rolling-restart.sh prints out:

Cluster: Cluster has performed a rolling restart

The DiskPageBufferMemory has now been changed to 512M.

Let's check what hit ratio we get:

mysql>  "see above"
+---------+---------------+
| node_id | hit_ratio_pct |
+---------+---------------+
|       3 |       98.2487 |
|       4 |       98.6386 |
+---------+---------------+
2 rows in set (0.01 sec)

Hit ratio is picking up and performance as well:

Thread 0 - 1141 tps (average tps measured after 1310 secs)
Thread 0 - 1149 tps (average tps measured after 1315 secs)

Now, we just have to wait for a while and let the system enter a steady state.
Then we can check back on the hit ratio and perhaps increase the DiskPageBufferMemory even more.

If you have 99.5 -- 99.9% hit ratio i would say you are good.

After 1780 seconds into the run we have:

+---------+---------------+
| node_id | hit_ratio_pct |
+---------+---------------+
|       3 |       99.2035 |
|       4 |       99.5251 |
+---------+---------------+
2 rows in set (0.00 sec)

and

Thread 0 - 1669 tps (average tps measured after 1775 secs)
Thread 0 - 1674 tps (average tps measured after 1780 secs)

... wow .. tuning the DiskPageBufferMemory really helped. And 3300 secs into the run, perf and hit ratio is still climbing:

Thread 0 - 2329 tps (average tps measured after 3305 secs)
Thread 0 - 2330 tps (average tps measured after 3310 secs

Good luck!

Pushed down JOINs - Webinar

2010-10-29T12:05:00.007+01:00

On Thursday, November 04, at 0900PST/1700CET/1600GMT there is a webinar about Pushed Down Joins. This webinar will explain how Pushed Down Joins works, and some performance numbers.
Register here: http://mysql.com/news-and-events/web-seminars/display-583.html.

NDB Pushed JOINs means query shipping instead of data shipping and it reduces drastically the network hops between the MySQL Server and data nodes, which in turn gives a tremendous performance improvement. For particular queries a 180x improvement has been measured.

MySQL Cluster - Performance (UPDATE on PK) - >120K tx/sec

2010-10-11T16:08:00.002+01:00

This post follows on the previous post on SELECT performance. In this post I want to show three things:

How many single row UPDATEs per second you can do on on a Cluster with two data nodes (updating 64B data by the PRIMARY KEY, no batching)
Show how MySQL Cluster scales with threads and mysql servers
How ndb_cluster_connection_pool affects performance

Next post will be what happens to INSERTs.

Setup

two data nodes
one to four mysql servers
interconnected with Gig-E (single NIC)

deployed on six computers (of varying quality, but not really modern, see below). www.severalnines.com/bencher was co-located with each mysql servers to drive the load. The inserts are non batched and looks like:

UPDATE t1 SET data1='64B of data' WHERE id=[random];

Table looks like:

CREATE TABLE `t1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data1` varchar(512) DEFAULT NULL,
`data2` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster

The configuration was generated using www.severalnines.com/config with the following options:

MySQL Cluster 7.1.7 (src)
Cluster Usage: Option 3) High read/High write
Cores: 8
Multi-connection: 16 (so that i later could easily change between 1,8,16 connections in the pool)
DataMemory=2500M

Hardware

Data nodes deployed on: 2x4 cores Intel(R) Xeon(R) CPU E5345 @ 2.33GHz
2 mysql servers deployed on: 2x2 cores with Intel(R) Xeon(R) CPU 5160 @ 3.00GHz
2 mysql servers deployed on: 2x4 cores with Intel(R) Xeon(R) CPU E5345 @ 2.33GHz

Results

A few notes:

conn= 8 means ndb_cluster_connection_pool=8
1 app means one pair of bencher + mysqld co-located one server
With identical HW (2x4 cores) for the mysql server 130K updates per second should be possible.
Data nodes are quite loaded at 4 applications (from 64 - 128 threads), and the TC peaks at 86% CPU util.
The network was not saturated (about 200Mb/s each for TX and RX between the data nodes)
Latency numbers - i have them if someone is interested.

How to improve this more:

More cores, faster cores
more mysql servers (to scale out more)
eventually more data nodes
inifiniband
use ndbapi (nosql)

MySQL Cluster - Performance (SELECT on PK)

2010-09-29T10:29:00.008+01:00

In this post I want to show three things:

How many single row SELECTs per second (on the PRIMARY KEY, no batching) you can do on on a Cluster with two data nodes
Show how MySQL Cluster scales with threads and mysql servers
How ndb_cluster_connection_pool affects performance

Next post will be what happens to INSERTs, and then UPDATEs.

Setup

two data nodes
one to four mysql servers
interconnected with Gig-E (single NIC)

deployed on six computers (of varying quality, see below). www.severalnines.com/bencher was co-located with each mysql servers to drive the load. The reads were a PK SELECT like:

SELECT data1,data2 FROM t1 WHERE id=[random];

data1 and data2 are each 256B, so in total 512B was read. There was 1M records in total in table t1, but this does not matter as the SELECT is on the PK (hash index) so it could have been 100B rows (if i have had the storage for it).

Table looks like:

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
 `data1` varchar(512) DEFAULT NULL,
`data2` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster

but only 256B of each 512B varchar was filled with data.

The configuration was generated using www.severalnines.com/config with the following options:

MySQL Cluster 7.1.7 (src)
Cluster Usage: Option 3) High read/High write
Cores: 8
Multi-connection: 16 (so that i later could easily change between 1,8,16 connections in the pool)
DataMemory=2500M

Hardware

Data nodes deployed on: Dual CPU Quad core Intel(R) Xeon(R) CPU E5345 @ 2.33GHz
2 mysql servers deployed on: Dual CPU Dual core with Intel(R) Xeon(R) CPU 5160 @ 3.00GHz
2 mysql servers deployed on: Dual CPU Quad core Intel(R) Xeon(R) CPU E5345 @ 2.33GHz

Results

A few notes:

conn= 8 means ndb_cluster_connection_pool=8
1 app means one pair of bencher + mysqld co-located one server
See below why 4 apps does not give >200K tx/s

Conclusions

Going from one mysql server to two mysql server gives a 99.9% throughput increase (32 threads).
At this stage I wanted to try with four mysql servers, as the two mysql servers were completely CPU bound at this stage.
Going from two mysql servers to four mysql servers gives a 66% throughput increase.

However, two of mysql servers were located on dual core, dual cpu, instead of dual cpu - quad core. The Dual CPU/Dual core servers were saturated already at 16 threads connected to MySQL.

I expect, with similar hardware to get well above 200000tx/s. The MySQL servers on the dual cpu/quad core machines could do 52000tx/s, but the dual cpu/dual core could only handle ~ 38000tx/s when executing the SELECT from 32 threads.

Hence, more than 200000tx/s should not be impossible, which would be a 92% increase in throughput compared to 2 mysql servers.
ndb_cluster_connection_pool=8 is a good value
ndb_cluster_connection_pool=1 is is really bad
ndb_cluster_connection_pool=16 does not give any real benefit, i can imagine it will be worse actually with more data nodes and more mysql servers b/c each data node has to read from more sockets. OTOH, epoll might help. Must be tested more :)
On my dual cpu/quad core machines the mysql server could scale up to 32 threads and then it hit the roof (100% CPU util including the benchmark program)
On my dual cpu/dual core machines the mysql server could scale up to16 threads and then it hit the roof (100% CPU util including the benchmark program)
CPU-bound -- the mysql servers got completely CPU bound with 32 threads
It would be fun to test with 16 or 24 core machines, but I don't have those. It would also be fun to test with faster CPUs.
The data nodes were not saturated on the peak results, but pay attention to the TC thread as in this case it accounts for most CPU util within the data node.
Latency numbers - i have them if someone is interested.

How to improve this more:

More cores, faster cores
more mysql servers (to scale out more)
eventually more data nodes
inifiniband
use ndbapi (nosql)

I think, despite MySQL Cluster being a relational database and despite we are using SQL, it is possible to get some decent numbers, and good scalability.

Cluster - spins/contentions and thread stuck in..

2010-09-02T14:35:00.002+01:00

I get a number of question about contentions/"stuck in..". So here comes some explanation to:

Contention
Thread Stuck in
What you can do about it

In 99% of the cases the contentions written out in the out file of the data nodes (ndb_X_out.log) is nothing to pay attention to.

sendbufferpool waiting for lock, contentions: 6000 spins: 489200
sendbufferpool waiting for lock, contentions: 6200 spins: 494721

Each spin is read from the L1 cache (4 cycles on a Nehalem (3.2GHz), so about a nanosecond).
1 spin = 1.25E-09 seconds (1.25ns)

In the above we have:
(494721-489200)/(6200-6000)= 27 spins/contention
Time spent on a contention=27 x 1.25E-09=3.375E-08 seconds (0.03375 us)

So we don't have a problem..

Another example (here is a lock guarding a job buffer (JBA = JobBuffer A, in short it handles signals for heartbeats and some other small things, all traffic goes over JobBuffer B).

jbalock thr: 1 waiting for lock, contentions: 145000 spins: 3280892543
jbalock thr: 1 waiting for lock, contentions: 150000 spins: 3403539479

(3403539479-3280892543)/(150000-145000)=24529 spins/contention
Time spent on a contention: 3.06613E-05 seconds (30.66us )

This is a bit higher than I would have expected and I think more analysis is needed. However, i tend not to get these contentions on a busy system.

Ndb kernel thread X is stuck in ..

Ndb kernel thread 4 is stuck in: Job Handling elapsed=100 Watchdog: User time: 82 System time: 667
Ndb kernel thread 4 is stuck in: Job Handling elapsed=200
Watchdog: User time: 82 System time: 668
Ndb kernel thread 4 is stuck in: Job Handling elapsed=300 Watchdog: User time: 82 System time: 669
Ndb kernel thread 4 is stuck in: Job Handling elapsed=400 Watchdog: User time: 82 System time: 670

Here the important is to look at how User time and System time behaves.
If User time is constant (as it is here - 82ms), but the System time is growing (667, 668 etc) which indicates that the OS kernel is busy.
Slow network? Sub-optimal kernel version? NIC drivers? swapping? some kernel process using too much cpu?

If User time is growing it is probably because the ndb kernel is overloaded.

What can you do about this?

In config.ini:
RealtimeScheduler=1
LockExecThreadToCPU=[cpuids]
check that cpuspeed is not running ( yum remove cpuspeed )
.. and finally ask us to optimize more!

Also, pay attention if you get the contentions on an idle Cluster or a busy Cluster.

MySQL Cluster - SPJ Preview - Feedback welcome

2010-04-27T11:55:00.003+01:00

SPJ (preview, not production ready) is a new feature allowing some types of JOINs to be pushed down and executed inside the data nodes! This allows for, in many cases, much faster JOIN execution.

Now we would love to get your feedback on this new feature:

Does what we have right now improve performance for you?
Are there other types of JOINs we should support in order to improve performance in you application (currently only eq_ref is supported)?
What application are you using?

There are some limitations currently:

node failure handling of SPJ is not complete, so if a data node crash, there are side-effects.
only eq_ref is supported - other JOINs are executed as normal.
Don't put this in production.

Obtaining the MySQL Cluster SPJ preview version:

You must know how to build MySQL Cluster from source
www.severalnines.com/config includes "7.1 SPJ Preview" (the scripts will compile and distribute the binaries for you) - just select "MySQL Cluster 7.1.3-SPJ" from the Cluster Version drop down.
The source code (if you don't use severalnines/config) can be found here.

How to test is:

Run a JOIN query:
mysql> set ndb_join_pushdown=0;
mysql> run join query
mysql> set ndb_join_pushdown=1; //ENABLES SPJ
mysql> run join query again
Did it help?
Run EXPLAIN on the queries (especially if SPJ did not help)

Feedback:

Send us the EXPLAINs of the queries where SPJ did not help!
or
Let us know if this feature was helpful for your application.
and in any case please include
What kind of speed up (or decrease) was observed
What kind of cluster configuration/hw was used.

Please send your feedback to us: spj-feedback (at) sun (dot) com . Thank you!

Tuning your Cluster with ndbinfo (7.1) part 1 of X

2010-04-20T00:06:00.003+01:00

The new ndbinfo interface in 7.1 is really useful to assist in tuning MySQL Cluster. Here is an example (more will follow):

I started with one test where I inserted two blobs (1KB + 1KB) in one table.
From 16 threads (colocated with one mysqld, two data nodes, separate computers) and one application driving the load I reached about 6960TPS, and the utilization of the redo buffers (controlled by the parameter RedoBuffer in config.ini) looked like:

mysql< select * from ndbinfo.logbuffers;
+---------+----------+--------+----------+----------+--------+
| node_id | log_type | log_id | log_part | total    | used   |
+---------+----------+--------+----------+----------+--------+
|       3 | REDO     |      0 |        1 | 50331648 | 196608 |
|       3 | REDO     |      0 |        2 | 50331648 | 294912 |
|       3 | REDO     |      0 |        3 | 50331648 | 131072 |
|       3 | REDO     |      0 |        4 | 50331648 | 229376 |
|       4 | REDO     |      0 |        1 | 50331648 | 229376 |
|       4 | REDO     |      0 |        2 | 50331648 | 262144 |
|       4 | REDO     |      0 |        3 | 50331648 | 163840 |
|       4 | REDO     |      0 |        4 | 50331648 | 229376 |
+---------+----------+--------+----------+----------+--------+
8 rows in set (0.01 sec)

Which is basically nothing.

I then increased the load and inserted 2 x 5120B BLOBs (from 16 threads one MySQL server), and run with an insert speed of 4320TPS:

mysql< select * from ndbinfo.logbuffers;
+---------+----------+--------+----------+----------+----------+
| node_id | log_type | log_id | log_part | total    | used     |
+---------+----------+--------+----------+----------+----------+
|       3 | REDO     |      0 |        1 | 50331648 | 11468800 |
|       3 | REDO     |      0 |        2 | 50331648 | 31522816 |
|       3 | REDO     |      0 |        3 | 50331648 | 42008576 |
|       3 | REDO     |      0 |        4 | 50331648 | 43057152 |
|       4 | REDO     |      0 |        1 | 50331648 | 14090240 |
|       4 | REDO     |      0 |        2 | 50331648 | 17432576 |
|       4 | REDO     |      0 |        3 | 50331648 | 10321920 |
|       4 | REDO     |      0 |        4 | 50331648 | 12615680 |
+---------+----------+--------+----------+----------+----------+

Above you can see that the redo buffers are used (the load will be spread around, and it is hard to catch a moment where the load is even on all buffers), and now the application started to throw the error "Got temporary error 1221 'REDO buffers overloaded (increase RedoBuffer)' from NDBCLUSTER (1297)"

I can now follow the instruction to increase the REDO buffer, but would it help in this case?
No, no and no.
The disk is too slow to keep up and cannot write out to disk in the same rate as the application writes out.

'iostat' gives:

< iostat -kx 1

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0        0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d1        0.00 27796.00    0.00 1454.00     0.00 115196.00   158.45    12.03    8.25   0.66  95.30
dm-0              0.00     0.00    0.00 29270.00     0.00 117080.00     8.00   274.79    9.33   0.03  95.20
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-3              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-4              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-5              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

And here you can see that the disks are quite utilized. This means that I have two options now if I want to be able to sustain the 4320TPS insert load:

Increase the number of data nodes (computers) so instead of having two computers, I should have four so that I spread the load across more hardware
Improve my disk subsystem (add better disks, e.g, to have 2-4 disk spindles to spread the load on), or by having the REDO log on device cciss/c0d1 and the the LCP on device cciss/c0d0.

The CPU, could that also been an bottleneck in this case? No, it was not the issue. The CMVMI thread (one of the data nodes threads) was spending 44.4% polling data from the other nodes, and it is reading in quite large packets so that is why it was the heaviest user of CPU of the data node threads.

5453 root      20   0 6594m 4.1g 6956 R 44.4 51.9   4:05.64 ndbmtd
5471 root      20   0 6594m 4.1g 6956 S 32.5 51.9   3:39.07 ndbmtd
5474 root      20   0 6594m 4.1g 6956 R 26.6 51.9   2:25.55 ndbmtd
5475 root      20   0 6594m 4.1g 6956 S 23.7 51.9   2:25.01 ndbmtd
5476 root      20   0 6594m 4.1g 6956 R 23.7 51.9   2:20.83 ndbmtd
5473 root      20   0 6594m 4.1g 6956 R 21.7 51.9   2:26.57 ndbmtd

MySQL Cluster - BLOB performance and other things

2010-04-07T11:24:00.007+01:00

At the UC 2010 I will have a session on MySQL Cluster Performance Tuning. This session will address a lot of the most common performance problems I see in my day to day job with Cluster. A small excerpt of the talk is below here and many other things will be addressed in the session (JOINs, schema optimization, batching, indexes, parameter tuning etc). I hope to see you there!

First a bit on ndb_autoincrement_prefetch_sz and then blobs!

ndb_autoincrement_prefetch_sz

One thing is contention on auto_increments, which can really slow down performance.
By default the ndb_autoincrement_prefetch_sz=1. This means that the mysqld will cache one auto_increment number and then go down to the data nodes to fetch the next number. It is better to let the mysqld cache more numbers, to avoid the unnecessary round-trip.

From MySQL Cluster 7.0.13 you can set it up to 65536 (previously max was 256)

With ndb_autoincrement_prefetch_sz=1024 the mysqld will cache 1024 numbers before fetching the next range of numbers from the data nodes.

Som numbers for inserting batches of 16 records from 8 concurrent threads on one mysqld:

ndb_autoincrement_prefetch_sz=1:                1211.91TPS
ndb_autoincrement_prefetch_sz=256:              3471.71TPS
ndb_autoincrement_prefetch_sz=1024:             3659.52TPS

Here we got an 3x improvement on INSERT performance. Nice!

This test is by no means trying to max out the cluster, far from it, just to illustrate how important it is to set the ndb_autoincrement_prefetch_sz.

BLOB/TEXT

Another is on BLOB/TEXT attributes - which in many cases are overused in applications (e.g, there is no reason to store an 'email' as a TEXT, or if the data is less than about 8000B).

If you can change them to VARBINARY/VARCHAR (as LinuxJedi suggests), do it:

BLOBs/TEXTs are significantly slower compared to VARBINARY/VARCHAR (because the BLOBs are stored in a separate table, and need to be locked with at least a shared lock when accessed).

CREATE TABLE `t1_blob` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data1` blob,
`data2` blob,
PRIMARY KEY (`id`)
)ENGINE=ndbcluster

and inserted 200K records. sizeof(data1) = 1024B, sizeof(data2) = 1024B.

SELECT data1, data2 FROM t1 WHERE id=<rand>

Executed from 1 App - 8 Threads on one MySQLD and two data nodes resulted in:

data1 and data2 represented as BLOBs : 5844 TPS
data1 and data2 represented as VARBINARYs: 19206 TPS

Again, this is not maxing out the data nodes, just to show you the idea.

Conclusion
If you can replace TEXT/BLOB with VARCHAR/VARBINARY (remember that the max record size in Cluster is 8052B) do it.

UC2010 - MySQL Cluster Deploy and Perf Tuning BP

2010-03-25T09:01:00.007Z

At the MySQL UC 2010 (on Tuesday 4/14 11:55 am) me and my colleague Joffrey Michaie will present MySQL Cluster - Deployment Best Practices. We will give talk about what is important to think about when deploying MySQL Cluster, what to do, what not to do, operational aspects and a few other practical things.
This session will be a great follow-on to the introductory session on MySQL Cluster (and the tutorial).

After the Deployment session, same day at 2:00pm, I will also have a session on MySQL Cluster Performance Tuning Best Practices. In this session you will learn tricks and tips how to tune your Cluster, e.g how to tune and design your schema and queries so it runs optimally on MySQL Cluster.

So if you want to see what happens after this slide you should come and join us!

MySQL Cluster Configurator v3

2010-02-03T09:28:00.004Z

Version 3.0 of the severalnines/Configurator has been released:

Improved scripts (a lot of cleanup), prefixing the script output with the hostname:
E.g,:
Cluster: Cluster Start Cluster: STARTING MANAGEMENT SERVERS ps-ndb01: Starting management server (nodeid=1) ps-ndb01: Copying ../config/config.ini to /etc/mysql ps-ndb01: Started management server (nodeid=1, pid=28253) ...
Reduced number of scripts
start-cluster-initial.sh --> start-cluster.sh --initial
rolling-restart-initial.sh --> rollling-restart.sh --initialstart-ndbd-<host>-<id>-initial.sh --> start-ndbd-<host>-<id>.sh --initial
Better error handling during rolling restarts
Scripts are checking if Node Failure handling is ready before starting nodes
start-ndbd-<host>-<id>.sh - new parameters [--initial] [--nowait] [--ignore]
--initial - initial node start (clear out local ndb_fs
--nowait - script will exit as soon as data node is starting
--ignore - ignore waiting for Node Failure handling to complete
Supports MySQL Cluster 7.1.1

Here is an example of how to try out 7.1.1 using the Configurator

1. Generate a Configuration (make sure you select "MySQL Cluster 7.1.1")

Currently 7.1.1 is only in source format so you need to have ncurses-devel/gcc/g++/make installed

2. Unpack the tarball you get and start the installation!
You are recommended to set up shared ssh keys between the "Frontend" (where you run the scripts) to the other nodes in the cluster in order to avoid typing passwords all the time.

tar xvfz mysqlcluster-71.tar.gz
cd mysqlcluster-71
cd cluster/scripts/install/
./shared-ssh-keys.sh ###WARNING - This script is experimental, let me know if it works!
./download-and-compile.sh
./install-cluster.sh
./bootstrap.sh
cd ..
./start-cluster.sh --initial

Voila!

MySQL Cluster 7.1.1 (beta) - what's in there

2010-02-03T09:09:00.004Z

The main new features in MySQL Cluster 7.1.1 (beta) is the following:

ndbinfo (aka ndb$info) - Finally!! System tables for MySQL Cluster describing live usage of a lot of resources (RedoBuffer, Redo Log space, counters etc etc). Looks promising - makes Cluster a lot more transparent. See examples below.
Cluster/J - for more information and to learn more about it - register to a webinar and meet the architect and developer of Cluster/J!

Severalnines/Configurator supports 7.1.1 - to try it out!

NDBINFO - examples

Ever wondered how much for the RedoBuffer you are actually using?

mysql>  SELECT * FROM ndbinfo.logbuffers;
+---------+----------+--ç------+----------+----------+--------+
|       3 | REDO     |      0 |        1 | 33554432 | 229376 |
|       3 | REDO     |      0 |        2 | 33554432 | 229376 |
|       3 | REDO     |      0 |        3 | 33554432 |  98304 |
|       3 | REDO     |      0 |        4 | 33554432 | 229376 |
|       4 | REDO     |      0 |        1 | 33554432 | 262144 |
|       4 | REDO     |      0 |        2 | 33554432 |  65536 |
|       4 | REDO     |      0 |        3 | 33554432 |  98304 |
|       4 | REDO     |      0 |        4 | 33554432 | 262144 |
+---------+----------+--------+----------+----------+--------+

And how about the Redo log space?

mysql>  SELECT * FROM ndbinfo.logspaces;
+---------+----------+--------+----------+------------+-----------+
| node_id | log_type | log_id | log_part | total      | used      |
+---------+----------+--------+----------+------------+-----------+
|       3 | REDO     |      0 |        0 | 3221225472 | 200278016 |
|       3 | REDO     |      0 |        0 | 3221225472 | 201326592 |
|       3 | REDO     |      0 |        0 | 3221225472 | 202375168 |
|       3 | REDO     |      0 |        0 | 3221225472 | 202375168 |
|       4 | REDO     |      0 |        0 | 3221225472 | 201326592 |
|       4 | REDO     |      0 |        0 | 3221225472 | 201326592 |
|       4 | REDO     |      0 |        0 | 3221225472 | 202375168 |
|       4 | REDO     |      0 |        0 | 3221225472 | 202375168 |
+---------+----------+--------+----------+------------+-----------+
8 rows in set (0.00 sec)

Statistics ...

mysql> SELECT * FROM ndbinfo.counters;
+---------+------------+----------------+------------+--------------+-----------+
| node_id | block_name | block_instance | counter_id | counter_name | val       |
+---------+------------+----------------+------------+--------------+-----------+
|       3 | DBLQH      |              1 |         10 | OPERATIONS   |   1747971 |
|       3 | DBLQH      |              2 |         10 | OPERATIONS   |   1748870 |
|       3 | DBLQH      |              3 |         10 | OPERATIONS   |   1749056 |
|       3 | DBLQH      |              4 |         10 | OPERATIONS   |   1777925 |
|       4 | DBLQH      |              1 |         10 | OPERATIONS   |   1747976 |
|       4 | DBLQH      |              2 |         10 | OPERATIONS   |   1749008 |
|       4 | DBLQH      |              3 |         10 | OPERATIONS   |   1749033 |
|       4 | DBLQH      |              4 |         10 | OPERATIONS   |   1777928 |
|       3 | DBTC       |              0 |          1 | ATTRINFO     | 470827014 |
|       3 | DBTC       |              0 |          2 | TRANSACTIONS |    245150 |
|       3 | DBTC       |              0 |          3 | COMMITS      |    245137 |
|       3 | DBTC       |              0 |          4 | READS        |        25 |
|       3 | DBTC       |              0 |          5 | SIMPLE_READS |         0 |
|       3 | DBTC       |              0 |          6 | WRITES       |   3512472 |
|       3 | DBTC       |              0 |          7 | ABORTS       |        13 |
|       3 | DBTC       |              0 |          8 | TABLE_SCANS  |        29 |
|       3 | DBTC       |              0 |          9 | RANGE_SCANS  |         0 |
|       4 | DBTC       |              0 |          1 | ATTRINFO     | 472683577 |
|       4 | DBTC       |              0 |          2 | TRANSACTIONS |    218857 |
|       4 | DBTC       |              0 |          3 | COMMITS      |    218834 |
|       4 | DBTC       |              0 |          4 | READS        |        10 |
|       4 | DBTC       |              0 |          5 | SIMPLE_READS |         1 |
|       4 | DBTC       |              0 |          6 | WRITES       |   3501562 |
|       4 | DBTC       |              0 |          7 | ABORTS       |        22 |
|       4 | DBTC       |              0 |          8 | TABLE_SCANS  |         3 |
|       4 | DBTC       |              0 |          9 | RANGE_SCANS  |         0 |
+---------+------------+----------------+------------+--------------+-----------+
26 rows in set (0.12 sec)

And there is a lot more!

mysql> show tables;
+-------------------+
| Tables_in_ndbinfo |
+-------------------+
| blocks            |
| config_params     |
| counters          |
| logbuffers        |
| logspaces         |
| memoryusage       |
| nodes             |
| pools             |
| resources         |
| transporters      |
+-------------------+
10 rows in set (0.01 sec)

Cluster Performance Tuning Webinar - EMEA

2010-02-01T16:27:00.003Z

The 2nd of February, 0900GMT/1000CET I will have a webinar on Cluster Performance Tuning for people located in EMEA timezone.

For more information and registration:

http://mysql.com/news-and-events/web-seminars/display-480.html

In addition to what is mentioned in the Agenda I will also present numbers on typical operations, how to design requests, explain how the optimizer works with MySQL Cluster etc etc.

See you there!

-johan

Gearman meets MySQL Cluster (NDBAPI)

2010-01-20T19:20:00.004Z

After a discussion with my colleague Stephane Varoqui we decided to see how Gearman and the NDBAPI could be used together. The result of the POC was a Gearman worker and a couple of clients (clients and workers use Google Protocol Buffers as the protocol). The worker can:

set/get/delete records on a single table in MySQL Cluster using the primary key
set/get/delete "any" type. It is not possible to dynamically add types but this is done at compile time.
supports the following SQL data types: (UNSIGNED) INTEGER, (UNSIGNED) BIGINT, CHAR, VARCHAR/VARBINARY
supports the following Google Protocol Buffer scalars: int32, uint32, int64, uint64, string, bytes.
not handle much errors for the time being

and a client that can

create a message and send it to the Gearman Job Server
clients ca n be written in either C++, Java, or Python (subject to what languages that Google Protocol Buffers supports)
receive (deserialize) the data.

So basically this is a new, albeit simple, connector to MySQL Cluster! Hopefully someone will find it useful.

The code can be downloaded here and some short instructions are here, and if you guys out there thinks this is usable, then it might make it to launchpad. Let me know!

Here follows some information what has been done and how to use this.

First you have to create the relation tables (engine=ndb). I will use a 'Person' (for the rest of the examples) that I will persist to the database. I have created the following relational table:

CREATE TABLE `Person` (
`id` int(11) NOT NULL,
`name` varchar(128) DEFAULT NULL,
`message` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

The relational tables needs to be translated to Google Protocol Buffers:

> cat proto/Person.proto
message Person {
required int32 id = 1;  //PRIMARY KEY attributes are 'required'
optional string name = 2;
optional string message = 3;
}

All columns in the relational table must exist in the protocol buffer definition.

There is union-proto buffer called NdbMessage.proto that then contains all the proto buffers that can be sent between the client and worker:

> cat proto/NdbMessage.proto
// import the relevant protos that can be in
// the NdbMessage
import "Person.proto";

message NdbMessage {
enum Type { Person=1;}
// Identifies which field is filled in.
required Type type = 1;
// list of possible protos comes here:
optional Person person = 2;
}

The proto files then needs to be run through the protocol buffer compiler:

> /usr/local/bin/protoc --proto_path=proto --cpp_out=`pwd` proto/Person.proto proto/NdbMessage.proto
# this generates .h and .cc files for Proto buffer files.

There are three clients for this, one for each operation (set,get,delete).

In the ndbapi_set_client.cpp (the clients are based on the reverse_client.cpp in gearman) we invoke the 'ndbapi_set' function, that will be executed by the worker:


#include "NdbMessage.pb.h" 
#include "Person.pb.h"
.
/**instantiate a NdbMessage object and associate a Person to it*/
NdbMessage m;
m.set_type(NdbMessage_Type_Person);
Person *  p=m.mutable_person();
/* I must set all fields for now */
p->set_id(1);
p->set_name("Johan Andersson");
p->set_message("hello world, my first insert");

string s;
m.SerializeToString(&s);

...

const char * data = s.data();
result= (char*)gearman_client_do(&client,
"ndbapi_set",
NULL,
(void *)data,
(size_t)p.ByteSize(),
&result_size,
&ret);

The worker (ndbapi_worker.cpp) registers three functions:

ret=gearman_worker_add_function(&worker, "ndbapi_get", 0, ndbapi_get,NULL);
ret=gearman_worker_add_function(&worker, "ndbapi_set", 0, ndbapi_set,NULL);
ret=gearman_worker_add_function(&worker, "ndbapi_delete", 0, ndbapi_delete,NULL);

And when the worker receives the function call to 'ndbapi_set' it has to deserialize the received message into a NdbMessage:

static void *ndbapi_set(gearman_job_st *job,
 void *context,
 size_t *result_size,
gearman_return_t *ret_ptr)
{
/** receive message and convert into c++ string
* construct the wanted object (dataObject) from parsing c++ string.
*/
  const void * rawmessage;
  rawmessage= gearman_job_workload(job);
  string s((char*)rawmessage,  gearman_job_workload_size(job));

  NdbMessage dataObject;
  if(! dataObject.ParseFromString(s))
    {
      *ret_ptr= GEARMAN_WORK_FAIL;
      return NULL;
    }

The worker then looks at the type of the message and gets the underlying object (in this case Person):


google::protobuf::Message  * message;
switch(dataObject.type())
{
case NdbMessage_Type_Person:
{
message= (google::protobuf::Message*)&dataObject.person();
reflection = (google::protobuf::Reflection *)message->GetReflection();
descriptor = (google::protobuf::Descriptor*)message->GetDescriptor();
}
break;
/*
case NdbMessage_Type_MyType:
{
// the myType() ..  is the name of the field in MyType.proto:
// MyType myType = ;
message= (google::protobuf::Message*)&dataObject.myType();
reflection = (google::protobuf::Reflection *)message->GetReflection();
descriptor = (google::protobuf::Descriptor*)message->GetDescriptor();
}
break;
*/
default:
cout << "unknown type: "<< ret_ptr=" GEARMAN_WORK_FAIL;"> the insert was successful */
*result_size=0;
*ret_ptr= GEARMAN_SUCCESS;
return NULL;
}

In order to add a new type, you need to add a new 'case' to handle the type and how to get that object from the NdbMessage object (dataObject).

The worker loops over all fields in the received Proto Message and creates a transaction in the NDBAPI and executes it. Thus this part agnostic to the type you give it. As long as the following is true:

The relational table only uses (UNSIGNED) INTEGER, (UNSIGNED) BIGINT, CHAR, VARCHAR/VARBINARY data types.
The .proto definition contains all columns in the relational table
The .proto file marks the PRIMARY KEY of the relational table as 'required'
For 'ndbapi_set' you need to set all columns in the table ( i will fix that as soon as possible)

The data is then persisted in the table (currently the worker expects all tables to be stored in the 'test' database):

mysql> select * from Person;
+----+-----------------+------------------------------+
| id | name            | message                      |
+----+-----------------+------------------------------+
|  1 | Johan Andersson | hello world, my first insert |
+----+-----------------+------------------------------+
1 row in set (0.00 sec)

Now the worker can also handle 'get' requests, and by using the get_client:

> ./get_client  1
name: Johan Andersson
message: hello world, my first insert

And there is also a client that does deletes (delete_client):

> ./delete_client  1
Delete successful: id=1

Summary/Conclusions

What are the performance implications of using Proto Buffer's reflection mechanism?
Proto Buffer only works for C++, Java, and Python - currently no support for PHP.
Is it better to use something else than Proto Buffers for this?
Gearman was super-easy to install so thanks for that!
Google Protocol Buffers was super-easy to install so thanks for that!
The worker needs also to be extended to support range searches and to make use of the batching interface so that it is possible persist either many types or many instances of a type in a batch.
NO-SQL -- YES-NDB !

CMON - Install Instructions

2010-01-14T21:45:00.003Z

CMON - the Cluster Monitor has recently been released and here is a little how to about how to install from binary and source (at the end), deployment etc.

1. Download the cmon binary package to a monitoring host
Currently there are binaries available for Linux 64-bit and 32-bit (statically linked) and works for MySQL Cluster 7.0.9 and later.

In this case the monitoring host will be on 'ndb05' (IP address 10.0.1.5) - see 9. Suggested Deployment for a picture describing the setup.

The Monitoring machine should have installed:

a mysql server up and running that will store the cmon database (if you build from source you need to have the mysql cluster libraries and include files)
apache (if you want to have the web interface) + php_mysql
rrdtool (if you want to have graphs in web interface)

The picture at the end of this post shows how you can deploy CMON.

> cd /usr/local
> wget http://www.severalnines.com/downloads/cmon/cmon-1.0.2-64bit-glibc23-mysqlcluster-709.tar.gz
> tar xvfz cmon-1.0.2-64bit-glibc23-mysqlcluster-709.tar.gz
> ln -s cmon-1.0.2-64bit-glibc23-mysqlcluster-709 cmon

2. Verify that you have a free API slot [mysqld] so that cmon can connect to MySQL Cluster

> ndb_mgm -e "show"
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @10.0.1.3  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)
id=4 @10.0.1.4  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1 @10.0.1.1  (mysql-5.1.39 ndb-7.0.9)
id=2 @10.0.1.2  (mysql-5.1.39 ndb-7.0.9)

[mysqld(API)] 6 node(s)
id=7 @10.0.1.1  (mysql-5.1.39 ndb-7.0.9)
id=8 @10.0.1.2  (mysql-5.1.39 ndb-7.0.9)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from 10.0.1.5)

> cd /usr/local/cmon/bin
> ./cmon_install.sh

This script will install cmon, rrd, init.d scripts, and generate SQL scripts for GRANTs
Distribution: fedora

**** MYSQL CONNECTION ****
CMON and the  RRD scripts needs a mysql connection to speak mysql server holding the cmon database.
Specify the BASEDIR where mysql is installed (default /usr/local/mysql/): /usr/local/mysql/mysql

Specify the hostname of the mysql server having the cmon database (default 'ndb05'): <return>
No hostname specified - using default 'ndb05'

Specify the port of the mysql server having the cmon database (default 3306): <return>
No port specified - using default 3306

Specify the password for the 'cmon' user (default no password): <return>
No password specified - using default (no password)

Specify the ndb-connectstring to the cluster (e.g, host_A;host_B): ndb01;ndb02
## Comment: ndb01;ndb02 are the hostnames of the two management
## servers in my cluster. YOU MUST SPECIFY THIS!

****  WWW interface ****
The www files will be copied to /var/www/html/cmon/
## Comment: The installation scripts tries to find the default
## location for www used in your distribution
Specify the WWWROOT of your webserver (default /var/www/html):  <return>
Copying files to /var/www/html ..

****  RRD ****
## Comment: If you don't have RRD installed then graphs will not be available
## from the web client, but there is no other functional impact!
Specify the full path to 'rrdtool' (default is /usr/bin/): <return>
No path to rrd specified - using default /usr/bin/

The rrdtool stores data files in a data directory.
Specify the full path to the data directory (about 20MB free space will be neeeded).
RRD data directory (default is /data/rrd/): <return>
No RRD data directory specified - using default /data/rrd/
Saving ../etc/cmon.conf

**** INITD SCRIPTS ****
Do you want to install /etc/init.d/cmon (y/n)? : y
Specify the directory where CMON should write its pidfile (default /var/run/): <return>
chkconfig
Saving configuration to ../etc/init.d/cmon
Installing /etc/init.d/cmon
Done - Installed init.d scripts
Now you can start cmon with '/etc/init.d/cmon start'

**** INSTALL CRONTAB ****
cron schedules jobs every 5 minutes to update the rrd database and generarate graphs for the web interface. You are recommended to install the cron jobs
Do you want to install cron jobs for cmon (y/n)? : y
You need to issue the following GRANTs before starting CMON:

GRANT super, replication client ON *.* TO 'cmon'@'ndb05';
GRANT select,update,insert,delete,create ON cmon.* TO 'cmon'@'ndb05';


Configuration now complete - you can edit the /usr/local/cmon/bin/../etc/cmon.conf manually if you wish.

Configuration is now complete, but you need to apply the suggest GRANTs to the cmon database:

## COMMENT: Connect a mysql client to the cmon database and do
## (actual GRANTs are subject to your particular settings):
mysql> GRANT super, replication client ON *.* TO 'cmon'@'ndb05';
mysql> GRANT select,update,insert,delete,create ON cmon.* TO 'cmon'@'ndb05';

4. Start CMON


## COMMENT: Make sure CMON database and Cluster is started (so initialization won't fail)
> /etc/init.d/cmon start
Starting cmon version 1.0.0 with the following parameters:
--mysqlpasswd=
--mysqlhost=ndb05
--mysqlport=3306
--ndb-connectstring=ndb01;ndb02
--savetime-clusterlog=48 (hours)

If that doesn't look correct, kill cmon and restart with -? for help on the parameters, or change the params in /etc/init.d/cmon

You need to GRANT (and specify a password if you wish) the following on mysql on ndb05:

GRANT create,select,update,insert,delete on cmon.* to 'cmon'@'ndb05';
GRANT super on *.* to 'cmon'@'ndb05';

Testing connection to mysqld..
Connection ok..
Please wait while cmon is starting up..
Recreating missing tables
Done
Registering managed cluster with cluster id=1
Managed cluster has been registered - registered cluster id=1
cmon has started successfully.
Going to daemoinze.. - cmon will write a log in syslog from now
start on service executed successfully

5. Access the Cluster status from SQL

## COMMENT: connect a mysql client to the mysql server holding the cmon database.
mysql> use cmon;

Database changed

mysql> show tables;
+-------------------------+
| Tables_in_cmon          |
+-------------------------+
| alarm                   |
| alarm_log               |
| backup                  |
| backup_log              |
| cluster                 |
| cluster_log             |
| cluster_state           |
| cluster_statistics      |
| configurator_nodemap    |
| diskdata                |
| email_notification      |
| mailserver              |
| memory_usage            |
| mysql_global_statistics |
| mysql_master_status     |
| mysql_server            |
| mysql_slave_status      |
| mysql_statistics        |
| mysql_variables         |
| node_state              |
| node_statistics         |
| restore                 |
| restore_log             |
| schema_object           |
+-------------------------+
24 rows in set (0.00 sec)

mysql> select status from cluster_state;
+---------+
| status  |
+---------+
| STARTED |
+---------+
1 row in set (0.00 sec)

mysql> select * from node_state;
+-----+--------+------------+-----------+-----------+---------------+---------+-------------+-----------------+---------------------+
| cid | nodeid | status     | node_type | nodegroup | host          | version | disconnects | last_disconnect | report_ts           |
+-----+--------+------------+-----------+-----------+---------------+---------+-------------+-----------------+---------------------+
|   1 |      1 | CONNECTED  | NDB_MGMD  |      NULL | 10.0.1.1      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      2 | CONNECTED  | NDB_MGMD  |      NULL | 10.0.1.2      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      3 | STARTED    | NDBD      |         0 | 10.0.1.3      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      4 | STARTED    | NDBD      |         0 | 10.0.1.4      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      7 | CONNECTED  | API       |      NULL | 10.0.1.1      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      8 | CONNECTED  | API       |      NULL | 10.0.1.2      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |     13 | NO_CONTACT | API       |      NULL | 0.0.0.0       | NULL    |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |     12 | NO_CONTACT | API       |      NULL | 0.0.0.0       | NULL    |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |     11 | NO_CONTACT | API       |      NULL | 0.0.0.0       | NULL    |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |     10 | NO_CONTACT | API       |      NULL | 0.0.0.0       | NULL    |           0 | NULL            | 2010-01-13 12:00:56 |
|   1 |      9 | CONNECTED  | API       |      NULL | 10.0.1.5      | 7.0.9   |           0 | NULL            | 2010-01-13 12:00:56 |
+-----+--------+------------+-----------+-----------+---------------+---------+-------------+-----------------+---------------------+

6. Web interface

Requires that you have a webserver with php-mysql installed.
Simply connect your web browser to e.g 127.0.0.1:8080/cmon

7. Add MySQL Servers to be monitored

As MySQL Cluster is not able to distinguish between an API or a MySQL Server, we need to tell CMON where our mysql servers are!

> cd /usr/local/cmon/bin
> ./cmon_add_mysqld.sh -ndb01 -p3306
Using default config file path ../etc/cmon.conf
Added MySQL Server

You need to execute the following GRANTs on mysqld@ndb01 for CMON agent to be able to connect:

GRANT REPLICATION SLAVE, SUPER ON *.* TO 'cmon'@'ndb05';
GRANT SELECT ON mysql.* TO 'cmon'@'ndb05';

## COMMENT: Put the scripts in /usr/local/cmon/bin
> cd /usr/local/cmon/bin
> sh get_cluster_status.sh
STARTED

> sh get_node_status.sh -n 4
STARTED

MySQL Cluster binaries/libs/includes installed on the build machine
gcc/g++/make
mysql_config and ndb_config on the PATH

> which mysql_config
/usr/local/mysql/mysql/bin/mysql_config
> which ndb_config
/usr/local/mysql/mysql/bin/ndb_config

## COMMENT: If 'mysql_config' or 'ndb_config' is not on the PATH,
## then you must make sure it is!

> wget http://www.severalnines.com/downloads/cmon/cmon-1.0.2.tar.gz
> tar xvfz  cmon-1.0.2.tar.gz
> cd cmon-1.0.2
> ./configure --prefix=/usr/local/cmon/
## COMMENT: end the prefix with 'cmon' and life will be easier.
> make
> sudo make install
> cd /usr/local/cmon/
Proceed at section 2 above.

Cluster Monitoring

2009-12-31T13:20:00.004Z

A new version of CMON - the monitor for MySQL Cluster - has been released. Many thanks to everyone who helped me with bug fixing and beta testing!

CMON monitors all events and information that you can get from MySQL Cluster.

Some of the things that CMON monitors:

Cluster status (starting, started, stopped, recovering, degraded etc)
Data node status (started, stopped, starting etc)
Application (mysqld) node status (connected/disconnected)
DataMemory/IndexMemory/Tablespace utilization
Statistics from data nodes
Statistics from mysql servers
Replication (basic currenty) support -picks up if a mysql server is a slave and checks if is running or not
Alarm generation

There are pre-built binaries for Linux 64-bit (I have not got around yet to do debian and rpm packages), as well as source code. It has been tested in Fedora 9 , Ubuntu 9.04, Centos 4/5, but I have not had the possibility to test on Redhat/SLES/Debian yet, so it would be interesting to know if there are any issues with it there. There are also installation instructions and a deployment guide (PDF).

CMON uses the Management API to collect information from MySQL Cluster and puts this information in a MySQL database, so it can be queried using SQL. This makes it very easy to write scripts around it and there are some sample scripts on the CMON web page.

# Get the status of Cluster for cluster with clusterid=1:
root@centos4-1 bin]# ./get_cluster_status.sh --clusterid=1
STARTED

# Get the list of node ids for type NDBD so that we can iterate over them easily.
[root@centos4-1 bin]# ./get_node_ids.sh --clusterid=1 -t NDBD
3 4

# Get the status of the node 3
[root@centos4-1 bin]# ./get_node_status.sh  --clusterid=1 --nodeid=3
STARTED

If you want you can also view the Cluster from a web interface (requires PHP), and you can get graphs using RRD.

Installation is very simple, just run the cmon_install.sh script that is part of the package which will ask you a number of questions and write a configuration file.

Node failure handling - take 2

2009-11-27T12:42:00.002Z

Jonas improved the node failure handling in MySQL Cluster 7.0.9 (and 6.3.29) so here comes a re-run of the a previous blog post. And the node failure handling time has improved a lot - see below.

I created in total 11345 tables, each with 128 columns, and then hit this bug.

When all tables were created, I stopped one node and measured how long time it takes for the other node to perform the node failure handling. Here is what was written into the cluster log (look at the bold lines):


2009-11-27 13:39:21 [MgmtSrvr] ALERT    -- Node 4: Node 3 Disconnected

2009-11-27 13:39:21 [MgmtSrvr] ALERT    -- Node 4: Network partitioning - arbitration required

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: President restarts arbitration thread [state=7]

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: Communication to Node 3 closed

2009-11-27 13:39:21 [MgmtSrvr] ALERT    -- Node 1: Node 3 Disconnected

2009-11-27 13:39:21 [MgmtSrvr] ALERT    -- Node 4: Arbitration won - positive reply from node 1

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: GCP Take over started

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: Node 4 taking over as DICT master

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: GCP Take over completed

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: kk: 9670/4 0 0

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: LCP Take over started

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: ParticipatingDIH = 0000000000000010

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: ParticipatingLQH = 0000000000000010

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: m_LCP_COMPLETE_REP_Counter_DIH = [SignalCounter: m_count=0 0000000000000000]

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: m_LCP_COMPLETE_REP_Counter_LQH = [SignalCounter: m_count=1 0000000000000010]

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: m_LAST_LCP_FRAG_ORD = [SignalCounter: m_count=0 0000000000000000]

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: m_LCP_COMPLETE_REP_From_Master_Received = 0

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: LCP Take over completed (state = 5)

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: ParticipatingDIH = 0000000000000010

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: ParticipatingLQH = 0000000000000010

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: m_LCP_COMPLETE_REP_Counter_DIH = [SignalCounter: m_count=1 0000000000000010]

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: m_LCP_COMPLETE_REP_Counter_LQH = [SignalCounter: m_count=1 0000000000000010]

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: m_LAST_LCP_FRAG_ORD = [SignalCounter: m_count=1 0000000000000010]

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 4: m_LCP_COMPLETE_REP_From_Master_Received = 0

2009-11-27 13:39:21 [MgmtSrvr] INFO     -- Node 3: Node shutdown completed.

2009-11-27 13:39:21 [MgmtSrvr] ALERT    -- Node 1: Node 3 Disconnected

2009-11-27 13:39:22 [MgmtSrvr] INFO     -- Node 4: Started arbitrator node 1 [ticket=2a74000295047d39]

2009-11-27 13:40:20 [MgmtSrvr] WARNING  -- Node 4: Failure handling of node 3 has not completed in 1 min. - state = 6

2009-11-27 13:41:03 [MgmtSrvr] INFO     -- Node 4: Communication to Node 3 opened

Now, the node failure handling was completed in 1 minute 42 seconds! This is quite some improvement from the 17 minutes (although measured with 16000 tables and I will comeback when the bug mentioned above is fixed) measured before Jonas made his magic.

What does it mean? It means that we can start to recover a failed data nodes much earlier now!

Scaling - going from 2 to X data nodes

2009-11-26T10:18:00.003Z

When moving from two data nodes to a bigger Cluster it is not necessarily true that you will have better performance. In fact you can get worse.

Here are some things to think about:

Database Load (traffic to Cluster) - if you can handle query load X on a two node cluster and move the same load X to a four data node cluster you will likely get new_load_X=0.8X, i.e., a performance degradation. This has to do with 1) buffers are not filled up fast enough so the data nodes will do "timeout" based sending or 2) that the access patterns aren't scaling. To correct 1) you need to increase the load on the cluster so that internal communication buffers fill up faster.

Access pattern related "problems":

For primary key operations (reads, updates, deletes) you will always go to the correct node to fetch the data with PK operations, no matter how many nodes you have. So no problems with these types of ops.
For index scans on a key you will always scan all data nodes. This can be expensive if the number of records you retrieve is "small" (about <=64 records), since setting up the index scan on every data node is expensive. Sending the actual data is relatively cheap. This can be the cause of performance degradation when moving from 2 data nodes to bigger cluster. This is not so much to do about, unless you can redesign your schema for most important requests to fit the index scans on part of PK way of accessing data.
For index scans on part of PK there are two options - index scan all data nodes or index scan only one data node. This can also be the cause of performance degradation when moving from 2 data nodes to bigger cluster.

Example with index scans on part of PK:

CREATE TABLE t1 (
userid integer,
serviceid integer,
data varbinary(512),
INDEX (serviceid),
PRIMARY KEY(userid, serviceid)
);

This table is by default partitioned on the primary key (every table in cluster is by default partitioned on the primary key). This means in a four data node cluster that you can have your user spread out on 4 different partitions.
You may have this data

<userid,serviceid,>
<1,1,aaa>
<1,2,aaa>
<1,3,aaa>
<1,4,aaa>

The record <1,1,aaa> may be on one data node. <1,2,aaa> may be on another and so on. It is random.

If you now do a SELECT * FROM t1 WHERE userid=<value>
then every data node will be scanned for data, the result then merged in the NDBAPI (mysqld).

Good or bad? Depends on - if your user has >64 services (CPU and network dependent), then it is better to scan all data nodes.

If your user has <=64 services then it is better to involve only one data node.

You can then create the table as follows:

CREATE TABLE t1 (
userid integer,
serviceid integer,
data varbinary(512),
INDEX (serviceid),
PRIMARY KEY(userid, serviceid)
) PARTITION BY KEY (userid);

- You may have still have this data:

<userid,serviceid,>
<1,1,aaa>
<1,2,aaa>
<1,3,aaa>
<1,4,aaa>

All records with userid=1 is now located in one data node (one partition) no matter how many data nodes you have!

Now, the primary key is still <userid,> but the data is put on the partitions by looking at <userid> only! This ensures that a user will be on only one partition, irrespectively how many services the user has. If you query on <userid> then only one partition will be involved in the search, since for a particular <userid> in the table t1, all data for that user is in only one partition.

If you now do a SELECT * FROM t1 WHERE userid=<value> then only one data node will be scanned.

For completeness of the example: SELECT * FROM t1 WHERE serviceid=<value> will cause all data nodes to perform an index scan on the index (serviceid), since the data is not partitioned by serviceid, and not the leftmost column in the index (the indexed columns are used left to right).

The main point is that the fixed cost of setting up an ordered index scan is expensive, but then to retrieve the records is cheap. So if you return many rows, read from all data nodes.

If you later on plan to increase the number of data nodes from 4 to 8 nodes. Then using PARTITION BY KEY when possible makes your upgrades (more data nodes) easier and less susceptible to performance decreases.

This is how it looks on my system (as you can see partitioned searches (using PARTITION BY KEY) is faster up to ~ 64 records).

MySQL Cluster 7.0.9b in Configurator and Sandbox

2009-11-26T07:57:00.003Z

Configurator and Sandbox scripts are updated to use 7.0.9b.

You are recommended to upgrade!

Upgrade can be performend using these scripts:
Binary distribution - Upgrade script from 7.0.* -> 7.0.9b is here.
Source distribution - Upgrade script from 7.0.* -> 7.0.9b is here.

Copy the scripts to "install/"
chmod u+x upgrade-script.sh
./upgrade-script.sh

After step 3) you need to run either:

download-binary.sh
download-and-compile.sh

and then

rolling-restart.sh

Cluster Performance Tuning

2009-10-23T17:27:00.003+01:00

The 3rd of November I will have a webinar on Cluster Performance Tuning.

For more information:

http://www.mysql.com/news-and-events/web-seminars/display-453.html

In addition to what is mentioned in the Agenda I will also present numbers on typical operations, how to design requests, explain how the optimizer works with MySQL Cluster etc etc.

See you there!

-johan

MySQL Cluster 7.0.8a and 6.3.27a released (source)

2009-10-07T08:13:00.003+01:00

Fixes regression - bug 47844.

Configurator and Sandbox scripts are updated to use this version.

If you build from source then you can upgrade using these scripts:
Upgrade script from 7.0.7 -> 7.0.8a is here.
Upgrade script from 7.0.8 -> 7.0.8a is here.
Upgrade script from 6.3.26-> 6.3.27a is here.

Follow the instructions in this blog post!

MySQL Cluster 7.0.8/6.3.27 - serious regression!!!

2009-10-06T14:52:00.002+01:00

A regression in MySQL Cluster 7.0.8 and 6.3.27 has caused them to be removed from download.

Problem:
If you have a mysql server with higher node id than 49, then it will cause cluster to crash.

An updated version of MySQL Cluster 7.0.8, called 7.0.8a will be released very soon.

The Configurator has been reverted back to use 7.0.7 and 6.3.26.

Configuring for large databases in MySQL Cluster

2009-10-02T10:21:00.007+01:00

If you need to create a big database into MySQL Cluster with:

A lot of tables indexes, columns, and tables
A lot of records

there are a few things to think about:

If a table has > ~90M records, you have to create the table with MAX_ROWS=<amount of records in table anticipating growth>:
CREATE TABLE t1(...) ENGINE=ndbcluster MAX_ROWS=200000000;
This way the data node will allocate more partitions for the table, since there is a limitation in how many records that can be stored in one partition (and the limit is around 90M records).
Many tables / table objects --> Make sure you increase MaxNoOfTables (kernel limit is 20320 tables). This creates a table object pool of size MaxNoOfTables.
- Each table you create will use one table object.
- Each unique index you create will use one table object
- Each ordered index you create will use one table object
- Each BLOB/TEXT attribute will use one table object.
For example if you create the following table:
```
CREATE TABLE `t1` (
`a` int(11) NOT NULL DEFAULT '0',
`b` char(32) DEFAULT NULL,
`c` blob,
`d` bigint(20) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`),
KEY `d` (`d`)
) ENGINE=ndbcluster;
```
The table will require table objects as follows:
- PRIMARY KEY (there is an ordered index on the PK): 1 table objects
- UNIQUE KEY (unique hash index + ordered index): 1 + 1 table objects
- BLOB: 1 table object
- Actual Table: 1 table object
- IN TOTAL = 5 table objects
If you had specified PRIMARY KEY USING HASH, and UNIQUE KEY USING HASH, then you can save two table objects, one for each ordered index.

Many UNIQUE indexes --> Increase MaxNoOfUniqueHashIndexes
Many ordered indexes --> Increase MaxNoOfOrderedIndexes
Many attributes - -> Increase MaxNoOfAttributes (should be sum of number of columns all tables and add 3-10% for spare).

Also you would probably want to increase:

DataMemory
IndexMemory

If you have a lot of BLOB/TEXT columns or have many client apps/users connecting to one MySQL Server:

SendBufferMemory=8M
ReceiveBufferMemory=8M

If you get "error 306" then you should increase

StringMemory=25 to StringMemory=50
IndexMemory=

(Note with 7.0.8 i can create 16000+ tables with 128 columns in each with StringMemory=25).

This page will be updated if I find other things.

If you still have problems - let us/me know, e.g, on the cluster mailing list!

The Configurator has these parameters specified already in the config.ini so it is a small thing to change (just run the ./rolling-restart.sh script).

Upgrade to 7.0.8 (with the Configurator)

2009-09-30T15:06:00.002+01:00

MySQL Cluster 7.0.8 was released as a source distribution 30th of Sept 2009. You should upgrade if you can build from source or use the Configurator.

See below how to upgrade when using the Configurator.

7.0.8 contains a number of fixes in almost every area and you are recommended to upgrade if you experience problems with earlier version(s). Especially if you have encountered the "error 306" problem.

The Configurator v2.9 has been updated to use this version.

If you are already using the Configurator and build from source you can upgrade from MySQL Cluster 7.0.7 to 7.0.8 (and you are recommended to upgrade because of the following changes/fixes) in four steps. Here is how:

1. run the upgrade-707-to-708-src.sh script (put it in install/ and chmod u+x ./upgrade-707-to-708-src.sh):

[cluster01]# pwd
/root/mysqlcluster-70-master/cluster/scripts/install
[cluster01]# chmod u+x ./upgrade-707-to-708-src.sh
[cluster01]# ./upgrade-707-to-708-src.sh
Upgrading scripts
done - now run ./download-and-compile.sh

2. run the script download-and-compile.sh

[cluster01]# ./download-and-compile.sh
After some time it will finish compiling:

mysql-7.0.8-linux-x86_64/sql-bench/test-alter-table
mysql-7.0.8-linux-x86_64.tar.gz created
Removing temporary directory
Downloaded binary distribution to ../../repo

(the 'downloaded binary' should really read 'Copied binary').

3. When you get prompted with the following question answer 'y' (yes):

Do you want to install the binaries on the hosts now
(else run 'install-cluster.sh' later)? (y/n):y

Important! Do not run the the bootstrap.sh script!!
4. change directory to the scripts/ to directory and run the ./rolling-restart.sh script

[cluster01]# pwd
/root/mysqlcluster-70-master/cluster/scripts/
[cluster01]# ./rolling-restart.sh

This in an online procedure so it requires no downtime of the Cluster.
If you are using binary distributions then you have to wait, because they are not ready yet.
Good luck!

Ps - Configurator 2.9 (released 2nd of September 2009) contains some minor fixes:

Adding of [mysqld] slot for administration purposes
./start-backup now backup the configuration files (config.ini, my.cnf and some internal files) as well.

Node failure handling

2009-09-16T13:29:00.003+01:00

When a data node fails, the other data nodes must perform clean-up of the failed node.

E.g, to abort transactions in the PREPARE phase (Cluster uses a variant of the Two Phase Commit protocol) that has been started on the Data node that fails.
Various protocols (if the failed data node is the master) must also fail-over.

These things are carried out quite swiftly (configurable, but a couple of seconds by default but it also depends on the size of transactions that in the prepare phase that needs to be aborted).

I wanted to check how long time it takes to do node failure handling with many tables (16000+) in the cluster, because in a "normal" database with a couple of hundred tables, then the time spent on node failure handling is not a factor.

In this case I have two data nodes:

Node 3 (master)
Node 4

I have created 16000+ tables each with 128 columns just to see what happens with failure handling and recovery.

Node 3 was killed, and the Node 4 then has to do node failure handling:

2009-09-16 14:23:41 [MgmSrvr] WARNING  -- Node 4: Failure handling of node 3 has not completed in 15 min. - state = 6

2009-09-16 14:24:41 [MgmSrvr] WARNING  -- Node 4: Failure handling of node 3 has not completed in 16 min. - state = 6

2009-09-16 14:25:41 [MgmSrvr] WARNING  -- Node 4: Failure handling of node 3 has not completed in 17 min. - state = 6

2009-09-16 14:26:13 [MgmSrvr] INFO     -- Node 4: Communication to Node 3 opened

Eventually, we get the message :

2009-09-16 14:26:13 [MgmSrvr] INFO     -- Node 4: Communication to Node 3 opened

We cannot start to recover Node 3 until this message has been written out in the cluster log!

If you try to connect the data node before this, then the management server will say something like "No free slot found for node 3", which basically means in this case that the node failure handling is ongoing.

Now we have got the message and we can now start data node 3 again and it will recover

Why is it taking long time:

The surviving data nodes that are handling the failures must write for each table that node three has crashed. This is written to disk. This is what takes time when you have many tables (about 17 minutes with >16000 tables) so about 1000 tables per minute.. So in most databases it does not take that much time. Thanks Jonas for you explanation.

However, it is important for you to understand that during this time it is STILL POSSIBLE to WRITE / READ to the Cluster, i.e., the database is online.

MySQL Cluster on two hosts - options and implications

2009-09-03T15:25:00.004+01:00

Considering that you have the following hosts and MySQL Cluster deployed in the following ways:

A: ndb_mgmd, ndbd , mysqld
B: ndb_mgmd, ndbd , mysqld

Now you have a couple of options on how to handle potential network partitioning/split brain which could occur if the computer A and B lose contact with each other. Let's see the implications of these options.

Option 1: ArbitrationRank=1, no NIC bonding

If ndb_mgmd on A is elected as the arbitrator and host A crashes, then data node on host B will also die (cannot reach arbitrator).
If ndb_mgmd on A is elected as the arbitrator and host B lose contact with host A, then the data node on B will shut down since it can't reach the arbitrator. For further discussions, call this situation X.
If you are in situation X and if you restart data node on B and you have not fixed the link), then it will start isolated (after some timeouts have kicked in, StartPartionedTimeout and StartPartialTimeout). Now you are in situation Y.
If you had an application writing data (and assume this application crashed when the connection from B -> A was lost) then the following can happen if you are in situation Y
If you do "select count(*) from t1" on mysqld host A, then it can differ from a "select count(*) from t1" on mysqld on host B, because the data node on B never got the message to flush the redo buffer to the redo log.
Now you have ended up in a very bad situation. Kill data node on host B. Fix network, restart data node with --initial.
However, this could be combined with STONITH to kill off one of the data nodes.

Option 2: ArbitrationRank=0, no NIC bonding

no arbitrators are used - no protection from split brain.
if host A lose contact with host B, then the data nodes will be split
Application can write to A and B, but e.g A will _never_ see the changes from B, and vice versa
The data nodes will drift apart and be out of sync (inconsistent). Very bad.
When the link comes up again the data nodes A and B will not reconnect to each other.
System administrator must kill one of the data nodes and restart it with --initial.

Option 3: ArbitrationRank=0, NIC bonding + redundant switch.

no arbitrators are used - no protection from split brain.
Same problems as Option 2) if a) both network links are broken or b) both switches crashes. However, the likelihood of this happening is very small.

Option 4: Aribtration=WaitExternal (new in MySQL Cluster 7.0.7)

External arbitrator is used: You, a sys admin, a dba, or a process.
When the data nodes lose contact with each other, they are read-only for ArbitrationDelay before unlocking (and becoming writable).
This means that you have ArbitrationDelay (configurable) time to decide which data node that should live and who should die.
The data node that you killed can then be restarted with "ndbd", i.e, without --initial.
"You" in this case can be an external program, sysadmin etc..
If you use an external program must run on a third computer.

Option 5: ArbitrationRank=1 and have ndb_mgmd on remote site

If you use asynchronous replication between SITE_A and SITE_B then it could be possible to have the management server there with ArbitrationRank=1, and then potentially local management servers on host A and B that only has configuration data (i.e, ArbitrationRank=0 for those).
This must be tested to see if the link between SITE_A and SITE_B is "good enough". The link should also be reliable.

Option 6: ArbitrationRank=1 and have a third host (called C)

This is the best and recommended approach (and best if even have a computer D that is like C for redundancy matters).
Run the management server on host C. Setup would look like:
A: ndbd + mysqld
B: ndbd + mysqld
C: ndb_mgmd (AribtrationRank=1) + mysqld

And the winner is...

What is the "best" option:

Option 6) - get a third computer - simple
Option 3), Option 4) - "you do it, but be careful"
Option 5) - must be tested
Option 1) (atleast the cluster will crash avoiding inconsistencies)
Option 2)

I am arguing with myself whether or not Option 1) should be on either 2nd or 3rd place, atleast it is simple and easy to understand.

Of course redundant NICs (bonding) and redundant switches can be used in all of the options above, even it is not explicitly said so for some of them.

Upgrade to 7.0.7 (with the Configurator)

2009-09-03T11:30:00.000+01:00

MySQL Cluster 7.0.7 was released as a source distribution 1st of Sept 2009. You should upgrade if you can build from source or use the Configurator.

The Configurator v2.9 has been updated to use this version.

If you are already using the Configurator and build from source you can upgrade from MySQL Cluster 7.0.6 to 7.0.7 (and you are recommended to upgrade because of the following changes/fixes) in four steps. Here is how:

1. run the upgrade-706-to-707-src.sh script (put it in install/ and chmod u+x ./upgrade-706-to-707-src.sh):

[cluster01]# pwd
/root/mysqlcluster-70-master/cluster/scripts/install
[cluster01]# chmod u+x ./upgrade-706-to-707-src.sh
[cluster01]# ./upgrade-706-to-707-src.sh
Upgrading scripts
done - now run ./download-and-compile.sh

2. run the script download-and-compile.sh

[cluster01]# ./download-and-compile.sh
After some time it will finish compiling:

mysql-7.0.7-linux-x86_64/sql-bench/test-alter-table
mysql-7.0.7-linux-x86_64.tar.gz created
Removing temporary directory
Downloaded binary distribution to ../../repo

(the 'downloaded binary' should really read 'Copied binary').

3. When you get prompted with the following question answer 'y' (yes):

Do you want to install the binaries on the hosts now
(else run 'install-cluster.sh' later)? (y/n):y

Important! Do not run the the bootstrap.sh script!!
4. change directory to the scripts/ to directory and run the ./rolling-restart.sh script

[cluster01]# pwd
/root/mysqlcluster-70-master/cluster/scripts/
[cluster01]# ./rolling-restart.sh

Adding of [mysqld] slot for administration purposes
./start-backup now backup the configuration files (config.ini, my.cnf and some internal files) as well.

Data node stuck in phase 101 - what to do?

2009-08-31T15:35:00.005+01:00

Sometimes I have seen and heard about that a data node gets stuck in start phase 101.

Unfortunately it is difficult to reproduce this (found no way yet), so no bug fix is in the pipe yet.

What happens is that in sp 101, the starting data node (actually a block called SUMA) should reconnect to the mysql server and take over the event handling (sending events to the mysql server), but it never gets the reconnect to the mysql server(s). A better explanation is here :)

If you see your data node stuck here then try the following:
1) Restart the mysql servers (one by one), the data node should now start
or
2) Restart the mysql servers and restart the data node.

Only do 2) if 1) does not work.

And if you know how to reproduce - let us know!

OPTIMIZE TABLE on Cluster (revisited)

2009-08-04T13:53:00.003+01:00

Jonas just wrote a patch to this bug on OPTIMIZE TABLE, and the issue that was also discussed in this blog post. Jonas also fixed this bug when he was at it.

Before, OPTIMIZE TABLE hardly freed up any pages and to defragment you had to do a rolling restart of the data nodes.

Now, there is only a 2% discrepancy between OPTIMIZE TABLE and doing a rolling restart. This is great stuff.
This will fix will make it into 6.3.26 and 7.0.7.

See below for details:

Creating two tables, t5 and t6:


CREATE TABLE `t5` (
`id` varchar(32) CHARACTER SET utf8 NOT NULL,
`name` varchar(32) NOT NULL DEFAULT '',
`unit` int(11) DEFAULT NULL,
PRIMARY KEY (`id`,`name`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

And then the following table:


'CREATE TABLE `t6` ( `id` varchar(32) CHARACTER SET utf8 NOT NULL, `name` varchar(32) NOT NULL DEFAULT '', c0 int unsigned not null, c1 int unsigned not null, data2 varchar(255),  `unit` int(11) DEFAULT NULL, PRIMARY KEY (`id`,`name`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

Then we use hugoLoad (a test program that isn't built by default in the src distribution of cluster) to load it with 1M records.


./hugoLoad -r 1000000 -d test t5
./hugoLoad -r 1000000 -d test t6

Memory usage after population:


Node 2: Data usage is 45%(14890 32K pages of total 32768)
Node 2: Index usage is 22%(3722 8K pages of total 16416)
Node 3: Data usage is 45%(14890 32K pages of total 32768)
Node 3: Index usage is 22%(3722 8K pages of total 16416)

Delete 500K records from t5.


mysql> set ndb_use_transactions=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t5 limit 100000;
Query OK, 100000 rows affected (2.73 sec)

mysql> delete from t5 limit 100000;
Query OK, 100000 rows affected (3.13 sec)

mysql> delete from t5 limit 100000;
Query OK, 100000 rows affected (3.11 sec)

mysql> delete from t5 limit 100000;
Query OK, 100000 rows affected (3.13 sec)

mysql> delete from t5 limit 100000;
Query OK, 100000 rows affected (2.97 sec)

Memory usage after deletion:


Node 2: Data usage is 45%(14890 32K pages of total 32768)
Node 2: Index usage is 19%(3120 8K pages of total 16416)
ndb_mgm> Node 3: Data usage is 45%(14890 32K pages of total 32768)
Node 3: Index usage is 19%(3121 8K pages of total 16416)

Run OPTIMIZE TABLE:


mysql> optimize table t5;
+---------+----------+----------+----------+
| Table   | Op       | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t5 | optimize | status   | OK       |
+---------+----------+----------+----------+
1 row in set (1 min 41.94 sec)

CPU UTIL during OPTIMIZE TABLE:
User:23.8% Sys: 4.9% ndbd

Memory usage after OPTIMIZE TABLE:

                  
ndb_mgm> Node 2: Data usage is 39%(13090 32K pages of total 32768)
Node 2: Index usage is 19%(3120 8K pages of total 16416)
Node 3: Data usage is 39%(13090 32K pages of total 32768)
Node 3: Index usage is 19%(3121 8K pages of total 16416)

Delete 500K (50%) of the records from t6:


mysql> set ndb_use_transactions=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t6 limit 100000;
Query OK, 100000 rows affected (2.77 sec)

mysql> delete from t6 limit 100000;
Query OK, 100000 rows affected (3.05 sec)

mysql> delete from t6 limit 100000;
Query OK, 100000 rows affected (3.12 sec)

mysql> delete from t6 limit 100000;
Query OK, 100000 rows affected (3.19 sec)

mysql> delete from t6 limit 100000;
Query OK, 100000 rows affected (3.24 sec)

Memory usage after deletion:


Node 2: Data usage is 39%(13090 32K pages of total 32768)
Node 2: Index usage is 15%(2521 8K pages of total 16416)
Node 3: Data usage is 39%(13090 32K pages of total 32768)
Node 3: Index usage is 15%(2522 8K pages of total 16416)

Run OPTIMIZE TABLE:


mysql> optimize table t6;
+---------+----------+----------+----------+
| Table   | Op       | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t6 | optimize | status   | OK       |
+---------+----------+----------+----------+
1 row in set (1 min 45.83 sec)

Memory usage after OPTIMIZE TABLE:


Node 2: Data usage is 28%(9249 32K pages of total 32768)
Node 2: Index usage is 15%(2521 8K pages of total 16416)
Node 3: Data usage is 28%(9249 32K pages of total 32768)
Node 3: Index usage is 15%(2522 8K pages of total 16416)

After restart of node 3:


Node 2: Data usage is 28%(9249 32K pages of total 32768)
Node 2: Index usage is 15%(2521 8K pages of total 16416)
Node 3: Data usage is 26%(8568 32K pages of total 32768)
Node 3: Index usage is 13%(2236 8K pages of total 16416)

Only two percent diff between rolling restart and OPTIMIZE TABLE. Excellent. Thank you Jonas for fixing this.

Upgrade 6.3.x to 7.0.6

2009-08-04T12:54:00.004+01:00

Currently a number of users has reported upgrade issues on 6.3.x to 7.0.6 (below is an example what might happen to you).

Thus, we don't recommend you to upgrade from 6.3.x to 7.0.6 in a production system yet.
However, we are very interested in bug reports and and help with testing.
We are also of course working on fixing these issues and will keep you posted when things has improved. Sorry for the inconvenience.

Currently the "best practice" to upgrade is to:
1) backup / mysqldump
2) start cluster 7.0.6 with initial so it is completely blank
3) restore backup / load dump

ERROR 1005 (HY000): Can't create table 'test.#sql-3d25_aa905' (Errno: 140)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------+
| Error | 1296 | Error 755 'Invalid tablespace' from NDB |
| Error | 1005 | Can't create table 'test.#sql-3d25_aa905' (Errno: 140) |
+-------+------+--------------------------------------------------------------------------+

Cluster + Dolphin DX = Sweet!

2009-08-03T08:15:00.004+01:00

At a customer I have had the opportunity to work with some really state of the art equipment.

The customer is in the online gaming industry and we are building a platform based on MySQL Cluster where we are consolidating a number of applications. Workload is mainly inserts, updates, pk reads, and index scans (quite frequent), and a few joins. The cluster handles the transient data (current working set), and is replicating to a long term data store (following this approach), and we are also feeding other back-end support systems from this.

The cluster (>8 computers) is interconnected with Dolphin DX adapters. Computers are SUNs Nehalem based servers (Intel XEON E5540). The cluster (7.0.6) consists of 4 data nodes each having 32GB of RAM, plenty of RAID 1+0 storage, and >4 mysql servers. All running on the Nehalems.. It is so sweet :)

So much did things improve with the DX adapters over Gig-E? Just by plugging it in we got an overall 2.5-3x performance improvement (throughput up, response times down). I love it. Now we will continue optimizing the setup, schemas, queries etc to really find out.

I can really recommend the investment in DX adapters (remote memory access is faster than local disk) if you are after the top-notch performance , and the company behind it, Dolphin ICS is really fantastic to work with. If the DX switch would fail, then it will automatically fall back on the Gig-E adapters, transparently, and no service interruption (you or the applications don't notice a thing, except that performance drops).

Note: Dolphin ICS' adapters offers a socket interface. Thus, any TCP/IP application can benefit from the low latency and high bandwidth.

ndb_restore - best practice

2009-07-30T15:50:00.003+01:00

A really simple best practice:

Make sure there are NO mysql servers connected to the Cluster while you do the restore!
It can cause the restore to fail badly and potentially even your cluster.

Also, make sure (if you use disk data) to remove all data files in the datadirs of the data nodes as they are not removed when doing an --initial ... I have written a bug report about that...

(i will update the severalnines/config scipts to include this best practice!)

Problems with .FRM files, auto-discovery and MySQL Cluster

2009-07-01T10:00:00.003+01:00

There are some bug reports on the auto-discovery protocol in MySQL Cluster.
The idea of the auto-discovery protocol is to fetch the .frm files for the NDB tables stored in the data dictionary of the data nodes, and put them in the data directory of the mysql server.

However, sometimes (not always, which makes it more difficult to reproduce and hence fix), the auto-discovery seems to make strange things (from this bug report):

After shuting down and restoring my cluster I get the following error.

090211  9:59:26 [Note] NDB: mismatch in frm for panel.gatewayquestions, discovering...
090211  9:59:26 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$panel/gatewayquestions
090211  9:59:26 [Note] NDB Binlog: logging ./panel/gatewayquestions (UPDATED,USE_WRITE)

This is due to the files already being in the mysql data directory. After the error the
frm does not match the data in memory this causes the following.
When running select count(*) from tablename;
You will get an accurate count.
When running select * from table name;
You get an error Can't find record in tablename.

I have seen it as well at some customers usually with bigger installations and many tables.

My current recommendation (work around) is to delete the FRM files associated with the NDB tables in the mysql server data directory before you start the mysql server(s).

So this is what i always include in my MySQL server startup scripts (and is included in the Configurator scripts):

files=`find $mysql_datadir  -name "*.ndb"`
for f in $files
do
    x=`basename $f .ndb`
    #make sure we leave out ndb_binlog_index and ndb_schema since they are myisam tables
    if [ "$x" == "ndb_binlog_index" ]  || [ "$x" == "ndb_schema" ] ;
    then
       echo "Ignoring $x"
    else
       y=`echo $f | sed  -e 's#ndb#frm#'`
       rm -rf $f
       rm -rf $y
    fi
done
#start the mysqld here

If I want to restore data I usually:

stop the cluster
start the data nodes with --initial
stop the mysql servers (make sure they are not started)
restore the cluster data
start the mysql servers (clearing out whatever .frm files coming from the ndb tables)

How to upgrade from 6.3 to 7.0

2009-06-29T10:00:00.004+01:00

In order to upgrade from 6.3 to 7.0 you must follow these rules:

IT IS ONLY POSSIBLE TO UPGRADE FROM 6.3 to at least 7.0.6!
I would recommend to upgrade from 6.3.x to 6.3.latest before doing the upgrade. If the upgrade does not work then and you have a good config.ini , you have probably hit a bug.
You cannot upgrade from 6.3 to the multi-threaded binary of 7.0.6 in one go.
You have to upgrade from 'ndbd' (6.3) --> 'ndbd' (7.0.6)
Then you can do 'ndbd' (7.0.6) --> 'ndbmtd' (7.0.6)

So don't try to upgrade to 7.0.5 - it will fail.
As usual when upgrading (version, configuration variables etc) you must do a rolling restart and restart:

ndb_mgmd (management servers)
ndbd (data nodes)
mysqld (mysql servers / direct api applications)

Distribute the 7.0.6 binaries
Copy the binaries to each host (i.e., replace existing 6.3 binaries) in the cluster.

Upgrading the Management Server(s)

In 6.3 you started the management server as (e.g):

ndb_mgmd -f /etc/mysql/config.ini

In 7.0.6 you have to start them slightly differently, but first you have to stop both (if you have two, but you really should) management servers (because of this bug):

killall ndb_mgmd

And.. I never use the management client to start/stop nodes, because I think it works sometimes which makes it difficult to file bugs on, and hard to rely on.

When you have killed both you can then do:

ndb_mgmd -f /etc/mysql/config.ini --configdir=/etc/mysql --reload

The management server in 7.0.6 writes a binary config file in --configdir, which is a configuration cache. --reload will reload the config.ini and update that cache.

Upgrading the Data Nodes

Restart the data nodes on each machine one at a time:

killall ndbd
ndbd --ndb-nodeid=X --ndb-connectstring="managementhostA;managementhostB"
ndb_waiter --ndb-connectstring="managementhostA;managementhostB"
# when ndb_waiter exits, you can restart the next data node.

Upgrading the MySQL Servers/Direct APIs

Stop and start the mysql servers:

killall mysqld  #(or mysqladmin shutdown , both sends signal 15)
mysqld_safe --defaults-file=/etc/config/my.cnf

Upgrading to multithreaded 7.0.6 data nodes

When the Cluster has been restarted with 7.0.6 you can upgrade to multithreaded data nodes.
The binary for the multithreaded data node is called 'ndbmtd'.

You need to set in config.ini (on both management servers):

[ndbd default]
..
MaxNoOfExecutionThreads=#number of cores you have (up to 8)
..

Then stop both management servers

killall ndb_mgmd

When you have killed both you can then start both by doing:

ndb_mgmd -f /etc/mysql/config.ini --configdir=/etc/mysql --reload

and then restart the data nodes one at a time:

killall ndbd
ndbmtd --ndb-nodeid=X --ndb-connectstring="managementhostA;managementhostB"
ndb_waiter --ndb-connectstring="managementhostA;managementhostB"
# when ndb_waiter exits, you can restart the next data node.

Using Severalnines Configurator

If you have a Severalnines installation of MySQL Cluster 6.3, then you can upgrade in the following way:

Make sure you have have the files: mysqlcluster-xy/cluster/scripts/install/.s9s/hostnamesmysqlcluster-xy/cluster/scripts/install/.s9s/ndb_hostnames
mysqlcluster-xy/cluster/scripts/install/.s9s/mysql_hostnames
mysqlcluster-xy/cluster/scripts/install/.s9s/mgm_hostnamesOtherwise you have a too old version of the Configurator package and you need to generate a new one.
Generate a new Config using 7.0.6 with the same nodes, data memory, data dirs etc.
Select "no MT" when asked for "Number of cores:"

Install the package
Let's assume you have 6.3 in:

/root/mysqlcluster-63/

Install mysqlcluster-70.tar.gz

cd /root/
tar xvfz mysqlcluster-70.tar.gz
cd mysqlcluster-70/cluster/scripts/install

Copy the .s9s catalog from 6.3 so you get the hostnames

cp  -r /root/mysqlcluster-63/cluster/scripts/install/.s9s  .

Download the binary or build the source:

./download-binary.sh

./download-and-compile.sh

Install and perform a rolling restart

./install-cluster.sh
cd ..
./rolling-restart.sh

Change from ndbd --> ndbmtd
```
vi ../config/config.ini
```
Locate MaxNoOfExecutionThreads and, un-comment it if needed and set it to as many cores as you have:
```
[ndbd default]
...
MaxNoOfExecutionThreads=8
...
```
Then change in the scripts from ndbd -> ndbmtd
```
sed -i 's#libexec/ndbd#libexec/ndbmtd#g' *.sh
```
Important! Don' leave out 'libexec' above!
And do a rolling restart:
```
./rolling-restart.sh
```
Luckily it is much easier to upgrade from e.g 7.0.6 to 7.0.x and I will show when 7.0.7 is out how to do that in two lines.

Webinar - MySQL Cluster: Architectural Deep Dive

2009-06-15T16:57:00.002+01:00

There will be a webinar presented by Tomas Ulin (Director MySQL Server Technologies) and Matthew Keep (MySQL Cluster Product Management). The webinar covers:

how MySQL Cluster achieves predictable and consistent real time performance
how MySQL Cluster achieves continuous availability with sub-second fail-over, automated recovery and geographic replication
how MySQL Cluster enables users to dynamically scale throughput and data capacity
how to get started with MySQL Cluster

and I will be on the panel anwering your questions!

When: Wednesday 17th of June 2009, 0930PST/1230EST/1730GMT/1830CET/
See details and how to register here.

MySQL Cluster - versions NOT to use!

2009-06-15T10:24:00.002+01:00

If you are using the following versions of MySQL Cluster:

MySQL Cluster 6.3.17 - SHOW TABLES can drop tables...
MySQL Cluster 6.3.18 - the above wasn't really fixed (fixed in 6.3.20)
MySQL Cluster 6.3.19 - the above wasn't really fixed (fixed in 6.3.20)
MySQL Cluster 6.3.21 - regression, pulled back because of this.

If you are on any of these versions - then upgrade!
The latest source and binary release is MySQL Cluster 6.3.24.

HA MySQL, write scaling using Cluster to non-cluster replication

2009-05-15T17:18:00.008+01:00

There are various setups and solutions to solve the problem of having redundant mysql masters and scaling writes on them. MySQL Cluster can be an alternative. Here is why and how:
Pros:

No need to worry about DRBD failover and recovery times
No need to worry about potential data loss when failover from active master to standby master (has all binlog really been replicated over from the failed master?)
Writes go to any mysql server connected to the Cluster - thus writes are easy to scale
The data nodes will then send the committed writes to every mysql server with --log-bin enabled.
MySQL Cluster can handle a lot of writes (but the slaves can become the bottleneck)
Data nodes can be added online (MySQL Cluster 7.0) - thus write scaling is easy and can be done on-demand
Add new, consistent, slaves online (thanks to the online backup in MySQL Cluster)
Simple and very fast master failover policy - all masters connected to the cluster will receive the same events, thus (when both are working) they will have the same content in the binary log - none is lagging behind.
No additional hardware compared to a normal HA setup of the masters (two computers are enough)

Worried that NDB stores data in RAM - well use disk based tables!

Setting up MySQL Cluster
You need to have at least:

2 computers (HA with dual switches, nics and bonding, split brain only if both links are broken)
- dual switches, dual nics (bonded).
- each computer runs:
1 data node (ndbd/ndbmtd),
1 mysql server (master),
1 ndb_mgmd
or
4 computers (better HA and no risk for network partitioning/split brain)
- dual switches, dual nics (bonded).
- two computers each runs:
1 data node (ndbd/ndbmtd),
- two computers each runs:
1 mysql server (master), 1 ndb_mgmd.

Then it is of course possible to have more data nodes, more mysql servers etc, but we recommend two mysql servers that are masters in the replication so it is possible to failover between them.

With www.severalnines.com/config you can easily generate either of these setups, and have a running Cluster in a couple of minutes.

The idea here (just as in a normal HA MySQL setup) is to have two masters, master_a and master_b. The slaves will replicate from master_a and failover to master_b if master_a fails!

Note that you can write to _all_ mysql servers, and the data nodes will send the changes to the mysql servers that has --log-bin enabled.

Preparation of Slaves
You must chose between 1) and 2) below, else the replication won't work:

1. Enable INDEMPOTENCY in my.cnf of the slaves using slave-exec-mode:

--slave-exec-mode=IDEMPOTENT

2. if you want to use STRICT (default), then you have to set on the master mysql servers (my.cnf):

--ndb-log-update-as-write=O

Meaning that updates will be logged as updates, and not as writes.This downside with this is that twice as much as data will be sent (there is a before image and an after image for each update).

Each slave must have the table mysql.ndb_apply_status:

mysql_slave> use mysql;
mysql_slave> CREATE TABLE `ndb_apply_status` (
`server_id` int(10) unsigned NOT NULL,
`epoch` bigint(20) unsigned NOT NULL,
`log_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`start_pos` bigint(20) unsigned NOT NULL,
`end_pos` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`server_id`) USING HASH)
ENGINE=INNODB DEFAULT CHARSET=latin1;

This table is used to know what data has been applied on the slave and from which master (see below more a longer explanation).

Preparation of Masters (masterA, masterB)
Ensure you have the following in the my.cnf of the master mysql servers::

[mysqld]
...
#if you didnt set --slave-exec-mode=IDEMPOTENT on the slaves you must set:
ndb-log-update-as-write=O
#else
ndb-log-update-as-write=1
#endif -- so pick one of the above!!

#enable cluster storage engine:
ndbcluster

#connect to cluster:
ndb-connectstring=<hostname of ndb_mgmd:port>

#sync after each epoch (10 ms of transactions) - here is a disk tuning possibility.
sync-binlog=1

#you may also want to set binlog_cache_size (if Binlog_cache_disk_use is growing):
binlog_cache_size=1048576

GRANT the replication user, e.g,

mysql>GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'password'

Creating the tables

On MySQL Cluster
Create all the tables for the application(s) using engine=NDBCLUSTER.
On each slave
Create all the tables for the application(s) using e.g engine=MYISAM or engine=INNODB.
Staging a slave - with traffic on Cluster:
In order to get a consistent slave you have to do as follows:

Backup the Cluster
Using the management client:

ndb_mgm>start backup

Copy all the backup files to the same location.
If you use the Configurator scripts:

./start-backup.sh /path/mycentral_backupdir/

all backup files will be copied from the nodes to /path/mycentral_backupdir/

Remember the stopGCP=<some number> that is printed at the end of the backup.
If you use ndb_mgm -e "start backup" then it will look like this:

ndb_mgm -e "start backup"
Connected to Management Server at: localhost:1186
Waiting for completed, this may take several minutes
Node 3: Backup 14 started from node 1
Node 3: Backup 14 started from node 1 completed
StartGCP: 98278 StopGCP: 98281
#Records: 2058 #LogRecords: 0
Data: 51628 bytes Log: 0 bytes

If you use ./start-backup /tmp then it will look like this:

Backup has completed on data nodes.
Copying backup files to /tmp//BACKUP-2009May09-150743
BACKUP-15.3.log                               100%   52     0.1KB/s   00:00
BACKUP-15-0.3.Data                            100%   26KB  26.1KB/s   00:00
BACKUP-15.3.ctl                               100%   11KB  10.6KB/s   00:00
BACKUP-15.4.log                               100%   52     0.1KB/s   00:00
BACKUP-15-0.4.Data                            100%   26KB  25.7KB/s   00:00
BACKUP-15.4.ctl                               100%   11KB  10.6KB/s   00:00
Backup completed and backup files can be found in /tmp//BACKUP-2009May09-150743
Use the StopGCP to setup Cluster to non-cluster replication:
StopGCP: 98482

This is important as this tells where the backup ended, and from what point the slave should get the changes. This number will be used later when the replication is setup.

Convert Cluster backup data files to CSV files
For each backup file (for each data node in the master cluster) run:

ndb_restore -c <connectstring>
-b <backupid>
-n <nodeid>
--print-data
--print-log
--append
--tab=<output directory>
--fields-enclosed-by="'"
--fields-separated-by=","
--lines-terminated-by="\n"

or if you are using the Configurator scripts:

./restore-backup.sh --csv --backupdir=/path/mycentral_backupdir/ --csvdir=/mycsv/

This will create one txt file for each table and put it in --csvdir (Configurator) or --tab=<outputdir> for the vanilla ndb_restore.

It will also tell you (if you used start-backup.sh) the stop gcp:

./restore-backup.sh --csv
        --backupdir=/cluster/backups/BACKUP-2009May10-165004
        --csvdir=/cluster/backups/csv

...

To setup replication use StopGCP: 1511

Data is appended to the txt files, so running the command more than once is bad. Better delete the old txt files first.

Loading the .txt (CSV) into Slave
For all tables:

mysql_slave> LOAD DATA INFILE 'tablename.TXT'
INTO table <tablename>
FIELDS TERMINATED BY ','
ENCLOSED BY '\''
LINES TERMINATED BY '\n';

Now the slave is at the same state as the when the backup finished in the Cluster.

Staging a slave - no traffic on Cluster:
You can use mysqldump for this.

host> mysqldump -u<user> -p<password> <database> > database_dump.sql

Then you copy the database_dump.sql and change the engine=ndb to engine=innodb

host> sed -i -e 's#ndbcluster#innodb# database_dump.sql

Then you have to find the good starting position from master_a:

master_a> show master status;

Remember the binlog filename and position, you will need it in the next step.

Starting the replication
Create and grant the replication user (not shown here)

If you have staged the slave using mysqldump, then you go directly to CHANGE MASTER.. using the BINLOG and POS from the previous step.

Find out where the slave should start replicating from using the stopGCP from the previous step "Backup the Cluster".

mysql_masterA> SELECT @file:=SUBSTRING_INDEX(File, '/',-1),
@pos:=Position
FROM mysql.ndb_binlog_index
WHERE gci>@stopGCP ORDER BY gci ASC LIMIT 1;

if the query returns nothing (this means that no changes has happened on the cluster after the backup was completed) run:

mysql_masterA> SHOW MASTER STATUS;

Then change master

mysql_slave>   CHANGE MASTER TO MASTER_HOST='masterA',
        MASTER_PORT=3306,
        MASTER_USER='repl',
        MASTER_PASSWORD='password',
        MASTER_LOG_FILE='@file',
        MASTER_LOG_POS=@pos;
mysql_slave>   START SLAVE IO_THREAD;
## verify slave connected to masterA:
mysql_slave>   SHOW SLAVE STATUS\g

if above is ok:

mysql_slave>   START SLAVE SQL_THREAD;
mysql_slave>   SHOW SLAVE STATUS\g;

Master Failover
Failover from masterA to masterB


mysql_slave>    STOP SLAVE;
mysql_slave>    SELECT @epoch:=max(epoch) FROM mysql.ndb_apply_status;
mysql_masterB>  SELECT @file:=SUBSTRING_INDEX(File, '/', -1),
            @pos:=Position
       FROM mysql.ndb_binlog_index
       WHERE epoch>@epoch ORDER BY epoch ASC LIMIT 1;

If the query returns nothing (meaning nothing has been written to the master since the failure of masterA) do:

mysql_masterB>  SHOW MASTER STATUS;
#use the position and file in the query below:

Then change master


mysql_slave>   CHANGE MASTER TO MASTER_HOST='masterA',
        MASTER_PORT=3306,
        MASTER_USER='repl',
        MASTER_PASSWORD='password',
        MASTER_LOG_FILE='@file',
        MASTER_LOG_POS=@pos;
mysql_slave>   START SLAVE:

Master crashes
When the master restarts after a crash, it will write a LOST_EVENTS event into its binary log.
If a LOST_EVENTS event is received on a slave, the slave will stop, and you have to do a Master Failover.

Master connection glitch between data node and a master mysql server
If the master would lose contact for a very short period of time with the cluster, then a LOST_EVENTS event will be written into the binary log. See above.

ndb_apply_status, epochs and ndb_binlog_index
The mysql.ndb_apply_status is very important as it stores what epochs from the Cluster that the slave has applied.
The epochs are consistent units and stores 10ms (controlled by TimeBetweenEpochs in config.ini) of committed transactions.
On the master side, the epochs are mapped to binary log filename and position in the mysql.ndb_binlog_index table.
The mysql.ndb_binlog_index table is a MYISAM table is local to each master and is the glue between what epoch has been applied on the slave, and what binary log and position it corresponds to for the master.

Monitoring
Replication - you can use Enterprise Monitor or whatever monitoring tool you prefer to monitor the replication and the mysql servers. Scripts for automatic failover must be implemented.
Cluster - you can use cmon to monitor the data nodes and the other components connected to the Cluster. The web interface will soon look better :)

Possible Extensions
You can of course give the master mysql servers one virtual IP and use Hearbeat to failover the IP. This simplifies for the applications.

Bugs and Issues - TIMESTAMP
Be careful with TIMESTAMP. Currently the ndb_restore program does not generate the timestamp using the MySQL TIMESTAMP, but rather it is a UNIX_TIMESTAMP.
I have written a bug report on this. In the meantime, you have to convert the TIMESTAMP columns in the .txt files to the MySQL TIMESTAMP format YYYY-MM-DD HH:MM:SS.

Please note that this is not a problem if you use mysqldump

I will update this part if you find any other problems - just let me know.

Thanks to Magnus Blåudd, Cluster Team, for your input on this post!

Cluster fails to start - self diagnosis (sanity check mysql cluster)

2009-05-08T10:11:00.009+01:00

If the MySQL Cluster fails to start, what can you do and what to check?
Here are some sanity checks.

Initial system start
If it is the first time you start up the cluster and it fails, then check the following:

Network - Check /etc/hosts
You should have (on the line for localhost)
127.0.0.1 localhost
and nothing else! Then usually, the normal IP address host mapping follows:
10.0.1.10 hostA
10.0.1.11 hostB
...
REDHAT and others can add a lot of other things to the "localhost" line(s), and this results in that the nodes won't be able to connect to each other (they will be stuck in phase 0 or phase 1)
Network - Check if you can ping the machines
Network - Check if you have any firewalls enables ( e.g check with /sbin/iptables -L)
Disable the firewall in that case. Exactly how depends on OS and Linux distribution.
On Redhat systems, then SELinux might be enabled. Googling "disable firewall <your distro>" should give answers. Firewall is the most common culprit preventing the nodes in the cluster talking to each other.
RAM - Check if you have enough RAM to start the data nodes
Check using 'top' on the computers where the data nodes running, while you start the data nodes. So always, have 'top -d1' running on the data nodes while they are starting up.
RAM - If you are allocating a lot of DataMemory, then you may also need to increase the parameter TimeBetweenWatchdogCheckInitial in [NDBD DEFAULT] of your config.ini. Set it to 60000 if you have >16GB of RAM.
Disk space - check using ' df -h' if you have enough space where the data nodes has its data directory.
CPU - if you use 7.0, enable multi-threading (8 cores) and only have a 4 core system or less, then there are chances that the Cluster won't come up. Competition for resources. I have seen this happening but no conclusive evidence yet.
OS - if you have a mix of OSs where the data nodes run, then it can be a problem. E.g, I have seen problems even when Fedora has been used on all machines, but one of the machines had a slightly older linux kernel. Also, it won't work if one of the nodes is a RH4 and the other is a RH5 (atleast mixing RH3 and RH4 doesn't).

So for the "initial start" it is mainly environmental factors preventing the cluster to start.
If you still have problems, ask on the Cluster Forum or MySQL Support if you have Support for advice.

Also, disable NUMA (Cluster is not NUMA aware) and make sure you dont SWAP!

System start
If you can't restart the cluster, and you haven't changed the configuration and haven't been filling up the disks with other things (i.e, check disk,ram, network as above) , then you have probably hit a bug. Ask on the Cluster Forum or MySQL Support if you have Support for advice.

In many cases it is recoverable by restarting one node in each node group (instead of all data nodes), and try out different combinations. When the "half" cluster has started, then you can restart the rest of the data nodes with --initial and they will sync up from the already started nodes.

Node (re)start
If you can't restart a failed data node, and you haven't changed the configuration and haven't been filling up the disks with other things (i.e, check disk,ram, network as above) , then you have probably hit a bug, but also there might have been corruption of the data files (this depends on how the computer/data node crashed).

You can try to do an initial node restart (see below).

Ask on the Cluster Forum or MySQL Support if you have Support for advice.

Initial Node (re)start
If you can't restart a failed data node with --initial, and you haven't changed the configuration and haven't been filling up the disks with other things (i.e, check disk,ram, network as above) , then you have probably hit a bug. Ask on the Cluster Forum or MySQL Support if you have Support for advice.

Collecting error data
The program 'ndb_error_reporter' is great to collect log files from the data nodes and management servers and puts them into a single bz file. Send this file to either Cluster Forum or MySQL Support if you have Support together with detailed steps what you have done.

Online configuration update and a new script

2009-04-28T18:50:00.005+01:00

If you are using the scripts from the Configurator (and yes, the broken link is now fixed so it is actually possible to get the config package..), and want to tweak your configuration on a started Cluster then you can do like written below.

There is also a new script in Configurator 2.2 (released 28th April 2009, PST1153AM , GMT07:53) that lets you check the values of different config parameters. Its use is described below.

Pretend we want to extend the DataMemory from 1024M to 1536M:

cd mysqlcluster-70/cluster/scripts
vi ../config/config.ini
This is the master config.ini file and will be copied out to the management server(s). Don't change /etc/mysql/config.ini, as that will have no effect.
Set DataMemory=1536M then save and exit
sh rolling-restart.sh
When it has finished, verify that the Cluster has the new settings by running query-param.sh:
sh query-param.sh DataMemory Parameter=DataMemory Data Node 3 : 1610612736 Data Node 4 : 1610612736Please note that the output is in bytes.

The rolling-restart.sh script implements this procedure described in the reference manual.
Many other parameters can be changed using the above steps (e.g, MaxNoOfConcurrentOperations etc). Be careful though that you have memory available (the script does not check for that yet).

Parameters that change the file system one way or another (NoOfFragmentLogFiles, FragmentLogFileSize, Datadir, BackupDataDir, FileSystem*, InitFragmentLogfiles) must be changed with an initial rolling restart (all parameters are listed here in the ref manual):

Pretend we want to change the size of the Redolog (NoOfFragmentLogfiles).
From the beginning we have:

./query-param.sh NoOfFragmentLogFiles
Parameter=NoOfFragmentLogFiles
Data Node 3 :  6
Data Node 4 :  6

Then we modify our "template" config.ini and go ahead:

vi ../config/config.iniThis is the master config.ini file and will be copied out to the management server(s)
Set NoOfFragmentLogFiles=7 then save and exit
sh rolling-restart-initial.sh
When it has finished, verify that the Cluster has the new settings by running query-param.sh:
./query-param.sh NoOfFragmentLogFiles Parameter=NoOfFragmentLogFiles Data Node 3 : 7 Data Node 4 : 7

Have fun upgrading but remember - don't try to start with a smaller configuration on an already existing configuration (i.e., it is safe to increase parameters, but be very careful if you decrease).

MC 7.0 New Features, LDAP For MySQL Cluster - UC2009 sessions

2009-04-27T07:58:00.003+01:00

The presentations from the sessions MySQL Cluster 7.0 - New Features and LDAP For MySQL Cluster - back-ndb, and all other sessions on MySQL Cluster, are available for download from www.mysqlconf.com.

Easiest way to try out new MySQL Cluster 7.0.5

2009-04-25T22:31:00.004+01:00

Here is how to install and start MySQL Cluster 7.0.5 in eight steps:

Go to the configurator and setup the configuration you wish to have. Make sure you select "MySQL Cluster 7.0.x" in the first drop down.
Copy the package you either download or receive by email to one of the computers that will run one of the management servers.
tar xvfz mysqlcluster-70.tgz
cd mysqlcluster-70/scripts/install
Download MySQL Cluster 7.0 from MySQL website and put it in mysqlcluster-70/scripts/install
sh dist-tgz.sh
sh bootstrap.sh
cd .. && sh start-cluster-initial.sh

Okay, step 1) requires that you setup shared ssh keys and a bit of typing and clicking, but gives you a production class configuration, and scripts to manage the cluster from a single point. Including backups and restores of MySQL Cluster!

I have mostly tested the scripts on Linux - please let me know about issues on other platforms!

Configurator - new version 2.1

2009-04-25T10:25:00.004+01:00

The Configurator has undergone some serious fixing as 2.0 had some issues.
Here is a brief list of the enhancements:

error handing and progress reports - I have rewritten almost all scripts with better error handling and progress reports. E.g, if a node fails to start during a rolling restart, then the rolling restart script is aborted and you get a suggestion how to recover. If you run start-cluster-initial.sh on an already started cluster, it will refuse to run. There are many more changes like this to check the status of the nodes (both data nodes and sql nodes) during startup
download link to package - When you have entered the email address, then you will also get a link where to download the package, in case you experience problems with receiving the email (I have raised an issue to the hosting company about this)
restructure - I have also moved the install scripts to scripts/install and the monit scripts to scipts/extra. The install_initd_linux.sh uses chkconfig and it does not work on all linux distros. Going to fix that.
bugs - fixed a number of small bugs and annoyances.

max_rows in cluster

2009-04-16T11:32:00.006+01:00

If you are going to load a lot of records into Cluster, don't forget to set max_rows!
My colleague, Yves at BigDBAhead, has also blogged about this, but I also ran into the same problem recently.

I did try to populate 100M records on a 4 node cluster, and the data nodes went down with the following error message in the error logs:

"2304 Array index out of range"

So the error message is crap - and my opinion is that there should be a proper error message propagated up to the mysql server. There is a bug report on this.

Simplified, what the error message means is that you have run out of "index slots" in the Hash Table storing the hashes of the Primary Keys. This is because each table is divided into a number of partitions, and each partition can only store X number of records in the hash table (about 100M).

The workaround when creating huge tables is to set max_rows=<2 x the number of records that you plan to store in the table>. 2x is just to be safe. Internally this will create more partitions. Internally the cost of this in terms of memory is negligible.

Example:

CREATE TABLE t1 (
  a INTEGER PRIMARY KEY
) ENGINE=ndbcluster MAX_ROWS=1000000000

Mac OSX support for Cluster Sandbox & Configurator

2009-04-09T18:33:00.003+01:00

Now you can use the Sandboxes and Configurator on Mac OSX.

You need to download the macos.sh script.

Sandbox users - Copy the script into themysqlcluster-XXX/scripts/ catalog
Configurator users - Copy the script into themysqlcluster-XXX/cluster/scripts/ catalog

Then run it:


sh macos.sh

before you run any other script.

This will convert all the LD_LIBRARY_PATH to DYLD_LIBRARY_PATH and also use 'tar xvfz' instead of 'zcat' which does not work in MAC OSX.

You also need to download apple xcode and wget.

Multi-source replication with MySQL Cluster

2009-04-09T16:51:00.004+01:00

With MySQL Cluster it is possible to aggregate data from many MySQL Servers using Replication. Here is how.

E.g, you might have a sensor network, where each sensor writes data into a mysql server. The problem is that you have a quite few of these sensors, and you want to do aggregate this data, e.g. in order to do data mining on the combined data set.

The standard MySQL server does not support multi-source (i.e, one slave server can be connected to many masters), but Cluster can be used for this, since you can have many mysql servers connected to Cluster acting as slaves.

It is also possible to extend this - e.g, to replicate the aggregated data from Cluster to e.g, Infobright for datamining.

I did a small PoC at home to show how to use MySQL Cluster for multi-source replication, and here is what I did:

Installed MySQL Cluster on the host computer (called barracuda)- I used the MySQL Cluster 6.3 slave medium sandbox. The Cluster is going to act as a slave collecting data from two master mysql servers. The sandbox I am using comes with two mysql servers that are acting as slaves in this setup.
Installed VirtualBox and two VMs (Linux, Ubuntu)
In each VM (server01 and server02) I installed one mysql server.
These two mysql servers are acting as masters.

Creating the tables

on the Cluster:


CREATE TABLE `sensor` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sensor_id` int(11) NOT NULL DEFAULT '0',
`data` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`id`,`sensor_id`),
KEY `sensor_id` (`sensor_id`)
) ENGINE=ndbcluster partition by key(sensor_id);

and on the masters:


CREATE TABLE `sensor` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sensor_id` int(11) NOT NULL DEFAULT '0',
`data` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`id`,`sensor_id`),
KEY `sensor_id` (`sensor_id`)
) ENGINE=innodb;

(btw - why does not innodb let me create the table if i change the order of 'id' and ' sensor_id' ???)
Then it was just a matter of setting up the replication links between the each master and slave:
server01 (master1) ---> barracuda (slave1)
server02 (master2) ---> barracuda (slave2)

Setup the GRANTs on master1 and master2:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'barracuda' identified by 'password'

Resetting master1 and master2

Just to make it simple to have a good position to start from in the binary logs (show master status would have worked to but it would have been more to wrote..)
RESET MASTER

On slave1

CHANGE MASTER TO MASTER_HOST='server01', MASTER_USER='repl', MASTER_PASSWORD='password';
START SLAVE;

On slave2



CHANGE MASTER TO MASTER_HOST='server02', MASTER_USER='repl', MASTER_PASSWORD='password';
START SLAVE;

Generating load on the masters

On server01 and server02 I then started bencher to insert data into the sensor table:

test@server01: src/bencher -s /tmp/mysql.sock -t 1 -l 10000000 -q "INSERT INTO sensor(sensor_id,data) VALUES (1, <1024B of data>)"

test@server02: src/bencher -s /tmp/mysql.sock -t 1 -l 10000000 -q "INSERT INTO sensor(sensor_id,data) VALUES (2, <1024B of data>)"

And the masters where looking like this:

(please not that I did not try to stress the system that much here since I only have one computer)

And the slaves on Cluster:

MySQL Cluster @ UC

2009-03-31T20:48:00.003+01:00

The UC is closing in fast and there are a number of (potentially) interesting talks around MySQL Cluster.
Personally, I will give two talks (sessions):

MySQL Cluster 6.4 (7.0) - New Features
I will talk about ONLINE ADD NODE, and multithreaded data node, and the rest of the things that are new in 7.0. Please note that MySQL Cluster 6.4 changed name to 7.0, but the session title didn't.
When and Where: Ballroom A at 05:15pm, Tuesday, 04/21/2009
LDAP for MySQL Cluster- back-ndb
Together with Howard Chu, CTO of Symas, we will present OpenLDAP for MySQL Cluster. Check out this blog post for some basic information about OpenLDAP for MySQL. We will talk about how it scales, performance, limitations, etc etc.
When and Where: Ballroom B at 02:00pm, Thursday, 04/23/2009

A list of talks about MySQL Cluster can be found here!

LDAP and MySQL Cluster

2009-03-31T20:13:00.007+01:00

There are two LDAP Directory Servers available that supports MySQL Cluster - OpenLDAP (supported and maintained by Symas Corp.) and OpenDS (Sun Microsystems). Both of them have implemented a back-end called back-ndb that talks direclty to the data nodes. This means that they use the NDBAPI directly to access data in the cluster, thus bypassing the MySQL Server.

Using MySQL Cluster as the back-end makes it possible to easily scale out the LDAP layer without using replication between LDAP servers. If you need to have more capacity in the LDAP layer, add another LDAP server (online, no service interruption), if you need more storage capacity, add data nodes (online, no service interruption). This offers incredible scalability. And no single point of failure.

But since the data is stored in MYSQL Cluster, you can also use SQL to access the data, simultaneously with the LDAP accesses. See the picture below.

OpenDS actually uses ndb-bindings which is a Java layer (JNI wrapper) on top of the C++ NDBAPI. OpenLDAP use the Direct C++ NDBAPI directly. The use of Direct APIs makes a good foundation for high performance.

Besides that there is the usual differences in functionality you can expect from two different Directory Servers (I am not going into details about that here), but both implementation has the similar layout when it comes to the data model that is used inside MySQL Cluster to support LDAP accesses. This similarity comes from the fact that I have had my fingers in both implementations :) . However, I must say that I know the implemenation of OpenLDAP better.

If you want more details you should come to the MySQL User Conference 2009. During the UC, I will have a session together with Howard Chu on OpenLDAP for MySQL Cluster. There will also be a BOF for OpenDS (TBD exaclty when, since the BoFs have not been scheduled yet when this post was written (31st of March, 2009), but keep your eyes open!

If you are interested in getting started with OpenLDAP for MySQL Cluster, then you can follow this quickstart guide.

I have also got lualdap to work with OpenLDAP for MySQL Cluster. Quite cool, since it then can be accessed from the MySQL Proxy (thanks to Stephane Varoqui, Star Consultant MySQL/Sun based in Paris, for this idea)... This opens up quite some interesting things when it comes to authentication.

online add node - preliminary numbers (2)

2009-03-19T20:01:00.011Z

Scenario: Grow from two data nodes to four using ONLINE ADD NODE and REORGANIZE PARTITION of a 2GB table, and UPDATE traffic

Setup for each data node:
* Dual cpu - quad core running at 2.33GHz.
* 8GB of RAM
* DataMemory=4096MB
* MaxNoOfExecutionThreads=8
* Configuration generated by Configurator
* Bound execution threads to cores (LockExec/Maint....Thread..)
* Completely distributed setup.

Created the following table and filled it with 2M rows which gives a table (different of last time to simplify further tests) of 2GB in size:


CREATE TABLE `t1` (
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`b` int(10) unsigned DEFAULT NULL,
`c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`d` varbinary(768) DEFAULT NULL,
`e` varbinary(256) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `c` (`c`)
) ENGINE=ndbcluster

Baseline (two data nodes)
Random updates of 256B:
UPDATE t1 SET e=<data> WHERE a=<random_value>
where DATA is 256 random bytes and RANDOM_VALUE=random value between 1..2M

Running with bencher-0.12:
src/bencher -t 20 -l 100000
20 threads
Total throughput = 18315.06 qps
Average response time=1089us for each update

CPU Util details at the end of this post (please note I was not trying to max out the data nodes with this test).

With reorg on + no load

mysql> alter table t1 reorganize partition;
Query OK, 0 rows affected (2 min 44.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

With UPDATEs and ongoing reorg
ongoing reorg means: ALTER TABLE t1 REORGANIZE PARTITIONS;
20 threads each doing updates as in baseline
Total throughput = 10907.70 qps
Average response time=1827us for each update
And the reorg took:


mysql> alter table t1 reorganize partition;                                
Query OK, 0 rows affected (3 min 39.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

Cost in performance during reorg
Throughput decreases with about 40% during the reorg.
Latency increases with about 68% during the reorg.

CPU DETAILS (baseline)

CPU DETAILS (with UPDATE + reorg)