本文详细介绍了MySQL数据库的配置文件加载顺序、隔离级别设置、逻辑架构、内存管理、日志文件配置、索引优化以及针对OLTP应用的性能调优策略，包括选择合适的CPU、内存配置、硬盘类型和RAID设置。
一.mysql查看配置文件加载顺序
mysql --help | grep my.cnf
加载顺序为：
/etc/my.cnf
/etc/mysql/my.cnf
~/.my.cnf
同一个参数多次出现，以最后一个为准
# mysql隔离级别设置，在配置文件增加下面内容
transaction-isolation = READ-UNCOMMITTED
transaction-isolation = READ-COMMITTED
transaction-isolation = REPEATABLE-READ
transaction-isolation = SERIALIZABLE
二.mysql查看datadir
show variables like 'datadir'
或者在配置文件中查看
三.mysql逻辑架构
四.mysql内存
1.缓冲池
InnoDb存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统。在数据库系统中，由于cpu速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。
缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将此磁盘读取到的页存放到缓冲池中，这个过程称为将页"FIX"在缓冲池中。下一次再读取相同的页时。首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。
对于数据库中页的修改操作，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为"Checkpoint"的机制刷新回磁盘。同样，这也是为了提高数据库的整体性能。
-- 查看缓冲池大小
show variables like 'innodb_buffer_pool_size'
-- 设置缓冲池大小(重启失效)
set GLOBAL innodb_buffer_pool_size = 2147483648
-- 永久修改 etc/mysql/mysql.conf.d/mysqld.cnf 中增加
innodb_buffer_pool_size = 2147483648
2.缓冲池中页的类型
索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDb存储的锁信息、数据字典信息等。其中数据页和索引页占大部分
3.多缓冲池实例
InnoDb可以允许有多个缓冲池实例。每个页根据哈希值平均分配到不通缓冲池实例中，这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。
-- 查看缓冲池实例数
show variables like 'innodb_buffer_pool_instances'
-- 永久修改 etc/mysql/mysql.conf.d/mysqld.cnf 中增加
innodb_buffer_pool_instances = 16
五.日志文件
1.错误日志(error log)
该文件不仅记录了所有的错误信息，也记录了一些警告信息
/etc/mysql/mysql.conf.d/mysqld.cnf 下配置
log-error = /var/log/mysql/error.log 开启错误日志
2.二进制日志(binlog)
二进制日子记录了对mysql数据库执行更改的所有操作，但是不包括select 和 show 这类操作
默认情况二进制日志并未记录，需要手动指定参数来启动，若开启二进制日志会使性能下降约1%
3.慢查询日志(slow query log)
默认慢查询是输出为file格式，可以通过 show variables like ‘log_output’ 来查看
在配置文件中
slow_query_log = ON 表示开启慢查询日志记录
slow_query_log_file = /var/log/mysql/slowQuery.log 这种慢查询日志位置
long_query_time = 2 表示超过2秒的查询被记录在慢查询中
lon_output = table 更改慢查询日志记录方式为table，可以在mysql库下的slow_log表中查看慢查询日志
4.查询日志(log)
记录查询的日志
六.索引相关
1.cardinality(基数)
表示索引列唯一值的数量，与总数的比值应该接近于1，说明索引建的好
由于不是实时统计，执行 analyze table 可以更新 cardinality
七.性能调优
1.选择合适的CPU
用户首先需要清楚当前数据库的应用类型。一般而言，可分为两大类：OLTP （Online Transaction Processing, 在线事务处理）和 OLAP （Online Analytical Processing,在线分析处理）。这是两种截然不同的数据库应用。OLAP多用在数据仓 库或数据集市中，一般需要执行复杂的SQL语句来进行査询；OLTP多用在日常的事 物处理应用中，如银行交易、在线商品交易、Blog、网络游戏等应用。相对于OLAP, 数据库的容量较小。
InnoDB存储引擎一般都应用于OLTP的数据库应用，这种应用的特点如下：
- 用户操作的并发量大
- 事务处理的时间一般比较短
- 查询的语句较为简单，一般都走索引
- 复杂的查询较少
可以看出，OLTP的数据库应用本身对CPU的要求并不是很高，因为复杂的春询可 能需要执行比较、排序、连接等非常耗CPU的操作，这些操作在OLTP的数据库应用中 较少发生。因此，可以说OLAP是CPU密集型的操作，而OLTP是10密集型的操作。 建议在采购设备时，将更多的注意力放在提高IO的配置上。
此外，为了获得更多内存的支持，用户采购的CPU必须支持64位，否则无法 支持64位操作系统的安装。因此，为新的应用选择64位的CPU是必要的前提。现 在4核的CPU已经非常普遍，如今Intel和AMD又相继推出了 8核的CPU,将来 随着操作系统的升级我们还可能看到128核的CPU,这都需要数据库更好地对其提 供支持。
从InnoDB存储引擎的设计架构上来看，其主要的后台操作都是在一个单独的 master thread中完成的，因此并不能很好地支持多核的应用。当然，开源社区已经通过 多种方法来改变这种局面，而InnoDB 1.0版本在各种测试下已经显示出对多核CPU的 处理性能的支持有了极大的提高，而InnoDB 1.2版本又支持多个purge线程，以及将刷 新操作从master thread中分离出来。因此，若用户的CPU支持多核，InnoDB的版本应 该选择1.1或更高版本。另外，如果CPU是多核的，可以通过修改参数innodb_read_io_ threads和innodb_write_io_threads来增大10的线程，这样也能更充分有效地利用CPU 的多核性能。
在当前的MySQL数据库版本中，一条SQL査询语句只能在一个CPU中工作， 并不支持多CPU的处理。OLTP的数据库应用操作一般都很简单，因此对OLTP应 用的影响并不是很大。但是，多个CPU或多核CPU对处理大并发量的请求还是会有 帮助。
2.内存的重要性
内存的大小是最能直接反映数据库的性能。通过之前各个章节的介绍，已经了解到 InnoDB存储引擎既缓存数据，又缓存索引，并且将它们缓存于一个很大的缓冲池中，即 InnoDB Buffer Poolo因此，内存的大小直接影响了数据库的性能。Percona公司的CTO Vadim对此做了一次测试，以此反映内存的重要性，结果如图9.1所示。
在上述测试中，数据和索引总大小为18GB,然后将缓冲池的大小分别设为2GB、 4GB、6GB、8GB、10GB、12GB、14GB、16GB、18GB、20GB、22GB,再进行 sysbench 的测试。可以发现，随着缓冲池的增大，测试结果TPS (Transaction Per Second)会线性增 长。当缓冲池增大到20GB和22GB时，数据库的性能有了极大的提高，因为这时缓冲池 的大小已经大于数据文件本身的大小，所有对数据文件的操作都可以在内存中进行。因此 这时的性能应该是最优的，再调大缓冲池并不能再提高数据库的性能。
所以，应该在开发应用前预估“活跃”数据库的大小是多少，并以此确定数据库服 务器内存的大小。当然,要使用更多的内存还必须使用64位的操作系统。
如何判断当前数据库的内存是否已经达到瓶颈了呢？可以通过查看当前服务器的状 态，比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率，通常InnoDB存储 引擎的缓冲池的命中率不应该小于99%,如：
从上面的例子看，缓冲池命中率=167 051 313/ (167 051 313+129 236+0) = 99.92%
即使缓冲池的大小已经大于数据库文件的大小，这也并不意味着没有磁盘操作。数 据库的缓冲池只是一个用来存放热点的区域，后台的线程还负责将脏页异步地写入到磁 盘。此外，每次事务提交时还需要将日志写入重做日志文件。
3.硬盘对数据库性能的影响
当前大多数数据库使用的都是传统的机械硬盘。机械硬盘的技术目前已非常成熟, 在服务器领域一般使用SAS或SATA接口的硬盘。服务器机械硬盘开始向小型化转型, 目前大部分使用2.5寸的SAS机械硬盘。
机械硬盘
机械硬盘有两个重要的指标：一个是寻道时间，另一个是转速。当前服务器机械硬 盘的寻道时间已经能够达到3ms,转速为15 000RPM (rotate per minute)。传统机械硬盘 最大的问题在于读写磁头，读写磁头的设计使硬盘可以不再像磁带一样，只能进行顺序 访问，而是可以随机访问。但是，机械硬盘的访问需要耗费长时间的磁头旋转和定位来査找，因此顺序访问的速度要远高于随机访问。传统关系数据库的很多设计也都是在尽量充分地利用顺序访问的特性。
通常来说，可以将多块机械硬盘组成RAID来提高数据库的性能，也可以将数据文件分布在不同硬盘上来达到访问负载的均衡。
固态硬盘
固态硬盘，更准确地说是基于闪存的固态硬盘，是近几年出现的一种新的存储设 备，其内部由闪存(Flash Memory)组成。因为闪存的低延迟性、低功耗，以及防震性， 闪存设备已在移动设备上得到了广泛的应用。企业级应用一般使用固态硬盘，通过并联多块闪存来进一步提高数据传输的吞吐量。传统的存储服务提供商EMC公司已经开始 提供基于闪存的固态硬盘的TB级别存储解决方案。数据库厂商Oracle公司最近也开始提供绑定固态硬盘的Exadata服务器。
不同于传统的机械硬盘，闪存是一个完全的电子设备，没有传统机械硬盘的读写磁头。因此，固态硬盘不需要像传统机械硬盘一样，需要耗费大量时间的磁头旋转而定位来査找数据，所以固态硬盘可以提供一致的随机访问时间。固态硬盘这种对数据的快速读写和定位特性是值得研究的。
另一方面，闪存中的数据是不可以更新的，只能通过扇区（sector）的覆盖重写，而在 覆盖重写之前，需要执行非常耗时的擦除（erase）操作。擦除操作不能在所含数据的扇区 上完成，而需要在删除整个被称为擦除块的基础上完成，这个擦除块的尺寸大于扇区的大 小，通常为128KB或者256KB。此外，每个擦除块有擦写次数的限制。已经有一些算法来 解决这个问题。但是对于数据库应用，需要认真考虑固态硬盘在写入方面存在的问题。
因为存在上述写入方面的问题，闪存提供的读写速度是非对称的。读取速度要远快 于写入的速度，因此对于固态硬盘在数据库中的应用，应该好好利用其读取的性能，避 免过多的写入操作。
图9-2显示了一个双通道的固态硬盘架构，通过支持4路的闪存交叉存储来降低固态硬盘的访问延时，同时增大并发的读写操作。通过进一步增加通道的数量，固态硬盘的性能可以线性地提高，例如我们常见的Intel X-25M固态硬盘就是10通道的固态硬盘。
由于闪存是一个完全的电子设备，没有读写磁头等移动部件，因此固态硬盘有着较低的访问延时。当主机发布一个读写请求时，固态硬盘的控制器会把I/O命令从逻辑 地址映射成实际的物理地址，写操作还需要修改相应的映射表信息。算上这些额外的开销，固态硬盘的访问延时一般小于0.1ms左右。
对于固态硬盘在InnoDB存储引擎中的优化，可以增加innodb_io_capacity变量的值 达到充分利用固态硬盘带来的高IOPS特性。不过这需要用户根据自己的应用进行有针 对性的调整。在InnoSQL及InnoDB1.2版本中，可以选择关闭邻接页的刷新，同样可以 为数据库的性能带来一定效果的提升。
此外，还可以使用InnoSQL开发的L2 Cache解决方案，该解决方案可以充分利用 固态硬盘的超高速随机读取性能，在内存缓冲池和传统存储层之间建立一层基于闪存固 态硬盘的二级缓冲池，以此来扩充缓冲池的容量，提高数据库的性能。与基于磁盘的固 态硬盘Cache类似的解决方案还有Facebook Flash Cache和bcache,只不过它们是基于 通用文件系统的，对InnoDB存储引擎本身的优化较少。
合理地设置RAID
4.性能测试工具
-
sysbench
-
mysql-tpcc
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
