• my.cnf 推荐配置


    disable transparent hugepages

    #!/bin/sh
    ### BEGIN INIT INFO
    # Provides:          disable-transparent-hugepages
    # Required-Start:    $local_fs
    # Required-Stop:
    # X-Start-Before:    mongod mongodb-mms-automation-agent
    # Default-Start:     2 3 4 5
    # Default-Stop:      0 1 6
    # Short-Description: Disable Linux transparent huge pages
    # Description:       Disable Linux transparent huge pages, to improve
    #                    database performance.
    ### END INIT INFO
    
    case $1 in
      start)
        if [ -d /sys/kernel/mm/transparent_hugepage ]; then
          thp_path=/sys/kernel/mm/transparent_hugepage
        elif [ -d /sys/kernel/mm/redhat_transparent_hugepage ]; then
          thp_path=/sys/kernel/mm/redhat_transparent_hugepage
        else
          return 0
        fi
    
        echo 'never' > ${thp_path}/enabled
        echo 'never' > ${thp_path}/defrag
    
        unset thp_path
        ;;
    esac

    数据库使用 tcmalloc 内存优化算法

    yum install libunwind gperftools gperftools-devel
    
    wget http://mirror.yongbok.net/nongnu/libunwind/libunwind-1.5-rc2.tar.gz
    
    wget https://github.com/google/tcmalloc/archive/master.zip
    
    malloc-lib=/usr/lib64/libtcmalloc.so

    undo log 需要在初始化时便依照配置文件初始化

    # 准备需要的目录
    groupadd mysql
    useradd -r -g mysql -s /bin/false mysql
    mkdir -p /data/mysql-{data,binlogs}
    mkdir -p /usr/local/mysql/log
    chown -R mysql.mysql /data/mysql-data
    chown -R mysql.mysql /data/mysql-binlogs
    chown -R mysql.mysql /usr/local/mysql
    
    # MySQL 5.6
    /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql-data/ --basedir=/usr/local/mysql/ --defaults-file=/usr/local/mysql/my.cnf --keep-my-cnf
    
    # MySQL 5.7
    /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf --initialize --user=mysql --datadir=/data/mysql-data/ --basedir=/usr/local/mysql/

    my.cnf 配置示例

    # MySQL Optimal Configuration File
    
    #This configration file opts for MySQL 5.6 and 5.7.
    #If you have any problem, dont hesitate to concact me.
    #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 
    
    #  my.cnf
    # author: jiangchengyao@gmail.com
    
    [client]
    user = root
    password = 1111aaA_
    
    [mysql]
    prompt = [\u@\p][\d]>\_
    no-auto-rehash
    
    [mysqld_safe]
    malloc-lib=/usr/lib64/libtcmalloc.so # yum install gperftools gperftools-devel
    
    [mysqldump]
    single-transaction
    
    [mysqld]
    # basic settings #
    user = mysql
    sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
    autocommit = 1
    server-id = 8888
    character_set_server=utf8mb4
    datadir=/data/mysql-data
    transaction_isolation = READ-COMMITTED
    explicit_defaults_for_timestamp = 1
    max_allowed_packet = 64M
    event_scheduler = 1
    
    # connection #
    interactive_timeout = 1800
    wait_timeout = 1800
    lock_wait_timeout = 1800
    skip_name_resolve = 1
    max_connections = 1024
    max_user_connections = 256
    max_connect_errors = 1000000
    
    # table cache performance settings
    table_open_cache = 4096
    table_definition_cache = 4096
    table_open_cache_instances = 64
    
    # session memory settings #
    read_buffer_size = 16M
    read_rnd_buffer_size = 32M
    sort_buffer_size = 32M
    tmp_table_size = 64M
    join_buffer_size = 128M
    thread_cache_size = 64
    
    # log settings #
    log_error = /usr/local/mysql/log/error.log
    log_bin = /data/mysql-binlogs/binlog
    log_error_verbosity = 2
    general_log_file = /usr/local/mysql/log/general.log
    slow_query_log = 1
    slow_query_log_file = /usr/local/mysql/log/slow.log
    log_queries_not_using_indexes = 1
    log_slow_admin_statements = 1
    log_slow_slave_statements = 1
    log_throttle_queries_not_using_indexes = 10
    expire_logs_days = 90
    long_query_time = 2
    min_examined_row_limit = 100
    log-bin-trust-function-creators = 1
    log-slave-updates = 1
    
    # innodb settings #
    innodb_page_size = 16384
    innodb_buffer_pool_size = 160G
    innodb_buffer_pool_instances = 16
    innodb_buffer_pool_load_at_startup = 1
    innodb_buffer_pool_dump_at_shutdown = 1
    innodb_lru_scan_depth = 4096
    innodb_lock_wait_timeout = 5
    innodb_io_capacity = 10000
    innodb_io_capacity_max = 20000
    innodb_flush_method = O_DIRECT
    innodb_undo_logs = 128
    innodb_undo_tablespaces = 3
    innodb_flush_neighbors = 0
    innodb_log_file_size = 16G
    innodb_log_files_in_group = 2
    innodb_log_buffer_size = 64M
    innodb_purge_threads = 4
    innodb_large_prefix = 1
    innodb_thread_concurrency = 64
    innodb_print_all_deadlocks = 1
    innodb_strict_mode = 1
    innodb_sort_buffer_size = 128M
    innodb_write_io_threads = 16
    innodb_read_io_threads = 16 
    innodb_file_per_table = 1
    innodb_stats_persistent_sample_pages = 64
    innodb_autoinc_lock_mode = 2
    innodb_online_alter_log_max_size=1G
    innodb_open_files=4096
    
    # replication settings #
    master_info_repository = TABLE
    relay_log_info_repository = TABLE
    sync_binlog = 1
    gtid_mode = on
    enforce_gtid_consistency = 1
    log_slave_updates
    binlog_format = ROW
    binlog_rows_query_log_events = 1
    relay_log = relay.log
    relay_log_recovery = 1
    slave_skip_errors = ddl_exist_errors
    slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
    
    # semi sync replication settings #
    plugin-load = "group_replication.so;validate_password.so;semisync_master.so;semisync_slave.so"
    loose_rpl_semi_sync_master_enabled = 1
    loose_rpl_semi_sync_master_timeout = 3000
    loose_rpl_semi_sync_slave_enabled = 1
    
    # password plugin #
    validate_password_policy = STRONG
    validate-password = FORCE_PLUS_PERMANENT
    
    # perforamnce_schema settings
    performance-schema-instrument='memory/%=COUNTED'
    performance_schema_digests_size = 40000
    performance_schema_max_table_instances = 40000
    performance_schema_max_sql_text_length = 4096
    performance_schema_max_digest_length = 4096
    
    [mysqld-5.6]
    # metalock performance settings
    metadata_locks_hash_instances = 64
    
    [mysqld-5.7]
    # new innodb settings #
    loose_innodb_numa_interleave = 1
    innodb_buffer_pool_dump_pct = 40
    innodb_page_cleaners = 16
    innodb_undo_log_truncate = 1
    innodb_max_undo_log_size = 2G
    innodb_purge_rseg_truncate_frequency = 128
    
    # new replication settings #
    slave-parallel-type = LOGICAL_CLOCK
    slave-parallel-workers = 16
    slave_preserve_commit_order = 1
    slave_transaction_retries = 128
    # other change settings #
    binlog_gtid_simple_recovery = 1
    log_timestamps = system
    show_compatibility_56 = on
    
    # 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

     

    更多动态请关注微信公众号 dbagrant
  • 相关阅读:
    Auto-Test 要点纪录(一)
    终端应用变身文件 MD5/SHA1 校验工具
    MD5 algorithm in Objective C
    iphone开发-SQLite数据库使用
    【React】354- 一文吃透 React 事件机制原理
    【Web技术】353- CDN 科普
    【CSS】352- 有趣的CSS弹跳动画
    【Vuejs】351- 带你解析vue2.0的diff算法
    【每周小回顾】7- 一起回顾上周精彩内容
    【Vuejs】350- 学习 Vue 源码的必要知识储备
  • 原文地址:https://www.cnblogs.com/laoyumi/p/14711847.html
Copyright © 2020-2023  润新知