MySQL uses Redo Logs internally during crash recovery to correct data written by incomplete transactions. But how do you know what the right Redo Log size is? We will walk through how to figure that out in this blog.

We already have a couple of posts related to this topic. “How to calculate a good InnoDB log file size” and “How to Choose the MySQL innodb_log_file_size.” The first one has the formula I’ll mention in this blog, and the second has more details regarding Redo Logs. Most of it is still valid, just replace innodb_log_file_size / innodb_log_files_in_group with innodb_redo_log_capacity.

So in this one, I want to focus on two things:

1. Present the new way of working with Redo Logs (No …

ProxySQL is an open-source MySQL proxy server, meaning it serves as an intermediary between a MySQL server and the applications that access its databases. ProxySQL can improve performance by distributing traffic among a pool of multiple database servers.

Consider 2 slaves are routed under Proxysql , In any one of the slave, if the replication is broken, we could still see the traffic routing to the broken replication slave. We can make Proxy to not send traffic to broken replication slave, by setting appropriate value to the variable mysql-monitor_slave_lag_when_null

…

Thank you to everyone who joined us for the 19th edition of MyWebinar. We hope that our time together helped to elevate and seam your work to perfection. We’re committed to giving back to the opensource database community in the coming days by presenting more LIVE events.

Recently, Mydbops conducted a webinar on the top 10 features of MySQL 8. MySQL is one of the most popular and widely used open-source relational database management systems. With every new version, MySQL brings new features and enhancements that make it more reliable, secure, and efficient. In this blog, we will wrap up the top 10 features of MySQL 8 discussed in the webinar by Mydbops.

Vinoth Kanna, co-Founder from  …

In my previous post about InnoDB Stalls on Empty Free List, I used a test environment that might look a little artificial : a table larger than the InnoDB Buffer Pool but fitting in the Linux Page Cache.  This configuration allows serving very quickly what MySQL thinks are IOs because these are hit in the filesystem cache.  In this post, I explain why this environment is not totally artificial

If, in Percona Server, you are observing tail latencies on queries that should be fast, this might be a side effect of Percona's improved InnoDB Empty Free List Algorithm.  When using this algorithm (the default in 5.6 and 5.7 and optional configuration in 8.0), a query needing a free page while none are available waits until the LRU Manager Thread refills the free list.  Because this thread is

The Primary key is like the hero of a row, which has more beneficial features in the table while performing any task on the table.

The DBA knows the importance of the primary key in the table and how to handle it.

1. Notable features of having a primary key:
2. Requirements:
3. Enabling GIPK:
4. Handling GIPK:
…

At Percona Managed Services, sometimes clients’ applications face deadlock situations and need all historic deadlock information for application tuning.

We could get the LATEST DETECTED DEADLOCK from SHOW ENGINE INNODB STATUS\G:

….
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 163 page no 3 n bits 72 index GEN_CLUST_INDEX of table `deadlock_test`.`t` trx id 78507 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 78508, ACTIVE 155 sec starting index read
mysql tables in use 1, locked 1
….

But how could we view all past deadlock information?

We could enable innodb_print_all_deadlocks,  and all deadlocks in InnoDB user transactions will be recorded in the MySQL error log.

Let‘s start the test.

Create the test …

We’re ecstatic and energised about the prospect of sowing seeds of knowledge in our opensource database community.

Thank you to everyone who joined us for the 17th edition of MyWebinar. We hope that our time together helped to elevate and seam your work to perfection. We’re committed to giving back to the opensource database community in the coming days by presenting more LIVE events.

Maha Lakshmi Ganapathineedi from Mydbops was successful in making this event more interactive, engaging, and, most importantly, productive for the audience. Thank you for your continuous contribution to the opensource community.

Topic: Redo …

MySQL 8.0.30 was released on 26-07-2022. It has a good list of features.

• Generated Invisible Primary Key
• Dynamic Redo log Sizing
• Instruments to monitor group replication memory
• Support for Ubuntu 22.04

A most notable feature for Database Administrators and database reliability engineers is Dynamic redo logging.

The more widely adopted open-source backup tool is Xtrabackup. The change in the redo log design has affected this hot backup tool.

2022-10-04T18:40:08.211998+05:30 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql 
2022-10-04T18:40:08.212264+05:30 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: …

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.