You are here

innodb

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.

Undefined

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.

Undefined

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 plug-in is enabled

Taxonomy upgrade extras: 

<p>InnoDB plug-in 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.

Situation

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

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

Analysis

We do some analysis first:

DEADLOCKS, INDEXING issue in MySQL

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.

Switching from MySQL/MyISAM to Galera Cluster

Taxonomy upgrade extras: 

Switching from MySQL/MyISAM to Galera Cluster requires that all tables (except those from the mysql, information_schema and performance_schema) are using the InnoDB Storage Engine.

For altering the Storage Engine of the tables we wrote a script (alter_engine.pl) long time ago already. Because we have made many of those switches recently we have extended its functionality.

Shrinking InnoDB system tablespace file ibdata1 PoC

Taxonomy upgrade extras: 

In this weeks MySQL workshop we were discussing, beside other things, about the innodb_file_per_table parameter and its advantages of enabling it. In addition there was a discussion if the InnoDB system tablespace file can be shrinked once it has been grown very large or not. We all know the answer: The InnoDB system tablespace file does never shrink again.

Deadlocks, indexing and Primary Key's

Recently a customer has shown up with some deadlocks occurring frequently. They were of the following type (I have shortened the output a bit):

Pages

Subscribe to RSS - innodb