Comparison of Limitations b/w MySQL Cluster 7.3.4, MySQL Fabric 1.5 and Spider Engine
| Parameters | MySQL Cluster 7.3.4` | MySQL Fabric 1.5 | Spider Engine |
| High Availability | Yes | Yes | Yes (little less than others) |
| Sharding | Yes ( Auto) | Yes | Yes |
| Read/Write Speed | Highest | Moderate | Lowest (among 3) |
| Cross-Shard Query | User don’t have to worry about the shards | Not possible | Yes (automatically handled) |
| Implementation Cost | High (Very High) | Low | Low |
| Implementation | Tough | moderate | Easy |
| Configuration | Tough | Moderate | Easy |
| RAM Required | Very High | Moderate | Low |
| Complex Queries | Joins and foreign key can’t be implemented | Joins can’t be implemented | Same as Normal SQL |
| Others | Maximum Column in a table – 128Maximum Size of a row- 8KBMaximum number of objects(Indexed, Column, Table and Databases)-20,120 |
MySQL Cluster 7.3.4
Operating System requirements: MySQL Cluster runs on several operating systems, and is specifically supported by the following:
- Linux (Red Hat and SUSE)
- Solaris
- Mac OS X
- Windows
When considering which of the supported operating systems to use, it is worth noting that far and away the most tested is the Linux operating system.
Limitations using indexes: There are some common limitations related to indexes inside MySQL Cluster tables as follows:
- Full-text indexes do not work with MySQL Cluster. Consider using Sphinx and / or a separate table for your full-text searches, possibly using another high-availability technique such as MySQL Replication in order to run your intensive search queries against a replica without affecting performance on the master.
- Text or BLOB fields cannot have indexes (however, VARCHAR fields can).
- You may only have one AUTO_INCREMENT field per table. If your table does not already have a primary key when it was created or altered to MySQL Cluster, a hidden AUTO_INCREMENT primary key field will be created (and used for partitioning). If this happens, you will not be able to create another AUTO_INCREMENT field, even though you cannot see the one that exists. Therefore, ensure that you always define a primary key in your tables (which are often the AUTO_INCREMENT fields). One of the key differences between MySQL Cluster and InnoDB tables is that for clustered tables in InnoDB, foreign key constraints are simply ignored (this is the same behavior with the MyISAM storage engine).
Limitations using transactions: While MySQL Cluster is transactional, in general,
it does not support very large individual transactions particularly well.
The limit is difficult to quantify and depends on node performance, network connections, and number of transactions. However, in general, applications that use larger numbers of smaller transactions are more likely to experience fewer problems with MySQL Cluster. Therefore, if you have the choice, design the application that is
to use MySQL Cluster for lots of small transactions wherever possible.
Common “Hard Limits” to reach: The following list of unchangeable limitations does vary significantly from release to release, but the limitations of the current version (that is, MySQL Cluster 7) are as follows:
- The total number of objects (databases, tables, and indexes) cannot exceed 20320
- The total number of attributes (columns and indexes) per table cannot exceed 128
- The total size of a row cannot exceed 8 KB
- The total number of storage nodes in a cluster cannot exceed 48
- The total number of nodes (storage, management, and SQL) cannot exceed 255
Networking requirements: MySQL Clusters require inter-cluster network traffic to have extremely low latency (small round trip (ping) times) and almost no packet loss.
If this is not the case, performance will generally be extremely poor and it is possible that nodes will continually be kicked out of the cluster for not replying to heartbeat packets from other nodes quickly enough. To achieve these requirements, it is desirable for all members of the cluster to be interconnected using the same
switch infrastructure, which should have a speed of at least one gigabit.
Any network design involving a layer-3 device (such as a router) should be avoided wherever possible (although with modern wire speed, layer 3 forwarding for network devices can be as fast as layer 2). It is not recommended to attempt to get a cluster to work over a large network such as the Internet.
While technically not truly impossible, it is strongly recommended that you do not attempt to change the timeout values to configure a cluster over a high-latency link, as this won’t really work properly!
If there is a need to replicate data across a WAN, consider replication between clusters (covered in the Replication between MySQL Clusters recipe in Chapter 3). It is possible to use high-speed cluster interconnects or Unix-like shared-memory segments, which themselves provide for extremely low-latency and high-reliability links.
System RAM requirements and best practice: MySQL Cluster is extremely
memory-intensive. Although, actual data can be stored both on disk and in memory (RAM), the performance of data in memory tables is, in most cases, better when compared with disk-based tables in terms of order of magnitude.
Furthermore, even for disk-based tables, indexes (which can take up a significant amount of space) must still be stored in memory. Therefore, the RAM usage on data nodes is high, and the overall RAM requirement for a cluster is likely to be order of magnitude more than that required by a standalone MySQL server using InnoDB
or MyISAM.
MySQL Fabric
The initial version of MySQL Fabric is designed to be simple, robust and able to scale to thousands of MySQL Servers. This approach means that this version has a number of limitations, which are described here:
- Sharding is not completely transparent to the application. While the application need not be aware of which server stores a set of rows and it doesn’t need to be concerned when that data is moved, it does need to provide the sharding key when accessing the database.
- Auto-increment columns cannot be used as a sharding key
- All transactions and queries need to be limited in scope to the rows held in a single shard, together with the global (non-sharded) tables. For example, Joins involving multiple shards are not supported
- Because the connectors perform the routing function, the extra latency involved in proxy-based solutions is avoided but it does mean that Fabric-aware connectors are required – at the time of writing these exist for PHP, Python and Java
- The MySQL Fabric process itself is not fault-tolerant and must be restarted in the event of it failing. Note that this does not represent a single-point-of-failure for the server farm (HA and/or sharding) as the connectors are able to continue routing operations using their local caches while the MySQL Fabric process is unavailable
Spider Engine:
Advantages and disadvantages of Spider SE
Advantages
- Divide the load among many database servers (scaling reads and writes) and processing in parallel.
- Independent from the application (storage layer).
- Easy to extend. When introducing a new host, a new partition needs to be added to the spider table.
- Creating non Spider SE tables is also possible on the Spider bundled server.
- If a shard failed, the application won’t be affected except for those queries which targeting that failed shard.
- Spider SE was introduced in MariaDB starting from version 10.0.4.
- A slave for the Spider server can be created to have all data in one server (XA transaction must be enabled).
- Spider supports SSL.
Disadvantages
- Query cache and FULL TEXT indexes are not supported in Spider tables. However, FULL TEXT indexes can be added on the individual backend tables.
- Physical backups (like Xtrabackup) won’t backup the data on the spider tables as the physical data is stored on the shards (shards can be backed up individually).
- Spider server is a single point of failure (we might need to make it High Available by duplicating it and using VIP on the top of them).
- Depends on MySQL Partitioning, which means choosing wrong partition key or condition might kill the performance.
- Also as a result of using MySQL Partitioning, foreign keys are not supported.
Taken Help From/ Source: