Mysql User Camp : 20th June - Mysql New Features

Insert Picture Here
What's new in MySQL 5.7
and
NoSQL Support in MYSQL
Sudipto Sahoo
Senior MySQL Engineer

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.

Program Agenda
§ What is MySQL ?
§ What’s New in MySQL 5.7
§ What is NoSQL?
§ NoSQL Support in MySQL
§ Q & A

Insert Picture Here
What is MySQL ?

§“MySQL is the world's most widely used open
source relational database management
system (RDBMS) that runs as a server
providing multi-user access to a number of
databases”

What is MySQL ?
§ Robust ACID compliant RDBMS
§ Open Source (GPL v2 + proprietary)
§ Used by some of the largest web properties in the world
§ Abundantly present in all major Linux distributions and hosting providers
§ Properly documented and professionally supported by Oracle
§ Simple to get and easy to use

Insert Picture Here
What’s new in MySQL 5.7 ?

Raising The Bar
Again and Again, Evolving MySQL for You

MySQL 5.7: DMR 4
 InnoDB for better transactional throughput, availability, IO
 Replication for better scalability and availability
 Utilities for dev/ops automation
 Performance Schema for better performance metrics
 Optimizer for better EXPLAINing, query performance, enhanced buffering and partition
optimization
 Connecting at higher rates, improve session efficiency
http://mysqlserverteam.com/the-mysql-5-7-4-milestone-release-is-available/

MySQL 5.7: InnoDB
 Improved InnoDB Online Alter Table
– Online Rename Index, Online Change Varchar
 Parallel “Dirty Page” Flushing
– Higher throughput, performance, and scalability
 Partitions – support for Transportable Tablespaces (TTS)
– TTS support for individual partitions
Many Improvements

MySQL 5.7: InnoDB Temp Tables
 New Separate tablespace for temporary tables
– One of the goals of 5.7 is to optimize InnoDB temp tables for better performance.
With the following work done in 5.7.2 it is about 11X faster for CREATE/DROP
temp table and about 2-4X faster for INSERT/DELETE/UPDATE on temp tables,
as compared with 5.6.

MySQL 5.7: Improved MDL locking
 Removes bottlenecks around DML access to a single table
– 10% increased throughput in OLTP_RO/POINT_SELECT sysbench tests
on higher core counts
– Optimized for typical DML heavy workloads
 Implemented fast-path for DML locks
 Implemented lock-free DML lock acquisition
 Implemented a lock-free hash
– Now uses MurmurHash library

MySQL 5.7.4: Replication
 Higher throughput
– Slave : applies transactions in parallel even within same database
– Change master without stopping SQL thread
– Master: better synchronization between replication-user sessions
 Lossless Replication through enhanced Semi-sync
– Make the master wait for more than one slave to acknowledge back
– Semisync separate acks collector
 Performance Schema tables for monitoring slave
 Dynamic Replication Filters: change filters on the fly
Better Performance, Improved Usability and Enhanced HA

Multi-Source Replication
 Consolidate updates from multiple
Masters into one Slave
– Consolidated view of all shards
– More flexible topologies
– Centralized point for backups, easier
reporting
 Compatible with Semi-Synchronous
Replication & enhanced MTS
 Master-specific slave filters planned
for GA
 Application must keep data sets
disjoint between sources
Slave
labs.mysql.com

MySQL Multi-Threaded Slave
 The evolution…
– 2010: Inter-schema MTS (labs)
– 2013: Inter-schema MTS (5.6 GA)
– 2013: Intra-schema MTS (5.7.2
DMR)
– Today: Tune size of group commit
buckets - trading commit latency
on master (labs)
 6X slave throughput
Slave keeps pace with the master
labs.mysql.com
0
500
1000
1500
2000
2500
3000
3500
4000
4500
0 4 8 12 16 22 24 28 32 36 40 44 48
Transaconspersecond
Slave Worker Threads
Slave Throughput
La r g e r B G C b u c k e t
N o M a st e r Im p a ct
B a se lin e

Built with input from Facebook
5.7 is able to handle 56K connect/disconnect per second
• mysql-bench
• 25 concurrent client threads
• Executing connect/select/disconnect
• 100000 iterations each
+32% +64%
MySQL 5.7: Connections / Second
Faster processing of new connections

MySQL 5.7: Performance Schema
 Extended the statement instrumentation
– Stored Procedures and Stored Functions
– Prepared Statements
– Transactions
Statement Instrumentation
 Instruments for Memory
– Added for over 200 Memory Types
 Aggregates memory usage statistics by
– Type of memory used (caches,
internal buffers, …)
– Thread/account/user/host indirectly
performing the memory operation
 Attributes include
– Memory used (bytes), Operation
counts, High/Low Water Marks
Memory Usage
 Additional instrumentation and metrics
– Replication slave status
– MDL lock instrumentation
Additional Data

MySQL 5.7 Sysbench Benchmark
Sysbench Point Select
Intel(R) Xeon(R) CPU X7560 x86_64
5 sockets x 8 cores-HT (80 CPU threads)
2.27GHz, 256G RAM
Oracle Linux 6.5
2X Faster than MySQL 5.6
Over 3X Faster than MySQL 5.5
630,000 QPS

MySQL Repositories: Yum
 Simple and convenient way to install & update MySQL products
 Supports the following distributions
– RedHat Enterprise/Oracle Linux
– Fedora
– Debian/Ubuntu
 Includes the latest packages
– MySQL Database
– MySQL Workbench
– MySQL Connector/ ODBC
– MySQL Connector/Python
– MySQL Utilities
Benefits both End Users and Linux Distributions
And now also on
NuGet for Windows Devs
http://www.nuget.org/profiles/MySQL/
Over 50% of downloads for
Fedora/RedHat already
from the repositories

Insert Picture Here
What is NoSQL ?

NoSQL
A NoSQL or Not Only SQL database provides a mechanism for storage and retrieval of data that is
modeled in means other than the tabular relations used in relational databases.
Consistency, Availability, Partition Tolerance (CAP)-
Consistency: all nodes see the same data at the same time
Availability: a guarantee that every request receives a response about whether it was successful or failed
Partition tolerance: the system continues to operate despite arbitrary message loss
The NoSQL taxonomy supports key-value stores, document store, BigTable, and graph databases.
MySQL NoSQL:
Concurrent NoSQL and SQL access to the database;
Simple multi-master replication with automated failover and recovery within and across data centers;
Auto-sharding and scale-out across commodity hardware;
Online scaling and schema changes;
ACID compliance, Foreign Key constraints (enforced on all APIs) and support for complex queries;
In-memory computing for real-time performance.

NoSQL in MySQL

NoSQL in MySQL
Oracle added NoSQL capabilities to the InnoDB engine in MySQL 5.6, providing a
9x improvement in transaction performance. How?
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.
Memcached API with InnoDB-
MySQL provides simple, direct, key-value interaction with InnoDB data via Memcached API.
Memcached is an in-memory key-value store for small chunks of arbitrary data (strings, objects) from results of
database calls, API calls, or page rendering.
Memcached is used by many large Web properties.
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, extending Memcached functionality
by integrating a persistent, crash-safe, transactional database back-end

Example Code
function get_foo(int userid) {
data = db_select("SELECT * FROM users WHERE userid = ?", userid);
return data;
}
After conversion to Memcached:
function get_foo(int userid) {
/* first try the cache */
data = memcached_fetch("userrow:" + userid);
if (!data) {
/* not found : request database */
data = db_select("SELECT * FROM users WHERE userid = ?", userid);
/* then store in cache until next get */
memcached_add("userrow:" + userid, data);
}
return data; }
get(), set(), put()

Working example of using Memcached API for InnoDB
1. Load the Memcached plugin at startup time by adding this option to your my.cnf file:
plugin-load = daemon_memcached=libmemcached.so
Or 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");

Working example
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
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 |
+---------+---------+------------+

Working example
5. Incorporate the new methods into your application. Java for example -
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; } } }

NoSQL inserts can be as much as 9x faster than using SQL
on an 8-core Intel server with 16GB of RAM, running Oracle Linux

5.6 vs 5.7 - 1M QPS

dml_reads/sec

References/Credits
More Information
http://dev.mysql.com/doc/refman/5.7/en/
https://blogs.oracle.com/mysqlinnodb/entry/mysql_5_7_3_deep
http://www.drdobbs.com/database/nosql-with-mysql/240167115?pgno=1
Dimitri's Blog
http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html
Geir's Blog
http://mysqlserverteam.com/the-mysql-5-7-4-milestone-release-is-available/

Mysql User Camp : 20th June - Mysql New Features

More Related Content

What's hot

What's hot (20)

Viewers also liked

Viewers also liked (9)

Similar to Mysql User Camp : 20th June - Mysql New Features

Similar to Mysql User Camp : 20th June - Mysql New Features (20)

Recently uploaded

Recently uploaded (20)

Mysql User Camp : 20th June - Mysql New Features