mysqlSystemConfigOptimization - juedaiyuer/researchNote GitHub Wiki

#mysql系统配置优化#

##操作系统配置优化##

数据库是基于操作系统的,对于操作系统的一些参数配置也会影响到MySQL的性能

CentOS

###参数优化###

Linux性能优化大师

内核相关参数

#网络方面的配置:/etc/sysctl.conf

net.core.somaxconn=65535 每个端口监听队列的长度
net.core.netdev_max_backlog=65535 
net.ipv4.tcp_max_syn_backlog=65535 增加tcp支持的队列数

#减少断开连接时,资源回收
net.ipv4.tcp_max_tw_buckets=8000
net.ipv4.tcp_fin_timeout=10 处理tcp连接的等待时间
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1

#tcp接受和发送缓冲区大小的默认值和最大值
net.core.wmem_default=87380
net.core.wmem_max=16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216


net.ipv4.tcp_keepalive_time=120
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_probes=3

#linux内核参数中最重要的参数之一,用于定义单个共享内存段的最大值
#参数设置的足够大,以便能在一个共享内存段下容纳下整个的Innodb缓冲池的大小
#这个值的大小对于64位linux系统,可取的最大值为物理内存值-1byte,建议值为大于物理内存的一半；一般取值大于Innodb缓冲池的大小即可

kernel.shmmax=4294967295


#内存不足时会对性能产生比较明显的影响
#Linux系统内存交换区
#free -m 中的swap就是交换分区
#mysql使用交换分区的争议:
#Mysql完全禁用交换分区---1.降低操作系统的性能；2.容易造成内存溢出,崩溃,或都被操作系统kill掉
#除非虚拟内存满,否则不要使用交换区

vm.swappiness=0

增加资源限制

打开文件数的限制,可以使用ulimit -a查看目录的各位限制

/etc/security/limits.conf

#Linux PAM,插入式认证模块的配置文件
#打开文件数的限制

*soft nofile 65535
*hard nofile 65535

# * 表示对所有用户有效
#soft 当前系统生效的设置
#hard 系统中所能设定的最大值
#nofile 所限制的资源是打开文件的最大数目
#保证可以打开足够多的文件句柄
#重启操作系统生效

最好的MySQL服务器上关闭iptables,selinux等防火墙
考虑到软件防火墙的消耗问题,硬件防火墙代替方案

磁盘性能优化

/sys/block/devname/queue/scheduler

#磁盘调度策略

echo <schedulername> /sys/block/devname/queue/scheduler

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

noop(电梯式调度策略),实现了一个FIFO队列,它像电梯的工作方法一样对IO请求进行组织,当有一个新的请求到来时,它将请求合并到最近请求之后,以此来保证请求同一介质.NOOP倾向于饿死读而有利于写,因此NOOP对于闪存,RAM,嵌入式系统是最好的选择

deadline(截止时间调度策略),确保了在一个截止时间内服务请求,这个截止时间是可调整的,而默认读期限短于写期限.这样就防止了写操作因为不能被读取而饿死的现象,Deadline对数据库应用是最好的选择

anticipatory(预料IO调度策略),本质于Deadline一样,但在最后一次读操作后,要等待6ms,才能继续进行对其它IO请求进行调度,它会在每6ms中插入一个新的IO操作,而会将一些小写入流合并成一个大写入流,用写入延迟换取最大的写入吞吐量.AS适合于写入较多的环境,比如文件服务器,AS对数据库环境表现较差

###文件系统对性能的影响###

window:fat,ntfs(***)

linux:ext3,ext4,xfs(***)

ext3/4系统挂载参数

/etc/fstab

#日志策略
data=writeback | ordered | journal

noatiome,nodiratime

/dev/sda1/ext4 noatime,nodiratime,data=writeback 11

##mysql本身性能影响##

MySQL配置文件,可以通过启动时指定配置参数和使用配置文件两种方法进行配置.在大多数情况下在/etc/my.cnf或者/etc/mysql/my.cnf

#MySQL查找配置文件的顺序
#后面覆盖前面的配置文件
mysqld --verbose --help | grep -A 1 'Default options'

插件式存储引擎

mysql体系结构

frm后缀文件记录了表的结构

###MyISAM###

mysql版本<5.5默认存储引擎

MyISAM存储引擎表由MYD和MYI组成

  • 并发性与锁级别

  • 表损坏修复

  • 支持的索引类型

  • 支持数据压缩

    check table tb_name repair table tb_name

    #命令 myisampack

限制

版本<5.0默认表大小为4G

如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH

适用场景

  1. 非事务型应用,比如数据仓库,报表类应用还有大多数不涉及到财务的应用
  2. 可以对表进行压缩,对于只读报表这样的应用也很合适
  3. 空间类应用

###Innodb###

版本5.5默认存储引擎

使用表空间进行数据存储

innodb_file_per_table

ON:独立表空间---tablename.ibd
OFF:系统表空间---ibdataX


mysql>show variables like 'innodb_file_per_table';

mysql>set global innodb_file_per_table=off;

如何选择系统表空间和独立表空间

  1. 系统表空间无法简单的收缩文件大小
  2. 独立表空间可以通过optimize table命令收缩系统文件
  3. 系统表空间会产生IO瓶颈
  4. 独立表空间可以同时向多个文件刷新数据

建议

  • 对Inoodb使用独立表空间

表转移的步骤

把原来存在于系统表空间中的表转移到独立表空间的办法

  1. 使用mysqldump导出所有数据库表数据
  2. 停止MYSQL服务,修改参数,并删除Innodb相关文件
  3. 重启MYSQL服务,重建Innodb系统表空间
  4. 重新导入数据

系统表空间

  • Innodb数据字典信息
  • undo回滚段

特性

  • Innodb是一种事务性存储引擎

  • 完全支持事务的ACID特性

  • Redo log和Undo log

  • 支持行级锁

  • 行级锁可以最大程度的支持并发

  • 行级锁是由存储引擎层实现的

  • Inodb状态检测

    show variables like 'innodb_log_buffer_size';

    ib_logfile文件

    show variables like 'innodb_log_files_in_group';

    show engine innodb status 状态检测

锁

  • 锁的主要作用是管理共享资源的并发访问
  • 实现事务的隔离性

锁的类型

  1. 共享锁(读锁)
  2. 独占锁(写锁)

锁的粒度

  • 表级锁

  • 行级锁

    lock table tablename write; 表级锁

阻塞和死锁

innodb适合于大多数OLTP应用

###CSV存储引擎###

  1. 数据以文本方式存储在文件中
  2. .csv文件存储表内容
  3. .csm文件存储表的元数据如表状态和数据量
  4. .frm文件存储表结构信息

特点

  1. 以CSV格式进行数据存储

  2. 所有列必须都是不能为NULL的

  3. 不支持索引,不适合大表,不适合在线处理

  4. 可以对数据文件直接编辑

    1,"aaa","bbb" 2,"ccc","ddd"

    mysql> create table mycsv(id int,c1 varchar(10),c2 char(10)) engine=csv; ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

    mysql> create table mycsv(id int not null,c1 varchar(10) not null,c2 char(10) not null) engine=csv;

    #表刷新操作,重新读取表 mysql> flush tables;

    #不支持建立索引 mysql> create index idx_id on mycsv(id); ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

适用场景

  1. 适合做为数据交换的中间表
  2. 电子表格--->CSV文件--->MySQL数据目录
  3. 数据--->CSV文件--->其它web程序

###Archive引擎###

文件系统存储特点

  1. 以zlib对表数据进行压缩,磁盘IO更少
  2. 数据存储在ARZ为后缀的文件中

特点

  1. 只支持insert和select操作

  2. 只允许在自增ID列上加索引

    mysql> create table myarchive(id int auto_increment not null,c1 varchar(20),c2 char(10), key(id)) engine=archive;

    #文件系统中的存储

    -rw-r----- 1 mysql mysql 8696 Jun 20 18:11 myarchive.ARZ -rw-r----- 1 mysql mysql 8608 Jun 20 18:11 myarchive.frm

    mysql> insert into myarchive(c1,c2) values('aa','bb'),('cc','dd');

    #不支持删除delete操作 mysql> delete from myarchive where id=1; ERROR 1031 (HY000): Table storage engine for 'myarchive' doesn't have this option

    #不支持更新update操作 mysql> update myarchive set c1='aaa' where id=1; ERROR 1031 (HY000): Table storage engine for 'myarchive' doesn't have this option

使用场景

  1. 日志和数据采集类应用

###Memory存储引擎###

文件系统存储特点

也称之为HEAP存储引擎,所以数据保存在内存中

mysql重启之后,表数据会丢失,表结构存在

功能特点

  1. 支持HASH索引和BTree索引

  2. 等值查找---HASH索引

  3. 范围查找---BTree索引

  4. 所有字段都为固定长度 varchar(10)=char(10)

  5. 不支持BLOG和TEXT等大字段

  6. 使用表级锁

  7. 表的最大大小由max_heap_table_size参数决定,修改后对已存在的表无效,需要重建

    mysql> create table mymemory(id int,c1 varchar(10),c2 char(10),c3 text) engine=memory; ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns

    mysql> create table mymemory(id int,c1 varchar(10),c2 char(10)) engine=memory;

    #文件系统存储 -rw-r----- 1 mysql mysql 8608 Jun 20 18:33 mymemory.frm

    #建立索引 mysql> create index idx_c1 on mymemory(c1); mysql> create index idx_c2 using btree on mymemory(c2);

    mysql> show index from mymemory\G;

    mysql> show table status like 'mymemory'\G;

使用场景

  1. 用于查找或者是映射表,例如邮编和地区的对应表
  2. 用于保存数据分析中产生的中间表
  3. 用于缓存周期性聚合数据的结果表

memory数据易丢失,所以要求数据可再生

###容易混淆的概念###

临时表

系统使用临时表

  1. 超过限制使用Myisam临时表
  2. 未超限制使用memory表

create temporary table 建立的临时表

###Federated存储引擎###

特点

  1. 提供了访问远程mysql服务器上表的方法
  2. 本地不存储数据,数据全部放到远程服务器上
  3. 本地需要保存表结构和远程服务器的连接信息

使用

  1. 默认禁止,启用需要在启动时增加federated参数

    mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name

    mysql> show engines\G;

    my.cnf federated=1

如何选择正确的存储引擎参考条件

  1. 事务
  2. 备份
  3. 崩溃恢复
  4. 存储引擎的特性

建议:不要混合使用存储引擎

##Mysql配置##

命令行参数

mysqld_safe --datadir=/data/sql_data

配置文件 /etc/my.cnf (centos) /etc/mysql/my.cnf(Debian)

#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 /data/mysqld/etc/my.cnf ~/.my.cnf

全局参数

set global 参数名=参数值;
set @@global.参数名:=参数值;

会话参数

set [session] 参数名=参数值;
set @@session.参数名:=参数值;

内存配置相关参数

#确定MySQL的每个连接使用的内存
#为每一个线程所分配,当有很多连接的时候,占据内存几倍的数量
sort_buffer_size
join_buffer_size
read_buffer_size(n*4k)
read_rnd_buffer_size 索引

#确定需要为操作系统保留多少内存

#非常重要的一个参数,只有innodb表,推荐配置为总内存的75%
#如何为缓存池分配内存
#影响了innodb的性能,延迟写入的功能
#总内存-(每个线程所需要的内存*连接数)-系统保留内存
Innodb_buffer_pool_size

#myisam引擎的配置
key_buffer_size

#myisam表索引所占用的空间大小
select sum(index_lengt) from information_schema.tables where engine='myisam'

#mysql的系统表还在使用myisam存储引擎

#MySQL5.5中新增加参数,可以控制缓冲池的个数,默认情况下只有一个缓冲池
#增加查询的并发性
innodb_buffer_pool_instances

#数据库Total MB
SELECT ENGINE,
ROUND(SUM(data_length+index_length)/1024/1024,1) AS "Total MB",
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema not in
("information_schema","performance_schema")
GROUP BY ENGINE;

IO相关配置参数

#innodb读写的IO进程数,默认为4；读写负载的实际情况调整
innodb_read_io_threads
innodb_write_io_threads

innodb_log_file_size
innodb_log_files_in_group

#32M~128M
innodb_log_buffer_size 事务日志缓冲区大小

#0:每秒进行一次log写入cache,并flush log到磁盘
#1[默认]:在每次事务提交执行log写入cache,并flush log到磁盘,如果数据安全性要求比较高则使用该选项
#2[建议]:每次事务提交,执行log数据写入到cache,每秒执行一次flush log到磁盘
innodb_flush_log_at_trx_commit 

innodb_flush_method=O_DIRECT

#默认为OFF,所有表都会建立在共享表空间
#建立单独表空间(强烈建议)
#无法收缩
innodb_file_per_table=1 

innodb_doublewrite=1 双写缓存

#什么情况下会刷新innodb表的统计信息
innodb_stats_on_metadata

事务日志总大小=innodb_log_files_in_group*innodb_log_file_size

选择合适的innodb_log_file_size

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

安全配置

expire_logs_days 指定自动清理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_data
	no_zero_in_date
	only_full_group_by

其它配置

sync_binlog 控制Mysql如何向磁盘刷新binlog

#一起使用的两个参数
#控制内存临时表大小
tmp_table_size
max_heap_table_size

max_connections 控制允许的最大连接数

数据库设计对性能的影响

  1. 过分的反范式化为表建立太多的列
  2. 过分的范式化造成太多的表关联
  3. 在OLTP环境中使用不恰当的分区表
  4. 使用外键保证数据的完整性

性能优化顺序

  1. 数据库结构设计和SQL语句
  2. 数据库存储引擎的选择和参数配置
  3. 系统选择及优化
  4. 硬件升级

##配置第三方工具##