With its distributed, shared-nothing, real-time design, MySQL Cluster has attracted a lot of attention from developers who need to scale both read and write traffic with ultra-low latency and fault-tolerance, using commodity hardware. With many proven deployments in web, gaming, telecoms and mobile use-cases, MySQL Cluster is certainly able to meet these sorts of requirements.
But, as a distributed database, developers do need to think a little differently about data access patterns along with schema and query optimisations in order to get the best possible performance.
Sharing best practices developed by working with MySQL Cluster's largest users, we recently ran a Performance Essentials webinar, and the replay is now available, on-demand, for you to listen to in the comfort of your own office.
The webinar also accompanies a newly published Guide to optimizing the performance of MySQL Cluster.
We received a number of great questions over the course of the webinar, and I thought it would be useful to share a selection of those:
Q. How do I calculate and then monitor memory usage with MySQL Cluster?
A. If designing a completely new database, the following calculations can be used to help determine the approximate memory sizing requirements for the data nodes:
(in memory) Data Size * Replicas * 1.25 = Total Database Memory Requirements
Example: 50 GB * 2 * 1.25 = 125 GB
(Data Size * Replicas * 1.25)/Nodes = RAM Per Node
Example: (2 GB * 2 * 1.25)/4 = 31.25 GB
To see how much of the configured memory is currently in use by the database, you can query the ndbinfo.memory usage table
If using MySQL Cluster CGE then you can view this information over time in a MySQL Enterprise Monitor graph.
Q. Would enabling Disk space Table Space be an impact on the Query Performance ?
A. It can do. The only reason to use Disk based table spaces is when you do not have sufficient memory to store all data in-memory. Therefore some of your disk based data will be uncached at some time, and reads or writes which access this data will stall while the necessary pages are read into the page buffer. This can reduce throughput.
Q. I've seen that MySQL Cluster 7.2 can speed up JOIN operations by 70x. How does it do this?
A. There are two new features in MySQL Cluster 7.2, which when combined, can significantly improve the performance of joins over previous versions of MySQL Cluster:
- The Index Statistics function enables the SQL optimizer to build a better execution plan for each query. In the past, non-optimal query plans required a manual enforcement of indexes via USE INDEX or FORCE INDEX to alter the execution plan. ANALYZE TABLE must first be run on each table to take advantage of this.
- Adaptive Query Localization (AQL) allows the work of the join to be distributed across the data nodes (local to the data it’s working with) rather than up in the MySQL Server; this allows more computing power to be applied to calculating the join as well as dramatically reducing the number of messages being passed around the system.
You can learn more about AQL and a sample query here
Q. Can all JOINs use AQL?
A. In order for a join to be able to exploit AQL (in other words be “pushed down” to the data nodes), it must meet the following conditions:
1. Any columns to be joined must use exactly the same data type. (For example, if an INT and a BIGINT column are joined, the join cannot be pushed down). This includes the lengths of any VARCHAR columns.
2. Joins referencing BLOB or TEXT columns will not be pushed down.
3. Explicit locking is not supported; however, the NDB (MySQL Cluster) storage engine's characteristic implicit row-based locking is enforced.
4. In order for a join to be pushed down, child tables in the Join must be accessed using one of the ref, eq_ref, or const access methods, or some combination of these methods. These access methods are described in the documentation
5. Joins referencing tables explicitly partitioned by [LINEAR] HASH, LIST, or RANGE currently cannot be pushed down
6. If the query plan decides to 'Using join buffer' for a candidate child table, that table cannot be pushed as child. However, it might be the root of another set of pushed tables.
7. If the root of the pushed Join is an eq_ref or const, only child tables joined by eq_ref can be appended. (A ref joined table will then likely become a root of another pushed Join)
These conditions should be considered when designing your schema and application queries – for example, to comply with constraint 4, attempt to make any table scan that is part of the Join be the first clause.
Where a query involves multiple levels of Joins, it is perfectly possible for some levels to be pushed down while others continue to be executed within the MySQL Server.
If your application consists of many of these types of JOIN operations which cannot be made to exploit AQL, other MySQL storage engines such as InnoDB will present a better option for your workload.
Q. What are best practices for data model and query design?
A. The data model and queries should be designed to minimize network roundtrips between hosts. Ensuring that joins meet the requirements for
AQL and avoiding full table scans can help with this.
Looking up data in a hash table is a constant time operation, unaffected by the size of the data set
Looking up data in a tree (T-tree, B-tree etc) structure is logarithmic (O (log n)).
For a database designer this means it is very important to choose the right index structure and access method to retrieve data. We strongly recommend application requests with high requirements on performance be designed as primary key lookups. This is because looking up data in a hash structure is faster than from a tree structure and can be satisfied by a single data node. Therefore, it is very important that the data model takes this into account. It also follows that choosing a good primary key definition is extremely important.
If ordered index lookups are required then tables should be partitioned such that only one data node will be scanned.
The distributed nature of the Cluster and the ability to exploit multiple CPUs, cores or threads within nodes means that the maximum performance will be achieved if the application is architected to run many transactions in parallel. Alternatively you should run many instances of the application simultaneously to ensure that the Cluster is always able to work on many transactions in parallel.
Take a look at the Guide to optimizing the performance of MySQL Cluster for more detail
Q. What are best practices for parallelising my application and access to MySQL Cluster?
A. As mentioned MySQL Cluster is a distributed, auto-sharded database. This means that there is often more than one Data Node that can work in parallel to satisfy application requests.
Additionally, MySQL Cluster 7.2 enhances multi-threading so data nodes can now effectively exploit multiple threads / cores. To use this functionality, the data nodes should be started using the ndbmtd binary rather than ndb and config.ini should be configured correctly
Parallelization can be achieved in MySQL Cluster in several ways:
- Adding more Application Nodes
- Use of multi-threaded data nodes
- Batching of requests
- Parallelizing work on different Application Nodes connected to the Data Nodes
- Utilizing multiple connections between each Application Node and the Data Nodes (connection pooling)
How many threads and how many applications are needed to drive the desired load has to be studied by benchmarks. One approach of doing this is to connect one Application Node at a time and increment the number of threads. When one Application Node cannot generate any more load, add another one. It is advisable to start studying this on a two Data Node cluster, and then grow the number of Data Nodes to understand how your system is scaling.
If you have designed your application queries, and data model according to best practices presented in the Performance Guide, you can expect close to double the throughput on a four Data Node system compared to a two Data Node system, given that the application can generate the load.
Try to multi-thread whenever possible and load balance over more MySQL servers.
In MySQL Cluster you have access to additional performance enhancements that allow better utilization on multi-core / thread CPUs, including:
- Reduced lock contention by having multiple connections from one MySQL Server to the Data Nodes (--ndb-cluster-connection-pool=X):
- Setting threads to real-time priority
- Locking Data Node threads (kernel thread and maintenance threads to a CPU)
Q. Does MySQL Cluster’s data distribution add complexity to my application and limit the types of queries I can run?
A. No, it doesn't. By default, tables are automatically partitioned (sharded) across data nodes by hashing the primary key. Other partitioning methods are supported, but in most instances the default is acceptable.
As the sharding is automatic and implemented at the database layer, application developers do not need to modify their applications to control data distribution – which significantly simplifies scaling.
In addition, applications are free to run complex queries such as JOIN operations across the shards, therefore users do not need to trade functionality for scalability.
Q. What hardware would you recommend to get the best performance from MySQL Cluster?
A. It varies by node type. For data nodes:
- Up to 32 x x86-64 bit CPU cores. Use as high a frequency as possible as this will enable faster processing of messages between nodes;
- Large CPU caches assist in delivering optimal performance;
- 64-bit hosts with enough RAM to store your in-memory data set
- Linux, Solaris or Windows operating systems.
- 2 x Network Interface Cards and 2 x Power Supply Units for hardware redundancy.
It is important to ensure systems are configured to reduce swapping to disk whenever possible.
As a rule of thumb, have 7x times the amount of DataMemory configured for disk space for each data node. This space is needed for storing 2 Local Checkpoints (LCPs), the Redo log and 3 backups. You will also want to allocate space for table spaces if you are making use of disk-based data – including allowing extra space for the backups.
Having a fast, low-latency disk subsystem is very important and will affect check pointing and backups.
Download the MySQL Cluster Evaluation Guide for more recommendations
The hardware requirements for MySQL Servers would be a little less:
- 4 - 32 x86-64 bit CPU cores
- Minimum 4GB of RAM. Memory is not as critical at this layer, and requirements will be influenced by connections and buffers.
- 2 x Network Interface Cards and 2 x Power Supply Units for hardware redundancy.
Q. I heard that MySQL Cluster doesn't support Foreign Keys, how can I get around that?
A. Foreign keys are previewed in MySQL Cluster 7.3 Early Access release which you can download and evaluate now. In MySQL Cluster 7.2 and earlier, you can emulate foreign keys programmatically via triggers.
Summary
If you are thinking about using MySQL Cluster for your next project, it is worth investing a little bit of time to get familiar with these performance best practices. The Webinar replay, the MySQL Cluster Performance Guide and the MySQL Cluster Evaluation Guide will give you pretty much everything you need to build high performance, high availability services with MySQL Cluster.