MySQL安装部署前的准备

一、参数设置

RDS云实例参数设置

loose_innodb_parallel_read_threads  0        --需设置为0，0默认即可
sql_mode                                     --是否需要设置ONLY_FULL_GROUP_BY，云实例默认：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
wait_timeout                        86400    --默认不用改
interactive_timeout                 7200     --默认不用改
thread_cache_size                   250      --通过processlist进行确认
group_concat_max_len                102400   --group_concat的长度
lock_wait_timeout                   3600     --行锁等待，InnoDB transaction waits for a row lock before giving up
innodb_purge_threads                4        --执行清除操作的后台进程，提高值可让DML操作在不同table中同时执行，{LEAST(内存/1G，8)}
loose_thread_pool_enabled           ON       --是否开启thread pool
loose_thread_pool_oversubscribe     32       --允许一个组有多少个活动的线程
loose_thread_pool_size              8        --设置几个线程组
innodb_print_all_deadlocks          ON       --开启打印死锁
innodb_sync_array_size              16       --定义mutex/lock wait数组的大小
innodb_rollback_on_timeout          ON       --innodb默认只会回滚最后一个语句当遇到事务超时，如果指定了该参数为ON，则事务超时会停止并回滚整个事务。
max_heap_table_size                 4194304  --指定内部内存临时表的最大值，会选tmp_table_size和max_heap_table_size两个参数中较小的值作为设定值，可以通过Created_tmp_disk_tables、Created_tmp_tables进行判断        
tmp_table_size                      4194304  --指定内部内存临时表的最大值，会选tmp_table_size和max_heap_table_size两个参数中较小的值作为设定值
character_set_server                utf8mb4      
loose_optimizer_switch                       --加上'prefer_ordering_index=off'，防止"order by 字段 limit N"引起的执行计划不合理。
innodb_deadlock_detect              OFF      --用innodb_lock_wait_timeout来防止死锁更有效，用死锁检测可能会减慢查询
binlog_rows_query_log_events        ON       --设置记录原始SQL
transaction_isolation                        --设置隔离级别 READ-COMMITTED、REPEATABLE-READ
join_buffer_size                    2097152  
internal_tmp_mem_storage_engine     Memory   --需设置为memory，不然存在bug
innodb_adaptive_hash_index          OFF      --关闭哈希索引区

 自建实例参数设置

[client]
port  = 3306
socket=/data/mysql/mysql.sock

[mysql]
prompt=\\u@\\d \\r:\\m:\\s>
no-auto-rehash

[mysqld]
server-id = 1001
default-time-zone="+08:00"
##错误日志时间配置当地时间
log_timestamps='SYSTEM'
port = 3306
socket = /data/mysql/mysql.sock

#dir structure
basedir=/usr/local/mysql
datadir=/data/mysql/
tmpdir=/data/mysql/
slave_load_tmpdir=/data/mysql/
pid-file=/data/mysql/mysql.pid

##log配置
log-error=error.log
slow_query_log = on
slow_query_log_file=slow_query.log
long_query_time = 1
# 记录那些慢的OPTIMIZE TABLE, ANALYZE TABLE和ALTER TABLE语句
log_slow_admin_statements=1


#包含errors和warnings,过滤掉notes(不然会有大量连接断开的日志信息)
log_error_verbosity=2

##system配置
#read_only = on
#super_read_only = on
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
transaction_isolation = REPEATABLE-READ
character-set-server=utf8mb4
collation_server = utf8mb4_unicode_ci
max_connections = 5000
skip_name_resolve
max_connect_errors=500
innodb_file_per_table=1
#超时参数
innodb_lock_wait_timeout = 60
interactive_timeout=7200
wait_timeout=7200
connect_timeout=10
#DDL锁等待
lock_wait_timeout = 3600
innodb_print_all_deadlocks=1
#需设置为memory 不然存在bug
internal_tmp_mem_storage_engine=Memory

##线程池
#thread_handling = pool-of-threads 
thread_cache_size = 256

##binlog&GTID配置
log-bin=mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=on
##binlog记录原始SQL binlog
binlog_rows_query_log_events=on
expire_logs_days=60
#binlog_expire_logs_seconds = 5184000

##半同步复制方式 MASTER宕机时 AFTER_COMMIT slave会丢失数据;AFTER_SYNC会比MASTER多数据
#rpl_semi_sync_master_wait_point=AFTER_SYNC

##slave复制
log_slave_updates=1
binlog_checksum=NONE
master_info_repository = TABLE
relay_log_info_repository = TABLE
#并行复制参数
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
relay_log_recovery=ON
slave_preserve_commit_order=1
#主库设置这两个参数 WRITESET XXHASH64
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_tracking  = WRITESET
# 作为从库时生效,从库复制中如何有慢sql也将被记录
log_slow_slave_statements=1
#relay_log文件名，防止hostname变动导致报错
relay_log=relay-log

##Innodb buffer pool配置
innodb_buffer_pool_size = 22G
thread_stack = 524288
innodb_log_buffer_size = 16M
#单thread 内存配置
binlog_cache_size = 1M
sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
#table 配置
table_open_cache = 4000
table_definition_cache = 2000
table_open_cache_instances = 16
tmp_table_sieze = 8388608

##Innodb引擎配置
innodb_log_file_size = 1024M
innodb_flush_method = O_DIRECT
max_allowed_packet = 512M
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000

#performance_schema 监控
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

#MGR配置

[mysqldump]
quick
max_allowed_packet = 512M

[mysqld_safe]
internal_tmp_mem_storage_engine 参考：https://forums.percona.com/t/mysql-8-0-the-table-tmp-sql1-f519f-7-is-full/10767

如何判断innodb_buffer_pool_size是否够用

#查看值
mysqladmin -uroot -p --socket=/data/mysql/mysql.sock ext|grep -i innodb_buffer_pool

#计算命中率
InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

#参数值代表的意思
innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。

prompt命令可以在mysql提示符中显示当前用户、数据库、时间等信息

[mysql]
prompt=mysql(\u@\h:\d)>
default-character-set=utf8
\u:连接用户
\h:连接主机
\d:连接数据库
\r:\m:\s:显示当前时间

二、安装部署前，Linux层面的设置优化 

1.磁盘优化

  • 建议使用NVMe或PCIe接口的Flash设备，IOPS一般在4-10万次/秒，延迟在微妙（us）级别
  • 可以把binlog或redo log放在ssd上存写，数据文件放在PCIe flash闪存

2.内存管理

  • 关闭NUMA ，可以直接在/etc/grub.conf文件的kernel行的最后添加numa=off
  • 关闭swap，可以编辑/etc/sysctl.conf文件，将vm.swappiness参数的值修改为0 ，然后使用sysctl -p命令使更改生效。
  • 使用jemalloc作为MySQL的内存分配管理器，避免内存不释放

1）关闭NUMA

 2）关闭swap

/etc/sysctl.conf

vm.swappiness = 0

 3）使用jemalloc内存管理器

安装：
yum install jemalloc -y

yum安装后默认/usr/lib64/libjemalloc.so.1
修改my.cnf，在[mysqld_safe]下添加：
[mysqld_safe]
malloc-lib = /usr/lib64/libjemalloc.so.1

lsof -n |grep mysql |grep jemalloc

3.文件系统

  • 文件系统选择XFS
  • 文件系统的mount参数加上noatime、nobarrier
  • 调度策略设置为Deadline

1）XFS对大文件的读写更友好

2）noatime、nobarrier参数

     noatime： 使用noatime mount的话，文件系统在程序访问对应的文件或者文件夹时，不会更新对应的Access时间。

     可以通过stat来查看文件的三个时间：

[root@cprod10-0-106-101 ~]# stat datax.tar.gz
  File: ‘datax.tar.gz’
  Size: 853734462       Blocks: 1667464    IO Block: 4096   regular file
Device: fd01h/64769d    Inode: 1051965     Links: 1
Access: (0644/-rw-r--r--)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2024-02-27 08:55:20.076895627 +0800
Modify: 2022-10-24 11:52:20.000000000 +0800
Change: 2023-06-25 15:14:17.575980190 +0800
 Birth: -
 
其中Access时间指文件最后一次被读取的时间，Modify时间指文件的文本内容最后发生变化的时间，
Change时间指文件的inode（比如位置、用户属性、组属性等）最后发生变化的时间

     一般来说，文件都是读多写少的，而我们也很少关心某个文件最近什么时间被访问了。所以建议采用noatime，

     这样文件系统就不会记录Access时间，避免浪费资源。

     nobarrier：数据库服务器底层如果存储设备采用RAID卡或Flash卡，则我们可以安全地使用nobarrier挂载文件系统。因为它们都有自我保护机制，保证数据不会丢失。设置方法如下：

　　                 对于Ext3、Ext4和ReiserFS，可以在挂载时指定barrier=0；对于XFS，可以指定nobarrier选项。

  barrier（或称为写屏障）是一种磁盘I/O操作，用于确保在发生系统崩溃或电源故障时数据的完整性和一致性。具体来说，写屏障是一种指令，
它强制硬盘在继续执行其他操作之前完成之前的所有写操作。这有助于防止在发生错误时数据的不一致状态。
  然而，写屏障也会带来一些性能开销，因为它们会引入额外的磁盘I/O延迟。在某些情况下，特别是在使用SSD或某些类型的SATA硬盘时，这种开销可能是不必要的，
因为这些存储设备已经具有自己的数据完整性和一致性保障机制。

3）调度策略设置为Deadline，

CFQ (Completely Fair Queueing)
特点：CFQ是一种完全公平的队列调度策略，它为每个进程维护一个I/O请求队列，并按照时间片轮转的方式为每个队列提供服务。CFQ旨在确保所有进程都能公平地访问磁盘资源，
防止某些进程长时间占用磁盘而导致其他进程饥饿。
适用场景：适用于需要平衡多个进程I/O需求的场景，如桌面环境、多用户系统等。

Deadline
特点：Deadline调度策略为每个I/O请求分配一个截止时间，并根据截止时间的先后顺序进行调度。如果某个请求的截止时间即将到期而尚未得到服务，Deadline会优先处理该请求。
这种策略旨在减少I/O请求的等待时间，提高系统的响应速度。
适用场景：适用于对响应时间有严格要求的场景，如实时系统、数据库等。

Noop
特点：Noop（No Operation）调度策略实际上是一种非常简单的调度策略，它不对I/O请求进行任何排序或合并操作，而是直接按照请求到达的顺序进行处理。
Noop策略适用于那些已经具有自己内部调度机制的存储设备，如某些SSD固态硬盘。
适用场景：适用于具有内部调度机制的存储设备，如某些高性能的SSD固态硬盘。
设置IO调度策略：
1.查看当前I/O调度策略：
首先，你可以通过运行以下命令来查看当前系统支持的I/O调度策略和正在使用的策略：
cat /sys/block/sda/queue/scheduler
这里的sda是磁盘设备的名称，它可能是sda、sdb等，具体取决于你的系统配置。命令的输出将显示一个列表，当前策略会被方括号[]包围。

2.临时更改I/O调度策略：
你可以通过写入新的调度策略名称到/sys/block/sda/queue/scheduler来更改它。例如，要将策略更改为deadline，可以运行：
echo deadline > /sys/block/sda/queue/scheduler
请注意，这种更改是临时的，重启系统后将恢复到默认设置。

3.永久更改I/O调度策略：
若要使更改永久生效，你需要编辑GRUB配置文件并更新内核启动参数。通常，在CentOS 7及更早版本中，你需要编辑/etc/default/grub文件，
并在GRUB_CMDLINE_LINUX行中添加elevator=deadline（或你选择的任何其他调度策略）。但是，在CentOS 8及以后版本，由于使用了新的GRUB2配置方式，
你可能需要编辑/etc/sysconfig/grub文件或在/etc/default/grub中的GRUB_CMDLINE_LINUX里添加参数（如果该文件存在的话）。

在编辑完GRUB配置文件后，你需要运行grub2-mkconfig -o /boot/grub2/grub.cfg（CentOS 7及以后版本）或相应的命令来重新生成GRUB配置。

但是，请注意，从Linux内核3.14开始，elevator参数已被弃用，新的方法是使用mq-deadline、none、bfq等作为启动参数（取决于你想要设置的调度器）。
不过，在较新的内核版本中，你可能不再需要（或能够）通过内核参数设置I/O调度器，因为许多现代系统默认使用mq-deadline或none（对于SSD尤其如此），
并且不再支持在内核命令行中更改它。相反，你应该使用blk-mq框架提供的工具和接口来管理和监视I/O性能。

4.重启系统：
如果进行了永久更改，需要重启系统以使更改生效。

4.CPU

  • 选择频率高、核数多的CPU
  • 关闭节能模式

2）关闭节能模式

发现CPU显示的当前频率竟然跟它出厂标识的频率不一样，建议将CPU设置为最大性能模式。

[root@cprod10-0-106-101 queue]# cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 85
model name      : Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
stepping        : 4
microcode       : 0x1
cpu MHz         : 1294.150
cache size      : 33792 KB
...
...

5.网络

  • 避免域名反解析，设置skip_name_resolve=on
  • 关闭iptables和SELinux
  • 网卡多队列避免CPU的IRQ瓶颈
  • 在短连接下降低TIME_WAIT Socket连接
  • 考虑调整sysctl.conf中的参数

1）设置skip_name_resolve=on避免域名解析

MySQL默认在内存中会维护Host缓存，保存IP地址和主机名的映射关系。

如果客户端连接使用的IP地址不在Host缓存中，MySQL需要反解析域名，导致客户端连接时间较长。

客户端授权用localhost或者IP地址表示，而不要使用DNS名称表示。

5）设置sysctl.conf的一些网络参数

net.core.rmem_default=16777216          #设置套接字接收缓冲区大小
net.core.wmem_default=16777216
net.core.rmem_max=16777216
net.core.wmem_max=16777216
net.ipv4.ip local port range=1024 65535
net.ipv4.ip_forward =0
net.ipv4.conf.default.rp filter=1
net.ipv4.conf.default.accept source route=0
net.ipv4.tcp syncookies=0
net.ipv4.tcp_rmem=40968738016777216      #设置tcp接收缓冲区大小
net.ipv4.tcp_wmem=4096 65536 16777216

6.进程资源限制

/etc/security/limits.conf

* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
* soft stack 65535
* hard stack 65535
mysql soft nofile 16384
mysql hard nofile 36384
在Linux中，stack size（栈大小）是一个重要的系统参数，它决定了每个线程可以使用的栈内存的大小。栈是用于存储局部变量、函数参数以及保存函数调用的上下文的区域。每个线程在创建时都会被分配一个栈，
该栈的大小就是在创建线程时指定的stack size。

栈大小的作用主要体现在以下几个方面：

资源管理：栈大小是系统资源管理的一部分，它限制了每个线程可以使用的内存量。这有助于防止单个线程消耗过多的内存资源，从而影响系统的稳定性和其他线程的运行。
防止栈溢出：如果线程的栈大小设置得过小，而线程在执行过程中需要使用的栈空间超过了分配的栈大小，就会发生栈溢出。栈溢出是一种严重的程序错误，它可能导致程序崩溃或产生不可预测的行为。
通过设置适当的栈大小，可以降低栈溢出的风险。
性能优化：栈大小的设置也可以影响程序的性能。如果栈大小设置得过大，会浪费内存资源；如果设置得过小，可能会导致频繁的栈溢出和程序崩溃。因此，需要根据程序的实际需求和系统的内存资源来合理设置栈大小。
在Linux中，可以通过多种方式查看和修改栈大小。例如，可以使用ulimit命令查看和修改当前shell会话的栈大小限制。另外，在创建线程时，也可以使用线程库提供的函数来指定线程的栈大小。
对于系统级别的栈大小设置，可以编辑/etc/security/limits.conf文件来配置。

需要注意的是，栈大小的设置应该根据程序的具体需求和系统的内存资源来进行合理的配置。过大的栈大小会浪费内存资源，而过小的栈大小可能导致程序崩溃或产生不可预测的行为。

Linux系统参数

nofile
nofile表示单个进程可以打开的最大文件句柄数（默认值，软限制：1024，硬限制：4096）
1）/proc/sys/fs/file-max表示整个系统内核可以分配的最大文件句柄数（默认值约为物理内存转换成kb的值/10），如需修改执行命令：echo ‘fs.file-max=104857600’ >> /etc/sysctl.conf && sysctl -q -p
2）/proc/sys/fs/file-nr表示整个系统内核的文件句柄统计数据（只读），包含三个值，1：整个系统内核已分配的文件句柄数，2：整个系统内核已分配但未使用的文件句柄数（一般情况下为0），3：整个系统内核可以分配的最大文件句柄数（等于file-max）
3）/proc/sys/fs/nr_open表示单个进程可以分配的最大文件句柄数（默认值为1048576），如需修改执行命令：echo ‘fs.nr_open=2097152’ >> /etc/sysctl.conf && sysctl -q -p
4）nofile的值不能超过nr_open的值，如果配置文件中nofile的硬限制的值超过nr_open的值将会导致无法登录系统
5）单个进程可以打开的最大文件句柄数由nofile和file-max同时约束，假如一个进程已打开的文件句柄数小于nofile，但整个系统内核所有进程打开的文件句柄数已经达到file-max，此时这个进程也不能再打开文件句柄了

nproc
1）nproc表示单个用户创建的进程数（默认值，软限制：threads-max/2，硬限制：threads-max/2），线程的实现其实是一个轻量级的进程，所以线程也算进程
2）/proc/sys/kernel/threads-max表示整个系统内核可以分配的最大线程数，如需修改执行命令：echo
‘kernel.threads-max=1048576’ >> /etc/sysctl.conf && sysctl -q -p
3）/proc/sys/kernel/pid_max表示整个系统内核可以分配的最大进程ID（默认值为32768，2个字节的最大值），也就是说，整个系统内核最多只能分配pid_max个进程或线程，如需修改执行命令：echo
‘kernel.pid_max=1048576’ >> /etc/sysctl.conf && sysctl -q -p
4）/proc/sys/vm/max_map_count表示单个进程可以分配的内存映射区域的最大数量（默认值为65530），由于java中每创建一个线程需要分配2个内存映射区域，并且jvm本身也要占用部分内存映射区域，
所以java程序理论上最多可以创建的线程数为略小于max_map_count/2（实际上还受jvm参数-Xms、-Xmx、-Xss以及操作系统物理内存的影响），如需修改执行命令：echo
‘vm.max_map_count=2097152’ >> /etc/sysctl.conf && sysctl -q -p
5）单个进程可以创建的线程数由nproc、threads-max、pid_max、max_map_count以及系统资源同时约束，达到其中一个的上限就不能再创建线程了
6）CentOS7中/etc/security/limits.d/20-nproc.conf（CentOS6是90-nproc.conf）会覆盖/etc/security/limits.conf中的nproc配置，前提条件是，(20|90)-nproc.conf和
limits.conf的domain相同或者(20|90)-nproc.conf比limits.conf的domain更具体，优先级从高到低分别为：
/etc/security/limits.d/(20|90)-nproc.conf中domain为具体用户的配置
/etc/security/limits.conf中domain为具体用户的配置
/etc/security/limits.d/(20|90)-nproc.conf中domain为通配符（）的配置
/etc/security/limits.conf中domain为通配符（）的配置

注意事项：
1）domain中具体用户比通配符（*）优先级高
2）软限制不能大于硬限制，如果配置文件中软限制设置的比硬限制高，则软限制会使用硬限制的值，通过ulimit命令设置软限制不能超过硬限制。root用户可以通过ulimit命令降低和提高硬限制
（nofile的值不能超过nr_open的值）。非root用户可以通过ulimit命令降低硬限制，但不可以通过ulimit命令提高硬限制。

7.MySQL添加批量插入参数

rewriteBatchedStatements参数设置，提高insert速度
https://blog.csdn.net/w1014074794/article/details/125858863

 

posted @ 2023-05-26 17:59  江曹  阅读(4)  评论(0)  编辑  收藏  举报