The ibdata1
file is arguably the most important file in the entire InnoDB infrastructure. Without the file InnoDB simply could not function – we will explore why in this post.
ibdata1
and Why Is It So Important?The ibdata1
file is the system tablespace for the InnoDB infrastructure – the file contains vital information for the InnoDB storage engine. Here’s how the infrastructure of InnoDB looks like (note the ibdata1
‘s place on the right):
The ibdata1
contains several classes of information vital for InnoDB to function properly.
ibdata1
?Unfortunately, MySQL does not provide information on what is stored in the ibdata1
by default, but we do know certain things. The file contains:
Here’s what that means:
ibdata1
– Proper ConfigurationIf you have ever worked with MySQL, you might have noticed that the ibdata1
file grows as you insert data into a table with an InnoDB engine. You might have also noticed that MySQL does not provide you with an easy way to make the size of the file smaller – the ibdata1
file cannot be shrunk unless you delete all databases, remove the associated files and start anew.
In order to shrink ibdata1
perform the following steps:
mysql
and performance_schema
databases;ibdata1
and ib_logfile*
files (these will be recreated on the next restart of MySQL);When you start MySQL after performing the steps outlined above, the ibdata1
and ib_logfile*
files will be recreated.
Now you’re good to go! When a new database will be created, its tables will be located in separate ibd*
files and not in ibdata1
– ibdata1
will still grow, but it will only contain metadata, not the data itself. In the event of the database getting dropped, the ibd*
files will be deleted.
The log files (ib_logfile0
and ib_logfile1
) contain the redo logs. If MySQL crashes, restarting it will perform a read across the ib_logfile0
and ib_logfile1
files and check for any data changes that were not posted to the double write buffer in ibdata1
and redo those changes. Once the changes are replayed and stored, MySQL is ready for new connections.
In other words, MySQL uses logs to achieve data durability – the engine is able to ensure that data would not be lost in the event of a crash or power loss.
Choosing a good InnoDB log file size is the key to a good InnoDB write performance – as a rule of thumb, the innodb_log_buffer_size
parameter should be 25% of the value of the innodb_log_file_size
.
As previously noted, if MySQL crashes, restarting it will perform a scan across both of the ib_logfile0
and ib_logfile1
files. This is the primary reason why setting innodb_log_file_size
to a proper value is very important – the log file size should be set as big as possible, but not bigger than it is absolutely necessary. By increasing the log file size you will get performance benefits, but it does have a drawback: the recovery time after a crash.
Setting the innodb_log_file_size
to a proper value is crucial to achieve the balance between reasonably good system performance and fast crash recovery time.
To set both of the innodb_buffer_pool_size
and innodb_log_file_size
to appropriate values, you can follow this approach:
free
” command);innodb_buffer_pool_size
parameter;innodb_buffer_pool_size
to the ..._log_file_size
parameters.The ibdata1
and ib_logfile*
files are crucial for the proper performance of InnoDB – although in some cases the contingency of these files might cause problems, those problems can usually be swiftly and efficiently resolved.
A hacking group related to North Korea is exploiting a zero-day in the Chromium browser…
What are crypto bubbles, how do they form, and should you worry about them? Learn…
Is the crypto-engine.pro blog legit and should you trust this resource? Learn here!
Reside in Brazil and found that your Twitter account suspended? There’s a good reason for…
This blog covers the recent Black Hat USA 2024 (DEFCON 2024) conference and digs into…
The CEO of Telegram and Telegram Web, Pavel Durov, has been released from custody and…