This is one of the exciting troubleshooting related to XA transactions let’s dive in. Let me provide a few backgrounds.

For one of our clients, when we try to drop a table, it is waiting for metadata lock. On debugging it is one of the XA transactions is holding the shared write lock and causing metadata lock on all the other local transactions.

Let us view the metadata locks from the Performance Schema.

A large table is a pain for many reasons as long as it is in a system. And as if that’s not enough, it is also a difficult task to get rid of it. In this post, we will understand why it is a pain to do this operation and what we can do about it. It will be like asking the table “Tell me what happened and I will ease up the eviction”.

So what happened? When a table is dropped (or truncated), InnoDB has to scan the pages throughout the buffer pool and remove all those belonging to that table. For a large buffer pool, this crawling in the buffer pool pages and eviction process will be slower. When we say “scan buffer pool”, it mainly looks for “LRU”, “FLUSH” (Dirty pages), and “AHI” entries.

LRU: Buffer pool pages are stored in a linked list of pages in order of usage. As the data reaches the end of the list, it is evicted to make space for new data. When the room is needed to add …

Since 8.0.30, you have the possibility to modify the InnoDB Redo Log Capacity online. An undersized Redo Log Capacity is problematic and lead to performance issues.

However, it’s not recommended to oversize the Redo Log either. Redo Log files consume disk space and increases the recovery time in case of a restart (innodb_fast_shutdown=1) or a sudden crash. And it also slows down shutdown when innodb_fast_shutdown=0.

This means that now, you don’t need to restart MySQL if you want to increase or decrease the size of the InnoDB Redo Logs files. In fact, we don’t talk anymore about file size but about capacity ! The DBA doesn’t need to specify any file size and/or amount of files for Redo Logs anymore …

In this blog, I will demonstrate how to use Percona Monitoring and Management (PMM) to find out the reason why the MySQL server is stalling. I will use only one typical situation for the MySQL server stall in this example, but the same dashboards, graphs, and principles will help you in all other cases.

Nobody wants it but database servers may stop handling connections at some point. As a result, the application will slow down and then will stop responding.

It is always better to know about the stall from a monitoring instrument rather than from your own customers.

PMM is a great help in this case. If you look at its graphs and notice that many of them started showing unusual behavior, you need to react. In the case of stalls, you will see that either some activity went to 0 or, otherwise, it increased to high …

When performing physical backup on system that are heavily used, it can happen that the backup speed cannot keep up with the redo log generation. This can happen when the backup storage is slower than the redo log storage media and this can lead in inconsistency in the generated backup.

MySQL Enterprise Backup (aka MEB) and probably Percona Xtrabackup, benefit from the possibility to sequentially write redo log records to an archive file in addition to the redo log files.

This feature was introduced in MySQL 8.0.17.

How to enable it ?

To enable this feature, two settings are necessary:

• set globally a directory where those archiving logs can be stored
• start the archiving process in a session by calling a dedicated function

The global variable is …

The following is a short HOWTO about deployment and use of Benchmark-kit (BMK-kit). The main idea of this kit is to simplify your life in running various MySQL benchmark workloads with less blood and minimal potential errors.

Generally as simple as the following :

$ bash /BMK/sb_exec/sb11-Prepare_50M_8tab-InnoDB.sh 32   # prepare data

$ for users in 1 2 4 8 16 32 64 128 256 512 1024 2048
do   
  # run OLTP_RW for 5min each load level..
  bash /BMK/sb_exec/sb11-OLTP_RW_50M_8tab-uniform-ps-trx.sh $users 300
  sleep 15
done

the latest public online version of the following HOWTO is always available from here : http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html

Read more... (42 min remaining to read)

Who dares diagram a system and process as complex as InnoDB page flushing? I do.

Who dares diagram a system and process as complex as InnoDB page flushing? I do.

Who dares diagram a system and process as complex as InnoDB page flushing? I do.

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 …