Skip to content

MySQL Concepts in NDB storage engine#

There are quite a number of different concepts that exists in MySQL. These concepts are developed such that they work for any storage engine. There is one thing that differs with NDB compared to a traditional MySQL storage engine. This is the fact that the NDB storage engine is connected to a cluster. In this cluster there can be many more MySQL Servers, creating something in one MySQL Server doesn’t automatically create it in all MySQL Servers connected to the cluster. In this chapter we will discuss many of those concepts and how they work in RonDB.

MySQL Queries#

Queries are the normal SELECT, INSERT, UPDATE, DELETE queries and a few more special query variants. These queries are used to read and write the data. This is an area where the syntax used to query tables in e.g. InnoDB and in NDB is the same. The performance characteristics will differ and as mentioned the consistency model differs, but a query executed will be executable towards NDB and other storage engines. A query is executed in one MySQL Server and as soon as it has executed it is gone, there is no relation to other MySQL Servers for the query.

MySQL Databases#

MySQL Databases are created from the MySQL Server. A database is automatically created in the cluster when it is created in one MySQL Server. Under the hood there is no special handling of different databases in RonDB. The database is present in the internal table name of tables in RonDB. This is the only handling of databases in RonDB.

MySQL Triggers#

Triggers comes into play with writes to the database. A trigger is fired and completely handled within one MySQL Server. This is the case for RonDB.

RonDB does currently not propagate the trigger definition to all other MySQL Servers. If one wants a trigger to be executed on the table in the cluster it is necessary to add the trigger on all MySQL Servers in the cluster.

MySQL triggers is something to be a bit careful with. The reason is that it is only executed in MySQL Servers. Given that tables in RonDB can also be accessed using the NDB API, ClusterJ and other direct APIs, it is important to consider this when using triggers.

If triggers are used to maintain some sort of constraint, this constraint must be maintained also by direct RonDB applications that are not using the MySQL Servers.

MySQL Views#

Views are a method to make it appear as if a table exists that is only a view of the real data. Views could be used for security reasons or to make it simpler to access data.

Views works perfectly fine with RonDB but it is important to remember that if a view is used in all MySQL Servers in the cluster, it has to be defined in all MySQL Servers in the cluster.

If a round robin router is used in front of the MySQL servers and the queries use a view it places special requirements that one has ensured that all those views are created in all MySQL Servers before they are used.

There are no problems related to views for the cluster, the views will not be seen from the NDB API, the NDB API will only see the real tables. A view is used when executing a query to translate the query into a more complex query. Thus the view is only related to the execution of a specific query. Thus views works fine with NDB as for any other storage engine. The only thing to remember is that a view is local to one MySQL Server and is not automatically replicated to other MySQL Servers in the same cluster.

MySQL Events#

Events are a method to have special actions happen at predefined times. This again works perfectly fine with RonDB.

In this case it is not necessarily a good idea to install the event on all MySQL Servers. Events are often some actions that need to be taken at certain intervals. The action is quite likely an action for the cluster and not for one specific MySQL Server.

Since events often need to execute once per time period for a cluster it seems more likely that it is necessary to install the event in a few MySQL Servers. At the start of the event processing one could check some special event table that ensures that the event is only executed once per time period. In this manner the MySQL Servers will discover that someone else already has executed the event.

It is fairly straightforward to handle MySQL events in RonDB and it is fairly easy to even make events such that they are highly available.

MySQL Stored Procedures#

Stored procedures is defined per MySQL Server. If it is needed in all MySQL Servers, it has to be created in all MySQL Servers.

Defining a stored procedure is local to the MySQL Server it is executed on. There is nothing special with stored procedures for RonDB, they are executed as any other stored procedure in MySQL. A stored procedure is a language to execute several SQL queries with one query call. The interpreter for this SQL language executes within one MySQL Server.

The impact on RonDB by stored procedures is the same as if there was an application program executing multiple SQL queries towards one MySQL Server.

MySQL Functions#

MySQL functions is very similar to stored procedures. There is nothing special about using MySQL functions for RonDB compared to a MySQL Server using InnoDB.

MySQL User Management#

User management in RonDB can be handled as a cluster-wide property. It is also possible to handle it separately for each MySQL Server in the cluster.

To prepare the cluster for distributed user management, the user table have to be converted to an RonDB table. We won’t go through the details of how to do this in this book, check the MySQL manual if you want to use distributed user management.

MySQL Transactions#

Executing transactions in MySQL translates into transactions in RonDB. Any transaction executed in RonDB involving only RonDB tables will be executing a distributed transaction in the RonDB data nodes. There is no support for transactions with multiple storage engines in one transaction.

Savepoints#

In MySQL we can declare savepoints in a transaction that we can roll back to. This is not supported for transactions involving RonDB tables, a rollback will always roll back the transaction entirely.

MySQL Prepared Statements#

Prepared statements are a way to avoid parsing and optimizing a query each time it is executed. This means that the MySQL Server will store a part of the query plan for the execution of the query and use this the next time the user wants to execute this query. This works fine for RonDB tables, there is nothing special about RonDB here. Each MySQL Server prepares queries independent of all other MySQL Servers in the cluster.

MySQL Explain statements#

MySQL has a special statement called EXPLAIN. This statement is used by the database developer to understand how a specific query is executed. There are some variants that are specific to RonDB using RonDB tables. One thing is that we can pushdown a join to the cluster. This means that a large part of the join execution is handled in the RonDB data nodes.

Explains list one line per table in a join operation and lists how this table is accessed and how it is joined to the other tables. A set of tables can be pushed down as a whole to the NDB storage engine such that they are executed together. If this pushes down evaluation of some conditions the speedup can be quite significant.

There are other things such as condition pushdown, execution of batched key access and many other techniques that will have a major impact on query execution time and that can be viewed in the EXPLAIN statement. There are many more details on this in the MySQL manual that explains in more detail how the EXPLAIN command works in RonDB.

Table locks#

At the moment the lock tables and unlock tables are only handled locally in one MySQL Server. We currently do not support lock tables that lock tables over the entire cluster. It only locks it within one MySQL Server. The only safe way of excluding access to a table is to use the single user mode described in the chapter on RonDB management client.

XA support#

RonDB doesn’t support running transactions in XA level.