Showing entries 1 to 10 of 116
10 Older Entries »
Displaying posts with tag: performance_schema (reset)
MySQL Lock information in MySQL Shell

Last Tuesday, it was the very first session of DB AMA, Morgan Tocker made a nice presentation of MySQL Performance_Schema and illustrated it with some nice queries to get Meta et Data Locks.

As those queries were not that simple to write or at least to remember, I thought it might be a good idea to add them to MySQL Shell, the best MySQL DBA Tool !

I’ve then added a new method to the check plugin: getLocks().

Let’t see it in action:

As you can see, this is a small extension that can offers you a better view of what’s locked per transaction.

You can find several MySQL Shell Extension directly on github: …

[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:

ext.check.getSlowQuery()ext.check.getQueryTempDisk()ext.check.getFullTableScanQuery()

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]
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]
MySQL InnoDB Cluster : Recovery Process Monitoring with the MySQL Shell Reporting Framework

As explained in this previous post, it’s now (since 8.0.16) possible to use the MySQL Shell Reporting Framework to monitor MySQL InnoDB Cluster.

Additionally, when a member of the MySQL InnoDB Cluster’s Group leaves the group for any reason, or when a new node is added from a backup, this member needs to sync up with the other nodes of the cluster. This process is called the Distributed Recovery.

During the Distributed Recovery, the joiner receives from a donor all the missing transactions using asynchronous replication on a dedicated channel.

It’s of course also possible to monitor the progress of this recovery process by calculating how many transactions have …

[Read more]
Using the new MySQL Shell Reporting Framework to monitor InnoDB Cluster

With MySQL Shell 8.0.16, a new very interesting feature was released: the Reporting Framework.

Jesper already blogged about it and I recommend you to read his articles if you are interested in writing your own report:

  • https://mysql.wisborg.dk/2019/04/26/mysql-shell-8-0-16-built-in-reports/
  • https://mysql.wisborg.dk/2019/04/27/mysql-shell-8-0-16-user-defined-reports/

I this post, I will show you one user-defined report that can be used to monitor your MySQL InnoDB Cluster / Group Replication.

Preparation

Before being able to use the report, you need to download 2 files. The first one is the …

[Read more]
What configuration settings did I change on my MySQL Server ?

This post is just a reminder on how to find which settings have been set on MySQL Server.

If you have modified some settings from a configuration file or during runtime (persisted or not), these two queries will show you what are the values and how they were set. Even if the value is the same as the default (COMPILED) in MySQL, if you have set it somewhere you will be able to see where you did it.

Global Variables

First, let’s list all the GLOBAL variables that we have configured in our server:

SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCE
FROM performance_schema.variables_info t1
JOIN performance_schema.global_variables t2
ON t2.VARIABLE_NAME=t1.VARIABLE_NAME
WHERE t1.VARIABLE_SOURCE != 'COMPILED';

This is an example of the output:

Session Variables

And now the same query for the session variables:

SELECT t1.VARIABLE_NAME, VARIABLE_VALUE, …
[Read more]
pre-FOSDEM MySQL Day 2019

For the third year in a row, we will take advantage of the mass presence of our MySQL Engineers during FOSDEM to organize the pre-FOSDEM MySQL Day.

The program of this 3rd edition is already on track, thank you to all the speakers who already confirmed their participation.

Start End Event Speaker Company Topic
Friday 1st February
09:30 10:00 MySQL Community Team Welcome
10:00
[Read more]
Showing entries 1 to 10 of 116
10 Older Entries »