My name is Oliver Sennhauser, and I will talk about performance schema and SUSE schema, and what we can do with it. I learned today that there are only two people in the world who really understand performance schema.

I am not one of those guys, so if you came because of this year, you have the time to change the room. I am working on a new company called Vua, and what we do basically is everything MySQL-related. So we do support, consulting, remote DVI training on MySQL.

We are going to be performing server and Galera cluster to do MySQL all the day, nothing else. What I want to look at for you, or with you, is the performance schema and the SUSE schema. First, some thoughts about database profiling, then the performance schema itself, how is it installed,

configured, instrumented, etc., then the SUSE schema, and then some use cases, how can we use the performance schema, what it is used for. You don't have to take photos about those slides, they are already online on our website, you can download it.

So what is it all about when we are talking about the performance schema? So it is about database profiling, we want to have a look what is going on in the database. So some questions the performance schema will answer is, where has my time gone? Am I very slow, when my server is slow, where has the time gone?

The second question the performance schema can answer is, where have my resources gone? For example, memory, or the IOs, and so on and so forth. What we want to have at the end is something like this, let's say this is our query,

I start the query here, I get the result back, and I want to know why could my query belong. So we have different steps which are taken in my queries, they can be some processing, for example sending data, you have already seen this state, it could be that one or it could be that one, and sometimes we have some time, we lose some time or lots or anything else,

we don't know whether time has gone, but my query is still slow. So we want to have something here, and if I show you this graph, you can see me within a second, without big things, where should we start tuning? So that and that are the big things, then we should start tuning,

but without those information, we don't have big chances to do it. So how is this done? Basically, you have inside an application, for example, you have to put in probes. So on every function you have, you might make a start tag and an end tag, you count how long is the duration, and at the end you get the information

which function was executed how many times, how long does it take, you can make such a graph. So here it's pretty obvious what function set, it looks 95% of our time, so you know where to start tuning. So that's basically the idea of the performance schema.

MySQL profiler, maybe you know about it, existed since MySQL 5.0, when I've seen it the first time, I thought, wow, that's really what I wanted, really what I needed, I tried it out immediately, it's quite simple. Set profiling equals one, then I execute my critical key,

select update insert delete, and then do a show profile, and it gets something like that. That's more or less what I want. And then I see here, most of the time is lost here, and sending data. So my N2000 was not that big anymore, not really helped to answer my problem.

And then in MySQL 5.6, show profile, or the profiler was deprecated, so what's now? Performance schema, first time it was at least discussed in 2006, it was introduced in MySQL 5.5 in 2010,

disabled by default, it had some reason to do that, and it was not really useful because it provided more or less this information. Then in MySQL 5.6 it was significantly improved, so 2011, 2012, since then we have the performance schema,

and MySQL 5.6 enabled it by default, and now it really became useful and interesting. MySQL 5.7, which is the actual current release, 2013 to 2015, more improvements, more probes, more information, now we have even memory probes,

we can look at transaction, at the replication, session status variables, and so on and so forth. When you do probes, it's always the question how much is the impact, and or how to say they want to have as little impact as possible. The overhead is small, so it depends to whom you believe,

some say it's 2 to 3%, others say 200%. Overhead, 200% is not small anymore. Why? That really depends on how you do it, so you should be careful enabling the performance schema. What is performance schema?

Monitoring the MySQL server execution at low level, and we want to do internal execution inspection of the server at runtime. How is it done? The server is instrumented and timing information is collected.

The output, how can we get access to this data? Simple SQL queries, mostly selects, on MySQL performance schema tables, which are more or less ring buffer things. Events, we are talking about events in performance schema, so everything is an event. For example, operating system function calls,

other function calls, wait for the operating system, SQL statements, statements, stages, memory, everything is called an event in performance schema. How is it installed and configured? It's installed by default in MySQL 5.5, 5.6, and 5.7.

If you do an upgrade from 5.1 to 5.5 or higher, MySQL update will do an upgrade or installation of the performance schema. I just found out yesterday it's a good idea to do a database restart after upgrade of the performance schema, otherwise it will not see the new structure.

How is it configured? In MySQL 5.5, it's off. You can configure it in the myCNF. You do it by performance schema equals one, or if you want to disable it, equals zero. And in 5.6 and later, it's on. Tuning itself, not just switching it off and on.

So you have new parameters, for example, performance schema, it waits quite long. You can say how many events should be stored here. I think that's the default for these specific tables. You can also look, as usual, at the performance schema with show global variables and about the status

of the performance schema with show global state. So you get some information how it is configured. So now you have the MySQL daemon up and running. It's configured properly. And now that's a new concept. You have to enable or disable different features in the performance schema.

And these are the set up tables. We have set up actors, consumers, instruments, objects, and timers. And you do this with update statements. So in this case, we want to enable the event wait, sue, mutex, in-a-db, out-a-db, mutex. It's an eternal mutex which controls the out-instrument

behavior of in-a-db. And we enable it by update on performance schema set of instruments. And we say, yes, it's enabled. And yes, we want to know how long it was running, not only how often, but also how long. So we have currently five different set up tables.

So one is the actor. Here, everything by default is enabled. And in the actor, you can configure which host and which user do you want to profile. So by default, everything is profiled. But if you want to switch something off, you can configure it here. The consumers are the performance schema tables

that are filled with data. For example, there is a table called event state current. And some of them are off. Some of them are on. You have to look at which ones are enabled. Instruments, those are the probes in the database.

And they are activated if they are not so expensive. If you want to know the expensive ones, you have to activate themself. For example, this out-increment mutex will be instrumented to get every insert and every out-increment. So it could be that this is an expensive one.

So my SQL has disabled them by default. Objects, you can also have a setup on which objects you want to sample the probes. Objects is a table, a trigger procedure function, or an event. And by default, for all user objects, they are enabled.

And then we can also configure the timers internally. So about instrumentation death, enabled probes and disabled probes, we can have probes on a transaction. So they are counted every time you run a transaction.

Then on every statement, those are the cheap ones. And they are enabled by default. And then inside a statement, we have the status. That's what you get out from show profiles. And they are a little bit more expensive, so they are disabled by default. And inside the state, you have even weights

and maybe also memory allocation, which is even more expensive. So as you deeper you go into this hierarchy, it's more expensive the probes become. So you should be careful to enable the red ones. Otherwise the database could be slowed down. And I'm pretty sure the guy told me

performance schema is 200% slower than normal without performance schema enabled, was enabling some probes here. So that makes the kernel, the database kernel slow. So this can have an impact on performance. Then we have different consumer types hierarchies.

So everything in the performance schema is an event. And we have for all the events, we have current, history and history long. What does it mean? Current, for example, event statement. Current is one statement per thread. Event, statement, history. The most recent 10 events per threads are collected here.

That means the most 10 recent statements for every thread is collected here. You can configure it. And history long means the most recent 10,000 events are collected. In this case, the most recent 10,000 queries are collected in that table.

Then we have other hierarchies or aggregations. So there are all events in, here we have aggregations by account. An account is host plus user. Here we have aggregations for per host and per user.

So we can aggregate on every, let's say, 127.0.0.1 or for the root user or your application user. And we can also aggregate on a thread base. Then we have some aggregations by a digest. What is a digest? A digest is the normalized query. For example, if you have select star from test

where A equals 42 and A equals 43, if the query is digested, it will be, the 42 and the 43 will be replaced by question mark and then the query is the same. So it gets the same digest. So for example, all primary key lookups will end up here at the same digest.

Then you can have a summary per program, per instance, instances, our files, mutex, prepare statement, read logs, objects and conditions. And if you want to clean out those tables to get a fresh snapshot, you have to run a truncate table in one of these performance schema tables

so they will be reset. That was the performance schema. Now let's continue to the source schema. Originally it was called PS Helper in 56, 2012. It came out. It was created by a guy called Mark Eve, working at Oracle.

And the idea of the source schema is to make the performance schema easier to understand and more human readable. It's a database in MySQL called SUS and it consists of tables, views, functions, procedures and triggers, yeah, and human interface or human readable and understandable interface.

The current release is 1.5. You can download it from this link and install it in the database. In MySQL 5.7 it's installed by default. If you have conflicts you can skip it during installation and in MySQL 5.6 you can install the SUS schema as follows.

If you do an upgrade from 5.6 to 5.7, MySQL upgrade will install the SUS schema automatically or if you get a new release of the SUS schema it will also do automatically an upgrade. If you look at the SUS schema tables you are first surprised about tables looking like that.

Everybody who has some Oracle experience will recognize the pattern. I've heard before, oh, it's the same like Oracle. No, it's not. X dollar means the information here are in the most fine granularity performance schema provides. That means picoseconds, thank you. And without the X dollar it's human readable.

So for SUS tables we have different topics, topics about hosts, everything by host is aggregated then some insight into InnoDB, some insight into the IO system, memory, information about schema,

then statements, users and weights. So everything is already aggregated for you to have a look. The performance schema can also be configured by SUS schema calls. We do it, for example, like that. We set all the defaults. We have misconfigured back to the default or if you want to enable or disable

some performance schema functionality you can do it with procedures from the SUS schema. So let's have a look at some use cases. You don't have to copy or write everything down. You can get it from this link. All the slides are available. One thing I found recently is who is using SSL encryption and who is not.

So you can have a look in the performance schema and you get an output like this. So you see this route of not using SSL encryption and the other route was using SSL encryption. So who is security sensitive? That would be nice information for you.

Or accounts not closing properly the connection. Who has such applications? Anybody? Or everybody is closing the connection to the database properly? Yeah, okay. We can easily find here on the performance schema who was not closing the database properly.

So here was one not closing. I really have to kill the connection to get the values. So 5% of my connections were not closed properly. But in reality it will look different. Accounts which never connected to the database since last service start-up. So if your database was running for 300 days

you can easily see which users never connected to the database and possibly can be cleaned up or dropped. I don't know if you clean up your database from time to time but if that will give you information. Bad SQL queries by users. So which user does bad SQL queries? Now the question is what is the definition of a bad query?

So for example in this situation we say everybody who does a temp disk table or who does a full join could potentially be a bad query so you find the users doing this. Or top long running queries. For this, first you have to enable a consumer.

Then you have to run a little bit and then you get out this information. So which query was the most evil one? So that's more or less what the query analyzer does in the MySQL enterprise model. Other question, thank you. We can answer these unused indexes.

You can do it on the performance schema or on the FUS schema. So you know since last restart which index was never used to drop them if nobody needs them. They only waste space. Redundant index is the same. Which indexes are redundant? Drop the redundant index.

Statements which cause errors. Some statement cause error, fix them. Tables who do full table scans. Or IO done by user, pretty interesting. Background process, unfortunately that don't really help much because I want to know which application you're using.

Latency, which are the files causing most latency? Okay, either the log files or the data files. I think I knew that by heart. Which user was using most memory?

Could sometimes be interesting. On which tables you did most update, insert, delete? Another question you could answer. So maybe a general advice how to proceed first. Define the question you want to answer with the performance schema. For example here, which transaction was logged by which other transaction?

That was our problem two weeks ago. Then checking the FUS schema first. Is FUS schema answering this question? If not, in this case it did. If not, go to the performance schema or information schema to answer that question. So we get it also. But what I wanted to know was transactions logged in the past,

not right now when I'm looking. And this is not yet provided, so I rise the feature request for that. Let's see if it comes up in a new release of the performance schema. So basically, performance schema is totally cool, but that was the old way doing it.

That's the new way doing it. So it's a little bit complicated and needs some time to get it. So that was it about the performance schema, FUS schema. Slides can be downloaded. Any questions? No questions? Okay.

Yes? Can you filter only one? So it's going to keep the history, you know, advanced, but only one. Because you know it definitely the first one too. Okay. I can filter on a user, but on a... Yes, there are some tables on a thread, but how do you want to get a number?

Like, you can see the history. So it's 10 per thread. I just want to give, for example, 1000 only on one thread. There is one of the most recent. Yeah. What you would do is you can turn instrumentation on and off for specific threads.

You turn it off for everyone and then you turn it on for one thread. So it doesn't quite answer your question to work around. It's possible via work around. You can't do it for one thread, let's say thread number 42. Yeah, turn everything off first and then turn instrumentation on for a thread.

Okay. No other questions? Otherwise you can ask me outside. I'm here today and to work. Or at the theater. Yes, sorry. I am at the theater today so you can ask me outside the theater. Thank you very much. Thank you very much.