Showing entries 1 to 10 of 14
4 Older Entries »
Displaying posts with tag: sys (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 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]
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]
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]
MySQL Memory Consumption and Open Prepare Statements

Today I read the nice post of Bhuvanesh on the memory leak issue they experienced with MySQL. In fact when you read the post, you realize that MySQL was just following the parameters that were setup (the default of max_prepare_stmt_count is 16382 even on 5.6).

I’m still convinced that the problem should be resolved in the application even if the use of ProxySQL for multiplexing is great.

In MySQL 8.0, we have enabled the …

[Read more]
MySQL and Memory: a love story (part 2)

We saw in the previous post that MySQL likes memory. We also saw how to perform operating system checks and some configuration changes for Swap and NUMA.

Today, we will check what MySQL server can tell us about its memory usage.

Introduced in MySQL 5.7 and enabled by default in MySQL 8.0, the Performance_Schema‘s Memory instrumentation allows us to have a better overview of what MySQL is allocating and why.

Let’s check on our MySQL server using SYS:

Pay attention that there is a bug related to how InnoDB Buffer Pool statistics are accounted in Performance_Schema. This is fixed in 8.0.13. …

[Read more]
MySQL: size of your tables – tricks and tips

Many of you already know how to retrieve the size of your dataset, schemas and tables in MySQL.

To summarize, below are the different queries you can run:

Dataset Size

I the past I was using something like this :

But now with sys schema being installed by default, I encourage you to use some of the formatting functions provided with it. The query to calculate the dataset is now:

SELECT sys.format_bytes(sum(data_length)) DATA,
       sys.format_bytes(sum(index_length)) INDEXES,
       sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'
FROM information_schema.TABLES ORDER  BY data_length + index_length;

Let’s see an example:

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