One thing must be noted that you probably never realized: The setting of the option innodb_file_per_table will determine how much space is inside each database folder.
First, take a look at the InnoDB Architecture
Note the system tablespace (which was know is named ibdata1 in the OS).
- In MySQL 5.0, the default value for innodb_file_per_table is 0 or OFF
- In MySQL 5.6, the default value for innodb_file_per_table is 1 or ON
- With innodb_file_per_table disabled (default in 5.0), the data and index pages for all InnoDB tables are stored inside ibdata1. When you create the table
mydb.mytable
, you get the following:
/var/lib/mysql/mydb/mytables.frm
- Data and index pages for
mydb.mytable
are inside ibdata1
- With innodb_file_per_table enabled (default in 5.6), the data and index pages for all InnoDB tables are outside ibdata1. When you create the table
mydb.mytable
, you get the following:
/var/lib/mysql/mydb/mytables.frm
- Data and index pages for
mydb.mytable
are stored in /var/lib/mysql/mydb/mytables.ibd
SUMMARY
Without setting innodb_file_per_table and just storing data
- Databases under MySQL 5.0 will have nothing but
.frm
files for its InnoDB tables. Thus, you only have kilobytes of files in each database.
- Databases under MySQL 5.6 will have
.frm
and .ibd
files for its InnoDB tables. Thus, you will have megabytes or gigabytes of files in each database.
From the MySQL Documentation
The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE:
By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
To use a consistent read in such cases, enable the innodb_locks_unsafe_for_binlog option and set the isolation level of the transaction to READ UNCOMMITTED, READ COMMITTED, or REPEATABLE READ (that is, anything other than SERIALIZABLE). In this case, no locks are set on rows read from the selected table.
Evidently, you do not need stronger locks when just doing reads. Picture copying data into a table using INSERT ... SELECT
or CREATE TABLE ... SELECT
. The SELECT
needs to be a frozen snapshot for loading the table. If it were a moving target, that would require a transaction within a transaction. If you wanted transaction within a transaction
behavior, you would need to script that using SAVEPOINT. Otherwise, stronger locks allow for consistent SELECTs for an atomic INSERT.
Doing the simple SELECT
allows your DB Session to trust other transactions not to play smoke-and-mirrors with your view of the data. If it has to violate that trust, it will politely lock every row it can to do its work, rather than deceive DB Sessions that are only doing SELECTs into thinking the data is stable when it is, in fact, changing.
No matter which isolation level you pick, none of them is so granular that a simple SELECT gets held up. That's why SELECT ... FOR UPDATE
and SELECT ... FROM ... LOCK IN SHARE MODE
were invented, so you can be that granular if politeless is not an option.
I wrote about this before : How can I make a select statement get blocked?
Best Answer
I use bvi to corrupt a tablespace. However you need to know InnoDB files format and how InnoDB works in order to make it start with particular recovery level. For example, for level 1 you need to change a checksum. For 3 - corrupt UNDO and so on.