MySQL NoSQL APIs

NoSQL APIs
Morgan Tocker 
MySQL Community Manager

Copyright © 2014, Oracle and/or its affiliates. All rights reserved.!2
The following is intended to outline our general product direction. It is intended  
for information purposes only, and may not be incorporated into any contract.  
It is not a commitment to deliver any material, code, or functionality, and should
not be relied upon in making purchasing decisions. The development, release,
and timing of any features or functionality described for Oracle’s products
remains at the sole discretion of Oracle.

MySQL 5.5
MySQL Enterprise Monitor 2.3 & 3.0
MySQL Enterprise Backup
Security
Scalability
HA
Audit
MySQL 5.6
MySQL Workbench 6.1
MySQL Utilities
MySQL Applier for
Hadoop
MySQL Workbench 5.2 & 6.0
MySQL Enterprise Oracle
Certifications
MySQL@Oracle: 4 Years of MySQL Innovation
MySQL Cluster Manager
Windows installer & Tools
MySQL Cluster 7.2
MySQL Cluster 7.1
MySQL Migration
Wizard
MySQL 5.7
MySQL Cluster 7.4
MySQL Fabric
MySQL Cluster 7.3

Discussion Topics
▪ What is memcached?
▪ History of HandlerSocket
▪ NoSQL Interface in MySQL 5.6
▪ Future in MySQL 5.7

What is memcached?

What do we know about memcached?
▪ Simple in-memory caching system.
▪ APIs available for most programming languages.
▪ Stores data in memory.
▪ Excellent read performance.
▪ Great write performance.
▪ Supports distribution across multiple servers.
▪ No security.
Good Introduction:
https://code.google.com/p/memcached/wiki/NewProgrammingFAQ

Common Configuration
▪ As a look-aside cache: 
 
 
 
 
 
 
 
 
▪ Also possible to rebuild cache from cron/inject new cached data.
value = get_from_memcache(key); 
if (value === null) { 
value = get_value_from_database(key); 
set_memcache(key, value) 
}

Scaling
▪ Hashing is performed by the clients.
▪ Mod-based hashing.
▪ Consistent hashing.
▪ No cross talk by memcached servers.

History of HandlerSocket

The beginning
▪ Yoshinori Matsunobu working at DeNA.
▪ Profiled MySQL and discovered a lot of time spent in:
▪ Parsing SQL statements
▪ Opening, locking tables
▪ Making SQL execution plans
▪ Unlocking, closing tables
http://yoshinorimatsunobu.blogspot.ca/2010/10/using-mysql-as-nosql-story-for.html

The Question
▪ Is SQL really good for fast PK lookups?
▪ MySQL could only perform 100,000+ queries per second
▪ Memcached 400,000+ gets per second.
▪ Is it possible to bypass some parts of MySQL and get higher
throughput?

Implementation
Credits: http://yoshinorimatsunobu.blogspot.ca/2010/10/using-mysql-as-nosql-story-for.html

The Result
!
approx qps server CPU util
MySQL via SQL 105,000 %us 60% %sy 28%
memcached 420,000 %us 8% %sy 88%
MySQL via HandlerSocket 750,000 %us 45% %sy 53%

Outcome
▪ Benefits:
▪ No inconsistent data.
▪ Crash-safe storage
▪ SQL can still be used.
▪ All operational benefits of
MySQL (backup, etc.)
!
!
▪ Limitations:
▪ New API
▪ No Security
▪ No Benefit for HDD bound
workloads
▪ In the case of DeNA:
▪ Switched from using memcached as a row cache.
▪ Kept memcached as a page/larger object cache.

NoSQL Interface in MySQL 5.6

MySQL 5.6
▪ Builds on the spirit of HandlerSocket.
▪ New implementation based on the standard memcached protocol.
▪ From a technical detail actually embeds memcached, but your
data remains safe between restarts.
▪ i.e. not just in memory.

Architecture

Implementation Details
▪ Plugin ships included in MySQL packages.
▪ Not enabled by default (security).
▪ Tables have to “opt-in” to be exposed via memcached.

Setup (1/3)
# Add this to my.cnf file:
!
plugin-load = daemon_memcached=libmemcached.so
!
# Load included SQL file to initialize innodb_memcached
# schema with permitted mappings.
!
mysql> source /usr/share/mysql/innodb_memcached_config.sql

Setup (Part 3/3)
# Pretend this is my hypothetical table:
!
mysql> create database myapp;
mysql> create table myapp.user_session_data (id_name
varchar(255), value varchar(255), expiretime int, primary
key (id_name));
!
# Insert your desired mapping into the containers table:
!
mysql> insert into innodb_memcache.containers values
("sessions", "myapp", "user_session_data", "id_name",
"value", 0, 0, "expiretime", "PRIMARY");

Setup - Complete
mysql> select * from containersG
..
*************************** 2. row ***************************
name: sessions
db_schema: myapp
db_table: user_session_data
key_columns: id_name
value_columns: value
flags: 0
cas_column: 0
expire_time_column: expiretime
unique_idx_name_on_key: PRIMARY

Testing
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 5
black
STORED
set @@sessions.1|size 0 600 5
large
STORED
quit

Testing (cont.)
mysql> select * from user_session_dataG
*************************** 1. row ***************************
id_name: 1|color
value: black
expiretime: 1398608066
*************************** 2. row ***************************
id_name: 1|size
value: large
expiretime: 1398608106

Updating Multiple Columns At Once
▪ Requires Serialization using the “|” character:
DROP TABLE myapp.users;
CREATE TABLE myapp.users (
`user_name` varchar(60) NOT NULL PRIMARY KEY,
`first_name` varchar(60),
`last_name` varchar(60),
expire_time_column INT
) ENGINE=InnoDB;
!
!
insert into innodb_memcache.containers
(name,db_schema,db_table,key_columns,value_columns,unique_idx_nam
e_on_key) VALUES (‘users’,'myapp','users','user_name',
'first_name,last_name','PRIMARY');

Benchmarks

Future in MySQL 5.7

What has changed?
▪ Improvements inside of InnoDB to scale better.
▪ Several contentions related to MVCC code reduced.
▪ memcached plugin tuned to keep several reads within a single
internal transaction.

Benchmarks
48 Core Machine / Read Only Memcached
http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html

Further Reading
▪ Article by Matt Lord and Andrew Morgan: 
http://www.drdobbs.com/database/nosql-with-mysql/240167115
!
▪ MySQL Manual page: 
https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-
setup.html

MySQL NoSQL APIs

More Related Content

What's hot

What's hot (20)

Viewers also liked

Viewers also liked (20)

Similar to MySQL NoSQL APIs

Similar to MySQL NoSQL APIs (20)

More from Morgan Tocker

More from Morgan Tocker (8)

Recently uploaded

Recently uploaded (20)

MySQL NoSQL APIs