NoSQL with MySQL

Oracle has implemented NoSQL interfaces to the MySQL database and MySQL Cluster. They bypass the SQL layer completely. Without SQL parsing and optimization, data can be written directly to MySQL tables up to nine-times faster, while maintaining ACID guarantees. Moreover, users can still run complex SQL queries across the same data set. In this article, we examine how to implement NoSQL with MySQL 5.6, and give a quick overview of how NoSQL is implemented in MySQL Cluster.

Memcached API with InnoDB

MySQL 5.6 provides simple, direct, key-value interaction with InnoDB data via the familiar Memcached API. Memcached is used by many large Web properties, typically as a key-value pair data caching layer. The API is implemented via a new Memcached daemon plug-in to mysqld. The new Memcached protocol is then mapped directly to the native InnoDB API. This design enables developers to use existing Memcached clients to bypass the expense of SQL and go directly to InnoDB data for lookups and transactionally guaranteed updates. The API makes it possible to re-use standard Memcached libraries and clients, while extending Memcached functionality by integrating a persistent, crash-safe, transactional database back-end. The implementation is shown in Figure 1.

Figure 1: The implementation.

Working example of using Memcached API for InnoDB

An example of a common use case is storing simple user-session data (which can then subsequently be the target of complex SQL-based analytics). Let's walk through an example together:

To begin using the Memcached interface, you will need to take the following steps:

1. Load the Memcached plugin at startup time by adding this option to your my.cnf file:
   
   plugin-load = daemon_memcached=libmemcached.so
   
   You can also load it at runtime this way:
   
   mysql> install plugin daemon_memcached soname "libmemcached.so";
   
   
2. Load the Memcached-specific system schema, where the custom storage mappings between InnoDB and Memcached, along with some behavior configuration, are defined:
   
   mysql> source /usr/share/mysql/innodb_memcached_config.sql
   
   
3. Define your custom data storage mapping:
   
   First, I created a table to store user session key-value pairs (the key will be stored in the id_name field in the "<id>|<name>" format):
   
   mysql> create database myapp;
mysql> create table myapp.user_session_data (id_name varchar(255), value varchar(255), expiretime int, primary key (id_name));
   
   Next, set it up as a valid Memcached container and define the mapping between Memcached and InnoDB:
   
   insert into innodb_memcache.containers values ("sessions", "myapp", "user_session_data", "id_name", "value", 0, 0, "expiretime", "PRIMARY");
   
   
4. Test it out from the command-line (the parameters/format for the raw telnet interface are "<key> <flags> <expiretime> <bytes>" followed by the <value>):
   
   shell> telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
set @@sessions.1|color 0 600 7
black
STORED
set @@sessions.1|size 0 600 7
large
STORED
quit
   
   Now we can see the data persisted within our InnoDB table:
   
   mysql> select * from myapp.user_session_data;
+---------+---------+------------+
| id_name | value   | expiretime |
+---------+---------+------------+
| 1|color | black   | 1389816198 |
| 1|size  | large   | 1389816211 |
+---------+---------+------------+
2 rows in set (0.00 sec)
   
   Again, we're using "<user id>|<key>" for the unique key in our key-value pairs. In the first example, the user/session ID is 1, the key is color, and the value is black.
   
   Note that there is no auto expiration of data based on the expiration time captured via Memcached. In this case, if we wanted that, we would need to enforce the expiration of this data manually by scheduling this query:
   
   mysql> delete from myapp.user_session_data where unix_timestamp(now()) >= expiretime;
   
   
5. Incorporate the new methods into your application. I'll use Java for my example here (I'll also be using Google's memcached client for Java, spymemcached):
   
   

   import java.net.InetSocketAddress;
   import java.util.HashMap;
   import net.spy.memcached.MemcachedClient;
   
   public class MemcachedTest{
   
   public static void main( String[] args ){
       MemcachedClient mc = null;
       int id = 2;
       String mysqlMemcachedMappingName = "@@sessions.";
   
       try{
              mc = new MemcachedClient( 
                        new InetSocketAddress( "127.0.0.1", 11211 ) );
              mc.set( mysqlMemcachedMappingName + id + "|color", 3600, "red" );
              String gColor = (String) mc.get( 
                        mysqlMemcachedMappingName + id + "|color" );
              System.out.println( "Color: " + gColor );
   
              mc.shutdown();
            } catch (Exception e) {
              System.err.println( "Error: " + e.toString() );
            } finally {
              mc = null;
            }
      }
   }
   

6. Test the new application:
   
   shell> java MemcachedTest
2014-02-03 15:36:13.038 INFO net.spy.memcached.MemcachedConnection:  Added {QA sa=/127.0.0.1:11211, #Rops=0, #Wops=0, #iq=0, topRop=null, topWop=null, toWrite=0, interested=0} to connect queue
2014-02-03 15:36:13.041 INFO net.spy.memcached.MemcachedConnection:  Connection state changed for sun.nio.ch.SelectionKeyImpl@7a542594
Color: red
2014-02-03 15:36:13.109 INFO net.spy.memcached.MemcachedConnection:  Shut down memcached client
   
   And now we can see the data persisted in the InnoDB table:
   
   mysql> select * from myapp.user_session_data where id_name = "2|color";
+---------+-------+------------+
| id_name | value | expiretime |
+---------+-------+------------+
| 2|color | red   | 1391463373 |
+---------+-------+------------+
1 row in set (0.00 sec)

Performance for InnoDB Memcached API

To understand the performance comparison for storing this session data, see Figure 2, which shows the results of an insert benchmark run by Oracle (on an 8-core Intel server with 16GB of RAM, running Oracle Linux).

Figure 2: NoSQL inserts can be as much as 9x faster than using SQL.

Not only do developers and DBAs obtain increased performance and flexibility, they also reduce complexity. They can compress previously separate caching and database layers into a single data management tier, as well as eliminate the overhead of maintaining cache consistency.