In my previous post, I explained how to deal with
Performance_Schema and Sys to identify
the candidates for Query Optimization but also to understand the
workload on the database.
In this article, we will see how we can create an OCI Fn Application that will generate a slow query log from our MySQL Database Service instance and store it to Object Storage.
The creation of the function and its use is similar to the one explained in the …
[Read more]
If like me you are an old experienced MySQL DBA, to analyze your
MySQL workload, you certainly have used the slow query
log with long_query_time set to
0.
The slow query log is a file that contains all the queries whose
execution time is greater than the value of
long_query_time. This file can be huge and uses up
all available disk space on busy systems.
Constantly writing to that file can also add an overhead to the server.
For those reasons, the slow query log is not available in MySQL Database Service (HeatWave) in OCI.
Plan B ?
As a DBA, what are my options for finding the queries that need to be optimized? As usual, the queries that consume the most time are the ones that need some attention
It can be a very long query or a short query executed too many times.
Currently the MySQL DBAs use …
[Read more]Recently, somebody asked me how he can find the long running transactions in MySQL.
I already have one MySQL Shell plugin that allows you to find the
current transactions sorted by time. The plugin allows you to
also get the details about the desired transaction. See
check.getRunningStatements().
Let’s see how we can easily find those long transaction that can be a nightmare for the DBAs (see MySQL History List Length post).
SELECT thr.processlist_id AS mysql_thread_id,
concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
Command,
FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
current_statement as `latest_statement`
FROM …[Read more]
The MySQL Performance Schema is a gold mine of valuable data. Among the many nuggets you can extract from it is an historical report of transactions: how long a transaction took to execute, what queries were executed in it (with query metrics), and idle time between queries. Mining this information is not trivial, but it’s fun and this blog post shows how to start.
The MySQL Performance Schema is a gold mine of valuable data. Among the many nuggets you can extract from it is an historical report of transactions: how long a transaction took to execute, what queries were executed in it (with query metrics), and idle time between queries. Mining this information is not trivial, but it’s fun and this blog post shows how to start.
The MySQL Performance Schema is a gold mine of valuable data. Among the many nuggets you can extract from it is an historical report of transactions: how long a transaction took to execute, what queries were executed in it (with query metrics), and idle time between queries. Mining this information is not trivial, but it’s fun and this blog post shows how to start.
How to inspect and report MySQL transactions using the Performance Schema
How to inspect and report MySQL transactions using the Performance Schema
Full table scans can be problematic for performance. Certainly if the scanned tables are large. The worst case is when full table scans are involved in joins and particularly when the scanned table is not the first one (this was dramatic before MySQL 8.0 as Block Nested Loop was used) !
A full table scans means that MySQL was not able to use an index (no index or no filters using it).
Effects
When Full Table Scans happen (depending of the size of course), a lot of data gets pulled into the Buffer Pool and maybe other important data from the working set is pulled out. Most of the time that new data in the Buffer Pool might even not be required by the application, what a waste of resources !
You then understand that another side effect of Full Table Scans is the increase of I/O operations.
The most noticeable symptoms of Full Table Scans are:
Today, the book I would like to recommend is Efficient MySQL Performance – Best Practices and Techniques, Daniel Nichter, O’Reilly, 2021.
I participated (just a bit) in the writing of this book as technical reviewer with Vadim and Fipar. I really enjoyed that role of carefully reading the early drafts of the chapters Daniel was writing.
Although Daniel says the book is not for the experts, I think even experts will enjoy it because several key InnoDB concepts are also covered. You can see that I refer to the book often in my A graph a day, keeps the doctor away ! series on monitoring and trending.
If you’re looking for information on transaction isolation and undo logs, fuzzy checkpointing, etc… you’ll find …
[Read more]