Monitoring MySQL with Performance Schema

Performance schema is an extremely powerful instrument to collect various performance related data on MySQL, and presents upon executing queries on performance schema. In this course, you'll develop an understanding and use of performance schema.
Course info
Level
Intermediate
Updated
Apr 28, 2017
Duration
1h 59m
Table of contents
Description
Course info
Level
Intermediate
Updated
Apr 28, 2017
Duration
1h 59m
Description

The latest version of MySQL has introduced a new feature of MySQL Performance Schema, which monitors MySQL server execution at a low level. The performance schema provides internal execution details of MySQL queries at run time, and it's minimum impact on the performance of MySQL resources. In this course, Monitoring MySQL with Performance Schema, you'll learn how to maximize your data-based performance with the use of performance schema. First, you'll dive into getting started with performance schema. The most important part of the performance schema is that it provides answers to many of the pressing performance issues, which MySQL developers and DBAs face with a "slow application" situation. Next, you'll cover how to identify resource bottlenecks, and develop an understanding for resource utilization. Finally, you'll explore how to address common monitoring real world scenarios. By the end of this course, you'll have the knowledge and fundamentals to monitor MySQL with performance schema.

About the author
About the author

Pinal Dave is a Pluralsight Developer Evangelist.

More from the author
Getting Started with MariaDB
Beginner
2h 23m
Nov 2, 2018
More courses by Pinal Dave
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hi everyone! My name is Pinal Dave, and welcome to my course, Monitoring MySQL with Performance Schema. I am an database tuning expert at sqlauthority. com. Data is very important for any application. But data-based performance is even more important. Everybody wants maximum performance for their application. Monitoring database for resource consumption and bottleneck is a very important task for any DBA. Once we know how each of the resources are consumed, we can come up with tricks to optimize the consumption of them. Some of the major topics in this course we will cover include getting started with Performance Schema, identifying resource bottlenecks, understanding resource utilization, and, finally, addressing common monitoring real-world scenarios. By the end of this course, you will know how to monitor your MySQL with the help of Performance Schema. I hope you will join me on this journey to learn Monitoring MySQL with Performance Schema.

Getting Started with Performance Schema
Hi! This is Pinal Dave, and I welcome all of you to the module, Getting Started with Performance Schema. In this module, we are going to talk about significance of Performance Schema. First, we will talk about what it is. And right after that, we will see why we should learn Performance Schema. Next, we will understand how Performance Schema works, and if you have not enabled Performance Schema in your MySQL, what are the next steps for the same. Before we end this module, we will see our very first script with Performance Schema. Well that's it for an introduction. Let's start our module with What is Performance Schema? in the next clip.

Identifying Resource Bottlenecks by Wait Events
Hi! This is Pinal Dave. I welcome you to the module, Identifying Resource Bottlenecks by Wait Events. This is one of the most important and critical modules of this course. Many of the MySQL users do not know how to identify resource bottlenecks with the help of Performance Schema wait events. This is because they do not know how to use them effectively and efficiently. When I'm not building courses for Pluralsight, I am helping various customers to tune their system for optimal performance. I have seen very slow and low adoption of this particular technology. I learned that lots of people find the entire process of Performance Schema very complicated and confusing. I promise that in this module, we will take some real-world scenarios and try to understand how we can identify resource bottlenecks with Performance Schema and wait events. Once we identify them, we will have various options to resolve them. Now let us see our agenda very quickly. First, we will understand what are wait events. Once we understand that, we will learn why should we use wait events. Next, we will jump to real-world scenarios where we will explore how to diagnose real-world problems with performance. Next, we will also see what are the various solutions available to solve the problems which are presented to us. Now let's quickly jump to our next clip where we will understand what are wait events.

Understanding Resource Utilization with Performance Reports
Hi! This is Pinal Dave, and I welcome you to the module, Understanding Resource Utilization with Performance Reports. This is one of the most simple but impactful modules of this course. As a matter of fact, when I go for performance tuning consulting, this is the one module which I prefer to teach them besides tuning their server. The reason for that is very simple. Every DBA wants to know what are the different resources available for their server and they can use them efficiently. With the help of performance reports, we can make this so easy that every DBA can use these reports themselves in the future. As a matter of fact, you may find performance reports a very handy tool to understand your various resource utilization. Now let us see our agenda very quickly. In this module, first, we will see performance reports related to hot spot for IO, high cost for SQL statements, database schema statistics, wait event times, and InnoDB statistics. From various performance reports, a DBA can find out what exactly is going right or wrong for their MySQL server. Based on that, they can do analysis and plan next steps for optimal state of their server. Well this is just an agenda. Let's get started with performance reports from the next clip.

Addressing Common Monitoring Real World Scenarios
Hi! This is Pinal Dave, and I welcome you to the module, Addressing Common Monitoring Real-World Scenarios. This is my favorite module in this entire course. My primary responsibility is performance tuning expert for relational databases. Every single place where I go to tune MySQL, I always start performance tuning exercise with seven practical scripts which I am going to cover in this module. The seven scripts are very, very simple, but their impact is very high if we look at performance tuning exercise. Trust me! When I'm sharing these seven practical scripts, I am feeling that I am giving away my business secret to you. If you want to build your career as a performance tuning expert, these seven scripts will be a great Step One in your career. Now in the next clip, we will see two very important lessons for real-world scenarios.