InnoDB file per table why?
if it is not started and failed what to do?
Today was a big day as a technical point of view in MySQL that saved a lot of storage for me and great deal of maintenance in the future.
To better explain the issue I have to talk a little bit about fundamental behaviour of MySQL InnoDB storage engine!
in past MySQL used MyISAM as its default storage engine. It didn't support transaction. It was not fault tolerant and data was not reliable when power outages occured or server got restarted in the middle of the MySQL actions. By now MySQL uses
In InngoDB by default all tables and all databases resides in a single gigantic file called
storage, our server went out of free space.
There a is mechanism in MySQL that you configure InnoDB to store each tables data into its own file not inside of
The
Do not use optimize table on a table, when you have not configured innodb file per table. Running
- Makes the table's data and indexes contiguous inside ibdata1.
- It makes ibdata1 grow because the contiguous data is appended to ibdata1.
You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. That way, only MVCC and Table MetaData would reside in ibdata1.
In the next post I explain how to do exactly that.
#mysql #innodb #myisam #ibdata1 #database #innodb_file_per_table
if it is not started and failed what to do?
Today was a big day as a technical point of view in MySQL that saved a lot of storage for me and great deal of maintenance in the future.
To better explain the issue I have to talk a little bit about fundamental behaviour of MySQL InnoDB storage engine!
in past MySQL used MyISAM as its default storage engine. It didn't support transaction. It was not fault tolerant and data was not reliable when power outages occured or server got restarted in the middle of the MySQL actions. By now MySQL uses
InnoDB as its default storage engine that is battery packed by transactions, fault tolerant and more.In InngoDB by default all tables and all databases resides in a single gigantic file called
ibdata. When data grows and you alter your tables, the scar gets worse! The size of the ibdata grows very fast. When you alter a table ibdata file would not shrink. For example we had a 120GB single file on server that altering a single table with a huge data would take a long time and would take longstorage, our server went out of free space.
There a is mechanism in MySQL that you configure InnoDB to store each tables data into its own file not inside of
ibdata file. This mechnism has great advantages like using OPTIMIZE TABLE to shrink table size.The
OPTIMIZE TABLE whith InnoDB tables, locks the table, copy the data in a new clean table (that's why the result is shrinked), drop the original table and rename the new table with the original name. That why you should care to have the double of the volumetry of your table available in your disk. If you have a 30GB table, optimizing that table needs at least 30GB of free disk space.Do not use optimize table on a table, when you have not configured innodb file per table. Running
OPTIMIZE TABLE against an InnoDB table stored ibdata1 will make things worse because here is what it does:- Makes the table's data and indexes contiguous inside ibdata1.
- It makes ibdata1 grow because the contiguous data is appended to ibdata1.
You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. That way, only MVCC and Table MetaData would reside in ibdata1.
In the next post I explain how to do exactly that.
#mysql #innodb #myisam #ibdata1 #database #innodb_file_per_table