CODEWITHSUNDEEP
mysqldatabase-performance
Douglas Gaskell
Douglas Gaskell

Reputation: 10080

How to reset MySQL performance reports?

I'm trying to use the MySQL performance reports to try and find what is bottlenecking my read and write performance under special situations and It's cluttered up with loads of old statistics regarding other queries to my tables.

I want to clear all the performance data so I can get a fresh look.

The Clear Event Tables button doesn't actually seem to clear anything.

How do I do this?

Upvotes: 4

Views: 3631

Answers (2)

DarDev
DarDev

Reputation: 153

Using MySQL Workbench

  • Go to Performance schema setup
  • Click Clear Event Tables
  • Refresh Reports page. All events will be cleared

Upvotes: 3

Rick James
Rick James

Reputation: 142453

(This does not answer the question as asked. Instead, it addresses the broader question of "how do I improve query performance".)

Here's a simple-minded way to get useful metrics, even for fast queries:

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

The numbers may match the table size or the resultset size. These are good indications of table scan and some action (eg, sort) on the resultset.

A table scan probably means that no index was being used. Figuring out why is something that metrics probably can't tell you. But, sometimes there is a way to find that out:

EXPLAIN FORMAT=JSON SELECT ...

This will give you (in newer versions) the "cost" of various options. This may help you understand why one index is used over another.

optimizer_trace is another tool.

But nothing will give you any clue that INDEX(a,b), which you don't have, would be better than INDEX(a), which you do have. And that is one of the main points in my index cookbook.

Here's another example of what is hard to deduce from numbers. There was a production server with MySQL chewing up 100% of the CPU. The slowlog pointed to a simple SELECT that was being performed a lot. It had

WHERE DATE(indexed_column) = '2011-11-11'

Changing that dropped the CPU to a mere 2%:

WHERE indexed_column >= '2011-11-11'
  AND indexed_column  < '2011-11-11' + INTERVAL 1 DAY

The table was fully cached in RAM. (Hence, high CPU, low I/O.) The query had to do a full table scan, applying the DATE function to that indexed column for each row. After changing the code, the index did what it is supposed to do.

Upvotes: -1

Related Questions