This post is the twelfth one of a series of articles on extending MySQL with the Component Infrastructure:
This post is the second of a series of articles dedicated to MySQL Database Service (MDS):
The goal of this series if to get familiar with MDS, HeatWave and useful MySQL Shell utilities.
Before we start, I would like to highlight that the fastest and recommended way to import data to a MySQL DB System in OCI is to use a parallel dump created using MySQL to Object Storage and load it …
[Read more]This post is the first of a series of articles dedicated to MySQL Database Service (MDS):
The goal of this series if to get familiar with MDS, HeatWave and useful MySQL Shell utilities.
In this first article we will start by creating a MySQL DB System and a Compute instance in OCI. One of the goal is also to see the benefits of HeatWave, so we will start by creating this …
[Read more]Recently, I saw many interest in understanding and getting information about database locking.
MySQL InnoDB’s locking can be complex and having an overview not always simple.
For more information about how InnoDB locking is working, I can only recommend this excellent series of articles by Kuba:
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]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:
This is an output of the method:
As …
[Read more]
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]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:
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 …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]