It is divided into dynamic parameters and static parameters .
Dynamic parameters ： adopt set global and set session Set it up .
Static parameters ： Need modification my.cnf file , Then restart the instance to take effect .
Define table space path 、 Initial size ( Default 10m)、 Automatic expansion strategy ( Default 64m)
The initial size is recommended to be 1G.
The first mock exam mode
The first mock exam mode ： More secure data innodb_flush_log_at_trx_commit=1 sync_binlog=1
Dirty page refresh
The first mock exam is the one mode. redo log and binlog Memory to file to disk refresh mechanism based on . Here is the refresh mechanism of data to disk .
innodb_max_dirty_pages_pct, Represents the percentage of dirty pages , Default 75%. Set to 25%-50%, Avoid the impact of later refresh TPS.
innodb_adaptive_flushing, adaptive flushing , On by default .
Improve DML Operating performance .
innodb_change_buffer_max_size, It is suggested to be changed to 50.
InnoDB Buffer pool memory size
innodb_buffer_pool_size, The server only runs a database application , It is suggested to be changed to In memory 50%-80%. Can support online modification , So you don't need to set it too large in the early stage .
innodb_buffer_pool_instance, Improve concurrency , Avoid memory contention . The default is 1, Recommended setting is 8.
How long does the server wait before closing the interactive connection
The two parameter settings are consistent , Avoid excessive connection time , Default 28800(8 Hours ), Recommended setting is 300-600s
The isolation level of the transaction
transaction_isolation, There are four isolation levels . The default is REPEATABLE-READ, Repeatable .
Can be opened at the same time .idb Number of files
The default is 300, It is suggested to adjust it to 65535.
MySQL maximum connection
max_connections, The default is 151.
More than one connection is common "too mang connections".
Setting too large is easy to cause instance downtime .
InnoDB Number of concurrent threads
innodb_thread_concurrency, It is suggested to adjust to service CPU Twice the number of cores .
The slow query
slow_query_log, Recommended Opening
long_query_time, Default 10s, Log when slow query times out
log_queries_not_using_indexes, Not indexed sql, Slow query log is also recorded .
Server node unique ID
server_id, The default is 0
IO Performance indicators
innodb_io_capacity, Default 200, Use ssd The parameters can be adjusted up .
《MySQL The way to the top 》 Zhang Su (sū)