V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Aluhao
V2EX  ›  MySQL

MYSQL 8.0.33 8 核 64G my.cnf 配置

  •  
  •   Aluhao · 2023-11-13 11:34:43 +08:00 · 2235 次点击
    这是一个创建于 407 天前的主题,其中的信息可能已经有所发展或是发生改变。
    自建 MYSQL, 1 主 3 从,前环境使用的 my.cnf 配置文件,准备把它迁移到 AWS 去,
    innodb_io_capacity 是根据当前系统购买配置时给的参数;
    innodb_buffer_pool_size 是内存的 75%;
    innodb_redo_log_capacity 64G 内存建议 4G
    其它有些参数调整不知道是不是合理。

    [client]
    port = 3306
    socket = /tmp/mysql.sock
    [mysqld]
    user = mysql
    port = 3306
    socket = /tmp/mysql.sock
    server_id = 100
    #bind_address = 127.0.0.1
    basedir = /usr/local
    datadir = /var/db/mysql
    tmpdir = /var/db/mysql_tmpdir
    log_error = /var/db/mysql/error.log
    replica_load_tmpdir = /var/db/mysql_tmpdir
    secure_file_priv = /var/db/mysql_secure
    authentication_policy = caching_sha2_password
    explicit_defaults_for_timestamp = ON
    max_connections = 2000
    max_connect_errors = 3000
    max_allowed_packet = 512M
    gtid_mode = ON
    enforce_gtid_consistency = ON
    innodb_file_per_table = ON
    #innodb_dedicated_server = ON
    innodb_sort_buffer_size = 64M
    innodb_buffer_pool_size = 48G
    innodb_redo_log_capacity = 4G
    innodb_write_io_threads = 8
    innodb_read_io_threads = 8
    innodb_log_buffer_size = 64M
    innodb_data_home_dir = /var/db/mysql
    innodb_log_group_home_dir = /var/db/mysql
    innodb_data_file_path = ibdata1:128M:autoextend
    innodb_temp_data_file_path = ibtmp1:128M:autoextend
    innodb_flush_method = O_DIRECT
    innodb_io_capacity = 3000
    innodb_io_capacity_max = 6000
    innodb_adaptive_hash_index = OFF
    sync_binlog = 1
    sync_relay_log = 1
    tmp_table_size = 32M
    max_heap_table_size = 32M
    thread_stack = 512K
    thread_cache_size = 30
    key_buffer_size = 256M
    read_buffer_size = 8M
    sort_buffer_size = 4M
    join_buffer_size = 4M
    read_rnd_buffer_size = 4M
    binlog_cache_size = 16M
    binlog_expire_logs_auto_purge = OFF
    binlog_expire_logs_seconds = 3280000000
    log_replica_updates = ON
    log_bin = master
    relay_log = relay
    #relay_log_purge = ON
    #relay_log_recovery = ON
    skip_name_resolve = ON
    net_buffer_length = 32k
    net_retry_count = 16380
    #read_only = ON
    #super_read_only = ON
    #performance_schema = OFF
    event_scheduler = OFF
    mysqlx = OFF
    mysqlx_port = 33060
    mysqlx_socket = /tmp/mysqlx.sock
    mysqlx_bind_address = 127.0.0.1,::1
    [mysqldump]
    max_allowed_packet = 1G
    quote_names
    quick
    第 1 条附言  ·  2023-11-13 18:13:10 +08:00
    上面的配置是已经上线稳定运行 1 年左右,因为要考虑搬迁,新的调整配置如下:
    第 2 条附言  ·  2023-11-13 18:14:04 +08:00
    [client]
    port = 3306
    socket = /tmp/mysql.sock

    [mysqld]
    user = mysql
    port = 3306
    socket = /tmp/mysql.sock
    server_id = 100
    # bind_address = 127.0.0.1
    basedir = /usr/local
    datadir = /var/db/mysql
    tmpdir = /var/db/mysql_tmpdir
    log_error = /var/db/mysql/error.log
    replica_load_tmpdir = /var/db/mysql_tmpdir
    secure_file_priv = /var/db/mysql_secure
    authentication_policy = caching_sha2_password
    explicit_defaults_for_timestamp = ON

    # Performance related settings
    max_connections = 888
    max_connect_errors = 2000
    max_allowed_packet = 1G
    innodb_file_per_table = ON
    innodb_sort_buffer_size = 64M
    innodb_buffer_pool_size = 48G
    innodb_redo_log_capacity = 4G
    innodb_write_io_threads = 8
    innodb_read_io_threads = 8
    innodb_log_buffer_size = 64M
    innodb_data_home_dir = /var/db/mysql
    innodb_log_group_home_dir = /var/db/mysql
    innodb_data_file_path = ibdata1:128M:autoextend
    innodb_temp_data_file_path = ibtmp1:128M:autoextend
    innodb_flush_method = O_DIRECT
    innodb_io_capacity = 3000
    innodb_io_capacity_max = 6000
    innodb_adaptive_hash_index = OFF
    tmp_table_size = 128M
    max_heap_table_size = 128M
    # thread_stack = 1M
    # thread_cache_size = 30
    # sort_buffer_size = 4M
    # join_buffer_size = 4M
    # read_buffer_size = 8M
    # read_rnd_buffer_size = 4M
    # net_buffer_length = 32k
    net_retry_count = 16380
    key_buffer_size = 256M

    sync_binlog = 1
    sync_relay_log = 1
    relay_log = relay
    # relay_log_purge = ON
    # relay_log_recovery = ON
    log_bin = master
    log_replica_updates = ON
    binlog_cache_size = 16M
    binlog_expire_logs_auto_purge = OFF
    binlog_expire_logs_seconds = 3280000000
    gtid_mode = ON
    enforce_gtid_consistency = ON

    skip_name_resolve = ON
    event_scheduler = OFF
    mysqlx = OFF
    mysqlx_port = 33060
    mysqlx_socket = /tmp/mysqlx.sock
    mysqlx_bind_address = 127.0.0.1,::1
    # read_only = ON
    # super_read_only = ON
    # performance_schema = OFF

    [mysqldump]
    max_allowed_packet = 1G
    quote_names
    quick
    第 3 条附言  ·  2023-11-13 18:15:51 +08:00
    max_connections 根据业务并发情况进行调整,设置过大会提前占用更多内存。
    第 4 条附言  ·  2023-11-13 20:38:28 +08:00
    MYSQL 启用大页面支持,huge page 能给 MySQL 带来性能提升。
    my.cnf [mysqld] 添加 large_pages = 1
    不同的系统也需在相应的设置;
    第 5 条附言  ·  2023-11-14 20:27:41 +08:00
    innodb_buffer_pool_instances = 48
    计算公式:
    mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
    11 条回复    2023-12-22 21:31:22 +08:00
    atonganan
        1
    atonganan  
       2023-11-13 13:55:22 +08:00
    max_allowed_packet = 512M
    不够用把 。
    ab
        2
    ab  
       2023-11-13 14:13:11 +08:00
    搭车问一下,如有重复项,是前置的优先,还是前置的?
    atonganan
        3
    atonganan  
       2023-11-13 14:23:05 +08:00
    @ab 同一区域的重复项,最后一个生效
    ab
        4
    ab  
       2023-11-13 14:30:12 +08:00
    @atonganan 谢谢
    Aluhao
        5
    Aluhao  
    OP
       2023-11-13 16:16:31 +08:00
    @atonganan
    max_allowed_packet = 512M
    设置过大会不会引发什么问题,我看文档有建议设置成 1G
    SunsetYe
        6
    SunsetYe  
       2023-11-14 16:51:14 +08:00
    迁移到 AWS 的话可以考虑直接用 RDS ?
    Aluhao
        7
    Aluhao  
    OP
       2023-11-14 20:26:58 +08:00
    @SunsetYe RDS 太贵了
    MoMMM
        8
    MoMMM  
       2023-11-15 11:20:26 +08:00
    请教下新的配置中,注释掉诸如 join_buffer_size 等一系列相关的 buffer_size 的考虑是什么?
    Aluhao
        9
    Aluhao  
    OP
       2023-11-16 10:10:47 +08:00   ❤️ 1
    @MoMMM 缓冲区设置较大可能会触发将这些缓冲区分页到磁盘,会极大地减慢数据库的速度并造成瓶颈。
    Aluhao
        10
    Aluhao  
    OP
       2023-11-16 10:12:07 +08:00
    @MoMMM MySQL 的文档警告说:较大的值可能会显着减慢内存分配速度。
    E1n
        11
    E1n  
       2023-12-22 21:31:22 +08:00
    max_allowed_packet = 512M 合理吗默认数值是 1G 吧,Mysql InnoDB Cluster 方案要求为 2G ,现在生产是 2G:)
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1034 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 19:42 · PVG 03:42 · LAX 11:42 · JFK 14:42
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.