Shulou(Shulou.com)06/01 Report--

Files (data files, log files)

1. Related parameters:

Innodb_data_home_dir

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

Note:

The values of an and innodb_data_file_path should be a list of one or more data file specifications. If you name more than one data file, separate them with a semicolon (';')

B, the autoextend attribute, and the following attribute can only be used to match the last data file in the innodb_data_file_path line.

C, InnoDB does not create a directory, so make sure the / ibdata directory does exist before starting the server

D. If innodb_data_home_dir is not specified, it defaults to mysql data directory

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:50M;/ibdata/ibdata2:50M:autoextend

Second, parameters

1. Innodb_autoextend_increment (dynamic, default is 8m)

The size (in MB) that is increased for expansion when the automatic extension table space is filled.

2. Innodb_fast_shutdown (dynamic, default is 1)

0: do a complete cleanup, insert buffer merge, and brush dirty pages before closing, the slowest, but the fastest to restart

1:InnoDB skips purge and insert buffer merge when shutting down, only scrubbing dirty pages

2: refresh the log and shut it down forcefully. Similar to crash, the data will not be lost, but a crash recovery will be done at startup.

What mysql does when it shuts down:

1. Purge all: delete useless undo pages

2 、 merge insert buffer

3 、 flush dirty page

3. Innodb_flush_log_at_trx_commit (dynamic, default is 1)

0: fastest, the log buffer is flushed to disk by default, but no action is taken when the transaction is committed, and the last second transaction will be lost when mysql crashes.

1: the safest, the log buffer is flushed to the disk by default, and flushed to the log file when the transaction is committed, and fsync is called to flush to the disk

2: compromise, the log buffer is flushed to disk by default and flushed to the log file when the transaction is committed, but fsync is not called. Only when the system crashes will the last second transaction be lost.

4. Innodb_force_recovery (static, default is 0), used to dump data from corrupted DB

0: indicates that all recovery operations are performed when recovery is needed (that is, check the data page / purge undo/insert buffer merge/rolling back&forward). When a valid restore operation cannot be performed, mysql may not be able to start and log errors.

1. (SRV_FORCE_IGNORE_CORRUPT): ignore checked corrupt pages.

2. (SRV_FORCE_NO_BACKGROUND): blocking the running of the main thread, if the main thread needs to perform full purge operations, will result in crash.

3. (SRV_FORCE_NO_TRX_UNDO): no transaction rollback operation is performed.

4. (SRV_FORCE_NO_IBUF_MERGE): merge operations that insert buffers are not performed.

5. (SRV_FORCE_NO_UNDO_LOG_SCAN): without looking at the redo log, the InnoDB storage engine treats uncommitted transactions as committed.

6. (SRV_FORCE_NO_LOG_REDO): roll forward is not performed.

When the parameter value is greater than 0, you can perform select, create and drop operations on the table, but operations such as insert, update and delete are not allowed.

5 、 innodb_lock_wait_timeout

The number of seconds an InnoDB transaction can wait for a lock before rolling back

6 、 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 section of show innodb status, you can check the distance between Last checkpoint at and Log flushed up to to determine the current dirty page.

In BUFFER POOL AND MEMORY, look at the proportional relationship between Modified db pages and Buffer pool size, which is approximately equal to innodb_max_dirty_pages_pct

Show innodb status\ G

LOG

-

Log sequence number 16 881655880

Log flushed up to 16 881649862

Last checkpoint at 16 546135914

You can see that there is a considerable gap between checkpoints and log sequence number,Log flushed up to.

--

BUFFER POOL AND MEMORY

--

Total memory allocated 19338953832; in additional pool allocated 13600768

Buffer pool size 1048576

Free buffers 17666

Database pages 1009478

Modified db pages 204553

The modified pages account for nearly 20% of the total database buffer pool pages, and the size is 204553*16k/1024=3.196G.

7 、 innodb_max_purge_lag

8. Innodb_mirrored_log_groups (default is 1, no mirrors)

The number of the same copies within the log group held for the database.

9 、 innodb_open_files

In InnoDB, this option is only relevant if you are using multiple tablespaces. It specifies the maximum number of .ibd files that InnoDB can keep open at a time. The minimum value is 10. The default is 300.

The file descriptor for .ibd files is for InnoDB only. They are independent of the descriptors specified by the-- open-files-limit server option and do not affect the operation of the table cache.

Innodb_thread_concurrency

InnoDB tries to keep the number of operating system threads within InnoDB less than or equal to the limit given by this parameter. If there is a performance problem, and

And SHOW INNODB STATUS shows that many threads are waiting for the signal, so you can make the thread "thrashing" and set this parameter smaller or more.

Big. If your computer has multiple processors and disks, you can try a higher value to make better use of the computer's resources. A recommended value

Is the sum of the number of processors and disks on the system. A value of 500 or more prohibits the invocation of concurrent checks. The default value is 20, and if the setting is large

At or equal to 20, concurrent checking will be prohibited.

Innodb_status_file

This option allows InnoDB to create a file / innodb_status for periodic SHOW INNODB STATUS output.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.