MySQL 优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

MySQL 注意事项与调优


硬件调优:

1        关闭“strike the f1 key to contime F2 to run the system setup program”的系统提示

2        关闭CPU节能模式

3        打开vt

不使用虚拟化可以不打开此参数

4        其他参数

5        iDRAC.IPMILan.Enable Enabled                       

开启通过ipmitool查看看idracIP地址

6        iDRAC.IPMISOL.Enable Enabled

通过服务器上的串口访问iDRAC

7        iDRAC.VNCServer.Enable Enabled

开启vnc

8        iDRAC.VNCServer.Password "calvin"

设置vnc的密码为“calvin”

9        BIOS.BiosBootSettings.BootMode Bios

 

10        BIOS.BiosBootSettings.BootSeq "HardDisk.List.1-1,NIC.Integrated.1-1-1"

启动顺序,第一顺序为硬盘启动,第二顺序为网卡启动

11        BIOS.MiscSettings.ErrPrompt Disabled

禁用错误提示

 

1.     关闭数据库服务器电源管理(BIOS)、节能模式

 

 

 

 

系统优化:

1[root@MySQL-01 ~]# sysctl -a|grep swap

      vm.swappiness = 0 

     把多少片放到swap0值代表不放进去

2、关闭swap

3、echo "deadline" > /sys/block/sda/queue/scheduler

改变系统的IO调度,deadline是最小延时调度,对多个任务加一起处理时间最短。

noop:先来服务

cfq:最公平

这里需要注意,通常的调度算法的行为都是合并请求,排序请求,这些行为是针对机械磁盘的特性来优化的,对于使用 SSD 硬盘的系统,由于没有了磁头寻道,磁片旋转定位等操作,对 SSD 硬盘使用通常的调度算法就变得没有意义,因此我们使用一种特殊的调度算法 NOOP(NO OPeration),即不对 IO 请求进行操作,直接按 FIFO 规则进行处理。

设置调度算法为 NOOP.

echo noop > /sys/block/sda/queue/scheduler

 

4、关闭CPU  numa架构

可以使用 numastat 或 numastat -p <PID> 查看 NUMA 结构 CPU 内存访问状态

1从 BIOS 关闭

2、在操作系统中关闭,在 /etc/grub/grub.conf 的 kernel 行追加 numa=off

3、修改 /etc/init.d/mysql 或者 mysqld_safe 脚本,这种方式较复杂,不便于管理,不推荐

4、启动 MySQL 的时候,关闭 NUMA 特性,使用 numactl --interleave=all mysqld &

最好的方法还是 1 和 2

 

 

 

5、文件的访问时间

查看/var目录的访问时间

[root@MySQL-01 /]# stat var

  File: `var'

  Size: 4096              Blocks: 8          IO Block: 4096   directory

Device: 802h/2050d        Inode: 781825      Links: 21

Access: (0755/drwxr-xr-x)  Uid: (    0/    root)   Gid: (    0/    root)

Access: 2015-11-26 00:08:53.865839614 +0800

Modify: 2015-08-25 06:03:37.811999686 +0800

Change: 2015-08-25 06:03:37.811999686 +0800

将其访问时间除掉

mount -o remount /dev/sda1

[root@MySQL-00 ~]# mount

/dev/sda1 on / type ext4 (rw,noatime,nodiratime,nobarrier)

proc on /proc type proc (rw)

sysfs on /sys type sysfs (rw)

devpts on /dev/pts type devpts (rw,gid=5,mode=620)

tmpfs on /dev/shm type tmpfs (rw)

只对针对MySQL数据盘进行修改即可

6、数据盘不建议放到/etc/fstab,放到/etc/rc.local,这样数据盘坏了,不会导致系统无法启动。

如果把数据盘的挂载放到/etc/rc.local 有相关的程序的写入也要放到这里面。

 

 

MySQL的优化:

1、更改字符集为UTF8

2sort_buffer_size = 4M  这个值适用于InnodbMyISAM,是用于排序时一个连接上来占用的大小,一般设置为4M或者8M

3query_cache_size= 0            query_cache_type= 0    查询缓存以及类型

4、expire_logs_days = 15 binlog的过期时间,一般设置为15天

5 interactive_timeout = 2800 、wait_timeout = 2800 java程序,php程序:interactive_timeout = 30、wait_timeout = 30

6max_connections 最大连接用户数,javat程序:max_connections = 8092,php程序:max_connections = 3000。与max_user_connections  每一个用户最大连接数,与max_connections相结合比如8092,那么max_user_connections 所有用户的值加起来,不能超时这max_connections这个值,最好留有5个连接。

7、max_connect_errors = 999999 连接错误数量,比如因为网络出现错误,他会记录一个值,超过这个数会报错。

8sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log

sync_binlog =N (N>0) MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

金融场景一般设置为1sync_binlog = 1,就是在机器突然掉电只丢失一个事务。

9、binlog_cache_size = 8M

#在事务过程中容纳二进制日志SQL语句的缓存大小,一般设置8m左右。

Innodb的优化:

1、innodb_buffer_pool_size    如果单机mysql,innodb 调整物理内存的80%

2、innodb引擎开启独立表空间。innodb_file_per_table = 1

3、default_storage_engine = innodb     缺省的最好也写在配置文件里

4、innodb_flush_log_at_trx_commit = 1   控制刷硬盘速度。

5、innodb_lock_wait_timeout = 50   事务锁,防止堵塞和性能损耗,默认50这个值比较合适

6、innodb_read_io_threads = 4                innodb_write_io_threads = 4       设置文件读写的速度,一般设4-8,和CPU的核数有关,如何有16核可以设为读8 写8

7、innodb_log_files_in_group = 2

对事务安全高,先写log在写数据。这个innodb默认值是2代表生成两个文件,先写log1满了之后写log2,log2满了之后在写log1。清空log1的任务交给系统来完成。如果是1 代表只有1个文件,但是当写满之后会产生比较大的性能开销。该值的默认值就是2个。

8

innodb_log_file_size = 1024M

innodb_log_buffer_size = 64M

#设置的大小,innodb_log_files_size最好设置1.3G,5.5以上的版本。5.5以下的版本最好设置512M以下

9

[mysqld]

max_allowed_packet = 64M

#主从同步,主库向从库同步的单个数据量。

10

[mysqld]

skip-name-resolve = 1

MySQL server端,选项就能禁用DNS解析,连接速度会快很多。不过,这样的话就不能在MySQL的授权表中使用主机名了而只能用ip格式。如果不禁用此选项MySQL写IP地址连接就很慢,server会去反解析。

 

 

 

 

MySQL安全:

1、不同业务不同的密码,为不同的业务创建不同的库。

安全

2、备份的数据也需要加密保证安全期,脱裤的原因的大部分原因是备份的数据。

3、 root必须要有密码,清理不必要用户和允许的连接地址

4、应用程序不能以root用户访问

5、数据库内网访问

6、数据库的网络隔离

7、授权能用IP就用IP,实在不行就用C段

8、代码防SQL注入

 

 

 

 

数据类型的设计:

1、实数是decimal  涉及的小数点的用这个类型存储,特别是金融的类型的。

2、char和vcahr在innodb没有区别,char设置的最大值超过后会有影响,建议直接使用vcahr。

3、BINARY和VARBINARY:建议直接使用VARBINARY

4、时间类型:

TIMESTAMP:建议直接使用TIMESTAMP,支持到2038年。

   1、TIMESTAMP 4个字节,DATETIME8个字节,TIMESTAMP性能要好

   2、有缺省值,不需要关心这些值的变化,就可以满足,什么时候插入的什么时候修改时间加上

 

 

 

版本升级:

版本升级后需要执行此命令把系统表检查一下

/usr/local/mysql/bin/mysql_upgrade -S /tmp/mysql.sock -ptestpassword

 

 

 

SSD硬盘:慢查询日志可以设置0.5秒,如果超过0.5秒。0.5秒在SSD上最少走了50IO,就有可能没有用到索引。0.5秒还是有点问题:如果从8000W中找一条记录,如果加上order 等计算耗时,比较小。

 

SSD线上参数设置

磁盘调度算法改为Deadline
echo deadline > /sys/block/sda/queue/scheduler  # deadline适用于数据库,HDD也建议改成Deadline

MySQL参数

innodb_log_file_size=4G 该参数设置的尽可能大

innodb_flush_neighbors=0
性能更平稳,且至少有15%的性能提升

innodb_io_capacity = 4000

innodb_io_capacity_max = 8000

innodb_page_size = 4096 /4K的大小,设置4k 缓存粒度更小

innodb_flush_neighbors = 0

 

 

 

MySQL 运行环境优化(Linux):  http://liaoph.com/mysql-optimize-in-linux/

Linux 性能优化之 IO 子系统: http://liaoph.com/linux-system-io/#io

 

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
11天前
|
SQL Java 关系型数据库
MYSQL--JDBC优化
MYSQL--JDBC优化
|
30天前
|
SQL 缓存 关系型数据库
下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
【5月更文挑战第20天】下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
39 3
|
1月前
|
SQL 存储 关系型数据库
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
|
7天前
|
SQL 缓存 关系型数据库
MySQL慢查询优化实践问答
MySQL慢查询优化实践问答
|
11天前
|
缓存 关系型数据库 MySQL
如何优化MySQL 8.0的性能?
【6月更文挑战第14天】如何优化MySQL 8.0的性能?
39 5
|
9天前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
13 2
|
13天前
|
缓存 监控 关系型数据库
mysql优化
【6月更文挑战第12天】mysql优化
21 3
|
17天前
|
SQL 关系型数据库 MySQL
MySQL大数据量分页查询方法及其优化
MySQL大数据量分页查询方法及其优化
|
18天前
|
存储 SQL 关系型数据库
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
228 1
|
24天前
|
SQL 关系型数据库 MySQL
mysql 故障排除与优化
mysql 故障排除与优化