Storage Engine

InnoDB Flushing in Action for Percona Server for MySQL

InnoDB Flushing in Action for Percona Server for MySQL

As the second part of the earlier post Give Love to Your SSDs – Reduce innodb_io_capacity_max! we wanted to put together some concepts on how InnoDB flushing works in recent Percona Server for MySQL versions (8.0.x prior to 8.0.19, or 5.7.x). It is important to understand this aspect of InnoDB in order to tune […]

Read more

Examining MySQL InnoDB Persistent Statistics

MySQL InnoDB Persistent Statistics

A few days ago I wrote about how grossly outdated statistics returned through MySQL’s Information_Schema can be. In that post, Øystein Grøvlen suggested taking a look at mysql.innodb_table_stats and mysql.innodb_index_stats as a better source of information. Let’s do just that!
Let’s start with the good news. Unlike MySQL Data Dictionary Tables (mysql.table_stats, etc),  mysql.innodb_table_stats and […]

Read more

Using Referential Constraints with Partitioned Tables in InnoDB

partioned tables innodb

One of our support customers approached us with the following problem the other day:

Shell

mysql> CREATE TABLE child_table (
`id` int unsigned auto_increment,
`column1` varchar(64) NOT NULL,
parent_id int unsigned NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent_table (id));
ERROR 1215 (HY000): Cannot add foreign key constraint

1234567

mysql> CREATE TABLE child_table (`id` int unsigned auto_increment,`column1` varchar(64) NOT NULL,parent_id int […]

Read more

Contention in MySQL InnoDB: Useful Info From the Semaphores Section

Contention in MySQL InnoDB

In a high concurrency world, where more and more users->connections->threads are used, contention is a given. But how do we identify the contention point easily?
Different approaches had been discussed previously, like the one using SHOW ENGINE INNODB STATUS command output.
SEMAPHORES
The SEMAPHORES section displays all the metrics related to InnoDB mechanics on waits. This section […]

Read more

Give Love to Your SSDs – Reduce innodb_io_capacity_max!

reduce innodb io capacity max

The innodb_io_capacity and innodb_io_capacity_max are often misunderstood InnoDB parameters. As consultants, we see, at least every month, people setting this variable based on the top IO write specifications of their storage. Is this a correct choice? Is it an optimal value for performance? What about the SSD/Flash wear leveling?
Innodb_io_capacity 101
Let’s begin with what […]

Read more