Transitioning from MySQL to MemSQL¶
Because MemSQL is wire-compatible with MySQL, transitioning your data and application from MySQL to MemSQL is very straightforward. Once you migrate your data via standard MySQL tools, all it takes to get your app running is to point it at MemSQL.
For simplicity, this guide assumes that both MySQL and MemSQL are running on your local machine and that MySQL is running on the standard port (3306) while MemSQL runs on 3307. Furthermore, it also assumes that both are accessible without a password by the root user.
Migrating Data with mysqldump¶
mysqldump is a popular tool packaged with the MySQL client infrastructure. It queries the database to produce a series of CREATE TABLE and INSERT statements that can be replayed to restore the database.
While moving your data from MySQL to MemSQL, there are a few considerations to keep in mind:
- For most MySQL storage engines (MyISAM, InnoDB, etc), indexes are stored as B-trees. In MemSQL, indexes can be unidirectional lock-free skip lists (ascending or descending) or lock-free hash tables. Picking the right data structure for your index can have a significant impact on the performance of your application. While hash tables are optimized for key-value look ups, skip lists are extremely flexible for complex range scans and sorts (ORDER BY). While transferring your schema, you should audit your table definitions and investigate whether your indexes can be optimized for MemSQL. The default BTREE notation is converted into an ascending skip list. If you need to do two-way range scans on a column, you should consider adding both an ascending and descending index. You can find more information on the indexes page.
- Because of code generation, loading a schema into MemSQL for the first time is slower than with MySQL. The first time MemSQL encounters a table schema, it generates and compiles code that implements infrastructure around the table (memory allocation, inserts, deletes, iterations, etc.). Once a table is compiled, MemSQL will be able to reuse it for the lifetime of your application — even if you restart the server or drop (and recreate) the table. The INSERT queries generated by mysqldump also have to be compiled exactly once.
- mysqldump will generate a few queries that are unsupported by MemSQL. For example, MemSQL does not support disabling UNIQUE_CHECKS. To make it easier to work with mysqldump, unsupported features are by default reported as warnings instead of errors. This functionality can be controlled by adjusting the warn_level variable. See Unsupported Features for more details. Some components of a CREATE TABLE statement might be blocked completely. If you run into this issue while loading a schema into MemSQL, you can manually massage the schema definition into something supported by MemSQL.
- If the machine running MemSQL does not have enough memory to support the data you’re loading, the server will issue an error on offending INSERT statements indicating its out-of-memory state. In this case, you should upgrade your machine to one with more memory. If you copy your existing memsqlbin directory to the new machine, MemSQL will be able to reuse the schema definitions and INSERT statements that have already compiled. If you are using MemSQL Developer Edition and have hit the maximum_table_memory limit of 10GB, then you should visit memsql.com/next to learn about upgrading.
You should separate your schema and data into separate files, so that you can easily review and modify your schema if necessary. To produce a dump of your database, run something like
$ mysqldump -h 127.0.0.1 -u root -B [database name] --no-data > schema.sql $ mysqldump -h 127.0.0.1 -u root -B [database name] --no-create-info > data.sql
You can then replay these files directly into MemSQL by running:
$ mysql -h 127.0.0.1 -u root -P 3307 < schema.sql $ mysql -h 127.0.0.1 -u root -P 3307 < data.sql
While this step runs, you can observe the memsql_tracelog (also printed to stderr) to see which unsupported features have been ignored. After the import is completed, you can connect to MemSQL and start querying the tables directly.
Transitioning Your Application¶
To transition your application, simply change the connection credentials to point to MemSQL.
Even if you’re connecting to MemSQL locally, use the explicit host 127.0.0.1 instead of localhost. Most MySQL clients will resolve localhost to use the global MySQL socket file and ignore the port setting. See this page for more information.