MySQL 优化-阿里云开发者社区

开发者社区> 数据库> 正文

MySQL 优化

简介:

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

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章