WIXLIB

MySQL 5.7 in a NutshellAlexander RubinPrincipal Architect, PerconaDecember 6, 2015

About MeMy name is Alexander Rubin Working with MySQL for over 10 years– Started at MySQL AB, then Sun Microsystems,– then Oracle (MySQL Consulting)– Joined Percona 2 years ercona.com

Agenda: New MySQL 5.7 featuresPerformance and Scalability– Enhanced Speed: MySQL 5.7 delivered 1,600,000 queries persecond (QPS) – 3x faster than MySQL 5.6.– Optimized InnoDB: New capabilities include increasedperformance and concurrency– More Robust Replication: multi-source replication enhanced Global Transaction Identifiers (GTIDs) improved multi-threaded slaves– Enhanced Optimizer: A new dynamic cost model providesbetter query performance and greater user control.www.percona.com

Agenda: New MySQL 5.7 featuresManageability enhancements§ JSON Data Type and Calculated Fields: Allows for efficient andflexible storage, search, and manipulation of schema-less data.§ Performance Schema: Enables instrumentation for memory,transactions, stored routines, prepared statements, replication,and locks.§ MySQL SYS Schema: Provides helper objects that answercommon performance, health, usage, and monitoring questions.§ Improved Security: Delivers easier and safer instanceinitialization, setup and management.§ Expanded Geographic Information System (GIS) Support:Spatial index support in InnoDB, GeoJSON, and GeoHash.www.percona.com

MySQL 5.7 Performance ImprovementsSysbench BecnhmarkStarts with 8 ThreadsWhat about 2-4 threads?*Information from Oracle OpenWorld presentation by Geir Hoydalsvikwww.percona.com

MySQL 5.7: Single-threaded workload§ Multi-threaded workload looks great§ Single-threaded workload shows some regressionhttps://bugs.mysql.com/bug.php?id .com

MySQL 5.7: InnoDB, NoSQL With Memcached*Information from Oracle OpenWorld presentation by Geir Hoydalsvikwww.percona.com

Sysbench OLTP Read Write*Information from Oracle OpenWorld presentation by Geir Hoydalsvikwww.percona.com

InnoDB vs. MyISAM in v. 5.7FeatureMyISAMInnoDBFull Text IndexesyesSince MySQL 5.6Portable tables(tablespaces)yesSince MySQL 5.6Spatial Indexes/RTREE(GIS)yesSince MySQL 5.7Last update for tableyesSince MySQL 5.7(http://dev.mysql.com/worklog/task/?id 6658)Suitable for temp tablesyesSince MySQL 5.7Also complex selects usesInnoDB ondisk temp tablesFast count(*)yes*Faster in MySQL 5.7 butdoes not store counterwww.percona.com

InnoDB Improvements Overview– Performance: Buffer pool improvements– Performance: Better Redo log handling and better index- lock handling– Performance: DDL & Truncate improvements– Performance: Temporary Table Optimizations– Feature: Native Partitioning for InnoDB– Feature: Dynamic buffer pool size re-size, more online alter table ops– Feature: UNDO Log Space Management– Feature: Transparent PageIO Compression– Feature: GIS indexes– Miscellaneous Implement update time for InnoDB tables Improve select count(*) performance by using handler::records(); Improve recovery, redo log tablespace meta data changeswww.percona.com

InnoDB: Online Operations Resize the InnoDB Buffer Pool online More Online ALTER TABLE operations Enlarge VARCHAR, Rename Index More dynamic configuration variables New variables are dynamic Work to make existing variables dynamicallysettablewww.percona.com

Dynamic buffer pool re-size§ May be useful in virtual enviroments where youcan resize RAM onlineinnodb buffer pool chunk size – resize done inchunk size§ Example:mysql SETGLOBALinnodb buffer pool size 4*1024*1024*1024;- ‐- - ‐buffer- ‐pool- ‐online- ‐resize.htmlwww.percona.com

InnoDB - Bulk Load for CreateIndex Much faster INDEX creation and bulk loads Performance results show 2-3x performance improvement for ADD/CREATEINDEX operations 2-5% improvement for standard INSERT operationswww.percona.com

InnoDB Temporary Tables New separate tablespace for temporary tables Optimize DML operations No REDO logging, no change buffering, less locking InnoDB storage engine is used for on-diskinternal temporary tables Complex select requiring ondisk temp tables will nowuse InnoDB by default (controlled byinternal tmp disk storage engine rver-systemvariables.html#sysvar internal tmp disk storage intrinsic-tables/www.percona.com

Transparent Page Compression Transparent Page Level Compression Happens transparently in background threads For supported Linux kernels and filesystems Uses sparse file and "hole punching" support Reduces IOApplies to all InnoDB data, including the system tablespace andUNDO logsBut – many file systems do not support large number of "holes" -pagecompression.htmlwww.percona.com

GIS - InnoDB Spatial Indexes– Full transactionalsupport– Only supports 2D datafor now Use helper functionsfor distancecalculations*Graphics from Oracle OpenWorld presentationby Geir Hoydalsvikwww.percona.com

GIS - Additional Features GeoHash– Quick lookups for exact matches– Not very accurate for proximity searches GeoJSON Helper functions (ST Distance Sphere)www.percona.com

GIS - Examplemysql SELECTshapeinto@zip shapeFROMzcta.tl 2013 us zcta510WHEREzcta5ce10 '27701’;QueryOK,1rowaffected(0.20sec)mysql SELECTname,ST Distance Sphere(shape,st centroid(@zip shape))asdist,ST AsGeoJSON(shape)asGeoJSON,ST GeoHash(shape,16)asGeoHashFROMpointsWHEREST Within(shape,@zip shape)andother tagslike'%"amenity" es":[- rowinset(0.02sec)www.percona.com

Generated (Virtual) ColumnsCREATETABLE ontime ( id int(11)NOTNULLAUTO INCREMENT, YearD year(4)NOTNULL, FlightDate datetimeDEFAULTNULL, Carrier char(2)DEFAULTNULL, OriginAirportID int(11)DEFAULTNULL, OriginCityName varchar(100)DEFAULTNULL, OriginState char(2)DEFAULTNULL, DestAirportID int(11)DEFAULTNULL, DestCityName varchar(100)DEFAULTNULL, DestState char(2)DEFAULTNULL,.SELECTFlight dayofweek,count(*)FROMontime sm virtualGROUPBYFlight dayofweekDoes not store the columnBut INDEX it Flight dayofweek VIRTUAL,PRIMARYKEY( id ))ENGINE InnoDB;altertableontimeaddkey(Flight /generated- ‐virtual- ‐columns- ‐in- ‐mysql- ‐5- ‐7- ‐labs/https://dev.mysql.com/worklog/task/?id 8114www.percona.com

Generated (Virtual) Columnsmysql EXPLAINSELECTcarrier,count(*)FROMontime sm virtualWHEREFlight dayofweek **************************id:1select type:SIMPLEUsing indextable:ontime sm virtualpartitions:NULLtype:refpossible keys:Flight dayofweekkey:Flight dayofweekkey ing(0.00sec)www.percona.com

JSON Supportmysql createtablejson test(idintprimarykeyauto increment,datajsonSame as)engine InnoDB;JSON EXTRACT(data," .type”)QueryOK,0rowsaffected(0.02sec)mysql select*fromjson testwheredata- ‐ ' .type' 'Point'limit1; - ‐- ‐- ‐- ‐ - ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐ id data - ‐- ‐- ‐- ‐ - ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐ 1 {"type":"Point","coordinates":[- ‐87.9101245,41.7585879]} - ‐- ‐- ‐- ‐ - ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐- ‐ www.percona.com

JSON Support: Indexesmysql explainselect*fromjson testwheredata- ‐ ' .type' ************************id:1select type:SIMPLEtable:json testpartitions:NULLtype:ALLpossible keys:NULLkey:NULLkey ingwheremysql altertablejson testadddata typevarchar(255)GENERATEDALWAYSAS(data- ‐ ' ds:0Duplicates:0Warnings:0mysql altertablejson testaddkey(data cates:0Warnings:0www.percona.com

JSON Support: Indexesmysql explainselect*fromjson testwheredata- ‐ ' .type' ************************id:1select type:SIMPLEtable:json testpartitions:NULLtype:refpossible keys:data typekey:data typekey .percona.com

Replication ImprovementsMulti-Source Replication(slave can have multiple masters)Better Multi-Threaded Slaves(does not require multiple databases)Better GTID(online GTID deployment)Group Replication Plugin(virtual synchronous replication)www.percona.com

MySQL Replication TopologiesMaster SlaveMasters Slave (Multi-Source), Since 5.7 Master SlavesMaster Slave SlavesNow 1 replication slave can have many master servers!Important for BI/Data Science/Ad-hoc analyticswww.percona.com

MySQL 5.7: OptimizerImprovements UNION ALL queries no longer use temporarytables Improved optimizations for queries with INexpressions Improved optimizations for full-text queries More efficient sortingwww.percona.com

Temporary Table for UNION ALL*************************** 1. row ***************************table: ontime 2012key: covered5.6: Will create temp table (as shown)5.7: Do not materialize in temporaryExtra: Using where; Using index*************************** 2. row ***************************tables (unless used for sorting) rowstable: ontime 2012are sent directly to clientkey: covered5.7: Client will receive the first row. Removed .fasterExtra: Using where; Using index*************************** 3. row ***************************5.7: Less memory and diskid: NULLconsumptionselect type: UNION RESULT. Removed .table:type:possible keys:key:key len:ref:rows:Extra: union1,2 ALLNULLNULLNULLNULLNULLUsing temporarywww.percona.com

Optimizations for IN Expressions Imagine that you got list of IDs from Full Text Search solution (solr/elasticsearch/sphinx)Now I need to get the actual documents mysql selectname,other tagsfrompoi infoWHEREosm idin(367909272,367841688,493001986,.); MySQL 5.7: IN queries with row value expressionsexecuted using range scans.www.percona.com

Performance SchemaimprovementsMemoryInstrumentations– Memory used(bytes)– Operation counts– Type of memoryused (caches,internal buffers,etc)StatementInstrumentations– StoredProcedures– Stored Functions– PreparedStatements– TransactionsOtherInstrumentation– Replication slavestatus– MDL lockinstrumentation– User variablesper thread– Server stagetracking– Track longrunning SQLwww.percona.com

SYS SchemaIncluded in MySQL 5.7Get the memory usage per user with SYS schema:mysql updateperformance schema.setup instrumentssetenabled 'YES',timed ql select*fromsys.memory global ****************total allocated:90.20MiB1rowinset(0.01sec)mysql select*fromsys.memory by user by current ****************user:rootcurrent count used:42current allocated:361.03KiBcurrent avg alloc:8.60KiBcurrent max alloc:248.04KiBtotal ***************************user:backgroundcurrent count used:0current allocated:0bytescurrent avg alloc:0bytescurrent max alloc:0bytestotal om

Improved MDL locking Removes bottlenecks around DML access to asingle table 10% increased throughput in OLTP RO/POINT SELECT sysbenchOptimized for typical DML heavy -myisam-and-innodb-tables/www.percona.com

Security - Encryption, Passwords,Installation Deployment: enable secure unattended installby default Random password set on install Removed anonymous accounts Deployment without test account, schema, demofiles AES 256 Encryption Password rotation policies Can be set globally, and at the user levelwww.percona.com

Explain on a Running Querymysql ndingdataInfo:selectcount(*),osm idfrompoints newgroupbyosm idmysql 1.row***************************id:1select type:SIMPLEtable:points newShows query plan on connectionpartitions:NULLtype:ALLApplicable for SELECT/INSERT/possible keys:NULLDELETE/UPDATEkey:NULLkey Usingtemporary;Usingfilesort id www.percona.com