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定义了页面清理线程每秒执行的工作量。推荐使用默认值。innodb_fast_shutdown = 0 ##设置innodb关机模式。有效值0、1、2。0表示执行慢关机，即在关闭前彻底清除和更改缓冲区合并，这种模式下开机是最快的；1表示关机时跳过上述操作，但是在开机时会执行上述操作，称为快关机；2表示刷新其日志并冷关闭，不会丢失任何已提交的事务下，获得最快的关机，这种模式是开机最慢的。innodb_max_dirty_pages_pct = 80 ##设置当缓冲池中脏页达到百分之80时，强制进行checkpoint,将一部分脏页刷新到磁盘上。默认值90。推荐值80。这个值不建议设置太小，设置太小会加重IO的写操作。innodb_buffer_pool_dump_pct = 80 ##正常关机时将innodb_buffer_pool中百分之八十的数据保存到磁盘中innodb_lock_wait_timeout = 5 ##innodb dml锁的等待超时时间，单位秒。innodb_rollback_on_timeout = ON ##等待超时的事务进行回滚。innodb_io_capacity = 400 ##设置后台线程每秒操作IO的次数。默认值200。理想情况下尽可能低，如果太大会从缓冲池中清除过多的数据，反而使缓冲池失去了“缓冲”的作用。对于SSD磁盘默认值200已经足够。15000转的机械磁盘建议设置为200。15000转以下的机械磁盘建议设置为100。innodb_io_capacity_max = 2000 ##设置在刷新落后时，后台任务执行的最大IOPS数。默认值2000。最小值也是2000。这个值在使用SSD磁盘时也无需调大。innodb_flush_method = O_DIRECTinnodb_flush_neighbors = 0 ##对于SSD盘将这个值设置为0。由于该功能会使不怎么脏的页进行了写入，而后该页又会很快变成脏页，而固态硬盘有超高的IOPS，因此要置为0。可选值1表示刷新相同范围内连续的脏页，可选值2表示刷新相同范围内的脏页。innodb_undo_tablespaces = 3innodb_log_file_size = 1900MB ##这个值是百度公司统一的。innodb_log_files_in_group = 2innodb_log_buffer_size = 64MBinnodb_checksum_algorithm = strict_crc32 ##设置innodb存储引擎每次读取页时，使用checksum函数的哪个算法。innodb_thread_concurrency = 32innodb_print_all_deadlocks = ON ##将死锁信息打印到mysql错误日志中innodb_sort_buffer_size = 64Minnodb_write_io_threads = 16innodb_read_io_threads = 16innodb_stats_persistent_sample_pages = 64innodb_online_alter_log_max_size = 1Ginnodb_open_files = 4096# replication settings #sync_binlog = 1 ##当有多个从库，并且开启了半同步复制这个值可以设置成1000。百度公司在半同步复制时统一设置成1000。##gtid_mode = ON##enforce_gtid_consistency = ON##binlog_gtid_simple_recovery = ONlog_slave_updates = OFFbinlog_rows_query_log_events = ONrelay_log_recovery = ON##slave_skip_errors = ddl_exist_errors# replication settings for slave ###slave_parallel_workers = 16 ##设置多线程SQL线程数量##slave_parallel_type = LOGICAL_CLOCK ##设置多线程复制的模式##slave_preserve_commit_order = ON##slave_transaction_retries = 128# semi sync replication settings ###rpl_semi_sync_master_enabled = ON##rpl_semi_sync_master_timeout = 1000# semi sync replication settings for slave ###rpl_semi_sync_slave_enabled = ON# perforamnce_schema settingsperformance_schema_digests_size = 40000performance_schema_max_table_handles = 40000performance_schema_max_table_instances = 40000performance_schema_max_sql_text_length = 4096performance_schema_max_digest_length = 4096# group replication settings ###plugin-load = "group_replication.so;validate_password.so;semisync_master.so;semisync_slave.so"##transaction-write-set-extraction = XXHASH64##report_host = 127.0.0.1 # optional for group replication##binlog_checksum = NONE # only for group replication##loose_group_replication = FORCE_PLUS_PERMANENT##loose_group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"##loose_group_replication_compression_threshold = 100##loose_group_replication_flow_control_mode = 0##loose_group_replication_single_primary_mode = 0##loose_group_replication_enforce_update_everywhere_checks = 1##loose_group_replication_transaction_size_limit = 10485760##loose_group_replication_unreachable_majority_timeout = 120##loose_group_replication_start_on_boot = 0

　　

MySQL8.0.20最优配置超级大卖家现场深度干货分享、 跨境平台浅谈、 国际标准书号、 选品避雷指南：夏季选品十大陷阱！、 速卖通怎么选品？速卖通选品策略如何制定？、 亚马逊自动广告的四大误区，颠覆你对自动广告的认知、 2017年国庆广东自驾游好去处推荐、 2017年广府文化节有什么活动？、 2017年国庆广州百万葵园有哪些活动？有什么好玩的？、