 So, MySQL troubleshooting and performance analysis, that's a very broad topic. Very broad topic. And there are a lot of very, very interesting tools and opinions on the internet, right? Especially Stack Overflow. Yes, that's actually a lot of time we do Google, right? And then the first one actually is that overthrow. What is it? That overthrow is like a forum. Yes, well, you know, we say that we are looking for full stack overflow developers, right? Correct. So, is there any definitive resources that we can look or read to really, really come to get a grip of Microsoft, sorry. MySQL performance analysis. That's what I'm going to talk about, and I will share some of the resources, which is one of those actually in my talk is Dimitri. Okay. So Dimitri is a person, but it's also a website. Very exciting. Thank you. Well, let's give a round of applause for Ivan, and we're looking forward to hearing from you. Thank you. Thanks, Masterist. And one of the things that I want to get your attention is there is one thing. Okay. Do you like to play games? Do you like to play games? Love games. Yes. Okay. So I like to share this game to you, this one. So do you know what the game is? Sudoku. So can you, do you know what's the number at the three? After five three is what? Easy. Is it? This should be easy. Either it's one, two, four, six, eight, nine, right? It has to be. So can you, do you consider actually in SQL how to run this? So data is very important. Processed data is also very important. To process the data, how can you solve a problem? This is what we, today's, every day's our life. Am I correct? So this is the problem solving questions. So how do we actually do this? So the problem is here and the solution is here. SQL. So this is, what it is, is common table expression, but with recursive way of doing things. So here, that you do, in here we have the problem, my problem, which is the, here, my problem. And as the input with how many digits? The digits is 81 digits, right? So it's actually my problem, five, three, dot, dot, bum, bum, bum, right? They are the digits and it is the problem. So the problem can be solved in here. So do you have the computer? Have you installed my SQL 8? Type it. Just joking. Anyway, yeah, you can find my, actually, my slide and actually my blog as well and putting this actually on blog and you can find it. Okay, you can just put it x tabs and copy and paste. So my problem and then this run will give you the answer. Am I serious? Look at this. This is the problem of that map. It runs and this is the SQL statement in just 0.03. The answer is 8492 blah, blah, blah, blah, blah, blah. You see? All this actually is coming quickly. So I'm just actually in here to show we are here. Everybody creating applications to solve problem. So either you do it this or you do it your own way, but we want some easy and handy way of solving problem. So my SQL can do this. Have you ever considered this is actually very powerful and what we solve problem is to solve some sort of data problem in many cases because we have data we need to solve. Without the data, you no problem to solve. Consider no input, no output. So that's the way the data is actually being stored somewhere and then we need to process. So this is so close to the box which the data sit onto it. So this is just a, I put it aside, another, my favorite game and the topic is not Sudoku. So this is in fact our topic. So let's share our main topic for today. So troubleshooting, like I want to share some good stuff which is we can use in our daily, if you are doing the daily activity with database, particularly in here in my SQL. In fact, troubleshooting and live performance tuning is not just database. This can also be applied to anything else. So do you remember what we have talked about the last three sections? The first one, the very basic, like DBA and also our commercial and our commodity. And my session with the NinoDB cluster and also the trend with low SQL and this is like all set already and we actually we touch the system is getting growing and growing. We need to be more performance or stable. So we need to look at those. So they are. So as troubleshooting, where we look at the informations? Informations likely on the screen. Something happened or complaint or the log files. Am I correct? The log files. Okay, we likely we see issues. Okay, complain. And then the system is very slow. And then we may look at the monitoring. Okay, whatever it is, something will report to you. CPU is getting slow. Memory is not enough. Okay, something, whatever we just put a code. Monitor. So when there is this issue, okay, we will have to what? The first somebody will tell, ah, shut it down and reboot the system. Is it sometimes, right? Sometimes or every day. Okay, reboot. Okay, things happening. Reboot. Okay, recover first. Recover first. Reboot. Okay, so as like another alternative, people will look at redundancy. Instead of reboot this, we have to put redundancy so that the servers can actually be run on this, another redundant server and you can look at this problem server. Problem, if you don't have the redundancy, you cannot touch this like the problem server because we need to offer the servers, right, immediately after the failure. So we need redundancy. So H8 is so important. So after this, then we can take a look at what we do like with class analysis and then troubleshoot, right? So this is kind of, okay, yeah, we need to do this. We may look at some other tools. In my SQL, we look at the logs. So the logs, maybe we have to look at where the log file is. So there is somewhere we put the log file. The variable name, log errors pointing to the files that we put it in and there is slow query logs which actually it points out the SQL statement which run for a while, which is greater than the long query variables and there is also a general log file which points to a file which any SQL statement it runs, it will just write to this log file. We turn this on, general logs. And we may look at the application log, your application, Java, PHP. So they are also the information that captures why there is errors, why there is problem. We may look at the system level, S trace, whatever trace. Do you use S trace? You do. So you are really engineering. So S trace, what else? Is it LSOF? Or is it LSOF? It's like which open files is attached. So they are kind of the tools that I usually look at this, LSOF, IOS that, MPs that. So they are quite a good tools and also like Perf tools which has a lot of information. Some sort of piece that, VMs that, etc. And there is one website. So if you look at, even you have handful, Type it, Dimitri, K.free.fr, type it and look at it, what it is, the website. This one actually is a person. It's a person who created this many, many years ago but still skipping always, actually every actor. And I like it. So there are other kind of tools for system monitoring. And this one is really free to use, Dimitri. It generates graph and also dictates what happens. So I'm going to talk about this in more great details. And there are other kind of enterprise tools. In MySQL, we expect people to know about MySQL Enterprise Monitor. I know there are many other kinds. No matter this is like enterprise company, HP, IBM, or whatever out there, there are many type of monitoring tools. So look at some basic information about MySQL. The website, does it work? Yep. So just register this website. Later on you will see a lot of good stuff from there. So here, the first thing here is talk about performance and system. There is a so-called the diagnostic function. So MySQL from 5.7. So what is the current version of MySQL? The latest one. What is the MySQL version? The current version. What is it? 8. So the previous one? 7? No, 5.7. It is 5.7 and now it's 8. So even this... Oh, do you know what date? We call GA, the first release date. The first release date of MySQL 5.7. What is the date? What is the years? The year of the GA date for 5.7. Do you know when? This is the first release. 1994 because 24 years. Good. Anyway, you remember well. I pass this to you. But my question is not that one. So 5.7. What is the release date for MySQL 5.7? No, 2,015. 5.7 is 2,015. MySQL 8 is 2001 in April. Meaning that last year in April, the MySQL 8 release, it has been in the market for one and a half years. So whatever so-called the product, we have a life cycle. No matter what, when we have a kind of product, it has to go to a life cycle. Open source, there is a life cycle. You create one and then never end. Never end. It means nobody support nobody. Actually, they are interested in it. If no life cycle. It just be there and then never change. Means no one is interested in it. So if someone interested in it, meaning that there are changes, there are changes, ongoing and the version will be up moving. So the product in Oracle has a process. The product is released over 5 years. We will schedule this product to be like going, going, going down. We call it 5 years after GA date. And then we will go into the extended model. And then forever model. Extended 3 years and then sustained forever. That means there are like 8 plus more years for each product. But for the 5 years, it means that we are no longer, we are no longer to put new things onto that 5-7, new things. 5-7 start the release date was 2015. 5 years after 2015 is 2020. That means next year, 2 months later. So I'm telling you, something actually 5-7 is kind of going there, right? So people look at 8.0. Why? Because there's nobody who tries to keep an old product forever. We have 8.0 in the middle already and it's actually people using it. When anything new we put onto new things, not to put back to the old things, right? So that's the reason. Move up to MySQL 8.0. It is time to run 8.0. Here, I'm talking about the sysgamer. It has been there even in 5-7. 5 years ago. So that means when people has problem. Wow, good thing is you can run core sys.dynastic. There is a package. There is a function. It will generate the information, internal information as a report. So do you know what is the Oracle command to run this kind of report? Do you know? Step back. Who is running Oracle? Who is Oracle DBA? Oh, no. Then what is the Microsoft way? So Microsoft, how can you generate a report? What is the package? Sys, SP what? Monitor. Side base. Right? So no matter what, every product they have a package and system service to run a report. So this is like you invoke this, it will try to generate over-the-time relations one in 20, I mean two minutes. Over the two minutes to get the report at the end of 30 seconds. So there will be delta. Start it after 30 seconds, after 30 seconds, after 30 seconds. So it captured the delta, delta, delta. And at the end, this is the report. And we know how many IOs, how the CPU differences, what are the logs and what are the table activities and what are the SQL statements running and what are the table structure actually changed. So they will be captured. And those are actually important for us to know. And in particular, when we have problems, we may ask people for help. Am I correct? If you ask people to help, so gentlemen, please help me. Then what do you say? Please help me. Then what do you say? You ask question. Am I correct? So what kind of question you ask? Please help. I have problem. And this guy just say, I have problem. Please help. And then what do you say? What are the symptoms? What are the symptoms? And then you will have so many questions giving a chance for him to speak. We pass the reports to him and then you look at it first. They are actually the what database we are running, by what time, what kind of objects, what kind of SQL statement, what kind of logs. So that's the ways that we collect the information and pass to somebody he may have basic at least basic information and then we can go and talk. Without information it's like Q&A for a week. Am I correct? So this is kind of good stuff we can actually dive into. There are things like the environments where we put this what are the port number and version what you use. A lot of time when we discuss and then talk to somebody to help they will what version do you use? Are you running Linux or Microsoft? So you have to answer a lot of questions just Q&A. This is the report. Just shut the mouth up. Right? It is quite important we share this and there are also the changes regarding so they will ask what variables do you use? Do you set this? Do you set this and how much memory do you use? There are a lot of things. It is all inside the report. So and many things that happen in the database about transaction. That log or memory and how it is written so they are actually in the InnoDB engine status. It is in the report and it is actually across the time slide 30 seconds, 30 seconds, 30 seconds so it is not just one so there are delta changes along the time. Along the time the changes may indicate something idle or activities. And there is also redundancy, the replication information. Here is none is something but actually this actually gives informations to the support of people supporting you to that you are running the environment with not just one server two servers, three servers and how it works and does it actually give some information and there are some other information what is the file I owe how the activity they will be shown in the report directly so without being asked they are like the best practice you see is already put into like a script and just to run and then you can see so from day to day maybe this is also a good report and then you keep track day to day because this is not very heavy, they are heavy one you just tune the parameters, you want a full report or medium or very just very lightweight so basically this can actually generate some kind of process file and also how memory is used and there are more and schema information and it tells how big the system is, how many tables and where they put those actually things and so they are all there, so the structure to certain point one look at those they have the idea how big your system and what we actually we talk right and there are things regarding all this how the I owe is not how much is actually the percentile the 95 percentile I owe is on this file then it's highlight all this like the high peak objects and as by SQL statement and also what the latency for the threat within the server is also highlight and by the latency with the file I owe and also the weighting and also the index statistics they are important for people to support and to look at it so this is kind of another statistic it has the idea what the report will show the update latency and it will tell all your kind of the feeling when the one who knows how to read this data if you know that oh that may be a table always read and the latency is too high your I owe may be problematic and then it will jump into what the actually can be the solution you can see there is also the I owe weighting and the background and what is actually doing and the binary is actively being written and in many cases my SQL if something being actually working on the database it has to be that we do a lot and do a lot so many cases they are really the top three file data and term in many cases they are so they are very important but without this chart you never know where to put maybe it is good idea from this you understand the I owe activity it is very hard and active on some files and some specific actions then what we do and make it better is okay I may put this specific files to somewhere else which is SSD or some other storage instead of right now this is very slow we can split the I owe so they are the important files and those you can see the latency to update okay they are the things it will be very very detail and highlight what they do is not by file is by action by action talking about your activity we are not talking about your server is just idle and you run the report your system is running and you run the report and at that time what kind of activity mainly the latency is putting there and what are the stress we are putting there and how we are solving this performance here at least we understand and then we know what to look at they are the key so by default MySQL has all this a lot of details to look into it you see to run this diagnostic it come up with the report and they will show also the delta status as what I said at interval 30 second it will try to dig out information once it's up to you 120 30 they are the parameters so it's up to you to get the delta of 30 second or the delta of 1 minute or delta for 1 hours likely it may be not too short or even not too long it has to be target to your application so here you can see the delta you can see all writing to the log file it is like this memory and also statement during this time it is running this ABC SQL statement so there is also other system metrics the metrics which talking about how many select statement, how many update there are many counter counter in the system so by comparing the delta we know during that time how many select statement it runs how many update statement it runs so there are so many delta counter or even the binary log cache and the disk and IO right all this information they are the counter in the system we compare the metrics we understand have the idea what are the activity during your monitoring stage so they are all there is this enough or what is so called the best practice so I just mention a kind of tools very useful and everybody can run because I don't use any tools it's just the database itself it has it but do you know that even you running my SQL you never try right who else my SQL do you know that diagnostic you never will you try yes because just so simple right just to run and then the report is there why not so I'm here to let you know there is something but is this just enough just text is that good I don't like just text okay but anyway do we have a very good practice of best practice there is so called up to you everybody is the best practice is different because everybody has their own constraint anyway so we need to look at our own okay so monitoring is a must so we have to know things performance 101 evidence you tell me you have problem I don't trust right you tell me is running very yeah slow slow to you maybe very fast to me one second to you is fast one second to me is slow so everybody to tell this kind of statement is okay we need to find the evidence so the evidence we have to issue some sort of report diagnostic and then I look at it and what you look at it and I look at it okay all yours low is this okay so we have to compare and then measure what your statement is so we don't trust every words but we listen to you and then we find ways to get the evidence for what you said so monitoring is important so this is kind of the report look at the URL it is that URL to generate all this reports it will show this kind of activity talking about benchmarking so sometime performance okay it's like you have problem and then you fix benchmarking is something you do not have the problem first but you go back to the early day zero and plan that's the benchmark so benchmarking is some sort of you anticipate what will happen and then putting in the magic your magic and then generate the chart and then you foresee what will happen and what you have to change so here there is actually talking about update online sections so here the section is going a lot a lot a lot and here the activity getting a lot a lot a lot but there will be more users to do update, activity will go up but at some point it just settle down there the system just to be able to run 10 is 10 it cannot run 100 cannot be linear at all time it has to be slow down as per two so what we do like performance tuning is an infinite loop tuning we change something and then come back then we change another thing bottlenecks it's never be able to solve because bottleneck is just a movable is you fix this so called a bottleneck it move to somewhere else you fix so called the CPU and it will be the IO you fix the IO will be back to the CPU just is that enough to you and your system okay the performance so likely applications contribute 95% area that we can fix the issues no matter the problem is done there at the bottom you change the way how it works avoid it or do it the other way we fix the issues doesn't mean that yeah we cannot actually do things but likely contributing to the problem 95% is just the application and the design and the rest we still want to look at it at this point we focus on the database and what we can do so there are many tools to work on the benchmarking so we need some kind of tool to generate the loads and then from there we make sure that we understand the system behavior so all this you may find you may need to expand DBT2 or whatever kind of the ideas okay generate the loading and there is also the default MySQL there is MySQL slap do you know that MySQL slap no MySQL SLAPD so it is a process to generate the multithread and generate the random sql statement and then create data and select multithread and then it is a load stressing tool very dummy very dummy but it can actually ask you to input the sql statement and the structure is up to you and then it's you to provide the sql statement it's just thread management to run 10 threads at the same time and then when it exit it run again so this is like a jmeter jmeter right so you understand that tools so now get to another core in 10 minutes I finished that you look at this screen am I correct when you hook up this is the screen so this is tool and it has a server it has the agent so what that means here the server and the agent agent is to collect the statistic from your target server and then that will collect the data and put it back to the server and chart so there are things in here the presentation that's quite a good one if you have time just look at it and you go into there and then you install there are you download the services tab war and then you do all this blah blah blah anyway I just put it here for the sql the slide will be yes spreading out and you can actually find and then you can do it all so the data collections when it is run so here when this is executed so what we do here when this is installed and I install as the statistic server surface this statistic service as the binary I run it so what I do is when I collect when this collection tool is to run it has to connect to database and grab information it connect to database it also collect the system data system data CPU and all this like mill text and kernel parameters so this is like CPU all this it collect data not just my sql but if I need to connect to my sql I need to provide the connection detail where this is like once install we need to put in edit the dot environment my sql and then change the connection and use the name password so it connects to it and then grab the information back so once they install and then you have this and you can actually turn this on and you can run so to run it is quite easy okay so there is just run a single command easy collect it's really easy stack easy okay there are many command but there is one very easy way and the command is easy start with easy EASY so you look at it and you'll see that it's a collection of collection a collection of collection tools so once we have all this so right here you see this here to test test test test at the end there is easy stack you see easy so the first just an example I want to collect the mill text information I want to collect inodb I want to collect my sql there are many there are many tools shell script right there okay but I don't care I mean I don't care I run easy stack the easy stack will collect all this data and this data will be just be there and it will be because the data will be a lot and this tool will collect collect and by time it will zip it up one file by time zip it up because text is very flexible text very flexible so it will do this job and once we have this we can go back because the file is a lot of many zip because many times done zip we go back and then comes to the server and this is like environment as what I said I put the connection I need to change and they are the binary so this is the tool and easy stack and where I put the data collection data and for how long and 10 so for 10 seconds every time and running for one hour this is so simple install run data is back no tools to visualize that useless so visualizations install so it process to install is here and download so from here to download from here and the servers we will just to run so it's just install script install.sh you get the file install.sh in there install once install is all there and then package add and then you have to put in parameters and after all this install at the end so whatever it is start very first time you see right here there is dim start here and then it means start force just only the first time to start this and later on you just start okay the first time is start force and then it just start very first time after install actually the instruction is there when you install last statement it tells you so once you get this the server is up and you have to browser connect to your port number on your server you just find another VM and then install it and you have this page and go into the home page main page welcome welcome and you will get into a page and those all the charts will come in okay but how do we do this welcome and then you will get into this page but the data but before we have the data we have to create repositories to load the data into that repository it is in here so we put this analyze but analyze to do this first to analyze so here to generate the chart we will analyze and come into here we select the corresponding data and choose multi-hose as example multi-hose and then put in and it will choose all this coming up okay all the data and select one of those but where those data I haven't upload the data this is I go to the end and I will come back and then show you how we generate the entry in here so you can choose suppose at this point you do not have data if the data is back I upload and you see entries so what that is and then you upload and you will see all this and then go I will see all the chart and a lot many charts will be there it will be all the charts so how we do this collect the data we hit preferences actually we need to hit the preferences and create a new database because this is a data set we need to create a database to load the data into this data set and create this and we put it into myICem or inodb so I change this to inodb and once we do this at the end that preference is done that preference is done we have the files we get better files in here and the files in here and what we do what we do in here from we have all this zip file gz unzip the file so we need to unzip all the files it's a big file, many big files and we need to change in the folder there is a file called what is the file name, loadDataSH so the zip file and in the folder there is one file called loadSH go into it and change the name which I create a database so that's the base and put the name so once we put a name right there and then the title is just to be shown on the screen by the line and then once we have all this we execute this and then execute and back to then and the graph will be there so easy and very buildable and all this CPU chart in loadDB memory all there and then if you have chart and then you can compare you have the idea what happened and all the logs all the information is that it's not just text it's graph and charts okay, so any questions yes, any questions if you look at the system function or system variable we can use this after something wrong happened so when something wrong happened the CPU consume a lot of resources or the memory spoon and then we come to the system variable to see what happened so is there any system variable or system function that can monitor and then we can test the threshold when something wrong when something is going to reach the threshold and then it's going to to notify or do anything back to the system or inform the system okay, thank you so this is like a preventive measure so one of the thing is like as what you said is like the postmortem what is the envy cells coming up and people complain then we put the action in but the other way is you are talking about best practice okay, so things like threshold you prevent people from changes to even higher values or actually like the SQL statement is running too long so you actually we have to proactively to tell there is something wrong so what we talk about this from the commercial point we have the tools we call this did you talk about my enterprise monitor there is the enterprise monitor in the first part of this in the first talk enterprise monitor is the commercial package when we have you subscribe my SQL commercial version there is backup, there is security concern, there is also manageability monitor the monitor has the features we call advisor advisor we have over 250 250 more advisors in the monitors what those actually are they are the like jobs and tasks and the scheduler which to run on schedule to check parameters or configuration like password for the security like specific variable which you specify but should not be so they are all this kind of like preventive measure we call this advisor or best practice okay we implement in the monitor and this is I hope this actually the tools which you maybe look at it any second question thank you, thank you if I am using my SQL version 5.7 and I want to make it version 8 do I can upgrade it right away or use something great thank you so who goes to upgrade who has done any who has experienced to upgrade you long time ago do you upgrade Microsoft do you upgrade Linux yes, do you upgrade Oracle do you upgrade any like Firefox what do you do click the button just take the action right so the most important you take the action without taking action is not change so how do we do upgrade your question how the reason is that here is an example my company using version 5.6 for a really long time and they insist on not upgrading to version 8 so they might have their reason for that so I think it may be some reason that upgrade to version 8 may not compatible with the old version like look at just the PHP PHP 7 is not compatible with PHP 5 something like that that's exactly so have you done upgrade so in many cases when we do upgrade there is for sure a risk for sure there is a risk you upgrade and then it fail and my SQL is very generous many times upgrade is just work many times it's just work and if you try to make a guess why people not to upgrade many reason is to me when I look at it many reasons just very simple just we do not want to move anywhere because we don't need to move because things just work why move instead of talking about move to this and there is problem they haven't try yet how do they know there is a risk or there is problem only when the time they actually the problem happen we actually stick to what happen the first hours usually in the 8.0 is the connection the connection is the problem usually when those problems from security because security cannot be generous security we enforce the security and means some has to be changed in certain way my SQL 8 we change the way to enforce a security but we can turn this off but we have to make sure that you are well aware the security enforcement so by having to upgrade the first time with the old version usually when we upgrade just the server the client also need to upgrade something if it is JDB driver or whatever driver to connect there is something but nobody care and connect and this one is old to connect to new so the security may break so that is actually one point there are many other things maybe the parameters but in many cases they are not the concern 99% there are no concern but the connection security part is the first part thank you and if I may actually add to that because I see this happening very often okay so the first thing is that when you develop applications you always need to write your applications with tests with automated tests so that is the only way that you know that your application actually works under all circumstances if you don't write your tests basically the reason you call it software is because it is easy to change if it is no longer easy to change it is hardware and the only way that you can maintain it as being software is to use practices like TDD or having a whole suit of automated tests the second thing which I always recommend people do when they write their application people have this tendency of making this huge ball of code where you stuff in all your business logic your view logic your database connection logic everything into just one little PHP file right or one huge PHP file so what I always say is that the part of your okay so let me put it this way when you do your file reads and writes do you actually worry whether your hard disk is a SATA or SSD or anything else no the reason for that is because there is a file access layer that handles the file access layer and the driver that handles all the particular of your hard disk so in the same way when you write your applications you have to take away your data access layers right and to make sure that that is independent from the rest of your code this way while you're working on the top part the business logic someone else can be working on the on the access layer and testing it against the new version of MySQL that is very important yes thank you sorry my two cents thanks this is a very good questions we always face this one when we work with customer and for the initial deployments we always ask people to upgrade during the testing phase usually when we start a project there is ongoing and there will be another release and some sort of actually during the process people start to stick to one version and never move but during this process we put in place a so called upgrade process then after this deployments they have seen that they have tested the upgrade and in the testing or the early stage they will see upgrades plausible otherwise when they see upgrade them they will wow they are faced to do anything they stop to move so build this upgrade process into the plan at the very early stage this will be very flexible for the upcoming project or actually deployment so I think time is up alright well thank you very much Ivan and Ryan please give them a hand will you guys be at the dinner tonight at the social dinner okay we have planned to go right to we have plans okay okay no worries alright well thank you everyone for coming and I hope you guys enjoyed the session hopefully we will see you tomorrow for another exciting day of Open Tech Summit Thailand thank you very much