 So we are getting to the next sections, Kajiyama. So this is about the performance tuning and also how we troubleshoot the way MySQL is doing. So MySQL can be heavy-duty. So how do we see the world is doing work and how we troubleshoot, how we make it better? So this is the talk. Okay, thanks, Kajiyama. Okay, plus to him. All right, hi everyone. My name is Ryusuke Kajiyama. I'm sales consulting team leader of MySQL. So today I'm introducing two topics, starting with troubleshooting. Well, it's more like, you know, how do you get information from MySQL which is useful for troubleshooting? I don't go into too much on the actual troubleshooting itself but how you can get the information and I rather go spend a bit more time on the performance tuning. So the baseline of the troubleshooting where you're gonna start with is always you have to find any issues inside of MySQL and it's mostly coming from log files. And what to do? Most importantly, protect your data, protect your environment, application, app, and running. So that's kind of ultimate goal. You're gonna do the troubleshooting but to avoid that kind of a situation, whoop, you have a pointer. You must have the redundancy of your environment which I've already explained using MySQL in-no-dv cluster for the high availability configuration. In case of failure of a single node, just remove it and rest of the servers will take care of application. So that's the beauty of the MySQL or in-no-dv cluster which comes with MySQL server, MySQL sort of 8.0 with the newest major version. No worry, even if you're using one major version older, that's 5.7 comes with in-no-dv cluster plugin as well. And then you gotta look into the details of what's happening inside of MySQL. Then there are many places you can look into the root codes. But first of all, where you gotta go is log files, especially the dialog of MySQL which gives you the old details of the alerts, what's happening inside of MySQL. Well, it's more like a server level of alerts. It doesn't give you the application level error. It should be coming back to your application side. So application team gotta look into the exceptions, error message back to application. Again, this error log is a server-side error only. And one more log you must turn on, by default it's off, but I will strongly suggest is a log file named a slow query log. You can turn on this log by the parameter slow or query log, and you can specify the file name. This slow query log, name sets, it will log every single set of SQL statements, which is slow. Then how slow is slow? It depends, right? So we can change the threshold. By default, if your query is running more than 10 seconds, your query will be written into the log file. And inside of a slow query log, it tells you actual query execution time as well as log wait time. The one common scenario, especially you try to update something, but someone else is also updating the same record that will conflict and because of MySQL has a nice log mechanism, one transaction is changing record, another transaction tries to change the same record, it might be or can be waiting for someone else. So slow query log also tells you because as a guy is doing something, you don't know that person who is actually doing, but you can know from the other points and at least this log file tells you how long this query was waiting for someone else. There's another log named a dener log. Usually we do not suggest, recommend you to turn on dener log because dener log logs everything. Every single queries and every single MySQL server's internal behavior will be written into this dener log file. So in the production environment, especially when someone was busy, this log file if you turn it on, it'll be huge. But when you need to debug behavior on MySQL, especially in test environment, you turn on dener log and running application one by one and you can see what's going on inside of MySQL well. And sometimes there can be the unexpected behavior, maybe application is running query so many times, which is totally, if it's not expected, yep, time to change your application. And skipping the next topic because it's referring to the next slide, of course it's important to know the environment, operating system environment first, before going to the troubleshooting. Because one of the common scenario or kind of mistake we are seeing is some queries, maybe so slow. And then application team is looking into, okay, let's try to choose SQL statements, maybe try to add index or removing index, modifying a part of SQL statements. But if by mistake, with some reason, that server is consuming a lot of swap, maybe you can find those information in the iOS startup, VM startup, especially VM startup or top, you can see server is doing the page, outer page in with swap. Swap is one of the slowest behavior inside of operating system. So even if you try to tune SQL statements, if server memory usage was not optimal, you could be maybe a bit faster, but still really slow because of a swap. So it's really important to study with broader scope and targeting the details. So the checking of the OS, especially tools like VM startup or top, to see the CPU utilization, memory usage, this guy, oh, and one more thing you gotta be careful is network performance. This is one of my experience of troubleshooting. At the one customer, customers complaining, my skill is so slow in response, especially for the big sector segment, a sector segment with big results. Then I looked into the environment, CPU utilization, not so high, this guy, not so high, they have a really good disk, but with some reason, the network engineer, by mistake, configure 10 gigabytes, one GB ESA network as a 100 megabps and not through Duprex even. It's kind of odd why he did it, hopefully not intentionally, but anyways, without checking network performance latency, if I could tune my SQL server parameter, our power factory and the query is truly optimal. If network performance is too slow, there's data set cannot travel back to the application immediately. So then we gotta check whenever you do the troubleshooting or performance tuning, really important for you to ask yourself, is this really the root cause? I'm not really looking at the right thing. You gotta have the bigger scope, bigger view of the entire things first and go into the details. Okay, then one more thing regarding to the performance and troubleshooting. I suggest this website named dimitri.dimitri.free.fr, this is a website of our performance architect. MySQL has a performance architect who is doing the benchmark only for his life. He's just doing benchmark, benchmark, benchmark every single day, but he also has a nice tools, this presentation includes a brief overview of that one. And lastly, if you have a large implementation, this is like commercial, the MySQL has our enterprise monitoring tool, enterprise monitoring. But today is not the main focus of my presentation. I'd rather go to the features, technologies available applicable to the communication as well. MySQL does come with the schema named the SIS schema. It was introduced in the earlier version of MySQL, but now by default it's available. But if you were using MySQL 5.6, you must install SIS schema. The good news is Ivermer wrote a nice blog to have the SIS schema for the MySQL 5.6. Newer version, don't worry, it's already there. Then one of the things I need to highlight here is this command in the SIS schema. It's actually routine, sort of routine, named Diagnostic. This command will help you to get all information you need to see inside of MySQL well. If you have experience using MySQL well, there's a command named show global status to know the MySQL server's internal behavior or really basic command, status. It tells you the really basic status of the MySQL server, like a version number, character set, or much more details. It's available single command. And one more thing is this will repeat every 30 seconds to get the fetches those statistics. But to use it, you gotta turn on the one parameter on top, sys.diagnostics.allowed underscore I S Tables. This is stored to fetching all the statistics information to store it into the cache. And you can see all the information. Then it's including global variables, inner-dv status. It's a do the show engine inner-dv status. And you can see the inside of the inner-dv storage and what's going on for the transaction, log, semaphore, and so on inside of inner-dv. This is a regular command show or engine inner-dv status, but this diagnostic gives you everything after a single command. What else? Replication information you can get, tons of things inside of a sys schema, including the inner-dv's transaction. For whole, sort of the process rest is like a client connection. And things related to IO and the weights inside of MySQL, it's like coming down to a social level of MySQL, even. And per table, per host, and so on, you can see with this command. So I guess this sort of was a really basic information, what kind of schema exists, how many tables, how many views there, and so on. There are a whole, a lot of information comes out. But if you tried in the console, output will be like this. That's too much, I know. It's too much to see on the screen, and as I mentioned, yep, so that's the output. Think of something else, something else, something else. On the screen, it's too much, and you can, you know, automatically refresh this one to get like every 30 seconds, every 10 seconds, whatever. But I know it's too much to view on the screen. So what I suggest is in reference manual, it tells you, so this is a part of the reference manual of this diagnostic command. Rather than only executing on the console, use a T command to specify the output file, okay. Then this command will repeat and push into this file. And you can read it later on, with, you know, text editor, whatever, or if you prefer to analyze whether, said or old, whatever the text commands if you love, and you can look into details. So there are lots of, lots of output, but the most important versus, yeah, the most important thing you gotta think about or look into is, of course, snapshot at the moment is one important thing to look into. But one more thing is you compare Delta from one output to another. Maybe the difference of the value inside of some output, different entries in the same output. So then you can know what's happening in the 30 seconds ago and now, and you can compare what's happening during that period. So this must help you to see a lot of details of MySQL server. But one more tool I need to introduce is this. Lovely MySQL Workbench. MySQL Workbench is a free GUI tool. It's a free, totally free to use, you know, to use all features. And I already have the pre-configured MySQL server connection. So I'm connecting the server. Sorry, this is not visible to me. So let me switch to the mirroring. Mirror the screen. Excuse me, just one moment. And switch to the laptop. Looks good. All right. So there are a couple of things we can do. Like you can type SQL statements here. You can write SQL statements like this. And of course, you can execute it. Whoops, some error it says. Yes, it's not connecting to MySQL server properly. Okay, so, okay, I'm not connecting to server properly. Okay, there we go. The next thing is you can have the really simple, but they're kind of good enough dashboard for you. Then you may run some benchmark. I'm just running a MySQL slap. It's a benchmark, easy benchmark tool. It's just mentioned quick. But now we see some spike in the, you know, DB's this guy, O or SQL statement usage. And below of this one, tell you like, well, there are like 16 selects per second in the last benchmark. And so on. So it gives you really, really basic view of what's happening inside of MySQL. Well, and one more thing you can check is you can see the sys inside of a sys scheme, as I mentioned, which is coming into MySQL 8 tells you a lot of performance statistics. So diagonal state commands, which is everything after trying for you, but how about memory usage? It's total memory is really small. And number of users is a bit small. So only like two connections, but each users are consuming this much. What else? This guy, oh, top file IO activities report. What is the last one? The last one is a new DB's data file. And under logs are also a bit busy. How about the SQL statements? Okay, diagonal sake, it usually takes a long time because like each query takes a long, long time, which I set like 120 sec in total. It's a repeat for 120 sec. And the insert statement took longer times than select, it seems like, by the number of execution time. Total time is right here in USAC. And you can see it on the screen. It's kind of simple. But internally, again, this one is using the so-called sys scheme. Okay, so that's the thing you gotta do. And this is otherwise from our performance architect, the guy named Dimitri. So what is the best practice, number one, for the performance tuning of my SQL? Is this, use your brain. As otherwise from our performance architect, yes, you gotta think about what's going on inside by yourself. But, of course, you gotta do, important thing number two is monitor your environment. Then to monitor your environment, there are different tools I mentioned, but one more thing you have to remember is the guy for the performance architect of my SQL created a tool to monitor your environment to create this kind of graph. And this tool helps you to tune your environment. It's like, you have to always tune your application, div itself, OS, and hardware, sometimes storage as well. But to know the query, the performance, the number of benchmark tools available which works with my SQL app. And our dev team is always using a tool named sysbench. That's a standard benchmark for the MySQL dev team. And our benchmarks are performance architect, Dimitri. He also created a nice statistics tool, or the monitoring tool named the dim start. And this dim start, including the set of the benchmark set, you can create environment, and I got tons of commands to setting up the environment. And it's internally using MySQL inside, by the way. And you can run these queries and you get the results. So these commands, for details, I suggest you to refer his blog again. It was Dimitri's blog, there we go. DimitriK.free.fr, slashblog. Slashblog tells you tons of benchmark, previous benchmarks of MySQL. And tools are available in the blog, the main page of his, and you can see the details. A couple things from his blog for the recent benchmark. Yeah, so one of Delta from the MySQL 5.7 and 8, read-only benchmark, more and more users, higher throughput, yeah, there are some more things we need to improve bit. And this is a read and write benchmark, I believe, yes. As you know, 5.7 is sort of MySQL 8, is way faster than MySQL 8. So our newer version is always faster, and there are lots of things we are working on inside of MySQL 8, and sorry. But back to the MySQL workbench, I want to show this one in a live demo, but the reason it didn't work. But a MySQL workbench, starting with MySQL 7 as well, it comes with a tool function, so-called visual explain. And this visual explain tells you how this query processed or optimized inside of the optimizer. This is the query doing the one, two, three, four, join of five tables. And each table, green ones are using a unique lookup, but wait, one table, we are doing a full table scan. So we may need to look into, to improve the, sort of look into adding index or modifying query to have a better performance of this query. You may get a better performance by adding index. And at the same time, there is a tiny number on the left top corner of each boxes. It says the numbers, which is the cost of each operations. So if this query is doing the full table scan, but cost was small, actually in this case, it's a really tiny cost. You may ignore full table scan of this query. You may change other things in this algorithm. Maybe this one having the largest number of the cost for the unique key lookup. So the unique key lookup, we may try to change the conditions, we may filter against this table first. And there are lots of output. It tells you, the blue one tend to usually okay better. Some green ones okay, red ones we gotta look into because it's doing the full table scan. So this is part of the MySQL workbench. It's a free tool. I strongly suggest if you download, if you haven't used it, I strongly recommend you to test with. And it tells you a lot of useful information. And I'm running a time, am I? Yes. So I direct to Financially O, single, single slide, so two slides. Inodivipa for pool is the most important parameter of MySQL to tune. If you cannot have enough time to tune anything else, tune this parameter to make this parameter as large as possible, but not make swap. Default size is 128 megabytes, usually too small for everything. If you have 16 GB in the server, and MySQL is only application running there, set this one to 10 GB. It's usually more than enough. Or 12 GB is kind of good to guess, but depending on the other parameters you tune. And one more thing, inodivipa log file size, you can just log file in the file system to store your ongoing sort of committed transaction. So larger is usually better. But maybe like, you know, if you have 10 GB of buffer, one GB of file can be okay. But you feel like, well, it's too much to tune one by one. The MySQL 8 comes with inodivididicated server, that's a new parameter in the MySQL server 8. You don't have to configure all the details, these several parameters about inodivis storage engine. MySQL, so inodivididicated server parameter, if you turn it on, it will detect size of memory and tune these parameters automatically. So this avoids you to do the manual tuning of the parameters, and we are expanding this feature, and we can automate parameter tuning with this parameter. So I will conclude my session by showing the data parameter in the console. Where is the console? Yeah, so this is a parameter named the inodivididicated server. Right now it's off, you can turn it on, and the MySQL server will tune inodivivuffer pool, inodivilog file size, and some more.