3分钟速读原著《高性能MySQL》(三)

第八章 优化服务器设置

一.MySQL配置的工作原理

1.查找配置文件

在类 UNIX 系统中，配置文件的位置一般在 /etc/my.conf 或者 /etc/mysql/my.conf 中

2.配置语法

配置项设置都使用小写，单词之间用下划线或横线隔开

3.配置文件示例

[mysqld]
#GENERAl
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock 
pid_file=/var/lib/mysql/mysql.pid
user=mysql
port = 3306 
default_storage_engine=InnoDB
#INNODB
innodb_buffer_pool_size=<value>
innodb_log_file_size=<value>
innodb_file_per_table=1
innodb_flush_method=0_DIRECT
#LOGGING
log_error=/var/lib/mysql/mysql-error.log
slow_query_log=/var/lib/mysql/mysql-slow.log
#OTHER
tmp_table_size=32M
max_heap_table_size=32M
max_connections=<value>
open_files_limit=65535
socket=/var/lib/mysql/mysql.sock
 
# Replication Master Server (default) 
# binary logging is required for replication 
log-bin=mysql-bin
 
# binary logging format - mixed recommended 
binlog_format=mixed

4.配置内存使用

• 配置内存的步骤
  • 确定可以使用的内存上限
  • 确定每个连接MySQL需要使用多少内存，例如排序缓冲和临时表
  • 确定操作系统需要多少内存才够用。包括同一台机器上其他程序使用的内存，如定时任务
  • 把剩下的内存全部给 MySQL 缓存，例如 INNODB 的缓存池，这样做很有意义

5.MySQL的并发配置

# 数据库命令行界面输入
show variables like '%max_connections%';

如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

• 合理的设置范围:对于mysql服务器最大连接数值的设置范围比较理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明mysql服务器最大连接上限值设置过高.

第九章 操作系统和硬件优化

一.限制MySQL性能的关键因素

• 服务器硬件
• 服务器系统
• 数据库存储引擎的选择
• 数据库参数配置
• 数据库结构设计和SQL语句

1.硬件选择

选择固态硬盘,传统机械硬盘就像是一个旋转的传送带,当我们每次访问磁盘的一个块时,磁臂就需要移动到正确的磁道上(这段时间就是为寻址时间),然后盘片就需旋转到正确的扇区上(这就是旋转时延).这套动作需要时间,这段时间就是我们在访问磁盘时会产生的时间.

2.系统选择

使用linux系统,相比之下比Windows系统减少了很多不必要的内存堆积

3.CPU选择

使用密集型CPU,处理核数越多越好

4.平衡内存和磁盘资源

从内存当中读取数据一定比使用I/O从磁盘中读取要快的多,所以能够将数据读取到内存当中进行处理,速度一定会更快,但是最大值是当磁盘内存数据量和运行内存数据量相等时,磁盘内存就失去意义了.所以需要平衡好这两者之间的关系

5.RAID的优化

RAID(Redundant Array of Independent Disk ):独立冗余磁盘阵列

• 5.1 作用
  • 提高传输速率
  • 通过数据校验提供容错功能
• 5.2 分类(RAID0-5)
  • RAID 0:是成本最低和性能最高的RAID配置（不考虑数据恢复的因素），因为RAID 0 没有冗余，建议只在不担心数据丢失的时候使用
  • RAID 1 在很多情况下提供很好的读性能，并且在不同的磁盘间冗余数据，所以有很好的冗余性。RAID1 在读上比RADI 0快一些。非常适合存放日志或者类似的工作，顺序写性能比较好通常也是只有两块磁盘又需要冗余的低端服务器的选择。
  • RAID 5:随机写是昂贵的，每次写需要在底层磁盘发生两次读和两次写以计算和存储校验位。顺序写，随机读，顺序读会好一些。所以RAID 5用于存放数据或者日志，最好是以读为主的业务。
  • RAID 10:对读写都有良好的扩展性。相对于5，重建简单快速。并且可以在软件层很好地实现。失去一块磁盘时，性能下降还是很明显的，因为条带可能成为瓶颈。（少了一块只能去一块盘读）
  • RAID 50:由条带化的RAID 5 组成。如果有很多盘的话，这可能是RAID 5 的经济性和RAID 10的高性能之间的一个折中。主要用处是存放庞大的数据集，例如数据仓库或非常庞大的OLTP系统。

6.DAS,SAN,NAS

• 6.1 介绍
  • DAS(Direct-attached Storage) 直连存储
  • NAS(Network Attached Storage) 网络附加存储——是一个网络上的文件系统
  • SAN(Storage Area Network) 存储区域网络——是一个网络上的磁盘
• 6.2 应用
  • NAS和SAN混合搭配的解决方案为大多数企业带来最大的灵活性和性能优势。服务器环境越是异构化，NAS就越是重要，因为它能无缝集成易购的服务器。而企业数据量越大，高效的SAN就越重要
  • NAS能简化对SAN的访问。事实上，NAS是SAN理想的网关，能帮助SAN提供的数据块以文件形式路由至适当的服务器。与此同时，SAN能通过减轻非关键数据的大容量存储负担，使NAS更为有效的工作。重要文件可以存储在本地的NAS设备上，不重要的可以卸到SAN中。
• 6.3 选择
  • 大块连续IO密集的环境：SAN
  • 高并发随机小块IO或共享访问文件的环境：NAS
  • CPU密集的环境：NAS
• 6.4 总结

DAS存储一般应用在中小企业，与计算机采用直连方式，SAN存储使用FC接口，供性能更佳的存储，NAS存储则通过以太网添加到计算机上，SAN和NAS的区别主要体现在操作系统在什么位置。

二.总结

• 存储方式选择SAN,虽然他比较贵,但是它将存储和服务器隔离，简化了存储管理，能够统一、集中的管理各种资源。
• 传统机械硬盘选择RAID 10,如果是固态硬盘SSD性价比更高

第十章 复制

一.复制的搭建步骤

• 1.配置主库和从库
• 2.创建复制的账号
• 3.创建主库一致性快照
• 4.根据主库的快照，建立从库
• 5.开启复制

二.MySQL复制的格式

1.Statement-based replication(SBR)

基于语句的复制，即master上执行的SQL语句原封不动的在slave上重放。该复制格式在MySQL 3.23即出现了。

• 优点:
  • 节省了binlog空间
  • 可用于审核,毕竟所有的DML语句都是直接记录在binlog中
• 缺点:
  • 很多函数在主从上执行的结果并不一致
  • DELETE和UPDATE操作，带了LIMIT子句，却没有带ORDER BY，可能导致主从执行的结果并不一致
  • 相对于基于行的复制，master上执行INSERT … SELECT操作需要更多的行锁
  • 自定义函数（UDF）必须确保执行的结果是确定的

2.Row-based replication(RBR)

基于行的复制，MySQL5.1引入的，相对于SBR，它记录的是DML操作涉及到的行。

• 优点
  • 安全。master上所有的变更都能复制到slave上
  • 在执行以下操作时，只需更少的行锁
• 缺点
  • 会产生大量的日志
  • 不会对二进制日志进行校验
  • 不推荐基于库级别的复制

3.MIXED

MIXED是上述两者的的结合，会根据执行的语句和涉及的存储引擎自动在这两种模式间切换。默认情况下，采用的是基于语句的复制模式，在遇到unsafe statements时，会切换为基于行的复制模式

4.总结

• 4.1 启用复制功能并不会增加服务器太多的开销，主要是开启binlog带来的开销，包括binlog文件的追加写操作开销，以及系统调用fsync带来的开销
• 4.2 MySQL的复制功能具有向后兼容性，因为较新版本的MySQL的binlog中会进入新的事件类型。所以，可以将较新版本的MySQL作为从库
• 4.3 MySQL复制是异步的
• 4.4 MySQL 5.6开始新增延迟复制功能，由master_delay参数来控制