阿里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查看看idrac的IP地址

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

把多少片放到swap，0值代表不放进去

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 查看 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

2、sort_buffer_size

= 4M这个值适用于Innodb和MyISAM，是用于排序时一个连接上来占用的大小，一般设置为4M或者8M。

3、query_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

6、max_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 连接错误数量，比如因为网络出现错误，他会记录一个值，超过这个数会报错。

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

当sync_binlog

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

金融场景一般设置为1：sync_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上最少走了50个IO,就有可能没有用到索引。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