mysql+optimal+configuration+template_MySQL8.0.20最优配置

#This configration file opts for MySQL 8.0.20#Let us make an optimal MySQL configuration file template for product enviroment.##I assume the MySQL Server as followings. You should tune the variables according to your server. ##* 32 CPU core#* 256G Memory#* SSD storage with 20000 IOPS in 16K page size [client]##client标签设置mysql、mysqldump、mysqladmin的变量。user = rootpassword = 1111aaA_[mysql]##mysql标签设置mysql客户端的变量。prompt = [\\u@\\p][\\d]>\\_tee = "/tmp/tee.log" ##设置客户端的查询日志pager = "less -i -n -S" ##分页显示查询出的数据，方便搜索查询到的数据no-auto-rehash ##关闭预读取元数据[mysqld_safe][mysqldump]single-transaction[mysqld]# basic settings #innodb_temp_data_file_path ##研究一下这个参数设置成多少合适。default_authentication_plugin=mysql_native_passwordsql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"##server_id = 8888##datadir = /mysql_data/datatransaction_isolation = READ-COMMITTEDevent_scheduler = OFFopen_files_limit = 327675 ##设置MySQL运行过程中可以打开的文件数，避免出现“too many open files”报错。建议设置值使用公式：10+max_connections+(table_open_cache*2)。secure_file_priv = /tmp# connection #interactive_timeout = 600 ##交互式连接超时时间(mysql工具、mysqldump工具等)wait_timeout = 600 ##非交互式连接超时时间。指连接mysql的api程序，jdbc连接等。lock_wait_timeout = 1800 ##DDL操作的锁等待时间skip_name_resolve = ONmax_connections = 1024max_user_connections = 256max_connect_errors = 1000000# table cache performance settings #table_open_cache = 4096table_definition_cache = 4096table_open_cache_instances = 64# session memory settings #read_buffer_size = 16Mread_rnd_buffer_size = 32Msort_buffer_size = 32Mtmp_table_size = 64Mjoin_buffer_size = 128Mthread_cache_size = 64# optimizer switch settings #optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on' ##优化器开关设置。# log settings #log_timestamps = system##general_log = ON##general_log_file = /usr/local/mysql/data/MySQL01-general.logslow_query_log = ONlong_query_time = 1##slow_query_log_file = /usr/local/mysql/data/MySQL01-slow.loglog_queries_not_using_indexes = ONlog_slow_admin_statements = ON##log_slow_slave_statements = ONlog_throttle_queries_not_using_indexes = 10expire_logs_days = 90min_examined_row_limit = 100log_bin_trust_function_creators = ON# innodb settings #innodb_buffer_pool_size = 160Ginnodb_buffer_pool_instances = 16innodb_page_cleaners = 16 ##设置page clean线程的个数。这个值推荐和innodb_buffer_poool_instances相等。该值大于innodb_buffer_poool_instances时，自动设置为与innodb_buffer_poool_instances相同的值。innodb_lru_scan_depth = 2048 ##控制LRU列表中可用页的数量。默认值1024。写密集型负载使IO饱和，或者使用机械盘时建议使用默认值。调整innodb_lru_scan_depth时，从一个较低的值开始，向上配置设置，目标是很少看到零空闲页面。另外，在更改缓冲池实例的数量时，考虑调整innodb_lru_scan_depth，因为innodb_lru scan_depth*innodb_buffer_pool_instances定义了页面清理线程每.........