{"id":2857,"date":"2016-09-25T18:50:26","date_gmt":"2016-09-25T10:50:26","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2857"},"modified":"2016-09-25T18:50:26","modified_gmt":"2016-09-25T10:50:26","slug":"%e5%b8%b8%e7%94%a8%e7%9a%84mysql%e9%85%8d%e7%bd%ae%e6%96%87%e4%bb%b6%e6%a8%a1%e6%9d%bf","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2857.html","title":{"rendered":"\u5e38\u7528\u7684MySQL\u914d\u7f6e\u6587\u4ef6\u6a21\u677f"},"content":{"rendered":"<p>=Start=<\/p>\n<h5>\u7f18\u7531\uff1a<\/h5>\n<p>\u8fd9\u662f\u5728GitHub\u4e0a\u770b\u5230\u7684\u4e00\u4e2aMySQL\u914d\u7f6e\u6587\u4ef6\u6a21\u677f\uff0c\u89c9\u5f97\u4e0d\u9519\uff0c\u5728\u6b64\u8bb0\u5f55\u4e00\u4e0b\uff0c\u65b9\u4fbf\u4ee5\u540e\u5728\u6709\u9700\u8981\u7684\u65f6\u5019\u8fdb\u884c\u53c2\u8003\uff08\u56e0\u4e3aMySQL\u7684\u53c2\u6570\u4f18\u5316\u662f\u4e2a\u5f88\u590d\u6742\u7684\u4e1c\u897f\uff0c\u5bf9\u4e8e\u4e0d\u540c\u7684\u7f51\u7ad9\uff0c\u53ca\u5176\u5728\u7ebf\u91cf\u3001\u8bbf\u95ee\u91cf\u3001\u5e16\u5b50\u6570\u91cf\u3001\u7f51\u7edc\u60c5\u51b5\uff0c\u4ee5\u53ca\u673a\u5668\u786c\u4ef6\u914d\u7f6e\u90fd\u6709\u5173\u7cfb\uff0c\u4f18\u5316\u4e0d\u53ef\u80fd\u4e00\u6b21\u6027\u5b8c\u6210\uff0c\u9700\u8981\u4e0d\u65ad\u7684\u89c2\u5bdf\u4ee5\u53ca\u8c03\u8bd5\uff0c\u624d\u6709\u53ef\u80fd\u5f97\u5230\u6700\u4f73\u6548\u679c\uff09\u3002<\/p>\n<p>\u5982\u679c\u89c9\u5f97\u8fd9\u4e2a\u914d\u7f6e\u6587\u4ef6\u592a\u590d\u6742\u4e86\uff0c\u4e5f\u53ef\u4ee5\u8003\u8651\u6309\u9700\u4f7f\u7528\u5b89\u88c5\u65f6\u81ea\u5e26\u7684\u4e00\u4e9b\u914d\u7f6e\u6587\u4ef6\uff1a<\/p>\n<pre class=\"lang:default decode:true\">\/usr\/share\/mysql\/my-large.cnf\n\/usr\/share\/mysql\/my-huge.cnf\n\/usr\/share\/mysql\/my-innodb-heavy-4G.cnf\n\/usr\/share\/mysql\/my-medium.cnf\n\/usr\/share\/mysql\/my-small.cnf<\/pre>\n<h5>\u6b63\u6587\uff1a<\/h5>\n<pre class=\"lang:default decode:true\"># author: jiangchengyao@gmail.com\n# This configration file opts for MySQL 5.6 and 5.7.\n# assume: 32 CPU core \/ 256G Memory \/ SSD storage with 20000 IOPS in 16K page size\n\n[mysql]\nprompt = [\\\\u@\\\\h][\\\\d]&gt;\\\\_\n\n[mysqld]\n# basic settings #\nuser = mysql\nsql_mode = \"STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER\"\nautocommit = 1\ncharacter_set_server=utf8mb4\ntransaction_isolation = READ-COMMITTED\nexplicit_defaults_for_timestamp = 1\nmax_allowed_packet = 16777216\nevent_scheduler = 1\n\n# connection #\ninteractive_timeout = 1800\nwait_timeout = 1800\nlock_wait_timeout = 1800\nskip_name_resolve = 1\nmax_connections = 512\nmax_connect_errors = 1000000\n\n# session memory setting #\nread_buffer_size = 16777216\nread_rnd_buffer_size = 33554432\nsort_buffer_size = 33554432\ntmp_table_size = 67108864\njoin_buffer_size = 134217728\n\n# log settings #\nlog_error = error.log\nslow_query_log = 1\nslow_query_log_file = slow.log\nlog_queries_not_using_indexes = 1\nlog_slow_admin_statements = 1\nlog_slow_slave_statements = 1\nlog_throttle_queries_not_using_indexes = 10\nexpire_logs_days = 90\nlong_query_time = 2\nmin_examined_row_limit = 100\nbinlog-rows-query-log-events = 1\nlog-bin-trust-function-creators = 1\nexpire-logs-days = 90\nlog-slave-updates = 1\n\n# innodb settings #\ninnodb_page_size = 16384\ninnodb_buffer_pool_size = 160G\ninnodb_buffer_pool_instances = 16\ninnodb_buffer_pool_load_at_startup = 1\ninnodb_buffer_pool_dump_at_shutdown = 1\ninnodb_lru_scan_depth = 4096\ninnodb_lock_wait_timeout = 5\ninnodb_io_capacity = 10000\ninnodb_io_capacity_max = 20000\ninnodb_flush_method = O_DIRECT\ninnodb_file_format = Barracuda\ninnodb_file_format_max = Barracuda\ninnodb_undo_logs = 128\ninnodb_undo_tablespaces = 3\ninnodb_flush_neighbors = 0\ninnodb_log_file_size = 17179869184\ninnodb_log_files_in_group = 2\ninnodb_log_buffer_size = 16777216\ninnodb_purge_threads = 4\ninnodb_large_prefix = 1\ninnodb_thread_concurrency = 64\ninnodb_print_all_deadlocks = 1\ninnodb_strict_mode = 1\ninnodb_sort_buffer_size = 67108864\ninnodb_write_io_threads = 16\ninnodb_read_io_threads = 16\ninnodb_file_per_table = 1\ninnodb_stats_persistent_sample_pages = 64\ninnodb_autoinc_lock_mode = 2\n\n# replication setting #\nmaster_info_repository = TABLE\nrelay_log_info_repository = TABLE\nsync_binlog = 1\ngtid_mode = on\nenforce_gtid_consistency = 1\nlog_slave_updates\nbinlog_format = ROW\nbinlog_rows_query_log_events = 1\nrelay_log = relay.log\nrelay_log_recovery = 1\nbinlog_gtid_simple_recovery = 1\nslave_skip_errors = ddl_exist_errors\nslave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'\n\n# semi sync replication settings #\nplugin_load = \"validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so\"\nrpl_semi_sync_master_enabled = 1\nrpl_semi_sync_master_timeout = 3000\nrpl_semi_sync_slave_enabled = 1\n\n# password plugin #\nvalidate_password_policy=STRONG\nvalidate-password=FORCE_PLUS_PERMANENT\n\n[mysqld-5.7]\n# new innodb setting #\nloose_innodb_numa_interleave=1\ninnodb_buffer_pool_dump_pct = 40\ninnodb_page_cleaners = 16\ninnodb_undo_log_truncate = 1\ninnodb_max_undo_log_size = 2G\ninnodb_purge_rseg_truncate_frequency = 128\n# new replication setting #\nslave-parallel-type = LOGICAL_CLOCK\nslave-parallel-workers = 16\nslave_preserve_commit_order=1\nslave_transaction_retries=128\n# other change setting #\nbinlog_gtid_simple_recovery=1\nlog_timestamps=system\nshow_compatibility_56=on<\/pre>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"https:\/\/github.com\/jdaaaaaavid\/mysql_best_configuration\/blob\/master\/my.cnf\">https:\/\/github.com\/jdaaaaaavid\/mysql_best_configuration\/blob\/master\/my.cnf<\/a><\/li>\n<li><a href=\"http:\/\/blog.itpub.net\/15480802\/viewspace-757509\/\">mysql\u7528\u4e8e\u5206\u914d\u5185\u5b58\u7684\u53c2\u6570<\/a><\/li>\n<li><a href=\"http:\/\/www.jb51.net\/article\/28363.htm\">MySQL\u914d\u7f6e\u6587\u4ef6my.cnf\u4e2d\u6587\u8be6\u89e3\u9644mysql\u6027\u80fd\u4f18\u5316\u65b9\u6cd5\u5206\u4eab<\/a><\/li>\n<li><a href=\"http:\/\/www.jb51.net\/article\/48082.htm\">MySQL\u914d\u7f6e\u6587\u4ef6my.cnf\u53c2\u6570\u4f18\u5316\u548c\u4e2d\u6587\u8be6\u89e3<\/a><\/li>\n<li><a href=\"http:\/\/msnvip.iteye.com\/blog\/542004\">Mysql my.cnf\u914d\u7f6e<\/a><\/li>\n<li><a href=\"http:\/\/blog.csdn.net\/wjc19911118\/article\/details\/51784783\">Mysql \u914d\u7f6e\u6587\u4ef6\u8be6\u89e3<\/a><\/li>\n<\/ul>\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u8fd9\u662f\u5728GitHub\u4e0a\u770b\u5230\u7684\u4e00\u4e2aMySQL\u914d\u7f6e\u6587\u4ef6\u6a21\u677f\uff0c\u89c9\u5f97\u4e0d\u9519\uff0c\u5728\u6b64\u8bb0\u5f55\u4e00\u4e0b\uff0c\u65b9\u4fbf\u4ee5\u540e [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,12],"tags":[141,16],"class_list":["post-2857","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-tools","tag-my-cnf","tag-mysql"],"views":3638,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2857","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=2857"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2857\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2857"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2857"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2857"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}