 Okay, so next up, we have Mayan Pesak, he is from Oracle, he will be sharing with us a little bit about MySQL performance schemas, so I'm sure we're looking forward to an awesome performance by you. I try, I really do. Ya, let's give it a few minutes while I set up 50m. So, I have the same refrain I have given in the last few talks. Please move forward, you know, because, you know, he likes to be hunked and in a cozy environment, stuff like that. So, if you can, one seat forward, you can't help too much. I'm not asking too much. Are you guys okay? Is it cold, is it too hot? Ya, I might have your lunch. Ya, do you need coffee? Or something different? Okay, I'm fine. We try to be bright-faring. I'm right for light people. Right, so, are you ready? Alright, so Mayan, take it away. Okay, hello everyone. My name is Mayan, and I've been working with Oracle MySQL for the last four years. I specifically work in MySQL Performance Schema Team and today I'll be talking about the same. But before I go, I mean, how many of you have used MySQL Performance Schema before? Have you ever heard about it? Okay, ya. So, this is an overview session. I will talk about what Performance Schema is and how it could be used. So, this is the idea of today's talk. So, I'll talk about need, origin, and designer performance schema. I'll talk about instruments and instrumentation. Then I'll take the use cases and explain how we can use Performance Schema to solve a particular issue. Can I talk about configuration of Performance Schema and what benefits we are getting from Performance Schema and the situation which we have. And then what's new in MySQL 5.7? DMR, DMR is Development MySQL. So, need. So, why do we need Performance Schema at all? So, there are multiple users of MySQL databases. There's an end user who might think, why my session is stopped? There might be MySQL level, but who is developing MySQL source code? Who might think, why there's a contention in my source code? Appliking level might think, why my applications are so slow? DBA might think, why this particular table is so hot? So, to answer all these questions and to give user insight of what is happening behind the scenes, Performance Schema comes into future. So, if you ask me to define Performance Schema, I would say Performance Schema is a mechanism to give user an insight of what is happening behind the scenes when MySQL server is running. The development of Performance Schema was started in 2008 by Markers. He is the chief architect of Performance Schema now and he is leading again. First time we introduced Performance Schema in MySQL 5.5GA, we introduced a new storage engine for Performance Schema. There was a new database introduced that is Performance Schema. There were tables introduced in this database which will store the statistical information of MySQL server. The DDL of these tables are hard to read. User is not allowed to modify the DDL of these tables. And the data, statistical data which is stored in these tables is not persistent. So, once the server is stopped, the data is no more. User can easily write MySQL query to get the statistical information out of the way. Now, the block diagram for the design of Performance Schema. So, there are two parts of Performance Schema. One is statistical collection. The other is statistics supporting. So, statistics collection is the part in which Performance Schema get the statistical data of MySQL server and store it into the buffer. Statistics supporting is the part in which a user issue a query and Performance Schema get the information of the user. So, let's say we have a MySQL server running. So, inside MySQL server source code, we have introduced few instrumentation points which we sometimes call Performance Schema Hoots. So, when the execution comes to these points, the control is transferred to Performance Schema. And then Performance Schema collects the statistical data and store it into the Performance Schema interval buffers. So, this is the statistics collection part. Now, suppose there is a user who wants to see the status which are collected. User just issue an SQL query MySQL server. This query will be passed and executed and that collected data will be fetched from the missing terminal buffers and reported to the user in Performance Schema tables. So, this is the word I use for Performance Schema Hoots. Now, let's talk about instruments and execution. So, what is an instrument? So, when MySQL server is running, there are multiple activities which are having a scene. So, all those activities are happening in MySQL server runs. And when we monitor an activity, we call that this activity is instrumented in Performance Schema. And as there are multiple activities in MySQL server, we uniquely identify those activities we have given them a name So, any monitor activity, the name of that monitor activity is called instrument. The name is nothing but a real-life structure in which different components are separated by forward slash. And as we move from left to right, we go from more generic to more specific name. I'll give you an example. So, let's say this is an instrument grade slash IO slash file slash MySQL slash now. This is an activity, what activity is this? So, this is an weight activity. This is the more generic thing. The weight could be multiple weights. It could be a weight for grade right clock, could be a weight for unit X, could be a weight for IO. So, this is a weight for IO. IO could be multiplied. It could be an IO on table, IO on file. What kind of IO it is? It is an IO on file. And what file is MySQL logon? So, this is an instrument in MySQL logon. Similarly, we have statement slash SQL slash collector. So, there could be multiple statement type, could be an SQL statement, could be a command. So, this is an SQL statement. What kind of SQL statement it is? It is a select SQL statement. It could be insert update also. So, this is also a name of the activity in mySQL logon. So, till 5.7.6 GMRs, we have 9 epithen instruments. So, close to 1000 activities that we are monitoring in mySQL logon. And the name of all these activities, name of all these instruments, and the information is stored in set up instrument schedule. Now, let's take a look at set up instrument schedule. How does this schedule look like? We have stored all the activities within mySQL logon. So, this is how that it will look like. The first column is name. The name of monitor activities. So, all 983 instruments are there. The second column is enabled. Do we really want to monitor this activity or not? So, on the client performance you are concerned with this table to see whether user wants to monitor this activity or not. And time means, you want to monitor this activity but do you really want to get the timing information of it? I mean do you really want to know how much time was spent when this activity was happening? This is the time column. So, if I take the first example, and time is equal to yes. So, I am monitoring this activity and I am interested in timing information also. But for statements slash sql slash creativity. I am monitoring this activity but I don't worry about the timing information. So, don't collect the timing information for this activity. Now, statements slash form slash creativity. I don't bother about the activity at all. So, performance now will not collect any data for this activity. So, when you are running my SQL server and you realize oh, I wanted to monitor select activity, you can go ahead and update this. You just issue an update command, this will be done. Now, let's talk about the use case where we can use performance schema to solve specific issues. Before we go into the use case, let me give you what information you can get from performance schema using very simple example. So, what I did, I updated performance to a set of instruments So, I have enabled for all the activities performance schema and for all the activity timing information also. Let's say, I have a match-up server running. I have two clans. One is with query 34 another is query 25. From this client, I have executed three transactions. And what I did is three transactions I just inserted into table T1. Then I executed select query. From collection T2 I executed two transactions. I inserted something into table T2. That's all I did. One match-up server, two connections, three transactions, three insert, two transactions to insert. And last one, select. Now, let's see what information what statistical information we get from performance schema. So, in performance schema, we have a table which is called event statements current. So, this table gives me information of the latest statement executed by any thread. So, last statement executed by number 24, select statement. So, it says, last statement executed by number 24, select statement. This is the time it took to execute the statement. This was the text of the statement. These many rows was written that I wrote in a scan. There was a scan there. There was a full table scan. Now, for standard 25, what was the last statement? The commit statement. So, these are only six which I have shown here. There are multiple columns in this event statements current. Okay, so, I got the information of all the latest statement executed by the thread. How about if I want to know all the statements executed by any thread? So, we have a table which is called event statement somebody by thread, by event name. For thread 24, how many inserts will happen? Three inserts of the experiment for thread 24. And for thread 25, two inserts of the executed. And how much time was taken for these three inserts? And how much time was taken for these two inserts? So, I got the latest statement executed by any thread. I got the statement based on thread ID or conservative statement. So, I want to know overall on the server how many statements executed. So, we have a table called event statement somebody global by event name. So, it gives me information. So, there are five insert statement three by thread 24, two by thread 25. It's written on the server. And five commit statement executed by the server. And this is the time taken in these two. Number of quotes affected by the schema statement statistics. Similarly, we can get information about the weight statistics. We can get information about the transactions. Now, let's come back to the use case. So, I have MySQL server running. There are multiple queries running on the server. There are a few queries which are running for very long. So, how do I identify what are the long rated queries? And how do I find out why these queries are running so long? Let's go to the performance schema. I issue a query on event statement display table. The total time I wait for time taken by the statement is given as X. Suppose X is the limit which I set if something is running more than X, it is a long running query. So, I got this question. I got okay from thread 25. The select statement is being executed. The text for the select is blah blah blah. This is a query which is taking so much of time. Okay, I identify the long running query. But wait, there is something more. Select scan for this query is 1. So, there has been a full table scan for this query. No index use is equal to 1. So, there is no index use for this query. So, this is the problem. This is the point we need to focus upon. So, there is a full table scan happening and there is no index use. So, if we can introduce an index in this table, they are performance of the query. What do we see? I explain the plan of this query. I don't have it here. But here you can get it. But it's in the table itself. So, true that the performance amount that we can see that we can plan for this query. Look, from performance amount, we cannot see the experience amount. Now let's talk about the new case 2. Suppose I didn't manage to start running and there are a lot of statements so, how do I identify what the statement is like in the algorithm? So, I execute a query on performance schema event statement summary by digest. So, this is a summary of statements which is summarized based on digest value of the statement. I get this. So, this kind of statement which is created in table if not over some topics. This statement has been executed 1725 times. Every statement is entered in an algorithm. So, this is a statement we need to focus upon. So, there is something wrong with this statement. So, I need to focus on it. I can get this information performance schema. And one more useful information you can get it. When was the first time this statement executed? This was the first time this statement was executed. And I am doing something in multi-terrain environment myself is done. Now, I want to conduct live session is done. Let's go back to performance schema. I have my thread table which is waiting. So, as I said, event statements currently will give the information of all the latest statement executed. Event bits current give me the information of all the latest bits executed for being executed. So, I find out what my thread T1 is waiting for. Select star from event bits current where thread ID is equal to T1. So, my thread T1 is waiting for a mutex which is mutex A. So, it is not getting mutex A that means somebody else is holding mutex A. Now, who is holding mutex A? Let's go back to performance we find out. Select star from mutex instances where object instance begin is equal to mutex A. I mean the name of the mutex in mutex A. So, I thought there is thread T2 who is holding mutex A. But why thread T2 is holding mutex A and not releasing it? T2 is also waiting for something. I'll go back to event statement current event bits current, same table and find out where thread ID is equal to T2. Now, what T2 is waiting for and so on. And I'll keep on going and going and find out where the portal is. Who is the guy who is incomplete for this simpler way or the wait cycle. Now, there is a very important aspect which is introducing performance schema. It's called event hierarchy. So, when MySQL server is running and the sessions are connected if session can execute multiple transactions in a transaction they could do multiple statements. Each and every statement goes through multiple stages. So, when I say stages these are like opening table, closing table fetching data, sending data all those are stages. And each and every stage could go through multiple stages. So, this hierarchy is maintained in performance schema. Now, I'll tell you how this hierarchy is maintained. So, as I said 4 events, transactions, statements, stages and waits multiple transactions running or MySQL server each transaction could have multiple statements each statement could have multiple stages and each stage could have multiple waits. Till now, this 4 events Okay So, these are multiple events when MySQL server is running to uniquely identify each and every event we have given them a name which is called event tidy. So, using event tidy I can easily identify what event it is in MySQL server. Now, there is something called nasty event tidy. So, what is it? So, nasty event tidy is the event tidy of parent event to which this particular event belongs to. So, for all these waits events if it belongs to this particular stage the nasty event tidy would be the event tidy for the stage. For all these stage events which belongs to a statement, nasty event tidy would point to the event tidy of the statement. And for all these statements, the nasty event tidy would point to the particular transaction. So, I can start on the lowest level of mutics wait and I can go back to the particular transaction and relate from which transaction this mutics wait is coming from. So, this is the event hierarchy which is maintained in the performance schema. Okay. So, there are a lot. I have given a few examples. There are a lot of things which are monitored in the performance schema. We have instrumentation for IO operations. We have 5 IO, 10 IO, socket IO. We have event stages, idle event monitor. We monitor program also procedure functions, events. And we also monitor activities based on user, based on host or based on account. So, you can say for this particular user how many statements executed for this particular account. How much memory has been taken in performance schema. We monitor memory also in performance schema and there are many more things. One is at build time when you are compiling my SQL server. One is startup. So, when you are starting my SQL server you can use multiplication file that is minor of CNF file or you can use command line operations. Or it is already running and you want to modify performance schema or configure it. So, at build time you can give this flag scene a flag element minus d with performance schema so it is equal to 1. So, performance schema code will be compiling. If you set it to 0, there will be no performance schema code in my SQL server. And suppose, there are multiple things in performance schema and you want to generate it. Okay, I want this, this, this to be a library or to be compiling. You can do that. You can say performance schema is what you want. But this is a QTX. All instruments of virtual activities you want to monitor. The performance schema instrument is called statement slash, skill slash, percentage. So, I want to enter all the SQL statements. And you can see the size of the performance schema internal buffers which I told in the first theory. And the command line also all these options you can give from the command line also you can configure the size of performance schema buffers you can configure the activities which you want to monitor but you cannot change the size because performance schema allocate all the memories for the buffers okay. Now, benefits and restrictions. So, the main benefits it gives an insight of running my SQL server. It's not a black box now. So, you can see what is happening behind the scenes. The granularity is very good. You can start from the lowest level of mutates and you can go back to the transaction from which transaction this mutate is coming from. It is available irrespective of platform. So, wherever my SQL server is available performance schema is available. And it has given it comes with a very user friendly experience interface all the TPS, all the users of database in those mountainous SQL. And multiple summary tables for consolidation. So, you can see the statistics based on user. You can see statistics based on account or based on post. And the best part is you can directly configure what you want to monitor and what you don't want to monitor. You don't have to restart the server again to start the computation. Restrictions. So, there is only one restriction because all this collection of statistics happens at the right time. So, there is little bit of performance enabled activities performance 5%. That is why it is advisable or it is advisable everybody if you are using performance schema configure or activate what activities you want to monitor. And in 5.7 dmr which was released on 6th of March we have lots of new things in performance schema. We have instrumentation for transactions we have instrumentation for metadata loss we have instrumentation for paid statement store programs memory usage which user is taking how much memory which performance being allocated how many times and how much memory stream as of now how many memory stream or this information installation performance schema or the user variables which you set when you run MySQL server that will be exposed to the performance schema skaters variable which you used to get from show status or show variables you can get it from performance schema now and the memory location of performance schema has been made scalable and the full print of performance schema have been reduced in the status dmr release and digest size has been made on three variable and till now we have 87 tables of performance schema so when we reduced in 5.5 we had only 17 tables now we have 87 tables and now we have 18 instruments so that means we have 18 rows of common activities in performance schema now and we have introduced the addition of 7 tables also so in the application whatever the status you can get from show status you can get all those information performance schema also we have deposit in MySQL okay so if you have any question anyway i would be sitting outside for MySQL so please come and talk to thank you