InnoDB is the first engine that provides foreign key constraints on MySQL. In addition to providing transaction processing, InnoDB also supports line locks, providing unlocked readings, increased and read, and improve performance. Do not increase the number of locks.
The INNODB design goal is to maximize performance when massaged, and its CPU utilization is the most efficient in all other disks based on disk-based relational database engines.
InnoDB is a complete database system in the MySQL background, INNODB has its own buffer pool, buffer data and index, InnoDB also stores data and indexes in the table space, may contain several files, this is completely Different, in Myisam, the table is stored in a separate file, and the size of the InnoDB table is limited to the size of the operating system file, typically 2GB.
In the source code of MySQL, it is activated from 3.23.34a to include the InnoDB table and activate in the binary version of MySQL -MAX.
If you use the source version version to download, you need to use the --with-innodb option:
./configure --with-innodb
When using InnoDB on MySQL-MAX-3.23, you must specify startup parameters in the [MySQLD] section in my.cnf or my.ini file:
InnoDB_Data_file_path = ibdata: 30m
InnoDB is released by the GNU GPL version 2.
MySQL-MAX-3.23: This product can be used in the production environment.
Mysql-4.0: This is a development version, and 3.23 adds a multi-table delete, query result buffer, SSL communication, 4.0.1 is a beta version.
In 3.23, at least the size of the InnoDB_Data_file_path must be specified, but this value is default 64MB in MySQL-4.0, and the file name is IBDATA1.
In order to get good performance, you must specify an InnoDB parameter, for example:
For a 128MB memory, the 10GB hard disk's Windows NT machine configuration is as follows:
[mysqld]
InnoDB_Data_Home_Dir = C: / IBData
# Data file must be able to accommodate data and index
InnoDB_Data_file_path = ibdata1: 2000m; IBData2: 2000M
# Set the buffer pool size of 50 - 80% of memory
Set-variable = innodb_buffer_pool_size = 70m
Set-variable = innodb_additional_mem_pool_size = 10m
INNODB_LOG_GROUP_HOME_DIR = C: / IBLOGS
# Log_arch_dir must be like log_group_home_dir
InnoDB_log_arch_dir = c: / iblogs
InnoDB_log_archive = 0
Set-variable = INNODB_LOG_FILES_IN_GROUP = 3
# Set the log file size to 15% of the buffer pool.
Set-variable = INNODB_LOG_FILE_SIZE = 10M
Set-variable = INNODB_LOG_BUFFER_SIZE = 8M
# If the recent recent transaction is not affected, set flush_log_at_trx_commit to 0.
Innodb_flush_log_at_trx_commit = 1set-variable = INNODB_FILE_IO_THREADS = 4
Set-variable = INNODB_LOCK_WAIT_TIMEOUT = 50
Note that InnoDB does not create a directory yourself, you must use the operating system command to create a directory, and pay attention to MySQL permissions.
When running MySQL for the first time, it is recommended to use the command line mode. Under Windows, use the command line to open:
C: / mysql> mysqld-max --standalone - Console
The principle of setting up a configuration file under Windows is:
My.cnf and my.ini can only be a valid.
My.cnf is placed in the C: root directory, the My.ini file is placed in a WINDIR directory, C: / Windows or C: / Winnt.
In Linux, the initial configuration file is searched in the order below:
/etc/my.cnf global option
Compilation_dataDir / my.cnf server range options
DEFAULTS-EXTRA-FILE uses - DEFAULTS-EXTRA-FILE = ....
~ / .my.cnf user specified options
Suppose you have a 512MB memory, three 20GB hard drives (paths from: "/", "/ dr2", "/ dr3"), setting:
[mysqld]
InnoDB_Data_Home_Dir = /
InnoDB_Data_file_path = ibdata / ibdata1: 2000m; DR2 / IBDATA / IBDATA2: 2000M
Set-variable = innodb_buffer_pool_size = 350m
Set-variable = innoDB_additional_mem_pool_size = 20m
InnoDB_log_group_home_dir = / DR3 / IBLOGS
InnoDB_log_arch_dir = / dr3 / iblogs
InnoDB_log_archive = 0
Set-variable = INNODB_LOG_FILES_IN_GROUP = 3
Set-variable = INNODB_LOG_FILE_SIZE = 50M
Set-variable = INNODB_LOG_BUFFER_SIZE = 8M
InnoDB_Flush_Log_AT_TRX_COMMIT = 1
Set-variable = innodb_file_io_threads = 4
Set-variable = INNODB_LOCK_WAIT_TIMEOUT = 50
# innoDB_flush_method = fdataasync
# innoDB_fast_shutdown = 1
# set-variable = innoDB_thread_concurrency = 5
To improve performance, we put the log files and data files in different disks, and the bare disk partition can be used to store data to improve performance.
What needs to be reminded is that do not have the memory settings on Linux X86, Glibc grows the process heap to the thread stack, you can get the server, the following value is very dangerous:
INNODB_BUFFER_POOL_SIZE KEY_BUFFER
Max_connections * (sort_buffer record_buffer max_connections * 2 MB Each thread will use 2MB (binary version 256 kB) stack, in the worst case, the additional memory of Sort_Buffer Record_Buffer is used.
Some main parameters of performance optimization:
Set-variable = max_connections = 200
Set-variable = Record_buffer = 1M
Set-variable = sort_buffer = 1m
# If you are a Myisam table, set key_buffer to 5 - 50% of memory,
# However, for InnoDB, it is necessary to ensure that the Key_Buffer InnoDB buffer pool is less than 80% of memory.
Set-variable = key_buffer = ...
Also worth noting a few parameters:
The number of threads I / O of the InnoDB_File_IO_THREADS file I / O, but under Windows, it can be set.
InnoDB_fast_shutdown InnoDB defaults before cleaning buffer, this process may take a few minutes, in extreme cases may take a few hours, if this parameter is set to 1, INNODB has skip this process, starting from 3.23.50. The default value is 1.
InnoDB_thread_concurrency InnoDB strives to keep operating system threads, the default value is 8, if your performance is relatively low, INNODB_MONITOR shows a lot of thread waiting signal, you should set this value, if your system has a lot of processors and disks This value can be raised. The recommended value is the number of disk numbers plus the number of disks.
After configuring my.cnf or my.ini, you can start MySQL to build InnoDB. Before the first establishment, it is recommended that you start from manual, so you can see the INNODB's establishment process:
Heikki @ donna: ~ / mysql-3.23.48 / sql> mysqld
020204
23:17:12
InnoDB: The First Specified Data File / DR2 / TMP / Heikki / Data / IBData1
DID NOT EXIST:
InnoDB: a New Database to Be created!
InnoDB: Setting File / DR2 / TMP / Heikki / Data / IBData1 Size To 20 MB
InnoDB: Database Physically Writes The File Full: Wait ...
020204 23:17:16 InnoDB: Data file / DR2 / TMP / Heikki / Data / IBData2 Did NOT: NEW
To be created
InnoDB: Setting File / DR2 / TMP / Heikki / Data / IBData2 Size To 200 MB
InnoDB: Database Physically Writes The File Full: Wait ...
020204 23:17:41 InnoDB: Data file / DR2 / TMP / Heikki / Data / IBData3 Did NOT: NEW
To be created
InnoDB: Setting File / Dr2 / TMP / Heikki / Data / IBData3 Size To 1000 MbinnoDB: Database Physical Writes The File Full: Wait ...
020204 23:21:37 InnoDB: log file ./IB_logfile0 Did NOT: New to Be CREATED
InnoDB: Setting log file ./IB_logfile0 size to 10 MB
InnoDB: Database Physically Writes The File Full: Wait ...
020204 23:21:39 InnoDB: log file ./IB_logfile1 Did NOT: New to Be CREATED
InnoDB: Setting log file ./IB_logfile1 size to 10 MB
InnoDB: Database Physically Writes The File Full: Wait ...
020204 23:21:41 InnoDB: log file ./IB_logfile2 Did NOT: New to Be created
InnoDB: Setting log file ./IB_logfile2 size to 10 MB
InnoDB: Database Physically Writes The File Full: Wait ...
InnoDB: DoubleWrite Buffer NOT: CREATING New
InnoDB: Doublewrite Buffer Created
Innodb: Creating Foreign Key Constraint System Tables
InnoDB: Foreign Key Constraint System Tables CREATED
020204 23:21:45 InnoDB: Started
MySQLD: Ready for Connections
If you use the mysqladmin shutdown command to close, it is displayed:
020204
23:34:45
MySQLD:
Normal
Shutdown
020204
23:34:45
InnoDB: Starting Shutdown ...
020204
23:34:47
InnoDB: Shutdown Completed
020204
23:34:47
MySQLD: SHUTDOWN COMPLETE
Enter the directory where the log file is located, we can see the files such as IB_ARCH_LOG_0000000000.
Let's build an InnoDB table, assume that we use mysql test to enter:
Create Table Customer (A INT, B CHAR (20), Index (a)) TYPE = InnoDB;
View status:
Show Table Status from test like 'Customer'
It should be noted that if you don't manually remove the .frm files under the InnoDB database, it is used by Create Table and Drop Table.
Transform Myisam into InnoDB
* Note * MySQL system table, User or Host, etc. cannot be converted to InnoDB format, they must use myisam format! !
If you want all the tables created in the future, join in the configuration file:
Default-Table-Type = InnoDB
InnoDB does not have a special index to establish an optimization mechanism, so you can't take the EXPORT / IMPORT, then establish an index method to transform, the fastest way is to change the table type to InnoDB, then insert data directly into data: alter table ... type = InnoDB Or establish an empty InnoDB table with the same structure, then insert data:
INSERT INTO ... SELECT * from ....
In order to better control the data insertion process, it is best to insert a large table insertion:
Insert Into NewTable Select * from Oldtable Where Yourkey> Something
AND Yourkey <= Somethingelse;
After all the data is inserted, you can rename the table name.
During insertion, you should set the InnoDB buffer pool to reduce disk I / O. At the same time, you should also increase the log file and log buffer.
It should be noted that don't let the table space are all exhausted, if the ALTER TABLE uses the table space, it will roll back, and if the disk is not enough, this process will last for a few hours.
Foreign key constraint
Starting with MySQL 3.23.43b, InnoDB supports foreign key constraints. As an important feature of data reference integrity, InnoDB has created a first-rate of MYSQL various table types on foreign key.
Establish foreign key:
example:
CREATE TABLE PARENT (ID INT NOT NULL, PRIMARY Key (ID)) TYPE = InnoDB;
Create Table Child (ID INT, PARENT_ID INT, INDEX PAR_IND (PARENT_ID),
Foreign Key (PARENT_ID) REFERENCES PARENT (ID)) TYPE = INNODB;
Both tables must be the INNODB type, foreign key and referenced key must be a column in an index. InnoDB does not automatically establish an index for foreign keys, which must be clearly indicated.
If the CREATE TABLE statement occurs 1005 error, the error message string references the 150 error, then the foreign key constraints play a role.
Only NULL is included in the sub-line only if the columns contain NULL in the father's line.
Any ALTER TABLE operations currently delete foreign key constraints, so it is recommended to use Drop Table and create table to modify Schema. Because ALTER TABLE uses a rename table internally, it can confuse the foreign key, and the same CREATE INDEX is also processed as an Alter Table, and cannot be used for foreign key constraints.
InnoDB allows you DROP any table, even though this will break the foreign key, the result of this operation is that the constraint is also Drop.
View foreign key constraint status in Table T:
Show Table Status from YourDatabaseName Like 'T'
InnoDB does not support Cascade On Delete or its special constraint options. (We know that there is this stuff in Oracle)
The problem you need to pay attention to the column of the automatic increment is: Auto-increment column does not participate in lock mechanisms and transaction processing, so that the neutrality in the middle of the sequence is caused.
Add InnoDB data and log files
The length of the InnoDB data file cannot be increased, and the measures for adding table space are new to add data files:
InnoDB_Data_file_path, then restart MySQL. Similarly, you can't delete data files, in order to reduce the length of the data file, you need to export data, create a new database, and then import it.
If you need to change the size of the log file, you can turn off Mysql first, then move the old log file to a backup directory, modify my.cnf, restart MySQL, confident that the InnoDB is started normally, you can delete the old log file.
Backup of the InnoDB database:
Turn off MySQL and make sure it is normal.
Copy all data files.
Copy all INNODB log files.
Copy my.cnf configuration file.
Copy all the .frm files of all InnoDB tables.
There is currently no tools for hot spare or incremental backups. For a highly reliable server, a copy form can be used.
The process of data recovery is to re-examine the previously backed up of these files back to the original position. This situation is only necessary if data file is corrupted. The general database is paralyzed, restarting MySQL, INNODB will recover the transaction.
If you do not reply if you have a table space, you can use Select Into Outfile to recover your data. This time you need to set in my.cnf:
Set-variable = innodb_force_recovery = 4
InnoDB data files are compatible on all platforms, so as long as the floating point format of the two machines is consistent, you can move files between the two machines.
The narrative and interpretation of the lock mechanism is relatively complicated. Here, skip the original chapter 8 (http://www.innodb.com/ibman.html) About the lock mechanism, we directly tell several tips for performance tuning:
1. If the CPU resource utilization is less than 70%, your load may be caused by disk, there may be too many transactions, or the buffer pool is too small. It is recommended to increase the buffer pool.
2. Put multiple modifications in a transaction, InnoDB requires refreshing the log to disk each time, and the speed of the disk is generally slower than the CPU, so it is recommended not to submit a transaction frequently.
3. If you can withstand the loss of recent transactions, it is recommended to set InnoDB_Flush_Log_AT_TRX_COMMIT to refresh a log at 0, InnoDB.
4. Increase log files, combined all log files should be as large as buffer pools, and small log files will result in a lot of unnecessary disk write operations, and the defect of large log files is longer.
5. It is also necessary to increase the log buffer, such as 8 MB.
6. Adopt VARCHAR classes, not a char type, a small table is more suitable for the buffer pool, thereby less disk I / O.
7. On Linux and some UNIX systems, use the UNIX fsync command to refresh files, but in some cases, it may be very slow, can modify INNODB_FLUSH_METHOD to o_dsync to improve write performance.
8. When importing data, make sure AutoCommit = 1 is not open, add the most in front of the imported SQL file:
Set autocmit = 0;
COMMIT;
If you use the mysqldump --opt option, your DUMP file can quickly import the InnoDB table.
9. Pay attention to the rollback operation when large batch insertion.
10. Pay attention to those who consume disk resources, use Drop Table or Truncate to empty the table instead of delete from. .11. If you want to insert multiple rows, use multi-line INSERT to mitigate the traffic of the client and server.
INSERT INTO YOTABLE VALUES (1, 2), (5, 5);
More in the above (original text is Multi-line, I think it should be multi-row). This prompt is not only for InnoDB, but also applicable to other table types.
Starting from 3.23.42, InnoDB includes the InnoDB monitors, which can output information in InnoDB internal state, which is very useful when performance tuning. When turned on, the monitor can output data to the standard output every 15 seconds to the standard output, If it is not started from the command line, these outputs are written to the .ERR file, on the Windows platform, must start in the DOS command line to start.
The main information includes:
Table and records of each activated transaction
Lock waiting for business
Thread signal waiting
Delayed file I / O request
Buffer pool statistics and deletion and inserting buffer merge
The started command is:
CREATE TABLE INNODB_MONITOR (a int) TYPE = INNODB;
The syntax of the stop is:
Drop Table InnoDB_Monitor;
If the database is turned off, the monitor is still running, and must be closed before the next startup monitor.
You can start InnoDB_Lock_Monitor and InnoDB_Tablespace_Monitor with the same syntax
In addition to the lock mechanism, the contents of this article have not described also have the structure of the table and index, the recorded physical structure, the management of the file space, the error handling, the limit of the InnoDB table.
