Common InnoDB Parameter Parsing

Common InnoDB Parameter Parsing

Common InnoDB Parameter Parsing

1. Files (data files and log files)

1. related parameters:

Innodb_data_home_dir

Innodb_data_file_path = file_name: file_size [: autoextend [: max: max_file_size]

Note:

A. The value of innodb_data_file_path should be a list of one or more data file types. If you name more than one data file, separate them with semicolons (';').

B. The autoextend attribute and the subsequent attribute can only be used for the last data file in the innodb_data_file_path row.

C. InnoDB does not create a directory. Therefore, before starting the server, make sure that the/ibdata directory exists.

D. If innodb_data_home_dir is not specified, the mysql data directory is used by default.

E. If you specify innodb_data_home_dir as an empty string, you can specify an absolute path for the data file listed in the innodb_data_file_path value.

[Mysqld]

Innodb_data_home_dir =

Innodb_data_file_path =/ibdata/ibdata1: 50 M;/ibdata/ibdata2: 50 M: autoextend

Ii. Parameters

1. innodb_autoextend_increment (dynamic, 8 MB by default)

The size (MB) added for expansion when the automatic expansion tablespace is filled ).

2. innodb_fast_shutdown (dynamic, default value: 1)

0: complete cleaning, insert buffer merging, and dirty page refreshing before closing, but the fastest restart

1: When InnoDB is disabled, purge and insert buffer merge are skipped and only dirty pages are refreshed.

2: refresh the log and disable it forcibly. Similar to crash, data will not be lost, but a crash recovery will be performed at startup.

When mysql is disabled:

1. purge all: delete useless undo pages

2. merge insert buffer

3. flush dirty page

3. innodb_lock_wait_timeout

InnoDB transactions can wait for a locked number of seconds before rollback

4. innodb_max_dirty_pages_pct

The main thread in InnoDB tries to write pages from the buffer pool so that the percentage of dirty pages does not exceed this value.

In the log segment of show innodb status, you can view the distance between Last checkpoint at and Log flushed up to determine the current dirty page.

In the buffer pool and memory, view the proportional relationship between the Modified db pages AND the Buffer pool size. The value is equal to innodb_max_dirty_pages_pct.

5. innodb_mirrored_log_groups (default value: 1, no image)

The number of log groups that are retained by the database.

6. innodb_open_files

In InnoDB, this option is only relevant when you use multiple tablespaces. It specifies the maximum number of. ibd files that InnoDB can open at a time. The minimum value is 10. The default value is 300. The file descriptor for the. ibd file is only for InnoDB. They are independent of the descriptors specified by the -- open-files-limit server option and do not affect table cache operations.

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

This article permanently updates the link address: