Okay, my name is Svetas Mironova, good evening, my name is Svetas Mironova and I work in

my school support group in Oracle. And like any other good engineer, I'm lazy. And this is why I like tools which make my job easier so I can be even more lazy. In one of these tools, it's performance schema, which is a great tool and which we'll discuss now today.

But looking at this slide, you may think that, watch, is it a simple, easy tool? It's 52 tables in the 5.6, 554 instruments and 31 variables just to tune it. In 5.7, it's even more.

But performance schema creates, it's like, it's schema very structured and the table names, it's like they are grouped in by things which we help to troubleshoot. And once you understand what you can do with performance schema, you can easily just create just like show tables, like create information schema about it, and you find out what it

does and how to write effectively use, write effective queries for performance schema. Okay, so which can we troubleshoot? Performance schema first introduced in version 5.5, and in that version, you could only

troubleshoot servers, bottlenecks, like caused by logs, mutexes, IO. So it's, actually performance schema works like, probably some of you know how to trace it, whose instrumentation is the code, and when code executes, this counter increases.

So in the very first version, it was only this instrumentation, it required quite knowledge of MySQL server internals. But in version 5.6, performance schema developers turned it face to DBAs who don't know MySQL

server code by heart, and created the instrumentation for troubleshooting statements, stages, it's like what you see in show process list, it's query stages, and connection issues.

Then in 5.7, added new amazing features, it's, I don't know if it's on this slide, it's metadata log instrumentation, it's memory usage instrumentation, and it has replication table, replication related tables, and more, and it's growing now, it's in development.

Okay, so before using performance schema, you need to set up it, that means you need to enable those instruments which you want to use, and disable what you don't want

to use. So you can do it by either writing query about performance schema, or by installing that's called C schema, which just set of views and stored routines which help to use

performance schema, it was written originally by Marc Leith, but now it's project grows, and I know support engineers work on it, it's open for contribution. This schema, it's like it enables consumers for troubleshooting weights, and this enables

everything for all instruments. Okay, so let's start with real troubleshooting case. So it's watch heap inside server, so you can see it's available size version 5.5,

but latest version contains more instruments, you can know more, like in the DB instrumentation, for example. So you have, at least first it's IO, it's files, it's what happens inside, it's the tables, then it's mutex, it's logs, conditions, so how much you can just run,

like sysbench test, and monitor which exact code executes it, and where is the bottleneck.

So let's show example, for example, but I'm not sure if it will work in 5.5, it's probably instrumentation exists only in later version, but, so what I did, I just created a big sysbench table, and altered it, then I run some load in parallel client, just to see more weights, and run this performance schema query to see what is going on.

And now we see, unfortunately I just cannot make all output here, because otherwise font will be so teeny, so nobody will see it, even me. But what we should do with, see it's counts maximum average timer weight, it's on the

first load, which something happens within the DB import file, and here it's counts start it's maximum read-write log about index 3. So let's do it, we are doing it like you see, we are adding key here.

So at least we know what something is, but what happens, to help what happens, it's cost, it will help sys schema view IO global by file by bytes, I'm sorry, I have to

read it, and here it clearly shows what is data here in the DB merged file, it's 156 GB, and here we see the size of our table is 480 MB, so now it's, I think many of you know, which alter was slow, but now it's proved by slow, you know, by slow.

Okay, so what else, another amazing feature, it's statement troubleshooting. So this performance schema contains like table name, it has table name like current,

about what is happened right now, history, which happened at the last, I think, 10,000

events, or I think the last 1000 events, and history alone, it's about the last, it's this is a table, size of history tables is a table. Okay, so this information about statements contains event statements tables, group of tables, it's information about statements, so queries which you run,

comments like quite, compete, which is like MySQL admin since the server, errors, they are specific, and 5.7 has instrumentation for storage routines, so which we, I think many of us always wanted. So which is how to use it, it's which, for example, we can ask like which queries

do not use indexes, we need this very condition, no index used or no good index used, and we see that we found some query which doesn't use index. We can do so even with nicer view from C schema, and again,

I need to read statements with full table scans, so we see total latency, no index used, examine it for 100,000 rows to just to send once. So certainly shows which problems which you can have.

Okay, so what else worth attention, of course, it's in columns in the statement tables, it's about temporary tables, about everything which shows that query is not optimized, like creating temporary tables, sort scan,

sort scan, etc. And what is good about performance schema? Before, we could not have this information per statement. We had handler status variables, we could have explained which, but we cannot find out, just like we have some statements,

we just run it parallel query performance schema, we know which statements are not effective. So that's, usually it will be like this support task for handler variables, so like create a temporary table, so increased. Ask it for a slow query log, do it, but

now we can just query performance schema, find out what is going on. And also, this is used in the C schema which related to statements table. Next, which is again available in 5.6, it's performance schema stages table.

We contain the stages which happen during query execution, it's like you could see in the show process list on information schema process list, and probably you know it's community extension show profile. This is a replacement, but more powerful replacement of show profile, but

you need distant limitation here is that stages which are shown in show process list, and they are server level. So no engine information here, no engine specific information here. Okay, so example, which states took critically long time,

so which, because queries, if you won't have a better condition, here we will see the many, many, many states. So most critical time with sending the data, it can be a knife and a roof if you doing like select star from table with millions of rows.

But now we are selecting a single row, so it shows creating a temporary table. Okay, so what else is worth attention? It's first, of course, it's related to temporary table. Everything related to logs, waiting for these are logs two.

But I want you to pay attention for these states. It's taken from my support engineer experience. And why these states is like, and free items and why they, about sending the data, I just told you why it's important.

Regarding these states, it's day before hour of work, and they actually contain many, many, it's actually, we should be split to more stages. We have support request, feature request, but it's not implemented. Like for example, like freeing items in some places, it frees query cache,

and sometimes, I think, and shows some query cache problems. And you don't know what happens, and if your statement's stuck in these stages, that's kind of a problem. Okay, so now it's, I miss a few features from 5.6.

But of course, I want to show you very nice features, 5.7. It's memory summary tables. When I wrote my book, My School Troubleshooting, which Vlad just advertised, I wrote about memory and asked if you see memory issues,

collect information from operating system. You can have some information about memory and status variable, but this is not, what's the problem? You cannot see, it's like, it's not per server part.

You don't know how memory is. You can just make basis like, if you will say, for example, status created temporary tables, it goes to temporary tables. In the DB, in the DB monitors output some information about in the DB internals, but it's a needle, and still no other engine does do it.

From operating system, again, you just cannot see what happens inside. But in 5.7, you see very detailed information, where your memory goes. No, it's first I just show it like, which total memory server executed for use case for it.

For example, sometimes it's like a server allocates memory, then frees, and sometimes operating system doesn't show it like frees. So you can just check this view and find out if this is the case and

or really this memory leak in my school server. But more interesting things, it's like, actually it's part, I want to point you to my blog where I wrote about this case, a long article with details.

But what happens here, here it was replication back reported box.mysql.com, it was repeatable back report, and slave leaked memory. So I decided to find out what is leaking memory because suggesting workaround is different. Like if it's a scale frees leaks memory, you could increase buffers,

it's doing things like scale frees executes better. If I offer it, it's very different. So you see now, it's I offer it. It's I let load run, run load, then stopped it, then waited it for some time. And I see it still shows I offer it still has one gigabyte allocated,

it isn't going to free it. And SQL slave, it's not the reason of a memory leak. So you can see it's even although if it shows server internals, I think it's readable for any DBA, you don't need.

So you can address what is going on, and you can find out workaround. Oops. Okay, so what else is worth attention? Else is tables. They are created, you can have statistic by thread, by host, by account.

So like if you, for example, if some connection is the most expensive regarding memory, so you can just kill it, close it. If you use some host, maybe you may find the attack and do something like this. And also there are system abuse for

this memory table, so you can use it too. Okay, so actually this is, the sessions here are short. And I did not include much about all features which, which did I lose?

Which did I miss? At first, which I want to point you, it's 5.7 feature. It's metadata lock instrumentation. Anybody knows what metadata lock is? Okay, so metadata lock is a lock which is set by server.

It's transactional lock which is set by server. In any case when you access a table, like run select, insert, any query. It sets to prevent parallel connection to run the DDL to alter this table. What's the problem with metadata locks?

So how you see queries, it's like in a show process list, like some query waits for the lock, and maybe waits for metadata lock. But you don't know which query holds the lock. And before 5.7, it was not possible, you could just imagine. In the 5.7, we have which holds the lock, which waits the lock, so we have.

Also, second feature, it's host cache table. Which is this host cache exists, it's very, very, very early versions of MySQL. And it's hosts, when MySQL has DNS, it hosts data retrieved to speed up future connections.

But sometimes, host cache have what might be corrupt. It might be not corrupt, it's just DNS changed. And people, customers, users start receiving wire terrors. So before it's like, no, it's actually now, we are doing so because 5.7.

Not now, I'm sorry, before 5.6. When the customer sent us about this such issues, we just asked them to apply all known workarounds one by one.

So this doesn't work, okay, so let's try this one. So now we have instrumentation, and we can see it. Next feature, it's host cache exists in 5.6. Next feature is replication tables. I remember one of, I think,

Justin about Flux's presentation told about they are harder to use. In my opinion, they are easier to use because they are tables. Where you can use them like in cron jobs and in vents. It's much easier to create a table than parse to show status output,

especially if you are doing from event on storage routine, etc. There are many, many new instruments. Instruments, which is this, it's what you can instrument. So there are hundreds of new instruments.

And you also, that's not feature how to assume performance schema. But what I describe, performance schema helps to show you what happens inside the server, but it's expensive.

Every counter, it takes some down time, because you need to do one more call. But to use it, so enabling whole instruments, it's not good idea.

Unless you are just for testing purpose. So you need to understand how it works, which instruments are more expensive, which instruments are less expensive. And great sources for information of this. This is post block of performance schema after Markov.

You see here, it's your country. And here, I don't see it's probably not updated, but regarding performance schema setup, it's still very actual.

And I recommend you to educate this block, examine this block. Next link, it's, we are at references slides. Next link, it's GitHub of very MySQL C schema, like I said. Then it's a block of Dimitri Krafchuk.

Dimitri Krafchuk is his performance lead in MySQL. In Oracle, he makes tests, and he uses performance schema for his tests. And explains a lot about how to use it, how to make it fast, and how to set up, it's worth reading.

Next, this is user manual. Then I wrote few blog posts about using memory tables in my blog, and it's slide share.

It's created, the slides created a year and a half ago. They contain more detailed version of the session, although they don't contain, I think, don't contain 5.7 features, just because it was created a year and a half ago. Okay, I need to show you this slide.

Again, thank you. If you have questions, please ask.

Yeah, okay, so what is the name of the tool? Is that? What is it? It's DBA Helper.

Ah, okay. The word system is DBA Helper. Your fork of DBA, just the fork of DBA Helper. Okay, so it will collect all the information of the time. Actually, I saw this feature, I heard this feature request from when I presented similar topic before.

I can send you a link. Okay, thank you. I'll put a blog about it too. Thank you. Thank you. Wait, I'm sorry.