ansible 一些基本使用方法的记录

造成性能问题的原因

• 大量 QPS(每秒处理的查询量) 和 TPS(每秒处理的事务量)
• 大量并发(同一时间的请求的数量)和高CPU使用率
• 磁盘IO是最主要的原因,要使用更快的磁盘设备,不要在主库进行数据备份
• 网卡IO激增会使用户无法连接数据库,应该减少服务器的数量,建立分级缓存,做好网络规划,避免使用select *
• 大表问题: 记录超过千万行或表数据超过10G, 引起慢查询,引起DDL操作延迟甚至锁表,可以用分库分表或归档历史数据的方式解决
• 大事务问题: 运行时间比较长,操作的数据比较多的事务, 锁定太多的数据,造成大量的阻塞和锁超时,回滚时所需时间比较大,容易造成主从延迟

数据库结构设计

范式与反范式

范式:

• 1NF: 数据库表中的所有字段都只具有单一属性, 每个属性都不可再分
• 2NF: 要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言), 在 1NF 的基础上消除非主属性对于码的部分函数依赖
• 3NF: 确保数据表中的每一列数据都和主键直接相关，而不能间接相关

反范式

范式化的数据表设计,可以有效减少冗余,节省磁盘空间,但在实际应用中发现,如果严格按照范式设计表不利于数据库的读性能,而对大多数应用而言,都需要高效的读取数据.

范式化的设计在进行查询时要对多个表进行关联,且因为字段分布在不同的表中,也不能进行索引优化,所以数据表设计的难点是如果平衡处理空间和时间的矛盾,如果正确的进行反范式化设计以提高数据库的读性能

选择合适的存储引擎

存储引擎	事务	锁粒度	主要应用	忌用	其他
MyISAM	不支持	支持并发插入的表级锁	非事务型应用,只读类应用	读写操作频繁	支持数据压缩(myisampack)
Innodb	支持	行级锁	事务处理	无	建议开启独立表空间(innodb_file_per_table=1)
Archive	不支持	行级锁	日志记录,只支持insert, select	需要随机读取,更新,删除	只支持在自增ID上增加索引
MRG_MYISAM	不支持	表级锁	分段归档,数据仓库	全局查找过多的场景
csv	不支持	表级锁	做为数据交换的中间表	大多数应用	数据以cs文本格式存储在文件中,所有列不能为 NULL, 不支持索引
memory	不支持	表级锁	用于查找或者是映射表,比如右边和地区的对应表,用于保存数据分析中的中间表,用于缓存周期性聚合数据的结果表	大多数应用	注意表中的数据丢失后无法恢复,所以所存的数据必须是可再次生成的

为字段选择合适的数据类型

当一个列可以选择多种数据类型时,应该优先选择数字类型,其次是日期或二进制类型,最后考虑字符类型.对于相级别的数据类型,应该优先选择占用空间小的数据类型

列出一些注意点:

• 整数类型根据取值范围合理的选择数据类型,可以节省很大空间,每种整数类型的存储空间都不同
• 实数类型要注意是否需要非常精确,float和double不精确,decimal精确
• varchar只占用必要的存储空间,长度小于255,额外占用一个字节存长度,大于255,额外占用两个字节存长度,应使用符合需求的最小长度, 适用于最大长度比平均长度大很多的场景,且该字段很少被更新,使用了多字节字符集存储字符串
• char 类型是定长的,会删除末尾的空格,最大宽度为255, 适合存储所有长度近似的值
• 绝不使用字符串存储日期时间
• datetime类型与时区无关,占用8个字节, timestamp 类型依赖所指定的时区,占用4个字节, 用int存时间戳不如用timestamp
• timestamp在行数据修改时可以自动修改为当前时间
• date类型只占用3个字节,比字符串,datetime,int都小,且可以利用日期时间函数进行日期间的计算

整型数据大小和范围
整型 | 大小 | 范围（无符号 unsigned）
—|— | —
TINYINT | 1 字节 | (0,255)
SMALLINT | 2 字节 | (0,65535)
MEDIUMINT | 3 字节 | (0,16777215)
INT/INTEGER | 4 字节 | (0,4294967295)
BIGINT | 8 字节 | (0,18446744073709551615)

索引优化

sql语句优化

找到需要优化的sql语句

sql语句优化技巧

MySQL服务器参数优化

/* 内存配置相关参数 详细说明 http://waterandair.top/mysql-memory-optimization.html */
# MySQL每个连接都要分配的内存
sort_buffer_size  # 连接进行排序时候分配该配置参数大小的内存进行排序操作，比如设置为100M，如果有100个连接同时进行排序将分配10G的内存，很容易造成服务器内存溢出；
join_buffer_size  # 定义mysql的每个线程所使用连接的缓冲区的大小，注意，如果一个查询中关联了多张表，那么就会为每个关联分配一个连接缓存，所以每个查询可能会有多个连接缓冲；
read_buffer_size  # 对MyISAM表进行全表扫描时分配的读缓存池的大小，mysql只会在有查询需要时为该缓存分配内存，分配的内存为配置参数指定内存的大小，大小一般为4K的倍数；
read_rnd_buffer_size  # 索引缓冲区的大小，有查询需要时才分配内存，分配的大小为需要内存的大小，而不是配置参数的大小

# 为 Innodb 缓存池分配内存(非常重要,用于缓存索引,数据,延迟写入等等,需要足够大) 
Innodb_buffer_pool_size
# 这个参数的值可以用这个公式计算: 总内存 - (每个线程所需要的内存*连接数) - 系统保留内存


/* Innodb IO相关配置 */
# 事务日志是循环使用的,使用完一个再写下一个,多个事务日志文件并不能起到并发写入的作用
innodb_log_file_size  # 控制单个事务日志的大小
innodb_log_files_in_group  # 控制事务日志文件的个数
innodb_log_buffer_size  # 控制事务日志缓冲区的大小,不需要很大, 32m-128m
innodb_flush_log_at_trx_commit  # 刷新事务日志的频繁程度,
                                # 0: 每秒进行一次log写入cache, 并flush log 到磁盘(可能会丢失这一秒的log)
                                # 1[默认]: 在每次事务提交执行log写入cache,并flush log到磁盘(最安全)
                                # 2[建议]: 每次事务提交,执行log数据写入cache, 每秒执行一次flush log到磁盘
innodb_flush_method=O_DIRECT
innodb_file_per_table=1  # 启用独立表空间
innodb_doublewrite=1  # 启用双写缓冲

/* myisam IO相关配置 */
delay_key_write  # OFF(最安全): 每次写操作后刷新缓冲中的脏块到磁盘
                 # ON: 只对在建表时指定了 dela_key_write 选项的表使用延迟刷新
                 # ALL: 对所有 myisam 表都使用延迟键写入

/* 安全相关配置 */
exprire_logs_days  # 指定自动清理binlog的天数
max_allowed_packet  # 控制MySQL可以接收包的大小,可以小一点 32M, 如果做了主从复制,那么主从这个配置必须一致
skip_name_resolve  # 禁用 DNS 查找
sysdate_is_now  # 确保 sysdate()返回确定性的日期
read_only  # 禁止非super权限的用户写权限,在从库中启用
skip_slave_start  # 禁止 slave 自动恢复
sql_mode  # 设置MySQL使用的sql模式,mysql默认的模式比较宽松,如果发现同样一行sql在有的mysql服务器可以运行,有的却不可以,首先考虑是 sql_mode 设置的问题

/* 其它常用配置 */
sync_binlog  # 控制 MySQL 如何向磁盘刷新 binlog, 主库建议设置为1
tmp_table_size 和 max_heap_table_size  # 控制内存临时表大小,两个值应该相同
max_connections  # 控制允许的最大连接数,默认100,要配置高一点, 2000

服务器系统参数优化

内核相关参数 (/etc/sysctl.conf)

/* 增加系统可接受请求的数量 */
# 系统中每一个端口最大的监听队列的长度，这是个全局的参数。
net.core.somaxconn=65535 
# 在每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目。
net.core.netdev_max_backlog=65535
# 对于还未获得对方确认的连接请求，可保存在队列中的最大数目。如果服务器经常出现过载，可以尝试增加这个数字。
net.ipv4.tcp_max_syn_backlog=65535

/* 加快回收 TCP 连接 */
# 对于本端断开的socket连接，TCP保持在FIN-WAIT-2状态的时间（秒）。对方可能会断开连接或一直不结束连接或不可预料的进程死亡。
net.ipv4.tcp_fin_timeout=10
# 表示是否允许将处于TIME-WAIT状态的socket（TIME-WAIT的端口）用于新的TCP连接 。
net.ipv4.tcp_tw_reuse=1
# 能够更快地回收TIME-WAIT套接字。
net.ipv4.tcp_tw_recycle=1

/* TCP 连接接收和发送数据缓冲区大小默认值的最小值和最大值,尽量调大*/
# 默认的TCP数据发送窗口大小（字节）。
net.core.wmem_default=87380
# 最大的TCP数据发送窗口（字节）。
net.core.wmem_max=16777216
# 默认的TCP数据接收窗口大小（字节）。
net.core.rmem_default=87380
# 最大的TCP数据接收窗口（字节）
net.core.rmem_max=16777216

/* 减少失效连接占用系统资源,加快资源回收的效率, 调小一些 */
# TCP发送keepalive探测消息的间隔时间（秒），用于确认TCP连接是否有效。
net.ipv4.tcp_keepalive_time=120
# 探测消息未获得响应时，重发该消息的间隔时间（秒）。
net.ipv4.tcp_keepalive_intvl=30
# 在认定TCP连接失效之前，最多发送多少个keepalive探测消息。
net.ipv4.tcp_keepalive_probes=3

/* 内存相关 */
# linux 中最重要的参数之一,用于定义单个共享内存段的最大值,这个参数应该设置的足够大,以便能在一个共享内存段下容纳下整个Innodb缓冲池的大小,可以取物理内存-1byte
ketnel.shmmax=4294967295

增加资源限制 (/etc/security/limit.conf)

增加可打开文件数(文件描述符)的限制, 关于文件描述符的详细介绍

* - nofile 65535

磁盘调度策略 (/sys/block/devname/queue/scheduler)

查看: cat /sys/block/sda/queue/scheduler, 默认为 cfq

修改为deadline策略: echo deadline > /sys/block/sda/queue/scheduler

磁盘调度策略介绍

noop (电梯式调度策略)

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

deadline (截止时间调度策略, 数据库类应用最好的选择)

deadline 确保了在一个截止时间内服务请求,这个截止时间是可调整的,而默认读期限短于写期限,这样就防止了写操作因为不能被读取而饿死的现象.

分布式架构优化

MySQL 复制

MySQL复制原理及实现

表分区

MySql 表分区介绍

服务器硬件优化

选择 CPU

在选择CPU前,要明确程序是计算密集型还是IO密集型,如果是计算密集型,就要选择频率更高的CPU,如果是IO密集型,就要选择核心数量更多的CPU.因为MySQL不支持对同一 sql 的并发处理, 所以多核的 CPU 对于 sql 执行速度并没有帮助.但是MySQL的应用一般会要求并发量要高,CPU核心数越多,并发能力就越强 ,所以在资金有限的情况下,优先选择核心数量更多的CPU

选择内存

把数据缓存到内存中可以提高 MySQL 的性能, 常用的 MySQL 引擎中, MyISAM将索引缓存到内存中,数据通过操作系统进行缓存;InnoDB 中会同时在内存中缓存索引和数据.选择内存时,应该选择主板支持额最大内存频率,尽量购买相同品牌,颗粒,频率,电压,校验技术和型号的内存,尽可能买大内存

配置和选择磁盘

使用传统机器硬盘

最常见,使用最多,价格低,存储空间大,读写慢

选择传统硬盘主要考虑以下几个因素:

1. 存储容量
2. 传输速度
3. 访问时间
4. 主轴转速
5. 物理尺寸(同等条件下,尺寸越小越好)

传统硬盘读取过程(2用的时间称为访问时间,3的过程称为传输速度):

1. 移动磁头到磁盘表面上正确的位置
2. 等待磁盘旋转,使所需的数据在磁头之下
3. 等待磁盘旋转过去,所有所需的数据都被磁头读出

使用 RAID 增强传统机硬盘的性能

RAID, 是磁盘冗余队列的简称,RAID的作用就是可以把多个容量较小的磁盘组成一组容量更大的磁盘,并提供数据冗余来保证数据完成性

RAID 级别:

• RAID0 :又称数据条带,是最简单的一种形式,只需要2块以上的硬盘,成本最低,但没有冗余和错误修复能力
• RAID1: 又称磁盘镜像,在写入一块磁盘的同时,会在另一块闲置的磁盘生成镜像文件,在不影响性能的前提下最大限度的保证系统的可靠性和可修复性
• RAID5: 又称分布式奇偶校验磁盘阵列,通过分布式奇偶校验块,把数据分散到多个磁盘上,这样如果一个盘数据失效,都可以从奇偶校验块中重建.适合用以读操作为主的场景.最好用在从服务器上.
• RAID10(最常用): 由称分片的镜像,对磁盘先做 RAID1 之后对两组 RAID1 的磁盘再做 RAID0,所以读写都有良好的性能,相对于 RAID5 更简单,速度也更快

等级 | 特点 | 是否冗余 | 盘数 | 读 | 写
—|— | — | — | — | — | —
RAID0 | 便宜, 快速,危险| 否 | N | 快 | 快
RAID1 | 高速读,简单,安全| 是 | 2 | 快 | 慢
RAID5 | 安全,成本折中| 是 | N + 1 | 快 | 取决于最慢的盘
RAID10 | 贵,高速,安全| 有 | 2N | 快 | 快

使用固态硬盘 SSD 和 PCIe 卡

相比机械硬盘,固态硬盘有更好的随机读写性能,能更好的支持并发,但更容易损坏,适用于存在大量随机 I/O 的场景, 用于解决单线程负载的 IO 瓶颈

类别	特点
SSD	使用 SATA 接口,可以替换传统磁盘而不需任何改变,且支持 RAID 技术
PCIe卡	无法使用 SATA 接口,需要独特的驱动和配置,价格更贵,性能更好