Mysql Innodb learning experience

Mysql Innodb learning experience for databases is mostly due to IO problems, so SSD devices can now become popular. Can I optimize the I/O of the database? I would like to give a general talk about my views here, hoping to achieve a better result. Here we will talk about the specific configuration of the database as follows: I/O optimization principles for the configuration of www.2cto.com: cache as much as possible to reduce random I/O requests to the database; at the same time, it reduces random IO writes at any time and uses various buffer to cache. Next let's take a look at this parameter: innodb_buffer_pool_size: This is the most important configuration parameter of Innodb. This parameter controls the slow size of Innodb and affects the amount of data that can be cached. We recommend that you set this parameter between 70% and 80% of the physical memory. Innodb_flush_method: the IO type that controls Innodb. What are: fsync, O_DSYNC, and so on? We recommend that you use: O_DIRECT, this reduces the Cache Usage of VFS at the operating system level and conflicts with the buffer of Innodb, which reduces the pressure on the operating system. Innodb_io_capacity: this parameter controls the I/O capability of Innodb checkpoint. Generally, it can be calculated based on 15000 to 200 SAS disks, the value of RAID 10 made by SAS of 6 disks can be set to 600. For a general SATA disk, only 100 is allowed. (Innodb-plugin, Percona has this parameter) innodb_max_dirty_pages_pct: this parameter controls the proportion of dirty pages. If it is innodb_plugin or a version later than MySQL5.5, we recommend that you set this parameter to 75%-90%. If a large amount of data is written and the written data is not too active, you can set this value to a lower value. If the written or updated data is the hot data, you can set this value to 95% innodb_log_file_size: MB or more. We recommend that you have two previous log files (innodb_log_files_in_group ). if the system is very capitalized, you can also use this parameter to improve the performance and increase the file size to reduce the occurrence of checkpiont. The maximum value can be set to innodb_log_files_in_group * innodb_log_file_size <512G (percona, MySQL 5.6). We recommend that you set it to 256 M-> innodb_buffer_pool_size/innodb_log_file_in_group. Innodb_log_buffer_size: if it is not in a large transaction, it can be controlled at 8M-16M. Other parameters that affect IO (subject to 5.6) innodb_adaptive_flushing can be used by default. If it is a daily value service, you can adjust this value to 50 innodb_change_buffering. innodb_flush_neighors is enabled by default, this must be on, making full use of sequential IO to write data. Innodb_lru_scan_depth: this parameter is professional by default. Innodb_max_purge_lag is not enabled by default. If a large amount of data is written and read, The read priority can be ensured. You can use this function. Www.2cto.com innodb_random_read_ahead is disabled by default. It is an active parameter. If you want to use it, test it more. For passport applications, innodb_read_ahead_threshold can be used by default. The 56 pre-read mechanism can be enabled based on business processing, and passprot can be used to disable it. If innodb_random_read_ahead is used, we recommend that you disable this function. innodb_read_io_threads is set to 4 by default. 8 innodb_write_io_threads is set to 4 by default. 8 sync_binlog is set to 0 innodb_rollback: 128 The undo log of another 5.6 can also be configured independently. We recommend that you configure it separately.