Uncategorized

Get to Know More About MySQL Performance Schema – Part 2

This section briefly introduces the Performance Schema with examples that show how to use it.

In Part 1, we have talked about how MySQL Performance Schema is useful and how it is architected using the performance schema database.

Now that we understand how it works with tables and what kind of information may be derived from it, you should also realize that not every consumer and instrument is enabled initially. This means that out of the box, the MySQL Performance Schema doesn’t gather information on all events. You can enable these events, enable timing for events and execute statements by using:

Screen Shot 2015-03-25 at 9.07.20 PM

Then you can get what the SQL server is currently doing by accessing the events waits current table. This will show you the latest monitored event, one row per thread.

[expand title=”Click here to read more about this article”]

Use cases

All of these information lead to one conclusion: the MySQL Performance Schema is very useful in troubleshooting a wide variety of very common problems with performance without you ever needing to buy or download any third party solutions. And it helps that it uses regular SQL!

The database administrator finds it useful because they are able to use the MySQL Performance Schema to get true measurements. Using event filtering, you can examine a repeatable issue using the MySQL Performance Schema.

You will need to enable the use case and then filter the tables to check out what the problem is, or what is causing the slowdown. Each time you are able to rule out a potential problem area, you will need to disable the corresponding instrument and then shorten the summary tables and history so that earlier collected events related to that ruled out problem area will not be shown. Repeat this process and each time you will have lesser noise being recorded by the events waits history long table.

On top of pushing out the insignificant instruments, you are also getting more data on the potential problem areas that you have not ruled out.

When you finally pinpoint what is causing the performance showdown, you can now correct it. Corrective actions include writing a query in another way in order to tune it, tuning the schema of the database, correcting the code, or tuning the parameters of the server such as memory, cache sizes and others.

You would need to repeat this entire process to see how your corrective steps have improved performance.

Another use case is finding performance bottlenecks using mutex_instances.LOCKED_BY_THREAD_ID as well as rwlock_instances.WRITE_LOCKED_BY_THREAD_ID.

For example, let us assume that Thread ABC is stuck and is expecting for a mutex. You can find out what Thread ABC is anticipating by using SELECT * FROM events_waits_current WHERE THREAD_ID = thread_1;

Once you find out which mutex is causing the slowdown, you can now pinpoint which other thread is fielding that mutex. For example, let us assume that Thread ABC is expecting mutex 123, you can use SELECT * FROM mutex_instances WHERE OBJECT_INSTANCE_BEGIN = mutex_A; to see that Thread DEF is holding mutex 123.

You can now look at what Thread DEF is doing by using SELECT * FROM events_waits_current WHERE THREAD_ID = thread_def;

Learn more about MySQL Performance Schema from Four Cornerstone. We also offer Oracle consulting in Dallas.[/expand]

Cloud Blog

4 Ways To Benefit from…

One of the benefits you get when you work with cloud applications is that you often have quarterly updates that are packed with features. This...

Keep Reading

Artificial Intelligence

Data and Analytics: Cross the…

  Artificial intelligence is a manna sent from digital heaven. That’s how blessed your business can get if you immerse into the AI of things....

Keep Reading

Business Intelligence Blog

How Brand Names Survive in…

  The age of digital marketplace has made it possible for unknown and smaller companies to compete with better-known and well-established brands. Take for example...

Keep Reading

Artificial Intelligence

The Phenomenon That Is Artificial…

  Artificial intelligence is when a machine does cognitive functions that are more associated with humans, such as thinking, learning, problem solving, and reasoning. As...

Keep Reading

Cloud

Digital Transformation in Banking: Shift…

  The start of a new decade is already shaping up to be exciting for financial services, especially for banks as they continue to compete...

Keep Reading

Business Intelligence Blog

Data Science and Its Economic…

It would seem that artificial intelligence is the focus of businesses that are looking to future proof their organizations and stay competitive. And why not?...

Keep Reading

Data Blog

Ensure Efficient Data Science and…

In an ever-expanding landscape called Internet of Things and the exploding development of artificial intelligence, we are bombarded with complex methods of integrating data science...

Keep Reading

Business Intelligence Blog

Cybersecurity: The Top 5 Expectations…

  If you think about it, cybersecurity is closely tied to human rights, privacy, freedom, and even basic safety. As a whole, we have become...

Keep Reading

Business Intelligence Blog

Tech Security Should Be Easy…

  IT professionals know that tech security is important in everything that they do. And that there are a lot of products, tools, innovations, and...

Keep Reading

Business Intelligence Blog

The Top 6 Success Stories…

Nearly nine out of 10 digital transformation initiatives fail. Digital transformation projects fail for a variety of reasons. It can be because of a lack...

Keep Reading

Live Chat | Emergency