You may have noticed that the software downloads and documentation are not currently available from www.mysql.com. Don’t panic, you can download the software from dev.mysql.com. The documentation is also available from dev.mysql.com.
Archive for January 28, 2010
MySQL Cluster Performance Tuning Best Practices
Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries?
Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for performance when using MySQL Cluster.
We will discuss guidelines and best practices covering the following areas:
- General Design Concepts and Guidelines
- Schema Optimization
- BLOB/Text vs VARBINARY/VARCHAR
- Partition by Key
- Index Selection and Tuning
- Basic Query Tuning
- MySQL Cluster Parameter Tuning Guidelines
- Tools to accelerate application development and testing
Johan Andersson and Mat Keep will be presenting and I’ll be helping with the Q&A.
Tuesday, February 2, 2009: 10:00 CET (Central European Time)/ 09:00 UK/GMT.
Following on from the earlier article on running MySQL Cluster on a single Windows host, this one looks at installing and running over multiple Windows machines.
In this post, the MySQL Cluster database will be split over 3 Windows machines:
- 2 hosts with each running 1 data node
- 3rd host running management node and 2 MySQL Servers
The first step is to download and install the software on each of the Windows hosts. There are 2 main ways that you can install MySQL Cluster on Windows – the first is an MSI installer and the second is extracting the files from a Zip archive – this article focuses on using the Zip file as the single host article used the MSI installer. You can get the Zip archive from the Cluster downloads page on mysql.com
Make sure that you choose the correct file MSI vs. Zip, 32 bit vs. 64 bit. Also make sure that you don’t download the source code rather than the binaries (you can tell the source archive as it’s a much smaller file).
Repeat this on each of the hosts that will run a Cluster node (or download it once and then copy it to each of the hosts).
Extract the contents of the Zip archive to “c:mysqlcluster”.
I prefer to keep the configuration and data files separate from the software and so I create a new folder “MySQL_ClusterMy_Cluster” in my home directory and in there create “conf” and “data” folders. Within the “data” folder on 192.168.0.19, create a sub-folder for each of the 2 MySQL Server (mysqld) processes and then copy “c:mysqlclusterdatamysql” to each – this creates the mysql database for each of the MySQL nodes containing data such as privileges and password.
To get access to the executables from the command line, add “c:mysqlclusterbin” to the PATH environment variable.
Create a “config.ini” file within the “conf” folder that was created on the host to run the management node (ndb_mgmd) – which in this example is 192.168.0.19:
[ndbd default] noofreplicas=2 datadir=E:am233268DocumentsMySQL_ClusterMy_Clusterdata [ndbd] hostname=192.168.0.201 id=2 [ndbd] hostname=192.168.0.4 id=3 [ndb_mgmd] id=1 hostname=192.168.0.19 mysqld] id=101 hostname=192.168.0.19 [mysqld] id=102 hostname=192.168.0.19
As both of the MySQL Server nodes will also run on 192.168.0.19, we create a cnf file for each of them in that same “conf” folder:
[mysqld] ndb-nodeid=101 ndbcluster datadir=E:am233268DocumentsMySQL_ClusterMy_Clusterdatamysqld_1 port=3306 ndb-connectstring=192.168.0.19:1186
[mysqld] ndb-nodeid=102 ndbcluster datadir=E:am233268DocumentsMySQL_ClusterMy_Clusterdatamysqld_2 port=3307 ndb-connectstring=192.168.0.19:1186
Note that the “ndb-connectstring” is not strictly needed as the MySQL Servers happen to be on the same host as the management node but they’ve been included to make it clearer what to change if you moved those nodes to another machine.
The information from this table can help you set up the firewall rules to allow the nodes to communitcate with each other but if possible, turn off the firewall for connections between these 3 nodes.
The next step is to start the nodes (processes) but before then, a hint.
If you run each command from a different command prompt then your screen will quickly fill with Windows. Instead, rather than just typing “c: <command>” use “c: start /B <command>” (for example “c: start /B ndbd –initial”) so that multiple processes can be run from the same Window. To keep things simple, the “start /B” has been skipped for the rest of this article.
The first process to start is the management node; run the following from the “<home>DocumentsMySQL_ClusterMy_Cluster” folder on 192.168.0.19:
192.168.0.19: ndb_mgmd --initial -f conf/config.ini --configdir=./conf
Next, start the data nodes on 192.168.0.201 and 192.168.0.4:
192.168.0.201: ndbd -c 192.168.0.19:1186 --initial 192.168.0.4: ndbd -c 192.168.0.19:1186 --initial
and then finally start the MySQL Server nodes on 192.168.0.19:
192.168.0.19: mysqld --defaults-file=confmy.101.cnf 192.168.0.19: mysqld --defaults-file=confmy.102.cnf
Finally, check that all of the nodes are up and running:
192.168.0.19: ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: localhost:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @192.168.0.201 (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master) id=3 @192.168.0.4 (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.19 (mysql-5.1.39 ndb-7.0.9) [mysqld(API)] 2 node(s) id=101 @192.168.0.19 (mysql-5.1.39 ndb-7.0.9) id=102 @192.168.0.19 (mysql-5.1.39 ndb-7.0.9)
Known limitation of running MySQL Cluster on Windows:
- No angel process for data nodes -> processes will not be automatically restarted even if you set StopOnError to FALSE
- Running nodes as Windows services is not really practical (as well as software limitations, would still need to activate processes through ndb_mgm)
- Safe to run management node or MySQL Servers (or NDB API Apps) on Windows in production: will soon be supported
- See open MySQL Cluster bugs reported for Windows:
- You tell us! Help us get to GA quality on Windows:
- Category = MySQL Cluster
- OS = Microsoft Windows
MySQL have been working on a new way of accessing MySQL Cluster using Java. Designed for Java developers, the MySQL Cluster Connector for Java implements an easy-to-use and high performance native Java interface and OpenJPA plug-in that maps Java classes to tables stored in the high availability, real-time MySQL Cluster database.
There is a series of 2 webinars coming up, as always these are free to attend – you just need to register in advance:
Part 1: Tuesday, February 16, 2010: 10:00 Pacific time
- an overview of the MySQL Cluster Connector for Java
- what these technologies bring to Java developers
- implementation details of the MySQL Cluster Java API and Plug-In for OpenJPA
- configuring the connection to MySQL Cluster
- creating the Java Domain Object Model for your tables
- managing insert, update, and delete operations
- querying the database
- how to get started developing new Java applications using these interfaces
Tue, Feb 16: 08:00 Hawaii time
Tue, Feb 16: 11:00 Mountain time (America)
Tue, Feb 16: 12:00 Central time (America)
Tue, Feb 16: 13:00 Eastern time (America)
Tue, Feb 16: 18:00 UTC
Tue, Feb 16: 18:00 Western European time
Tue, Feb 16: 19:00 Central European time
Tue, Feb 16: 20:00 Eastern European time
Part 2: Wednesday, March 03, 2010: 10:00 Pacific time
- how MySQL Cluster Connector for Java coexists with existing OpenJPA / TopLink / JDBC-based apps
- how to evaluate the MySQL Cluster Connector for Java alternatives
- performance comparisons with both existing Java access and with native NDB API access to MySQL Cluster
- what the future holds for this technology
Wed, Mar 03: 08:00 Hawaii time
Wed, Mar 03: 11:00 Mountain time (America)
Wed, Mar 03: 12:00 Central time (America)
Wed, Mar 03: 13:00 Eastern time (America)
Wed, Mar 03: 18:00 UTC
Wed, Mar 03: 18:00 Western European time
Wed, Mar 03: 19:00 Central European time
Wed, Mar 03: 20:00 Eastern European time
This functionality isn’t yet GA but it is available for you to try and we’d love to get feedback (which you can provide through the MySQL Cluster forum or by emailing firstname.lastname@example.org
If you want to see for yourself then take a look at the Blog entry from Bernhard Ocklin – the engineering manager responsible for this work.