 My name is Mayesh Prasad and I work in MySQL.com from Bangalore, India and today I will be talking about MySQL performance schema. This is the safe harbor statement. Here is the end of today's talk. I will talk about the million design of performance schema, the instruments and its locations, the statistics, table dynamic performance schema and I will take the loose cases to explain how we can use performance schema to diagnose real-world issues. And then I will give you a glimpse of what is new in MySQL 8.0 to performance schema. Before we go further, how many of you have used performance schema before? Right. So the question comes, why do we need performance schema? So there are a lot of MySQL database users where there is an end user who might see why my session is stuck. There might be a master developer like me who is developing source code and he might see why there is a content change in my source code. There might be an application developer who is developing an application and he sees okay, I think he is developing source code. So we will have these guys and to answer these questions, performance schema comes in the future. So if we ask you to define a performance schema, I will say performance schema is a mechanism to give an insight of what is happening behind the scene, the master server is running. It was introduced in master 5.5 way back. We introduced a new storage engine which we call performance schema. Any database was introduced, performance schema. And the statistics of running master server are stored in the tables, which are part of this database. These tables have output engineers. You are not supposed to modify the structure of the table. You cannot add tables or delete tables. And then we provided sql interface as well. So if you want to get the statistics of running master server, you can simply find sql query and get that data. The important thing is there is no need of application change if you want to use performance schema. Okay, so let me give you a glimpse of what are you, how the performance schema design looks like. So there are two parts in performance schema, one is statistics collection and another is statistics reporting. The statistics collection is the part where the server collects the statistics and the reporting is the part where the user wants to see the data and ask for the statistical data. So let's see we have a master server running. In the source for itself, we have instrumentation points. So when the flow comes to these points, the control is transferred to performance schema module. It collects the data and store the data into performance schema and now if the user like me, suppose I want to get some statistical data out of the performance schema, I just pass the list of queries to master server. And the query will be executed, data will be fetched from these purpose and give it to the users to performance schema tables, right? Let's talk about the instruments and instrumentation. As soon as master server is running, a lot of activity is happening behind the scene. So you need to identify each and every activity in performance schema monitors. We are giving them a name. So these names are called instruments. They are like a tree structure of different components are separated by a power slash. Let me give you an example. So let's see these are the two instruments or name of one activity. The first one is a real event. The activity is happening on an I.O. This I.O. is happening on a file. And the file is my some log file. Similarly, the other example is statement. This is a statement, SQL statement. And what kind of SQL statement? Select SQL statement. So performance schema is capable of monitoring these activities, right? In 8.0 we have 200 plus activities or 200 plus instruments. All these instruments are listed in table which is for performance schema setup instruments table. We look at the table in the next slide. So as I said, the activities which we monitored in master performance schema listed in this setup instruments table there are three main columns. Name, Enable, and Time. Name is the name of the activity. Enable says whether performance schema is collecting at statistics of this activity or not. And Time says whether performance schema is collecting the time information of this activity or not. So the first one is statement. SQL select. Enable is your standard. So performance schema is monitoring all the select statements on the server and collecting the time information of those statements as well. So the second one is create table. Performance schema is collecting status points but it is not collecting the timing information of the create table. And create data base performance schema is not doing anything. This is what we promote performance schema. We tell performance schema what to collect and how to collect. And the best part is all these are complicated at runtime. So you have the server running and at runtime you decide okay, I want to monitor all these activities. You don't have to restart your server. You just go over there, run an update command on this table and select whatever activities you want to monitor. Right? Let's have a look at what statistics table performance schema provides. There are a number of tables. I try to categorize them into categories based on how they are used. So this is set of tables. These are the tables using which we tell performance schema what to collect and how to collect. Like I said, this is a table which is on the previous slide. We have set a factors table. We tell for what users we want to collect the statistics for. Objects table, what are the objects we want to collect the statistics for, which table. Then we have tables for different events which are happening on the server like transactions, statements, stages, rates, and other events. And as I explained all of them in the future slides. We have instances tables for all the new text we have in master server code. We write logs we have and profiles which we are using in master server. We have lock tables for metadata logs, data logs and data logs which is added in 8.0. We have connection attributes, type of connection and tables for application summary. So previously we used to do the show slave status to get the status of the application. Right? All those information has to go to the performance schema in 5.7 itself. Then we have tables for system and such variables. And you will be able to get any variable info in 8.0 that I will talk about. And apart from that we have the various tables like threads, tables and all. Apart from this these are the statistics collected. Performance schema also aggregates the status states to be very good consolidated output of those statistics based on different factors. So we have some variables of units. These units are nothing but traditional state-to-stage. We have some variables of memory, IOS, connections and all the summary tables are categorized by different factors like summary by global, summary by specific user, summary by specific host this is the session and by digest. Now let's take a use case to see how we can use master performance schema to diagnose real-world issues. Before we go into use case let me give you the names of the functions you can get from the performance schema. So let's say IRAN is going to update the performance schema set of instruments and then it will be between us and the time it will be used. So what I am doing here is I am telling you in the performance schema you collect everything all the activities and collect time information let's say I have two sessions session one and session two in session one I did three insert statements and then I did a select star in session two I did two insert statement and you comment that's it. Before we go to the next slide just keep in mind there are three insert statement in session one two insert statement in session two the last statement is select star in session one and last statement is complete in session two. So this is all and that's all happening in the server as of now. Let's go to performance schema and see what it is. Performance schema has a table called event statement current table which gives us information of the current statement being executed or the last statement executed by the thread. So if I look at this table I see okay the 3024 has executed the select statement and this is the next object statement. Similarly, 3025 is the correct statement. So with this table you can see all the last statement executed by any thread on the server. Okay so I got the last statement. How about if I want to see the consolidation by session or by thread. So we have a table called event statement summary by thread which gives a summary of the statement executed by a specific session. So we see the first section has executed three insert statement and the second section has executed two insert statement. So we got the last statement. We got the statement by thread ID. Now how about at a global level it doesn't matter from which thread the statements are coming from. So we have this event statement global summary global where we get the file insert statement executed as of now on the server and file commit statement executed on the server. So that's a glist what we can get from performance schema. Now let's see if we use cases. Let's see if it is happening on the server. A lot of queries are executing. We want to find out the long run query. So get performance to my help here. So let's say x is a threshold. So anything goes beyond x when you take time to do more than x if you say that's a long run query. So we have this event statement executed. The current reversal is the one which gives you the last statement. The history table gives a historical data of the thread. So I see if you give me all the statements you can find more than x. So performance can be used in this information. It says ok now there is a select statement with the text like this. This is the guy who is taking time more than x. And if I go further I see select scan is going to be the one. And no index need this one. So for this select statement it has to be put into the scan and there is no index used for that. So if at all I could introduce an index and an index which is the select statement I would like to put into the performance of this query. Let's take another use case. So we have server running which has a lot of queries running. And I see in the event statement by Dalit Est these are the statements that we have executed and we stated that we executed first time to do for that time. And for all of them it has resulted which we have to look into. And the first time the statement was seen is this. Let's go to the third use case. I am looking at my source code and in my source code I see in the decision that the one which is not moving is stuck. And the performance is going to come if I have to make this scenario. So I said we have an event statement color and deliver right. It gives me the color statement that I executed. Similarly we have an event statement where it is going to be the performance schema which tells me the way it is going to be executed on the server. Similarly the way it is going to be the current way it is going to be D1 because D1 is the one which has been stuck. So I get this. So D1 is waiting for a mutex A. Now D1 is waiting for a mutex A and it is not able to proceed. That means somebody is holding mutex A. Now I go to the mutex instances table and ask who is the guy who is holding mutex A. So from this table I come to know okay there is a 32 which is holding mutex A. Now 32 is holding mutex A and not using it. That means 32 is also a bit more something. So I go further with the same table and this time I will ask about D2 and so on. So I can keep on going like this and finally I can find out the culprit was the guy who is making everybody vary in this chain. Okay so event hierarchy. So performance scheme of maintenance and event hierarchy what are the events? So when a session is connected to the server it might execute multiple transactions. So all those transactions belong to that session. In a transaction there would be multiple statements and in a statement it goes to multiple stages. The stages could be passing the query, optimizing the query, opening the tables, fetching the data. At every stage it has multiple weights event. So when you say opening tables it might go through a way to get along on the table. So this hierarchy is maintaining performance scheme out. I will give you a glimpse how this is maintained. So as I said in transaction I have multiple statements. Statements I have multiple stages. Stages I have multiple weights event. All these are the events which are happening on the server. To uniquely identify these events we have given them a specific ID that is called event ID. This event ID is unique for every transaction on the server. Apart from this event ID there is something called nesting event ID. So the nesting event ID of weights events which are coming from a specific stage event points to the event ID of that stage event. Similarly all the stage events which are coming from a specific statement will have nesting event ID pointing to the event ID of that stage event. Similarly all the statements which are coming from a specific transaction will have the nesting event ID of the transaction. So with this hierarchy we can take the very low level weights event and we can trace it back to the transaction from which transaction this weight is coming from. Now let's see once we have in MySQL 8.0. So in MySQL 8.0 added a lot of instruments. We added instrumentation for errors. We added instrumentation for variable e-function. We added instrumentation for data loss data loss weights. We added instrumentation for statement histograms. And we added instrumentation for the added new feature like plug-in component table service. We added the aspects as well. We have indexes and performance tables now. We have data square sample. Let's talk about the added instrumentation first. So if at all any error comes on the server and this instrumentation is enabled performance schema collects the statistics for the error. This error could have been handled error or not handled error. And they are summarized based on the user from which user how many errors are coming from which section error is coming. Let's take an example to see. Let's say there are two connections. One is in a select star from an non-existing table. The second is in a try to use a data which does not exist. And try to select from a table which does not exist. So there are three notable does not ever exist error. There is one data which does not exist error. Let's see how the performance you are getting in this information. So we have this clearance error summary by thread. Number 47 has one does not exist error and 47 has one bad data does not exist error. So this is by thread. Let's say at the global level it says on the server till now there are three table does not exist error and one data does not exist error. So there is a new table and variable info. With the help of this table you can see which variable has been set by whom and what kind of variable has been set and what is the source of the variable. This is a persistent value or explicitly set by the user or it has been set from the command line. Let's see an example. So here we have this variable name max section. We see the variable source is compiled. So it has a compiled and default value. The minimum value of this variable is this, max value is this and nobody has set this variable. Now I will write this variable and eliminate persist. Restart the variable will exist here and now I will write to this table and I see this variable source is persisted and persisted variable. This is the file from where the variable function has been taken from. This is the guy who has set the variable and this is the time stamp when this variable was set. Now we have indexes and performance tables in 8.0. Here you do a select stamp or you ask for a specific row for performance. All the rows with the upper layer and the upper layer used to do the filtering with this feature. Performance method does the filtering and returns on the specific row to the upper layer. It improves the performance of performance query. Let's see an example how it does that. So let's say we have the variable name from this and the same index. Indexes has been stored here. Now I try to look at handler's ABI execution. So I see this handler read random next ABI has been called 291 times. So this ABI this is an ABI which is called the data proposal in here. So in this particular example 291 times it has been called it is asking the row for performance query. Now let's modify this example. In this time I am using index and if I see the handler statistics I see that a handler read next has not been called in months. And only one time the row is encrypted and passed to the pathway performance to the upper layer. Now we have data logs tables also added to performance here. So let's say transaction A is locking a row R in transaction D also it intends to lock the same row. So B is typically locked by the transaction A. So this table gives me which data has been locked who has locked this table and who has locked this data and who is waiting for this data. Let's say an example this is a guy who has been selected star for even for update. So all the rows on this table have been locked. If I see the monitoring session, I see the thread added 51 as the question that it is locked on the record and it has been monitored. Now the session 2 it tries to get the rows from the table but because it has been locked by this particular guy it goes for a way. And if I see monitoring session performance in the session, I see thread added 52 is waiting for the record lock. So this is an article this time. And then we have a statement list column which has been added 8.0 also. So earlier we had from this, let's say from statement digest statement we can see the minimum and the statement we can see in the minimum time, the statement we can see in the maximum time and the average time. With the help of this type we have defined specific pockets of the latency and the statement which is taking a specific time falls into a specific pocket. So this is event statement list column by digest. So these two digest are same. So these are the same type of statements but it's pocketed type of low is this bucket number 116 and bucket number 112 is this and timer high is this. So this is the minimum and maximum time time for the pocket. And this is a pocket quantile which is 0.4 which is 1.0. So let me say that 40% of the statement of this digest falls into this pocket and has been executed with the latency lower than this time. And here the quantile is 100 to 100% of the queries of this digest have been executed tiny less than this particular. And apart from that we have we already have a stable event statement by digest where 3 new columns added in the stable are quantile 95, 99 and 99. So it says 95% of the time that query was executed less than this particular time. This is 99% and this is 99.99% of the time that query has been executed less than this particular time. Then we have this flammie and component table service. So as performance table you are not supposed to modify you cannot modify the definition, you cannot add your table. There are also differences in the capability that we want to expose our data in the performance table. So this we have provided a flammie table service. It gives you two methods add tables and delete top tables. So right, so using this service you can add your own table performance and expose your data. And once you are done you can view your table and say view table. So there are two examples implemented which are shipped with the master server one is flammie example one is component example. The flammie example of plugin is example of component example of component. Just go through these two examples and you can easily know how to add your own table performance table. And this is another request from the community. So we have this statement that is on the table. This has a consolidated summary of all the statements. They wanted to have one sample query in the schedule itself. So we added that there is a new variable added to the flammie example. So the query is a sample for this particular time. If the time expires it will give you a sample. Here is the example. So let's say there is a session. It executes three statements. These three statements are of similar type. So they will have the same digest. And if I see event statement, somebody by digest table. These three new things are there. Quays and pretexts are from T1, VSEO and VSEO. This is the first query. When was the query seen? And how much time this particular query has taken. So this is the summary. In 8.0 we have 100 plus tables. We have 1200 plus instruments and activities. We have instruments for high operations, logs, memory usage, connection types. We have instruments for transactions, statements, stages. We have instruments to program, procedures, functions, triggers, events. Instrumentation for global. Global session variables, global session status. Summary tables, indexes and all these summaries are categorized based on users, sources, account sessions. Okay, apart from that we have something called SIS schema. SIS schema is a collection of procedures, functions and views. They are the day-to-day things with DBS faces and we have put them in the form of procedures, functions and views. And these guys will take information from performance schema tables and information schema tables and with the statistical information in the form of a user. This has been very eager to see features by the community and important feedback about it. So maybe you guys can go back and look at it right. This is happening at the runtime so definitely it will have some overhead. So if you enable performance schema, you will see some overhead. That's for sure. That's why we have to be very careful of what you enable or not to enable. But by default we have enabled fewer instruments and I guess there was a batch content on that and it was noted that 5% with a default instrumentation enabled there is a performance of 5%. What is another question? There is self-reporting in self-reporting because you might have consolidated statistics of where that is. Okay, so self-reporting is if I guess the performance schema suggests something, that's what you are saying. So performance schema does not suggest anything. I'll tell you. It collects the statistics, it presents you. This is what the status is. Now you have to use your whole view to speak or your own many comments or whatever you want to say and then you have to decide based on that. It does not do anything so it just don't give you any function what is happening. Third question is it doesn't have an UI. It has this MySQL Enterprise monitor which uses the performance schema at the very low level and it gives you a new UI of what is happening on the server the graphs and pie charts and all those. That's an enterprise. For the session event we have showed us the common common event with do we have a heatsink event with just like in the local that we have at least something heatsink. What is the heatsink event? What is the heatsink event? The current session. You want to see the heatsink? Oh yeah, the heatsink is there. The point in time is that you see that in the past 1 hour throughout the heatsink. What is the heatsink? That is not there. So there is a limited buffer in the performance that you can collect the statistics for. So you have to specify you want let's say historical statement statistics of the last 200 statements or last 1000 statements. Yeah, so you can say collect the statistics of the last 500 statements but you cannot say collect the statistics from last 2 hours or last 4 hours you cannot say that. Any questions?