Mysql Parameter Optimization

Mysql parameter optimization ### used to store the internal directory of InnoDB. For big data, setting 16 M is sufficient to use innodb_additional_mem_pool_size = 16 M ### set the total cache size of InnoDB, generally, it is set to 70%-80% innodb_buffer_pool_size = 12G ### specify the path and size of all InnoDB data files to be allocated innodb_data_file_path = ibdata1: 18 M; ibdata2: 1000 M: autoextend ### file read/write io count settings: innodb_file_io_threads = 4 ### InnoDB kernel's concurrent thread count settings innodb_thread_concurrency = 16 ### set the Daily Value innodb_log_file_size = 256 M ### set the daily value group count limit = 3 # # cache size of a transaction in the memory innodb_log_buffer_size = 8 M ### control the transaction submission method, set to 2 the fastest performance innodb_flush_logs_at_trx_commit = 2 ### InnoDB directly writes data to the disk, avoid repeated buffering and reduce the pressure on linux swap partitions innodb_flush_method = O_DIRECT ### control InnoDB dirty pages in the buffer by a percentage of innodb_max_dirty_pages_pct = 90 ### set to allow other transaction modifications the number of seconds before transaction rollback innodb_lock_wait_timeout = 120 ### we recommend that you set the following rules based on the physical memory: 1G => 8, 2G => 16, 3G => 32, greater than 3G => 64thread_cache_size = 64 ### query_cache_size = 256 M ### buffer size available for a single query, the default value is 1Mquery_cache_limit = 2 M ### according to the ratio of key_reads/key_read_requests, it should be as low as possible, at least, better key_buffer_size = 128 M ### MySql read buffer size, if the table's Sequential Scan requests are very frequent, you can increase the performance by adding the variable value and the memory buffer size. read_buffer_size = 4 M ### MySql random read (query Operation) buffer size read_rnd_buffer_size = 16 M ### Number of days when binary logs are automatically deleted expire_logs_days = 10 ### twice the number of Cpu cores thread_concurrency = 8 ### log format, set to MIXED mode binlog_format = MIXED