MySQL Performance Schema - Percona

MySQL PerformanceSchemaBy Vadim Tkachenko and Alexander RubinCopyright 2006-2014 Percona LLC

MySQL performance schemaTable of ContentsChapter 1: MySQL 5.6 Performance Schema in multi-tenant environmentsChapter 2: Performance Schema overheadChapter 3: Performance Schema tables stats3710About PerconaPercona was founded in August 2006 by Peter Zaitsev and VadimTkachenko and now employs a global network of experts with a staff ofmore than 100 people. Our customer list is large and diverse, includingFortune 50 firms, popular websites, and small startups. We have over1,800 customers and, although we do not reveal all of their names,chances are we're working with every large MySQL user you've heardabout. To put Percona's MySQL expertise to work for you, pleasecontact us.Is this an emergency? Get immediate assistancefrom Percona Support 24/7. Click hereSkype: oncall.perconaGTalk: oncall@percona.comAIM (AOL Instant Messenger): oncallperconaTelephone direct-to-engineer: 1-877-862-4316 orUK Toll Free: 44-800-088-5561Telephone to live operator: 1-888-488-8556Customer portal: https://customers.percona.com/Copyright 2006-2014 Percona LLC

MySQL performance schemaChapter 1: MySQL 5.6 Performance Schema in multi-tenant environmentsMySQL 5.6 Performance Schema in multi-tenant environmentsBy Alexander RubinHosting a shared MySQL instance for your internal or external clients (“multi-tenant”) was alwaysa challenge. Multi-tenants approach or a “schema-per-customer” approach is pretty commonnowadays to host multiple clients on the same MySQL sever. One of issues of this approach,however, is the lack of visibility: it is hard to tell how many resources (queries, disk, cpu, etc) eachuser will use.Percona Server contains userstats Google patch, which will allow you to get the resourceutilization per user. The new MySQL 5.6 performance schema has even more instrumentationwhich can give you a better visibility on per-user or per-schema/per-database level. And if you arerunning MySQL 5.6.6 or higher, Performance Schema is already enabled (with minimum set of“instrumentation” thou) and ready to use. In this post I will share examples of using PerformanceSchema for multi-tenant environments.OverviewIf you want to use Performance Schema, make sure it is enabled (enabled by default starting withMySQL 5.6.6):Performance schema provides you with the “raw” metrics and it may be difficult to select datafrom it. The good news is that you can use the project by. The “sys” schema (old name:ps helper) is a collection of views and stored procedures which will provide you with reports inhuman readable format. The installation is easy, it from github and run:(it will only create database “sys” and a set of views and stored procedures/stored functions in it)ExamplesFor the multi-tenant environment the most interesting is resource utilization breakdown. Lets sayyou want to “charge per IO” similar to model. You can now run this simple query against “sys”schema, which will use Performance Schema in MySQL 5.6 to generate report (in my example allusers starts with “a”):3

MySQL performance schemaChapter 1: MySQL 5.6 Performance Schema in multi-tenant environmentsIf you need more extensive metrics you can use this report:Or a breakdown per user per statement:4

MySQL performance schemaChapter 1: MySQL 5.6 Performance Schema in multi-tenant environmentsIf you are using “schema per customer” approach you get get the per-schema resource utilizationusing sys.schema table statistics. Example (from one of Peter Zaitsev’s recent webinars):This report may be really slow if you have lots of tables. If you are only interested in disk utilizationper database you can directly query the performance schema:5

MySQL performance schemaChapter 1: MySQL 5.6 Performance Schema in multi-tenant environmentsIf you also need to get a quick “hot tables” report with focus on disk utilization (io requests) youcan use this query:This will give you a breakdown per table (and will include system files as well).ConclusionPerformance schema in MySQL 5.6 is very useful tool and can help you to get better visualizationin MySQL multi-user (multi-tenant) installations. It can also do much more, i.e. find all queries withtemporary tables or profile locks and mutex. “sys” schema provide you with the useful pre-definedreports, the table (view) names are almost self explanatory:If you are using MySQL 5.6, performance schema will give a additional valuable inside.6

MySQL performance schemaChapter 2: Performance Schema overheadPerformance Schema overheadBy Vadim TkachenkoAs continuation of my CPU benchmarks it is interesting to see what is scalability limitation inMySQL 5.6.2, and I am going to check that using PERFORMANCE SCHEMA, but before that let’sestimate what is potential overhead of using PERFORMANCE SCHEMA. So I am going to run thesame benchmarks (sysbench read-only and read-write) as in previous post with differentperformance schema options and compare results.I am going to use Cisco UCS C250with next settings:PERFORMANCE SCHEMA disabled (NO PS)PERFORMANCE SCHMEA enabled, with all consumers ON (PS on)PERFORMANCE SCHMEA enabled, but only global instrumentation consumer enabled. Itallows to gather table and index access statistics (PS only global)PERFORMANCE SCHMEA enabled, but all consumers OFF (PS all off)The full results with details are not our Benchmark WikiThere is graph for read-only case:7

MySQL performance schemaChapter 2: Performance Schema overheadand for read-write:To have some numeric impression, let’s see ration of result with PS to result without PSThere is table with ratios for read-only case:8

MySQL performance schemaChapter 2: Performance Schema overheadThere is table with ratios for read-write case:So this allows us to make next summary:In read-only case, Performance Schema with all consumers gives about 25% overhead,with “global instrumentation” only -10%, and with all disabled consumers – about 8%.For read-write case, Performance Schema with all consumers gives about 19% overhead,with “global instrumentation” only -11%, and it is about the same with all disabled consumers.Is that big or small ? I leave it for you to decide, I think it may be acceptable in some cases and notin some others.I wish only that Performance Schema with all disabled consumers gives less overhead, 8-11%seems significant.If nothing helps I would like to be able to fully disable / enable performance schema in run-time, notat start-time.As I understand dtrace / systemtap probes can be disabled / enabled at run-time, and when theydisabled – it is almost 0% overhead, why Performance Schema can’t do the same ?(Disclaimer: This benchmark is sponsored by Well Know Social Network, and they are generousto make it public)9

MySQL performance schemaChapter 3: Performance Schema table statsPerformance Schema tables statsBy Vadim TkachenkoIn the previous chapter, the benchmark on Performance Schema was mainly in memory workloadand against single tables. Now, after adding multi-tables support to sysbench, it is interesting tosee what statistic we can get from workload that produces some disk IO.So let’s run sysbench against 100 tables, each 5000000 rows ( 1.2G ) and buffer pool 30G.The scripts and results are on Benchmark Wiki.If we look on performance overhead it appears rather big in read-only benchmark, and it is wellexplained in , so let’s keep this question aside and wait on further 5.6 releases with fixes.Now I am going to post some statistics we are able to get from performance schema tables.1. table io waits summary by table10

MySQL performance schemaChapter 3: Performance Schema table statsOr using this data we can TOP 5 accessed tables via2. There is table with statistic per index:11

MySQL performance schemaChapter 3: Performance Schema table statsInteresting that UPDATE/DELETE operations are not counted in INDEX NAME: PRIMARY,: “Inserts are counted against INDEX NAME NULL”, butit does not mention UPDATEs and DELETEs.12

MySQL performance schemaChapter 3: Performance Schema table stats3. Beside logical access to tables, we can see physical IO to files:. or we can get top tables that required read IO:Interesting that top tables that required IO are not the same that most accessed.13

About the authorsVadim Tkachenko co-founded Percona in 2006 after four years in the High PerformanceGroup within the official MySQL Support Team. He serves on Percona's executive teamas CTO. He also leads Percona's development group, which produces Percona Server,Percona XtraDB, and Percona XtraBackup. He now lives in California with his wife andtheir two children.Alexander Rubin joined Percona in 2013. He's worked with MySQL since 2000 as aDBA and application developer. He was a MySQL consultant for 7 years prior toPercona (starting with MySQL AB in 2006, then Sun Microsystems and then Oracle).Alexander has helped many customers design large, scalable and highly availableMySQL systems and optimize MySQL performance. He has also helped customersdesign Big Data stores with Apache Hadoop and related technologies.Visit the Percona library for more free MySQL eBook booksCopyright 2006-2014 Percona LLCPowered by TCPDF (www.tcpdf.org)

utilization per user. The new MySQL 5.6 performance_schema has even more instrumentation which can give you a better visibility on per-user or per-schema/per-database level. And if you are running MySQL 5.6.6 or higher, Performance Schema is already enabled (with minimum set of “instrumentation” thou) and ready to use.