![Page 1: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/1.jpg)
Insert Picture Here
What's new in MySQL 5.7and
NoSQL Support in MYSQL
Sudipto SahooSenior MySQL Engineer
![Page 2: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/2.jpg)
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.
![Page 3: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/3.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.3
Program Agenda
§ What is MySQL ?§ What’s New in MySQL 5.7§ What is NoSQL?§ NoSQL Support in MySQL§ Q & A
![Page 4: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/4.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.4
Insert Picture Here
What is MySQL ?
![Page 5: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/5.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.5
§“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”
![Page 6: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/6.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.6
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
![Page 7: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/7.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.7
Insert Picture Here
What’s new in MySQL 5.7 ?
![Page 8: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/8.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.8
Raising The Bar
Again and Again, Evolving MySQL for You
![Page 9: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/9.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.9
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/
![Page 10: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/10.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.10
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
![Page 11: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/11.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.11
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.
![Page 12: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/12.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.12
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
![Page 13: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/13.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.13
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
![Page 14: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/14.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.14
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
![Page 15: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/15.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.15
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
Tran
sac
ons
pers
econ
d
Slave Worker Threads
Slave Throughput
L a r g e r B G C b u c k e t
N o M a s t e r I m p a c t
B a s e l i n e
![Page 16: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/16.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.16
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 / SecondFaster processing of new connections
![Page 17: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/17.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.17
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
![Page 18: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/18.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.18
MySQL 5.7 Sysbench BenchmarkSysbench Point Select
Intel(R) Xeon(R) CPU X7560 x86_645 sockets x 8 cores-HT (80 CPU threads)2.27GHz, 256G RAMOracle Linux 6.5
2X Faster than MySQL 5.6 Over 3X Faster than MySQL 5.5
630,000 QPS
![Page 19: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/19.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.19
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 onNuGet for Windows Devshttp://www.nuget.org/profiles/MySQL/
Over 50% of downloads for Fedora/RedHat already
from the repositories
![Page 20: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/20.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.20
Insert Picture Here
What is NoSQL ?
![Page 21: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/21.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.21
NoSQLA 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.
![Page 22: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/22.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.22
NoSQL in MySQL
![Page 23: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/23.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.23
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
![Page 24: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/24.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.24
Example Codefunction 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()
![Page 25: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/25.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.25
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");
![Page 26: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/26.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.26
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 |
+---------+---------+------------+
![Page 27: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/27.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.27
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; } } }
![Page 28: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/28.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.28
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
![Page 29: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/29.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.29
5.6 vs 5.7 - 1M QPS
![Page 30: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/30.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.30
dml_reads/sec
![Page 31: Mysql User Camp : 20th June - Mysql New Features](https://reader033.vdocuments.us/reader033/viewer/2022060107/554bd3d9b4c905ac708b4cf8/html5/thumbnails/31.jpg)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.31
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/