MySQL-DB参数、内存、I/O、安全等相关参数设置

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL-DB参数、内存、I/O、安全等相关参数设置

20200129003012618.png

生猛干货

带你搞定MySQL实战,轻松对应海量业务处理及高并发需求,从容应对大场面试


官方文档

https://dev.mysql.com/doc/



20200131202811239.png

如果英文不好的话,可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html



20200131203226295.png


服务器参数介绍

MySQL获取信息配置领

1) 命令行

mysqld_safe --datadir=/xxx/xxxx


2) 配置文件

持久化的配置都要写到配置文件中.

MySQL读取的配置文件可以通过如下命令来查看

[root@artisan mysql]# mysqld --help --verbose |grep -A  1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
[root@artisan mysql]# 


首先从/etc/my.cnf 读取,逐步往后找,后面的覆盖前面的。


MySQL配置参数的作用域

  • 全局参数
set global 参数名=参数值;
set @@global.参数名 := 参数值;  --- 两种方式都可以 


  • 需要在MySQL客户端中执行
  • 会话参数
set [session]  参数名=参数值;
  set @@session.参数名 := 参数值;  --- 两种方式都可以


操作下看看

[root@artisan ~]# mysql -u root -p
Enter password: 
.....
mysql> # 这两个参数要一起设置,否则会取大的那个为准。
mysql> show variables where variable_name = 'wait_timeout' or variable_name = 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
| wait_timeout        | 28800 |
+---------------------+-------+
2 rows in set (0.00 sec)
mysql> set global wait_timeout=3600; set interactive_timeout=3600;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> 


  • 设置了global 参数,有些已经登录的会话,要退出重新登录才生效。

内存配置相关参数


确定可以使用的内存的上限 ,不要超过服务器的内存


32位的操作系统,能使用的不足4G,这个也需要注意


确定MySQL每个连接使用的内存

sort_buffer_size: 举个例子,如果配置的过大,比如100M, 这个时候如果有10个连接过来,那就要分配1G的内存。。。

join_buffer_size

read_buffer_size

read_rnd_buffer_size


这4个参数都是给每个线程分配的


如何为缓存池分配内存

Innodb_buffer_pool_size : 确保分配足够多的内存

key_buffer_size


需根据实际情况调整


I/O相关配置参数


这部分参数决定了MySQL如何同步缓冲池中的数据到缓存。


Innodb I/O 相关的配置


  • Innodb_log_file_size 单个事务文件的大
  • Innodb_log_files_in_group 事务日志文件的个数
  • Innodb_log_buffer_size 缓冲区的大小
  • 事务日志中大小 Innodb_log_file_size * Innodb_Log_files_in_group , 循环使用。 先写到缓冲区,Innodb_log_buffer_size 指定缓冲区的大小,不用设置太大(32M-128M就够了),默认1秒刷一次,然后再写到日志。
  • Innodb_flush_log_at_trx_commit : 刷日志的频率
  • 0: 每秒进行一次log写入cache,并flush log到磁盘
  • 1[默认]:在每次事务提交执行log写入cache,并flush log到磁盘
  • 2[建议]:在每次事务提交执行log写入cache,每秒执行一次flush log到磁盘
  • Innodb_flush_method = O_DIRECT (建议值)
  • Innodb_file_per_table = 1 (启用表空间)
  • Innodb_doublewrite = 1 (启用双写缓存)


MyISAM I/O 相关的配置


MySQL的系统表,使用了MyISAM存储引擎,所以也是需要了解的。

  • delay_key_write


  • OFF: 每次写操作刷新键缓冲中的脏块到磁盘
    ON: 只对在键表时指定了delay_key_write选项的表时使用延迟刷新
    ALL:对所有MyISAM表都使用延迟键写入



安全相关配置参数


expire_logs_days : 指定自动清理binlog的天数,如果开启了binlog,最好配置这个参数,防止磁盘被占满


max_allowed_packet : 控制MySQL可以接手的包的大小 , 主从节点最好一致,否则容易同步失败


skip_name_resolve: 禁用DNS查找


sysdate_is_now :确保sysdate()返回确定性日志


read_only: 禁止非super权限的用户写权限, 从节点建议开启。


skip_slave_start : 禁用slave自动恢复。 从节点建议开启


sql_mode :设置MySQL所使用的SQL模式


可选项: strict_trans_tables、 no_engine_subtitution、 no_zero_date 、no_zero_in_date、 only_full_group_by


其它常用配置参数


sync_binlog :控制MySQL如何向磁盘刷新binlog , 默认为0 。 建议设置为1

tmp_table_size 和 max_heap_table_size 控制内存临时表的大小 ,建议这两个值设置一样大,超过最大内存后,将转化为磁盘存储

max_connections : 控制允许的最大连接数,默认100, 建议2000 ,根据应用调整。


小结


说了这么多参数配置, 可不要指望修改一两个参数,性能就能有质的提升。

首先应该聚焦于 数据结构 和 SQL的优化,实在没有优化的空间了,再考虑这些。

优化的顺序

  1. 数据库结构设计和SQL语句,最容易看到成果
  2. 数据库存储引擎的选择和参数配置
  3. 系统优化
  4. 硬件升级

由上到下优化


搞定MySQL


https://artisan.blog.csdn.net/article/details/104116743?spm=1001.2014.3001.5502

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 缓存 监控
Docker容器性能调优的关键技巧,涵盖CPU、内存、网络及磁盘I/O的优化策略,结合实战案例,旨在帮助读者有效提升Docker容器的性能与稳定性。
本文介绍了Docker容器性能调优的关键技巧,涵盖CPU、内存、网络及磁盘I/O的优化策略,结合实战案例,旨在帮助读者有效提升Docker容器的性能与稳定性。
202 7
|
2月前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
3月前
|
传感器 人工智能 物联网
C 语言在计算机科学中尤其在硬件交互方面占据重要地位。本文探讨了 C 语言与硬件交互的主要方法,包括直接访问硬件寄存器、中断处理、I/O 端口操作、内存映射 I/O 和设备驱动程序开发
C 语言在计算机科学中尤其在硬件交互方面占据重要地位。本文探讨了 C 语言与硬件交互的主要方法,包括直接访问硬件寄存器、中断处理、I/O 端口操作、内存映射 I/O 和设备驱动程序开发,以及面临的挑战和未来趋势,旨在帮助读者深入了解并掌握这些关键技术。
85 6
|
3月前
|
Java
JVM内存参数
-Xmx[]:堆空间最大内存 -Xms[]:堆空间最小内存,一般设置成跟堆空间最大内存一样的 -Xmn[]:新生代的最大内存 -xx[use 垃圾回收器名称]:指定垃圾回收器 -xss:设置单个线程栈大小 一般设堆空间为最大可用物理地址的百分之80
|
3月前
|
关系型数据库 MySQL 数据库
【赵渝强老师】MySQL的参数文件
MySQL启动时会读取配置文件my.cnf来确定数据库文件位置及初始化参数。该文件分为Server和Client两部分,包含动态与静态参数。动态参数可在运行中通过命令修改,而静态参数需修改my.cnf并重启服务生效。文中还提供了相关代码示例和视频教程。
105 6
|
3月前
|
缓存 监控 关系型数据库
如何根据监控结果调整 MySQL 数据库的参数以提高性能?
【10月更文挑战第28天】根据MySQL数据库的监控结果来调整参数以提高性能,需要综合考虑多个方面的因素
120 1
|
3月前
|
关系型数据库 MySQL Linux
Linux系统如何设置自启动服务在MySQL数据库启动后执行?
【10月更文挑战第25天】Linux系统如何设置自启动服务在MySQL数据库启动后执行?
216 3
|
3月前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
288 2
|
3月前
|
关系型数据库 MySQL 数据库
MySQL事务隔离级别及默认隔离级别的设置
在数据库系统中,事务隔离级别是一个关键的概念,它决定了事务在并发执行时如何相互隔离。MySQL提供了四种事务隔离级别,每种级别都解决了不同的并发问题。本文将详细介绍这些隔离级别以及MySQL的默认隔离级别。
|
4月前
|
关系型数据库 MySQL 数据库连接
MySQL 表整行数据唯一性设置
MySQL 表整行数据唯一性设置
83 2