You are here


Verkleinern des InnoDB-System-Tablespaces

Taxonomy upgrade extras: 

Ein Feature, das mich im neuen MariaDB 11.4 LTS Release wirklich begeistert hat, ist das Verkleinern bzw. Schrumpfen des System-Tablespaces (ibdata1). Auf dieses Feature habe ich seit ca. 2006 sehnsüchtig gewartet und nun ist es mit MariaDB 11.4 endlich gekommen.
Eigentlich gibt es dieses Feature schon seit dem MariaDB 11.2 IR (Juni 2023).

Leider ist die Ankündigung dieses Features etwas zu kurz gekommen. In den MariaDB Release Notes heisst es lapidar:

MariaDB Foreign Key Constraint example

Foreign Key Constraints are used to model a parent/child relation in an entity relationship (ER) model:


How to force InnoDB Buffer Pool flushing

InnoDB tries to keep pages in Buffer Pool to be fast. If a page is changed by a DML statement (INSERT, UPDATE, DELETE) this change will be done in InnoDB Buffer Pool and not directly on disk. But those changed InnoDB pages residing in InnoDB Buffer Pool must be flushed sooner or later to disk to become persistent. This is done by the InnoDB background writer thread(s) (default 4).

InnoDB full-text index corruption

In a recent customer engagement we experienced a lot of corrupted (normal) indexes and also corrupted full-text indexes on InnoDB tables in a Galera Cluster (v10.4.13).

In the error log we did not see which table it was but we have only a few log entries every here and there indicating a full-table index is corrupt:


innodb_deadlock_detect - Rather Hands off!

Recently we had a new customer who has had from time to time massive database problems which he did not understand. When we reviewed the MySQL configuration file (my.cnf) we found, that this customer had disabled the InnoDB Deadlock detection (innodb_deadlock_detect).

Because we have advised against doing this so far, but I never stumbled upon this problem in practice, I have investigated a bit more about the MySQL variable innodb_deadlock_detect.

Eher Finger weg: innodb_deadlock_detect

Kürzlich haben wir bei einem unserer Kunden, der gelegentlich massive Datenbankprobleme hat, bei der Durchsicht der MySQL Konfigurationsdatei (my.cnf) festgestellt, dass er die InnoDB Deadlock-Erkennung (innodb_deadlock_detect) deaktiviert hatte.

Da wir davon bisher immer abgeraten haben, ich aber noch nie konkret über dieses Problem gestolpert bin, bin ich der Sache noch etwas nachgegangen und habe zur Variable innodb_deadlock_detect recherchiert.

InnoDB Page Cleaner intended loop takes too long

Recently we migrated a database system from MySQL 5.7 to MariaDB 10.3. Everything went fine so far just the following message started to pop-up in the MariaDB Error Log File with the severity Note:

InnoDB: page_cleaner: 1000ms intended loop took 4674ms. The settings might not be optimal. (flushed=102 and evicted=0, during the time.)

I remember that this message also appeared in earlier MySQL 5.7 releases but somehow disappeared in later releases. I assume MySQL has just disabled the Note?

InnoDB Log Buffer is too small for large transactions

Your InnoDB Redo Log Buffer is too small for large transactions. Make the InnoDB Redo Log Buffer (innodb_log_buffer_size) bigger if you have enough RAM available to avoid additional I/O.
1 Mibyte is good for databases with small transactions. 8 Mibyte is good for medium size transactions. 64 MiByte is good for large transactions.


Understanding InnoDB - Buffer Pool Flushing

InnoDB Page Cleaner Thread

The InnoDB Page Cleaner Thread is an InnoDB background thread that flushes dirty pages from the InnoDB Buffer Pool to disk. Prior MySQL 5.6 this action was performed by the InnoDB Master Thread.


UNDO logs in InnoDB system tablespace ibdata1

Taxonomy upgrade extras: 

We see sometimes at customers that they have very big InnoDB system tablespace files (ibdata1) although they have set innodb_file_per_table = 1.

So we want to know what else is stored in the InnoDB system tablespace file ibdata1 to see what we can do against this unexpected growth.

First let us check the size of the ibdata1 file:

InnoDB Storage Engine

Taxonomy upgrade extras: 
Test body

How to move InnoDB-Logfiles on a Galera Cluster

Taxonomy upgrade extras: 

Somebody recently asked, what they had to do, if they wanted to move their InnoDB-Logfiles back to the datadir. As a challenge, the servers were part of a Galera Cluster.

My first thought was:

The problem is not the Galera Cluster itself, it is the rsync-SST (wsrep_sst_method = rsync) that could cause trouble and destroy your InnoDB-Logfiles, by simply overwriting or deleting them.

InnoDB plugin is enabled

Taxonomy upgrade extras: 

<p>InnoDB plugin is enabled.</p>

InnoDB Log File size is too small

The InnoDB Log File (innodb_log_file_size) size is possibly too small.
Choosing a too small InnoDB Log File size can have significant write performance impacts.

For further details please consult MariaDB or MySQL documentation.

InnoDB Foreign Key error detected

MySQL (InnoDB, PBXT, NDB, TokuDB) support Foreign Keys to show relatations between tables. Those relations can be enforced through Foreign Key Constraints.

Foreign Key Constraint Errors are always either a bug in your application (which should be fixed) or inconsistencies in your data (which should be fixed) or both (first fix the bug in the application, then clean-up your data).

Foreign Key Constraint Errors can be found with the following command:

InnoDB Force Recovery is enabled

Taxonomy upgrade extras: 

InnoDB Force Recovery (innodb_force_recovery) is enabled. This mode should be used for data recovery purposes only. It prohibits writing to the data.

InnoDB Flush Log at Transaction Commit

InnoDB Flush Log at Transaction Commit is set to a values != 1. This can lead to a loss of committed transactions in case of a power failure or an unclean shutdown of your database.

InnoDB Deadlock detected

Taxonomy upgrade extras: 

You got an InnoDB Deadlock. Please talk to your development department to fix this problem. Deadlocks are an application problem!

InnoDB Buffer Pool Instances is too small

If you are using MariaDB/MySQL 5.5 and newer you should use several InnoDB Buffer Pool Instances for performance reasons.
Some rules to size InnoDB Buffer Pool instances are:

  • One InnoDB Buffer Pool Instance should be at least 1 Gibyte in size (innodb_buffer_pool_size / innodb_buffer_pool_instances >= 1 Gib).
  • InnoDB Buffer Pool Instances you can set equal to the number of cores of your machine.

InnoDB Flush Method has changed

The InnoDB Flush Method has changed. This can have an impact on InnoDB write Performance.

innodb_checkpoint_age in plain MySQL?

Taxonomy upgrade extras: 

In Percona Server we have a STATUS variable indicating roughly possible recovery time:

How to recover deleted tablespace?

Sometimes, MySQL tablespace file(s) might be deleted by mistake, e.g. delete the shared tablespace (ibdata1) or an individual tablespace (table_name.ibd).

In this post I will show you how to recover those files (on Linux OS) having only one condition, MySQL service should still be running. If MySQL service stopped after deleting that file, this method will not work, so it is extremely important to act as quick as possible to avoid data loss.

The following is a simple table creation (innodb_file_per_table is enabled) and the records count inside that table:

Get rid of wrongly deleted InnoDB tables

Taxonomy upgrade extras: 

Precaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this.


A MySQL user has delete its InnoDB table files for example like this:

shell> rm -f $datadir/test/test.*


We do some analysis first:


Taxonomy upgrade extras: 

Dear Team,

I am frequently facing deadlock issue in mysql for below query. Due to this i have increased the innodb lock wait time out to 1800 seconds. Please help.


Subscribe to RSS - innodb