MySQL needs to adjust the parameters

Mysql parameters that need to be adjusted

# explained in MySQL 5.6 release example

First, InnoDB settings

innodb_buffer_pool_size : save InnoDB Data and index in this buffer,

For dedicated MySQL servers, it is recommended to 50-80% physical memory size,

such as 5-6GB (8GB RAM), 20-25GB (32GB RAM),

100-120GB (128GB RAM) .

innodb_log_file_size : control the size of the redo log, redo logs can speed up writing,

Persistence and crash recovery. Usually use two files, MySQL 5.5 Total redo log limit is 4GB, to write load type of application, should increase this value, make background checkpoint activity reduce, improve performance, such as 4G, too general make recovery take longer time.

Innodb_flush_method : control how data and logs are flushed to disk, and if a RAID controller is used,

You should use O_direct.

innodb_flush_neighbors : If SSD can be turned off, there is no performance improvement on continuous IO.

innodb_io_capacity and Innodb_io_capacity_max: Set how many background threads are working per second,

Set according to the capabilities of the hardware.

innodb_lru_scan_depth : If you increase the innodb_io_capacity, you also increase the value

innodb_file_per_table : Set to on , each table uses a separate. ibd file to store data and indexes, allowing the dropping, truncating, or rebuilding tables to be reclaimed and reused for remaining space, as well as supporting extended features such as compression.

Innodb_flush_log_at_trx_commit : The default is 1, which means that InnoDB is fully compliant with the acid feature, refreshes each change to the redo log, is set to 2, refreshes once per second, and 0 is faster, but some data is lost at crash.

innodb_log_buffer_size : Transaction log buffer, when there is a large blob/text field, you can consider raising the value to avoid additional I/O load. This value is incremented by observing the innodb_log_waits state variable, if it is not 0.

Second, copy

Log-bin : Enable binary log, default is not crash secure, set according to business conditions,

If you need persistence, you need to turn on sync_binlog=1,sync_relay_log=1,

relay-log-info-repository=table and Master-info-repository=table

or Sync_relay_log_info=1 and sync_master_info=1.

expire-log-days : 1-10 days recommended

Server-id : In the master-slave replication environment, each server needs to have a unique Server-id

Binlog_format=row : Use row-based replication to improve performance and reduce locks. Also need to set

transaction-isolation=read-commitied ,

innodb_autoinc_lock_mode=2

Three, mixed parameters

Timezone=gmt : For globalization

Character-set-server=utf8mb4

Collation-server=utf8mb4_general_ci : UTF8 #是更好字符集的格式,

You can also set Skip-character-set-client-handshake

Ignore character sets for application settings

Sql-mode : #默认是宽容的, will silently truncate the data, should be better set

Strict_trans_tables,error_for_division_by_zero,no_auto_create_user,no_auto_value_on_zero,

No_engine_substitution,no_zero_date,

No_zero_in_date,only_full_group_by

Skip-name-resolve : Disable domain name reverse lookup for incoming connections, prohibit the use of host names for authorization,

Must use IP address for authorization

max_connect_errors : Set to 100000 for blocking access attacks with firewalls

max-connections : Recommendation 300-500, recommended not too high, recommended to use the application connection pool, or the MySQL thread pool

query_cache_size : Turn off this option by using a different approach, such as good indexing, sharing read load using replication, or using the external cache mechanism (memcached, Redis) to improve performance.

This article is from the "Boyhack" blog, make sure to keep this source http://461205160.blog.51cto.com/274918/1885588

MySQL needs to adjust the parameters