"MySQL" MySQL InnoDB configuration detailed

MysqlInnoDB Configuration Detailed




Innodb_buffer_pool_size: This is the most important setting for InnoDB, which has a decisive impact on InnoDB performance. The default setting is only 8M, so the default database setting below InnoDB performance is poor. You can set 60-80% memory on a database server that has only the InnoDB storage engine. To be more precise, set the memory size to 10% larger than InnoDB tablespaces under the memory capacity allowed.

Innodb_data_file_path: Specifies the space for table data and index storage, which can be one or more files. The last data file must be automatically expanded, and only the last file will allow automatic expansion. Thus, when the space is exhausted, the auto-expansion data file will automatically grow (in 8MB) to accommodate the additional data. For example: Innodb_data_file_path=/disk1/ibdata1:900m;/disk2/ibdata2:50m:autoextend two data files on different disks. The data is first placed in the ibdata1, and when it reaches 900M, the data is placed in the IBDATA2. Once the 50MB,IBDATA2 is reached, it will automatically grow in 8MB units. If the disk is full, you need to add a data file to the other disk.

Innodb_data_home_dir: Place table space Data directory, default in MySQL data directory, set to and MySQL installation file different partition can improve performance.

Innodb_log_file_size: This parameter determines the recovery speed. If it's too big, recovery will be slow, too small. Impact query performance, generally take 256M to balance performance and recovery speed
。
Innodb_log_buffer_size: Disk speed is very slow, directly log writes to the disk will affect the performance of InnoDB, this parameter sets the size of the log buffer, generally 4M. If you have a large blob operation, you can increase it appropriately.

innodb_flush_logs_at_trx_commit=2: This parameter sets the processing of log information in memory when the transaction commits.

1) = 1 o'clock, when each transaction commits, the log buffer is written to the log file, and the log file is refreshed with the disk operation. Truly ACID. Slow speed.
2) = 2 o'clock, when each transaction commits, the log buffer is written to the file, but the log file is not refreshed with disk operations. Only the operating system crashes or loses power to delete the last second of the transaction, or the transaction will not be lost.
3) = 0 o'clock, the log buffer is written to the log file once per second, and the log file is refreshed with disk operations. The crash of any mysqld process will delete the last second of the transaction before the crash

Innodb_file_per_table: You can store each InnoDB table and its index in its own file.

Transaction-isolation=read-comitted: If the application can run at the Read-commited isolation level, this setting will have some performance gains.

Innodb_flush_method: How to set InnoDB Sync io:

1) default– use Fsync ().
2) O_sync open files in SYNC mode, usually slower.
3) O_direct, use DIRECT IO on Linux. Can significantly increase the speed, especially on RAID systems. Avoid additional data duplication and double buffering (MySQL buffering and OS buffering).

Innodb_thread_concurrency:innodb kernel The maximum number of threads.

1) Minimum set to (Num_disks+num_cpus) * *.
2) You can disable this restriction by setting the 1000来 to

"MySQL" MySQL InnoDB configuration detailed