Innodb

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

SQL> CREATE TABLE team (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT
, name VARCHAR(48) NOT NULL
, PRIMARY KEY (id)
);

SQL> CREATE TABLE employee (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT
, first_name VARCHAR(32) NOT NULL
, last_name VARCHAR(64) NOT NULL
, team_id BIGINT UNSIGNED NOT NULL
, PRIMARY KEY (id)
, CONSTRAINT `fk_employee_team` FOREIGN KEY (team_id)
  REFERENCES team (id) ON …

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 flushes the dirty pages with a pace of innodb_io_capactiy (default 200) pages/s. This variable should be set depending on …

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:

2020-07-08 22:09:03 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.
2020-07-08 22:09:06 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary …

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.

The MySQL documentation tells us the following
[1
]: …

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?

You can find …

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.

See also for MariaDB MySQL.

See also our former articles:

• Do not underestimate performance impacts of swapping on NUMA database systems
• MariaDB and MySQL swap analysis

When we do analysis of MariaDB Database servers we also check the memory (RAM and Swap) available:

# free --kilo --wide
              total        used        free      shared     buffers       cache   available
Mem:       16106252     4329952      703356      199008      307872    10765072    11042748
Swap:      31250428      528684    30721744

The values for …

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.

SQL> SELECT thread_id, name, type
  FROM performance_schema.threads
 WHERE name LIKE 'thread/innodb/page%'
    OR name LIKE 'thread/innodb/srv_master%';
+-----------+-----------------------------------+------------+
| thread_id | name                              | type       | …

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:

# ll ibdata1 
-rw-rw---- 1 mysql mysql 109064486912 Dez  5 19:10 ibdata1

The InnoDB system tablespace is about 101.6 Gibyte in size. This is exactly 6'656'768 InnoDB blocks of 16 …

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.

So I tried to confirm my thought and realised, I was wrong. This works anyway, because the node just takes the dataset from the …

InnoDB plugin is enabled.

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.

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:

SHOW ENGINE INNODB STATUS<br>G

SHOW ENGINE Syntax …

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

Please consult MariaDB and MySQL documentation for further details or InnoDB Recovery Modes.

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.

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

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.

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

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

Combined size of InnoDB log files defines how many changes not reflected in the tablespace we may have where innodb_checkpoint_age shows how much changes we actually have at the current moment, being an actual driving factor of recovery time. If you have very large log files allocated but for your workload innodb_checkpoint_age stays low chances are recovery will be quick.
[ InnoDB crash recovery speed in MySQL 5.6
] …

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 …

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:

mysql> DROP TABLE test;
ERROR 1051 (42S02): Unknown table 'test'

mysql> CREATE TABLE test (id INT) ENGINE = InnoDB;
ERROR 1050 (42S01): Table '`test`.`test`' already exists

The MySQL error log shows us the following …

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.

New features

• Recognizes VIEW’s and does NOT try to alter their Storage Engine (bug).
• Script is MySQL version aware. Complain if …

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.

But why should it not be possible? Other databases like for example Oracle can shrink or even get rid of tablespace files… After some …

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

*** (1) TRANSACTION:

TRANSACTION 22723019234, fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1216, 14 row lock(s)
update location set expires='2012-08-10 04:50:29' where username='12345678901' AND contact='sip:12345678901@192.168.0.191:5060' AND callid='945a20d4-8945dcb5-15511d3e@192.168.0.191'

*** (1) WAITING FOR THIS LOCK TO …

InnoDB Buffer Pool

The InnoDB Buffer Pool is the memory area where the InnoDB Storage Engine caches its data and index blocks. Each InnoDB data and index block has a size of Innodb_page_size (16384 byte = 16 kbyte). The InnoDB Buffer Pool is configured in bytes with the innodb_buffer_pool_size variable. On a dedicated system the InnoDB Buffer Pool can be configured up to 80% of the systems physical RAM (free).

The innodb_buffer_pool_awe_mem_mb variable is relevant only on 32-bit Windows systems with more …

Its a known problem that changing the Storage Engine from MyISAM to InnoDB can cause some problems
[ 1
] if you have queries of this type:

SELECT COUNT(*) from table;

Luckily this query happens rarely and if, the query can be easily omitted or worked around by guesstimating the amount of rows in the table. For example with:

SHOW TABLE STATUS LIKE 'test';

But in some rare cases customer really needs these values for some reasons. To not exhaust the resources of the server with this query which can be fired …

Because we are a bit short off disk space on one of our servers I had the idea to try out the MySQL feature Data Compression for InnoDB. This feature is useful if you have tables with VARCHAR, BLOB or TEXT attributes.

To not make it not too simple our table is partitioned as well. Our table looks like this:

CREATE TABLE `history_str` (
  `itemid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) unsigned NOT NULL DEFAULT '0',
  `value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY …

Helsinki, Finland, Uster, Switzerland – February 18, 2012 – Codership, the provider of Galera Cluster for MySQL, and FromDual, a MySQL consulting company, today announced collaboration to offer Galera Cluster technology and related support and consulting services for Galera users all over the world, especially in German speaking countries Germany, Austria and Switzerland (DACH). Galera Cluster is a synchronous, true multi-master replication cluster for MySQL using the well known InnoDB storage engine. …

Recently I found in a forum the following request for help:

My MySQL instance crashed because of free disk space fault. I saw in /var/lib/mysql all the files: ibdata1, ib_logfile* and all the folders containing frm files. Well, when i solved the problem and run successfully the instance, some databases disappeared. One of those is the most important, and i don’t know how many tables had and their structures. Is there any way for recover the entire lost database (structure and data) only having the …

Introduction

Recently a customer claimed that his queries were slow some times and sometimes they were fast.

First idea: Flipping query execution plan caused by InnoDB could be skipped because it affected mainly MyISAM tables.

Second idea: Caching effects by either the file system cache caching MyISAM data or the MyISAM key buffer caching MyISAM indexes were examined: File system cache was huge and MyISAM key buffer was only used up to 25%.

I was a bit puzzled…

Then we checked the table_open_cache …

Galera features

Galera provides the following features:

• Synchronous replication
• Active/active multi-master topology
• Read and write to any cluster node
• Automatic membership control, failed nodes drop from the cluster
• Automatic node joining
• True parallel row level replication
• Direct client connections
• Drop-in replacement for native MySQL

Galera benefits

Benefits using Galera Replication:

• High Availability
• No slave lag
• No lost transactions
• No more data inconsistency
• Smaller client latencies
• Read scalability …

Table of Contents

• Reasons to Upgrade MariaDB or MySQL
• How to Upgrade to MariaDB or MySQL
• MariaDB and MySQL Reserved Keywords
• MariaDB and MySQL Problems we hit in real life
  • Upgrade MySQL 4.1 to MySQL 5.1
  • Upgrade MySQL 4.1 to MySQL 5.6
  • Upgrade MySQL 5.0 to MySQL 5.1
  • Upgrade MySQL 5.0 to MySQL 5.6
  • Upgrade MySQL 5.0 to MariaDB 10.3
  • Upgrade MySQL 5.1 to MySQL 5.5
  • Upgrade MySQL 5.5 to MySQL 5.6
  • Upgrade MySQL 5.6 to MySQL 5.7
  • Upgrade MySQL 5.7.12 to MySQL 5.7.21
  • Upgrade MySQL 5.7 to MySQL 8.0
  • Upgrade MariaDB 5.5 …

Abstract: In this article we discuss how big is the performance impact of MySQL TRIGGERs compared to application side logging (with INSERT) into a MySQL table.

What was in my mind from the past

A while ago when MySQL released its Stored Language features in v5.0 I have seen a book
[1
] about this topic. In this book was a performance comparison between different implementations of computational tasks, one was done in MySQL Stored Language. The result was, that MySQL Stored Language feature sucks also …

With the new MySQL 5.6 release there are some more InnoDB Data Dictionary Tables in the INFORMATION_SCHEMA:

New with MySQL 5.5 are:

New with MySQL 5.6 are:

Abstract: Heating up the InnoDB Buffer Pool during the MySQL instance startup should significantly improve InnoDB Performance in the beginning of the life of the Instance. This is achieved by sequential scans of the needed data instead of random I/O reads which would happen when we just let the system work it out by itself.
How to find the database objects which can be loaded during MySQL start-up and how to load them automatically is described in this article.

New Table in the INFORMATION_SCHEMA

Some of my …

We have just released v0.7.1 of the FromDual MySQL Performance Monitor. The new release can be downloaded from here.

In all editions some error messages have been cleaned-up, the fall-back data gather method mysql has been removed and the Maria SE template has been renamed to Aria.

In the edition dedicated to our customers most of the InnoDB graphs which are available with MEM v2.3.3 have been implemented now.

Please let us know, what you want to have monitored in addition and send us your feedback and …

In the old days everything was simpler. We had one maker of our favourite database management system and possibly the choice between different Storage Engines. Mostly the decision has to be taken between MyISAM and InnoDB. When you care about your data integrity you have chosen InnoDB.

Nowadays it is more complex. We have several different makers of our favourite database management system: Oracle/MySQL, Monty Program AB and Percona with their products: MySQL, MariaDB and Percona Server.

We have different …

We recently had the case with one of our customers where we got externally generated random hash values (up to 70 bytes) and they were used as Primary Keys in InnoDB.

As we know, this is not a very good idea because the size of all secondary indexes becomes large and because a random hash value as a Primary Key gives us a bad locality of our rows in the table ¹.

If we do not specify a Primary Key and have no Unique Key InnoDB generates a hidden Clustered Index based on the Row ID which is a 6 byte field. …

InnoDB is known to have crash-recovery capabilities and thus is called a crash safe storage engine (in contrary to MyISAM). Nevertheless under certain circumstances it seems like InnoDB pages can get corrupt during a crash and then a manual crash-recovery is needed.

Oracle/MySQL blames in such cases the Operating System, the I/O system or the hardware. What we have seen is that such incidents occur more often on Windows systems and when people are running their databases in a virtualized environment …

Table of Contents

• Result set with temporary sequence
• Determination of optimal length of prefixed indexes
• Using MySQL keywords in table or columm names
• Missing Primary Key Index
• Problems while installing a MySQL 5.5 database
• InnoDB AUTO_INCREMENT at 2^nd position

Result set with temporary sequence

Sometimes you would like to have a result set with something like a rownum. You can do this at least in the following two ways:

a) with a TEMPORARY MEMORY table:

CREATE TEMPORARY TABLE mem (
    seq  INT NOT NULL …

Table of Contents

• Search with special characters
• Why is InnoDB disabled?
• How to find MySQL system information?
• What is the difference between MySQL certified server and community server?
• MySQL monitoring
• MySQL backup
• Corrupt MyISAM table
• How to compile MySQL
• Test restore procedure
• Reset a MySQL user password
• Reset the MySQL root user password
• How to enable the InnoDB plugin
• Storage Engines shipped with MariaDB / MySQL
• Compiling MySQL Cluster ndb-test fails
• NDB information schema does not show up
• Hyper …

Recently we had a case where a customer got some corrupted blocks in his InnoDB tables. His largest tables where quite big, about 30 to 100 Gbyte. Why he got this corrupted blocks we did not find out yet (disk broken?).

When you have corrupted blocks in InnoDB, it is mandatory to get rid of them again. Otherwise your database can crash suddenly.

If you are lucky only “normal” tables are concerned. So you can dump, drop, recreate and load them again as described in the InnoDB recovery procedure …