MySQL 优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

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

 

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
25天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
29天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
64 3
|
1月前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
64 1
|
2月前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
91 9
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
185 1
|
2月前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
66 5
|
2月前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
93 1
|
2月前
|
缓存 关系型数据库 MySQL
一文彻底弄懂MySQL优化之深度分页
【10月更文挑战第24天】本文深入探讨了 MySQL 深度分页的原理、常见问题及优化策略。首先解释了深度分页的概念及其带来的性能和资源问题。接着介绍了基于偏移量(OFFSET)和限制(LIMIT)以及基于游标的分页方法,并分析了它们的优缺点。最后,提出了多种优化策略,包括合理创建索引、优化查询语句和使用数据缓存,帮助提升分页查询的性能和系统稳定性。
151 1
|
2月前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万数据量的优化实录
【10月更文挑战第6天】 在现代互联网应用中,随着用户量的增加和业务逻辑的复杂化,数据量级迅速增长,这对后端数据库系统提出了严峻的挑战。尤其是当数据量达到百万级别时,传统的数据库解决方案往往会遇到性能瓶颈。本文将分享一次使用MySQL与Redis协同优化大规模数据统计的实战经验。
141 3
|
2月前
|
NoSQL 关系型数据库 BI
记录一次MySQL+Redis实现优化百万数据统计的方式
【10月更文挑战第13天】 在处理百万级数据的统计时,传统的单体数据库往往力不从心,这时结合使用MySQL和Redis可以显著提升性能。以下是一次实际优化案例的详细记录。
145 1