读书笔记《高性能MySQL》第三章 服务器性能剖析

  问10个人关于性能的问题,可能会得到10个不同的回答,比如“每秒查询次数”、“CPU利用率”、“可扩展性”之类的。这其实都没有问题,每个人在不同场景下对性能都有不同的理解。本书的作者将性能定义为:完成某件任务所需的时间度量,换句话说,性能即是响应时间。
  很多人对什么是优化很迷茫,假如你认为性能优化是降低CPU利用率,这样就可以减少对资源的使用。但这是一个陷阱,资源是用来消耗并工作的,所以,有时候如果消耗更多的资源,就能够加快查询的速度,也是可取的。

性能剖析一般有两个步骤:
  1、测量任务所花费的时间;
  2、对结果进行统计和排序,将重要的任务排到前面。

尽管性能剖析输出了排名、总计和平均值,但还是有很多需要的信息是缺失的,如下所示:
  1、值得优化的查询:性能剖析不会自动给出哪些查询值得花时间去优化。一些占总响应时间比重较小的查询,是不值得优化的,根据阿姆达尔定律,对一个占总响应时间不超过5%的查询进行优化,无论如何努力,收益也不会超过5%。如果优化的成本大于收益,就应当停止优化。
  2、异常情况:某些任务,即使没有出现在性能剖析输出的前面,也需要优化。比如,某些任务执行的次数很少,但每次执行都非常慢,严重影响用户体验。因为其执行频率低,所以总的响应时间占比并不突出。
  3、未知的未知:一款好的性能剖析工具会显示可能的“丢失时的间”。丢失的时间指的是,任务的总时间和实际测量到的时间之间的差。例如,如果处理器的CPU时间是10秒,而剖析到的任务总时间是9.7秒,那么就有300毫秒的丢失时间。这可能是有些任务没有测量到,也可能是由于测量的误差和精度问题的缘故。如果工具发现了这类问题,则要引起重视,因为有可能错过了某些重要的事情。即使性能剖析没有发现丢失时间,也需要注意考虑这类问题存在的可能性,这样才不会错过重要的信息。
  4、被掩藏的细节:性能剖析无法显示所有响应时间的分布。只相信平均值是非常危险的,它会隐藏很多信息,而且无法表达全部情况。Peter经常举例说医院所有病人的平均体温没有任何价值。

性能瓶颈可能有很多影响因素:
  1、外部资源,比如调用了外部的web服务或搜索引擎;
  2、应用需要处理大量的数据,比如分析一个超大的XML文件;
  3、在循环中执行昂贵的操作,比如滥用正则表达式;
  4、使用了低效的算法,比如使用暴力搜索算法,来查找列表中的项;

测量工具:New Pelic、xhprof、xdebug、Valgrind、cachegrind、Enterprise Monitor( 它是Oracle提供的MySQL商业服务支持中的一部分 )

剖析MySQL查询:
  1、慢查询日志,可以通过设置long_query_time为0来捕获所有的查询。它是开销最低、精度最高的测量查询时间的工具,但它并不是万能的。例如,当数据库负载已经过高时,即使原本执行速度非常快的查询,也有可能会变的很慢;
  2、抓取TCP网络包,可以先通过tcpdump将网络包数据保存到磁盘,然后使用pt-query-digest的–type=tcpdump选项来解析并分析查询。此方法精度比较高,并且可以捕获所有查询。还可以解析更高级的协议特性,比如可以解析二进制协议,从而创建并执行服务端预解析的语句及压缩协议;
  3、使用show profile,它是在MySQL5.1以后的版本引入的,来源于开源社区的Jeremy Cole的贡献。此命令非常强大,可以分析查询语句具体慢在哪里。例如:是创建临时表慢、还是执行排序慢;
  4、使用show status,这个命令会返回一些计数器。既有服务器级别的全局计数器,也有基于某个连接的会话级别的计数器。它可以分析出,查询语句创建了多少张临时表,是磁盘临时表,还是内存临时表,几条结果用到索引的读操作,几条结果没有用到索引的读操作,也非常强大;
  5、使用explain,它能分析语句的执行计划。可以判断语句是否有用到索引,用了哪些索引,索引的长度,所需扫描的记录数,等等;
  6、使用show processlist,可以通过该命令,来观察是否有大量线程处于不正常的状态,或有其它不正常的特征。例如,查询很少会长时间处于statistics状态;

  MySQL的性能剖析,除了对单条查询语句本身,需要做详细的分析以外,有的时候还需要对整个应用程序,甚至整个MySQL服务或服务器做分析。就像作者说的,没有什么是放之四海而皆准。找到确定的问题点,然后使用正确而有效的方法,就能做到以不变而应万变。

读书笔记《高性能MySQL》第二章 MySQL基准测试

  这一章,将会是最水的一章。并不是说书写的不好,而是我根本看不下去。这一章大篇幅的讲解了,为什么要进行基准测试,基准测试的策略、方法和指标,以及如何通过测试结果,评估系统的性能。最后,还介绍了一些测试工具、测试案例。

测试的指标:
  1、吞吐量:单位时间内事务处理数。
  2、响应时间或者延迟:任务所需的整体时间。
  3、并发性:在任意时间有多少同时发生的并发请求。
  4、可扩展性:给系统增加一倍的工作,在理想情况下就能获得两倍的结果。

基准测试有两种主要的策略:
  1、集成式 ( full-stack ):针对整个系统的整体测试。
  2、单组件式 ( single-component ):单独测试MySQL。

集成式测试工具:
  1、ab:它是一个Apache HTTP服务器基准测试工具,它可以测试HTTP服务器每秒最多可以处理多少请求。这是个非常简单的工具,只能针对单个URL进行尽可能快的压力测试。
  2、http_load:它和ab类似,但比ab更加灵活。可以通过一个输入文件提供多个URL,http_load在这些URL中随机选择进行测试。也可以定制http_load,使其按照时间比率进行测试。
  3、JMeter:它是一个Java应用程序,可以加载其他应用并测试性能。JMeter比ab和http_load都要复杂的多。例如,它可以通过控制预热时间等参数,更加灵活地模拟真实用户的访问。JMeter拥有绘图接口,还可以对测试进行记录,然后离线重演测试结果。

单组件式测试工具:
  1、mysqlslap:可以模拟服务器的负载,并输出计时信息。它包含在MySQL5.1的发行包中,应该在MySQL4.1或者更高的版本中都可以使用。测试时可以执行并发连接数,并指定SQL语句。如果没有指定SQL语句,mysqlslap会自动生成查询schema的SELECT语句。
  2、MySQL Benchmark Suite:它是单线程的,主要用于测试服务器执行查询的速度,结果会显示哪种类型的操作在服务器上执行得更快。
  3、Super Smack:是一款用于MySQL和PostgreSQL的基准测试工具,可以提供压力测试和负载生成。这是一个复杂而强大的工具,可以模拟多用户访问,可以加载测试数据到数据库,并支持使用随机数据填充测试表。
  4、Database Test Suite:是由开源软件开发实验室设计的,发布在SourceForge网站上,这是一款类似某些工业标准测试的测试工具集。
  5、Percona’s TPCC-MySQL Tool:它是由本书作者开发的,一个类似TPC-C的基准测试工具集,其中有部分是专门为MySQL测试开发的,该工具的源代码可以在 https://launchpad.net/perconatools 下载。
  6、sysbench:是一款多线程系统压测工具。它可以根据影响数据库服务器性能的各种因素来评估系统的性能。例如,可以用来测试文件I/O、操作系统调度器、内存分配和传输速度、POSIX线程,以及数据库服务器等。sysbench支持Lua脚本语言,Lua对于各种测试场景的设置可以非常灵活。sysbench是一个全能测试工具,支持MySQL、操作系统和硬件的硬件测试。

  MySQL还有一个内置的函数:BENCHMARK(),可以测试某些特定操作的执行速度,参数可以是需要执行的次数和表达式。该函数可以很方便地测试某些特定操作的性能,比如通过测试可以发现,MD5()函数比SHA1()函数要快:

SET @input := 'hello world';
SELECT BENCHMARK(1000000, MD5(@input));
SELECT BENCHMARK(1000000, SHA1(@input));

读书笔记《高性能MySQL》第一章 MySQL架构与历史

今年3月份,早就买了这本《高性能MySQL》,一直还没有去看。前后一起买的,还有一本《MySQL必知必会》,也早已看完。后来,去看了一些其他书籍。像是什么《Redis实战》、《数据结构C语言版》、《Linux命令行与shell脚本编程大全》。不过,我看书一般都是有选择性的看。看能看懂的,看迫切需要掌握的,毕竟贪多嚼不烂。我看书的习惯是:先看目录,看看这本书大致有什么内容,然后,再看看前言,看看作者想告诉我们些什么。这本书总共分为16章,6个附录。我计划每看完4章,就开始做个笔记。非常推荐大家,去购买这本书来看。

MySQL采用三层架构:

第1层
负责连接处理、授权认证、安全等。
第2层
负责查询解析、分析、优化、缓存、内置函数( 例如,日期、时间、数学和加密函数 )、存储过程、触发器、视图等。
第3层
包含了存储引擎,负责数据的存储与提取,服务器通过API与存储引擎进行通信。除了InnoDB会解析外键定义,其它存储引擎一般不会解析SQL,不同存储引擎之间也不会相互通信。


MSQL5.5加入了企业线程池插件( https://dev.mysql.com/doc/refman/5.5/en/thread-pool.html )

MySQL会解析查询,并创建内部数据结构( 解析树 ),包括重写查询、决定表的读取顺序,以及选择合适的索引等。

对于SELECT语句,在解析查询之前,服务器会先检查查询缓存,如果能够在其中找到对应的查询,服务器就不必再执行查询解析、优化和执行的整个操作,而是直接返回查询缓存中的结果集。

并发控制可以采用共享锁、排他锁来解决问题。

锁粒度包含了表锁、行锁( MyISAM不支持行锁,InnodDB支持行锁 )。

事务的ACID概念:原子性( atomicity )、一致性( consistency )、隔离性( isolation )、持久性( durability )。

事务的隔离级别:未提交读( READ UNCOMMITTED )、提交读( READ COMMITTED )、可重复读( REPEATABLE READ )、可串化( SERIALIZABLE ),InnoDB事务的默认隔离级别是可重复读。

隔离级别 脏读 不可重复读 幻读 加锁读
未提交读 Yes Yes Yes No
提交读 No Yes Yes No
可重复读 No No Yes No
可串行化 No No No Yes

MySQL默认采用自动提交模式。每个查询都会被当作一个事务执行,并自动提交。

InnoDB支持通过特定的语句进行加锁,这些语句不属于SQL规范,经常被滥用,实际上应当尽量避免使用,如下:
SELECT … LOCK IN SHARE MODE ( 共享锁 )
SELECT … FOR UPDATE ( 排他锁 )

InnoDB采用MVCC多版本并发控制,MVCC的实现,是通过保存数据在某个时间点的快照来实现的,并保存系统版本号、行记录的版本号。每开始一个新事务,系统版本号都会自动递增。事务开始时刻的系统版本号,会作为事务的版本号,用来和查询的每行记录版本号进行比较。
SELECT时,只查询行的系统版本号,小于或等于事务的系统版本号。行的删除版本,要么未定义,要么大于当前事务版本号。
INSERT时,为插入的每一行,保存当前系统版本号,作为行版本号。
DELETE时,为删除的每一行,保存当前系统版本号,作为行版本号。
UPDATE时,为插入一行新记录,保存当前系统版本号,作为行版本号。同时保存当前系统版本号,到原来的行,作为行删除标识。
MVCC只在“提交读”、“可重复读”两个隔离级别下工作,其他两个隔离级别都和MVCC不兼容。

在MySQL5.1以及之前的版本中,MyISAM是默认的存储引擎。从MySQL5.5.5开始,新表的默认存储引擎改为InnoDB,并引入了InnoDB Plugin的特性。InnoDB采用MVCC来支持高并发,并且实现了4个标准的隔离级别。其默认级别是可重复读,并通过间隙锁策略,防止幻读的出现。InnoDB表是基于聚簇索引建立的。
MyISAM提供大量特性,包括全文索引、压缩、空间函数( GIS )等,但它不支持事务和行级锁,崩溃后无法安全恢复。

内建的其他存储引擎:
1、Archive引擎,只支持INSERT和SELECT,MySQL5.1之前也不支持索引。
2、CSV引擎,能直接读取csv文件,可以作为一种数据交换的机制。
3、Memory引擎,比MyISAM还要快一个数量级,适合需要快速访问,并且不需要修改的数据。重启后数据会丢失,通常作为临时表。因为所有的数据都保存在内存中,所以不需要进行磁盘I/O。
4、NDB集群引擎,MySQL服务器、NDB集群存储引擎,以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合,被称为MySQL集群。

权限、文件系统与安装软件

  当面试的时候,别人问我Linux的掌握情况,我诚实的说只会简单操作。别人又问我ls -l第一列的含义,我却答不上来。这时候,我就觉得我要好好看看Linux书了。虽然我只是看了第一部分,后面的内容,大概的预览了一遍,以后有机会再看看吧。
  这本《Linux命令行与shell脚本编程大全(第3版)》,整本书只有前三分之一在讲shell基础操作命令,后面大篇幅的都在讲如何编写脚本,内容还不错。在不同的发行版,涉及到类似的功能命令时,会区别开来,逐一讲解。一共有26章、附录A和附录B。其中,26章又分为4个部分。第一部分(1-10章)主要讲了shell的基础,涵盖了linux的桌面环境、文件/文件夹的处理、磁盘/分区的管理、环境变量、用户/权限的处理,最后还介绍了包管理和编辑器。后面3个部分,都是在讲如果编写shell脚本,从基础到高级,再从高级到实用的脚本,下面总结下我觉得有用的命令。
1、文件权限

命令 说明
cat /etc/passwd 保存了:用户名、密码、用户的uid、用户组的gid、用户HOME目录位置、用户的默认shell,等等
cat /etc/shadow 保存了:用户名、密码、上次修改密码后过去的天数、多久后才能改密码、多久后必须改密码、密码过期提前多久提醒、密码过期多久禁用用户,等等
cat /etc/group 保存了:组名、组密码、gid、属于该组的用户列表( 当用户在/etc/passwd文件中,指定了某个组为默认组时,用户不会再出现在这里)
useradd -D 查看新用户的默认值
useradd xxx -p xxx 添加新用户,并指定默认密码
userdel -r xxx 删除用户,并删除用户的HOME目录以及邮件目录
usermod 修改用户名、密码、锁定帐户、解除锁定
passwd/chpasswd 修改密码
passwd/chpasswd 修改密码
groupadd 创建新组
umask 权限掩码(这个要好好理解
chmod 修改文件权限,支持“8进制模式”或“符号模式”
chown 改变文件所属关系

2、管理文件系统

命令 说明
fdisk /dev/sdb 分区管理
sudo mkfs.ext4 /dev/sdb1 创建文件系统
fsck 检查/修复文件系统
sudo pvcreate /dev/sdb1 创建物理卷
sudo pvdisplay /dev/sdb1 显示已创建的物理卷
sudo vgcreate Vol1 /dev/sdb1 创建卷组
sudo vgdisplay Vol1 显示已创建的卷组
sudo lvcreate -l 100%FREE -n lvtest Vol1 创建逻辑卷
sudo lvdisplay Vol1 显示已创建的逻辑卷
sudo mkfs.ext4 /dev/Vol1/lvtest 创建文件系统 ( 逻辑卷 )

3、安装软件程序

命令 说明
yum list installed 列出已安装的包
yum list xxx 显示指定软件包的详细信息
yum provides xxx 查看该文件属于哪个软件包
yum install xxx 安装软件包
yum list updates 列出已安装包的可用更新
yum update xxx 更新指定的软件包
yum update 更新所有软件包
yum remove xxx 卸载软件包,保留配置文件和数据文件
yum erase xxx 卸载软件包,并删除它所有的文件
yum clean all 清理损坏的依赖关系
yum deplist xxx 列出包的库依赖
yum repolist 查看yum的软件仓库
./configure && make && make install 编译安装

环境变量

  这段时间,我在阅读Linux书箱。前几天刚好读到环境变量,还记得以前在学校学java的时,第一步,学的就是配置环境变量,今天就作个总结。也算是做个笔记吧,好记性不如烂笔头,虽然也不是笔。
  Linux的环境变量,跟windows的环境变量类似。因为Linux系统的shell存在父子关系,所以Linux的环境变量,也分全局环境变量和局部环境变量。全局和局部的作用域不同,操作的方式也不相同。下面是一些命令介绍:

命令 说明
printenv 显示全局环境变量(所有或单个)
env 显示全局环境变量(只显示所有)
set 显示全局环境变量、局部环境变量、用户定义变量(显示所有,并排序)
unset 删除环境变量
export 把局部环境变量,导出到全局环境变量

环境变量也有数组类型的,需要通过特殊的方式全部打印出来,如: ” echo ${BASH_VERSINFO[*]} ” ,variable代码你的变量名。shell有很多的默认环境变量,下面再列举一些:

变量名 说明
HOME 当前用户的主目录
PWD 当前工作目录
MAIL 当前用户收件箱的文件名
MAILPATH 冒号分隔的当前用户收件箱的文件名列表
BASH 当前shell实例的全路径名
BASH_VERSION 当前运行的bash shell版本号
BASHPID 当前bash进程的pid
PPID bash shell父进程的pid
COLUMNS 当前bash shell实例所用终端的宽度
LINES 定义了终端可见的行数
LINENO 当前执行的脚本的行号
HOSTNAME 当前主机的名称
HOSTTYPE 当前运行bash shell的机器
LANG shell的语言环境类别
OSTYPE 定义了shell所在的操作系统
RANDOM 返回一个0~32767的随机数( 对其的赋值可作为随机数生成器的种子 )
SECONDS 自从shell启动到现在的秒数( 对其赋值将会重置计数器 )
UID 当前用户的真实用户id( 数字形式 )
EUID 当前用户的有效用户id( 数字形式 )

再遇指针,不递归转树结构

  时隔几年,我又遇到关于传引用的问题,这次终于可以运用引用了,先说说起因,以前在做数组转树结构时,我通常采用递归的方式,代码如下所示。
[code lang=”php”]
<pre><?php
function recursion_to_tree($data = [], $pid = 0)
{
$tree = [];
foreach ($data as $item) {
if ($item[‘pid’] == $pid) {
$son = recursion_to_tree($data, $item[‘id’]);
if (!empty($son)) {
$item[‘son’] = $son;
}
$tree[] = $item;
}
}
return $tree;
}
$array = [
[‘id’ => 1, ‘pid’ => 0, ‘name’ => ‘广东省’],
[‘id’ => 2, ‘pid’ => 1, ‘name’ => ‘广州市’],
[‘id’ => 3, ‘pid’ => 2, ‘name’ => ‘白云区’],
[‘id’ => 4, ‘pid’ => 2, ‘name’ => ‘天河区’],
[‘id’ => 5, ‘pid’ => 1, ‘name’ => ‘深圳市’],
[‘id’ => 6, ‘pid’ => 0, ‘name’ => ‘香港’],
];
$result = recursion_to_tree($array);
echo var_export($result);
[/code]
效果如下所示

array (
  0 => 
  array (
    'id' => 1,
    'pid' => 0,
    'name' => '广东省',
    'son' => 
    array (
      0 => 
      array (
        'id' => 2,
        'pid' => 1,
        'name' => '广州市',
        'son' => 
        array (
          0 => 
          array (
            'id' => 3,
            'pid' => 2,
            'name' => '白云区',
          ),
          1 => 
          array (
            'id' => 4,
            'pid' => 2,
            'name' => '天河区',
          ),
        ),
      ),
      1 => 
      array (
        'id' => 5,
        'pid' => 1,
        'name' => '深圳市',
      ),
    ),
  ),
  1 => 
  array (
    'id' => 6,
    'pid' => 0,
    'name' => '香港',
  ),
)

  那时还沾沾自喜,以为自己掌握了多么炫酷的奇技淫巧。就在几个月前,阅读数据结构的书籍时,才发现递归其实是没效率的,也被我曾经的组长聪哥提醒过,但一直也没在意。
  直到昨天,听说华哥遇到一个奇葩的面试题目,要求:不使用递归,不使用函数,只遍历一次数组,将数据转为树结构。这就尴尬了,不过,我以前查资料时,貌似有了解到,可以不用递归转树结构,只是一直不怎么关注。
  正好趁着这个机会,我再次百度,很容易就找到了方法,他使用的是引用( & ),抱着试一试的心态,我试了下,代码如下所示
[code lang=”php”]
<pre><?php
function iteration_to_tree($data = [])
{
//重组键名
foreach ($data as $item) {
$temp[$item[‘id’]] = $item;
}

//转为树
$tree = [];
foreach ($temp as $item) {
$pid = $item[‘pid’];
$id = $item[‘id’];

if (isset($temp[$pid])) {
$temp[$pid][‘son’][] = &$temp[$id];
} else {
$tree[] = &$temp[$id];
}
}
return $tree;
}
$array = [
[‘id’ => 1, ‘pid’ => 0, ‘name’ => ‘广东省’],
[‘id’ => 2, ‘pid’ => 1, ‘name’ => ‘广州市’],
[‘id’ => 3, ‘pid’ => 2, ‘name’ => ‘白云区’],
[‘id’ => 4, ‘pid’ => 2, ‘name’ => ‘天河区’],
[‘id’ => 5, ‘pid’ => 1, ‘name’ => ‘深圳市’],
[‘id’ => 6, ‘pid’ => 0, ‘name’ => ‘香港’],
];
$result = iteration_to_tree($array);
echo var_export($result);
[/code]
最终的结果,和上面递归的结果是一样的。

乍一眼,确实不好理解,怎么一个foreach,一个if else 就搞定了呢?

  重点是要先理解,引用传递的作用( http://www.php.net/manual/zh/language.references.pass.php ),它和C的指针类似。理解了引用传递,再仔细看 15、16、18 这三行,就很好理解了。

代码流程:
  1、先把数组重组:将键名改为id,然后再遍历重组后的数据;
  2、第18行代码:遍历时,如果,当前记录的pid,不存在于数组中,则把当前记录存入“树结构”。注意:这时候的$temp,已经带了引用的状态,对$temp进行改变值,会影响到已经存入“树结构”的数据;
  3、第16行代码:遍历时,如果,当前记录的pid,存在于数组中,则把当前记录存入$item。注意:这时候又有一个引用,对这第二个$item,进行值的改变,就会影响$item里面的内容;

就这么简单的实现了递归效果,但实际的效率,我也不知道是不是比递归强。

杂项:延时执行、后台作业、协程、查找、历史记录、别名、查看手册

命令 说明
sleep 休眠若干秒,结果加连接符(&),可以置入后台执行
jobs 显示后台作业信息
coproc 协程,可将命令置入后台模式
find 在指定目录下,查找文件
which 在环境变量$PATH设置的目录下,查找文件
whereis 在特定目录下,查找文件 ( 该指令只能用于查找二进制文件、源代码文件和man手册页 )
type 了解某个命令是否内建的,还可以查看命令的不同实现,如: type -a echo
history 查看最近使用过的命令列表,
!! 执行,最近一次,执行过的命令
alias 定义别名,如: alias li=’ls -li’
unalias 删除别名
man 查看指定命令的手册

监测进程、监测磁盘、压解文件

命令 说明
ps 显示进程,推荐: ps -ef
top 实时显示进程
kill/killall 结束进程
mount 挂载磁盘
umount 卸载磁盘
df 磁盘使用情况,推荐: df -h
du 目录使用情况,推荐: du -sh *|sort -nr
sort 排序数据
grep/egrep/fgrep 搜索数据
gzip/gunzip 压缩/解压单个文件
tar 压缩/解压多个文件