Showing entries 1 to 10 of 151
10 Older Entries »
Displaying posts with tag: performance_schema (reset)
Fun with Bugs #96 - On MySQL Bug Reports I am Subscribed to, Part XXX

My weekdays are still busy even during this lockdown period, but weekend is a perfect time for yet another blog post about MySQL bugs I considered interesting. Very few followers read my posts on other topics anyway, especially if they have the "M....DB" word...

So, here is a new list of optimizer, replication, InnoDB and few other bugs in GA versions of MySQL that I've subscribed to since the end of February, 2020:

  • Bug #98675 - "LIMIT clause in derived table garbles EXPLAIN Note". It's not a big deal and in specific case presented by
[Read more]
Fun with Bugs #95 - On MySQL Bug Reports I am Subscribed to, Part XXIX

With conferences cancelled or postponed and people forced to stay at home due to COVID-19 wide spreading, what can be better than to give my readers a new list of MySQL bugs to check? Useful reading should help! So today I continue my previous post with a quick review of bugs I've subscribed to in February, 2020, while things were still going as usual for most of us...

Here is the list of InnoDB, replication, optimizer and some other bugs to note while working with recent MySQL 5.7.x and 8.0.19 releases:

  • Bug #98473 - "group replication will be block after lock table". This problem report by
[Read more]
MySQL Shell Plugins: InnoDB

Today, we will cover a totally different MySQL Shell plugin: InnoDB.

Currently only 3 methods have been created:

Those related to the Table space fragmentation, have already been covered in this recent article.

Let’s discover the getAlterProgress()method. This method allows us to have an overview of the progress of some alter statements status like:

  • stage/innodb/alter table (end)
  • stage/innodb/alter table (flush)
  • stage/innodb/alter table (insert)
  • stage/innodb/alter table (log apply index)
  • stage/innodb/alter table (log apply table)
  • stage/innodb/alter table (merge sort)
  • stage/innodb/alter table (read PK and internal sort)
  • stage/innodb/alter tablespace (encryption)

This is an output of the method:

As …

[Read more]
MySQL Shell Plugins: check (part 2)

In the first part of this article related to the check plugin, we discovered information retrieved from the binary logs. This part, is about what Performance_Schema and SYS can provide us about the queries hitting the MySQL database.

Currently, 3 methods are available:

  • getSlowerQuery()
  • getQueryTempDisk()
  • getFullTableScanQuery()

The method’s name should be self explaining.

This is an overview of the parameters for each methods:


Some methods allow a select parameter if only SELECT statements should be returned.

When only one query is returned (default), it’s also possible to …

[Read more]
MySQL: Check who’s trying to access data they should not

To illustrate how easy it’s to see who’s trying to access data they have not been granted for, we will first create a schema with two tables:

mysql> create database mydata;
mysql> use mydata
mysql> create table table1 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));
mysql> create table table2 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));

Now, let’s create a user :

mysql> create user myuser identified by 'mypassword';

And as it’s always good to talk about SQL ROLES, let’s define 3 roles for our user:

  • myrole1: user has access to both tables in their entirety, reads and writes
  • myrole2: user has access only to `table2`, reads and writes
  • myrole3: user has only access to the column `name`of `table1` …
[Read more]
Time in Performance Schema

I've seen questions like this:
"Is there a way to know when (date and time) the last statement captured in ... was actually ran?"more than once in some discussions (and customer issues) related to Performance Schema. I've seen answers provided by my colleagues and me after some limited testing. I've also noticed statements that it may not be possible.

Indeed, examples of wall clock date and time in the output of queries from the performance_schema are rare (and usually come from tables in the information_schemasys.format_time() function converts time to a nice, human readable format, but it still remains relative - it is not a date and time when something recorded in performance_schema happened.

In this post I'd like to document the answer I've seen and …

[Read more]
Dynamic Tracing of MySQL Server With perf probe - Basic Example

I am going to write a series of blog posts based on my talks and experiences at Percona Live Europe 2019. The first one would be a kind of extended comment for a couple of slides from the "Tracing and Profiling MySQL" talk.

We can surely wait until Performance Schema instruments every other line of code or at least every important stage and wait in every storage engine we care about, but there is no real need for that. If you run any version of MySQL under Linux with more or less recent kernel (anything newer than 4.1 is good enough, in general), you can easily use dynamic tracing for any application (at least if there is symbolic information for the binaries), any time. As Brendan Gregg put it here:

[Read more]
Fun with Bugs #89 - On MySQL Bug Reports I am Subscribed to, Part XXIII

I have to celebrate the anniversary of my last day in Oracle (that was 7 years ago!) somehow, and I think writing yet another blog post about Oracle MySQL bugs is a good way to do this. I am actually surprised (and happy) that public bugs database is still alive, maintained and considered important in Oracle, and I know who in Oracle was working hard all these years for this to happen!

In my previous post in this series I've stopped on Bug #95954 and had not completed review of interesting MySQL bug reports that I've subscribed to in June 2019. So, below I start with the next bug in my list, complete review for June and cover some bugs reported in July. There were many.

[Read more]
MySQL 8.0: if I should optimize only one query on my application, which one should it be ?

Answering this question is not easy. Like always, the best response is “it depends” !

But let’s try to give you all the necessary info the provide the most accurate answer. Also, may be fixing one single query is not enough and looking for that specific statement will lead in finding multiple problematic statements.

The most consuming one

The first candidate to be fixed is the query that consumes most of the execution time (latency). To identify it, we will use the sys schema and join it with events_statements_summary_by_digest from performance_schemato retrieve a real example of the query (see this post for more details).

Let’s take a look at what sys schema has to offer us related to our mission:

> show tables like …
[Read more]
MySQL 8.0 Memory Consumption on Small Devices

Recently, PeterZ pointed a huge difference in memory usage of MySQL 8.0 compare to MySQL 5.7. This can be an issue for small instances if the same configuration for buffers like the buffer pool are not changed.

As explained in Peter’s article, this can lead to the awakening of the so feared OOM Killer !

MorganT, pointed accurately in his comment what is the source of such difference and how this was then caused by the new instrumentation added in MySQL 8.0.

Nothing is free, even as a …

[Read more]
Showing entries 1 to 10 of 151
10 Older Entries »