Channels ▼
RSS

Database

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.

NoSQLandMySQL
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).

NoSQLandMySQL
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.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video