作者归档:admin

Ubuntu 16.04 上安装Docker

Docker是一个轻量级的虚拟化容器技术,既可以为不同应用程序提供了隔离空间(内存,资源,网络),又可以将应用程序与环境一起打包,发布到其他机器上,避免环境的差异,这点像Vagrant。 但是,它又与传统的虚拟机不容(如VirtualBox),它的应用程序仍然是跑在宿主机上面的,并且镜像里面通常仅包含所必须的组件而非完整的系统,因此拥有极高的性能。这又有点像Python的Virtual Environment,不过Docker更加强大,本质是是个Linux,可以打包的不同语言的应用程序。
Docker要求linux内核版本大于3.10,可以在通过以下命令查看是否支持

$ uname -r
4.4.0-38-generic

需要先更新以下apt仓库

$ sudo apt-get update
$ sudo apt-get install apt-transport-https ca-certificates
$ sudo apt-key adv --keyserver hkp://p80.pool.sks-keyservers.net:80 --recv-keys 58118E89F3A912897C070ADBF76221572C52609D
[/sell]
检查以下是否已经创建Docker软件仓库文件,如果没有需要手动创建,并添加以下内容

[email protected]:~# cat /etc/apt/sources.list.d/docker.list
deb https://apt.dockerproject.org/repo ubuntu-xenial main
[/sell]
确认是否配置成功

[email protected]:~# sudo apt-get update
[email protected]:~# apt-cache policy docker-engine
docker-engine:
  Installed: 1.12.1-0~xenial
  Candidate: 1.12.1-0~xenial
  Version table:
 *** 1.12.1-0~xenial 500
        500 https://apt.dockerproject.org/repo ubuntu-xenial/main amd64 Packages
        100 /var/lib/dpkg/status
     1.12.0-0~xenial 500
        500 https://apt.dockerproject.org/repo ubuntu-xenial/main amd64 Packages
     1.11.2-0~xenial 500
        500 https://apt.dockerproject.org/repo ubuntu-xenial/main amd64 Packages
     1.11.1-0~xenial 500
        500 https://apt.dockerproject.org/repo ubuntu-xenial/main amd64 Packages
     1.11.0-0~xenial 500
        500 https://apt.dockerproject.org/repo ubuntu-xenial/main amd64 Packages

Docker容器建立在Auf文件系统之上,以便支持将不同的目录挂载到同一虚拟文件系统下面,并设置不同的读写权限。需要安装linux-image-extra:

$ sudo apt-get install linux-image-extra-$(uname -r) linux-image-extra-virtual
[/sell]
现在可以开始安装Docker了:

$ sudo apt-get install docker-engine
$ sudo service docker start

试运行一下:

[email protected]:~# docker run hello-world

Hello from Docker!
This message shows that your installation appears to be working correctly.

To generate this message, Docker took the following steps:
 1. The Docker client contacted the Docker daemon.
 2. The Docker daemon pulled the "hello-world" image from the Docker Hub.
 3. The Docker daemon created a new container from that image which runs the
    executable that produces the output you are currently reading.
 4. The Docker daemon streamed that output to the Docker client, which sent it
    to your terminal.

To try something more ambitious, you can run an Ubuntu container with:
 $ docker run -it ubuntu bash

Share images, automate workflows, and more with a free Docker Hub account:
 https://hub.docker.com

For more examples and ideas, visit:
 https://docs.docker.com/engine/userguide/

如果你本地没有hello-world这个镜像,它是会自动去Docker官网上下载的。
查看最近运行过的容器,不加参数-l,是查看正在运行的容器

[email protected]:~# docker ps -l
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS                     PORTS               NAMES
1931765938dc        hello-world         "/hello"            4 minutes ago       Exited (0) 4 minutes ago                       suspicious_lichterman

运行镜像的时候也可以指定名称(而不是suspicious_lichterman)

[email protected]:~# docker run --name hello hello-world

Hello from Docker!
...

[email protected]:~# docker ps -l
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS                     PORTS               NAMES
e4773606e6cf        hello-world         "/hello"            7 seconds ago       Exited (0) 6 seconds ago                       hello

但是再次运行同样名称为hello的容器时会报错

[email protected]:~# docker run --name hello hello-world
docker: Error response from daemon: Conflict. The name "/hello" is already in use by container e4773606e6cf4222b786d5301ab38e4dddb90e11a0358f64d33109bf829f0b5a. You have to remove (or rename) that container to be able to reuse that name..
See 'docker run --help'.

这时候需要先把旧的给删掉,再运行就不会了

[email protected]:~# docker rm hello
hello

通过Docker stop停止运行指定容器。
查看本地的Docker镜像

[email protected]:~# docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
nginx-php-fpm       phalcon             1c97ee169a55        3 hours ago         364.5 MB
nginx-php-fpm       latest              4fc9ac9f2945        7 hours ago         228.5 MB
ubuntu              16.04               c73a085dc378        47 hours ago        127.1 MB
ubuntu              latest              c73a085dc378        47 hours ago        127.1 MB
nginx               mainline-alpine     00bc1e841a8f        5 days ago          54.21 MB
mysql               5.7                 4b3b6b994512        5 weeks ago         384.5 MB
hello-world         latest              c54a2cc56cbb        12 weeks ago        1.848 kB

这里先简单的介绍Docker的安装、运行,后续将介绍Docker命令行交互,网络,文件存储等。

参考链接:
Docker Overview
Docker:利用Linux容器实现可移植的应用部署
Installation
on Ubuntu

Docker:具备一致性的自动化软件部署
Docker — 从入门到实践
Docker简介与入门
剖析Docker文件系统:Aufs与Devicemapper
What is the runtime performance cost of a Docker container
Docker – The Linux Container
当流浪者(Vagrant)遇见码头工人(Docker): 实战

Centos 6.4 安装 Python 2.7

终于又开始学Python了,不过这次是在Centos 6.4 上面,也碰到了好多问题。Centos6.4
并不能通过yum安装Python 2.7,系统自带的yum等使用的都是Python2.6.6,将系统的Python软链接指向2.7版本会有各种问题,包括依赖库等等;或者只能创建新的可执行命令,如Python27。最终按照这篇文章的介绍成功安装了Python2.7。
首先安装相关的工具,要不然等下编译Python会报各种各样的错:

$sudo yum groupinstall "Development tools"
$sudo yum install zlib-devel
$sudo yum install bzip2-devel
$sudo yum install openssl-devel
$sudo yum install ncurses-devel
$sudo yum install sqlite-devel

然后下载Python并安装,注意这里是make altinstall而不是make install,参考这里

$ wget https://www.python.org/ftp/python/2.7.11/Python-2.7.11.tar.xz
$ tar xf Python-2.7.11.tar.xz
$ cd Python-2.7.11
$ ./configure --prefix=/usr/local
$ sudo make 
$ sudo make altinstall

检查一下是不是安装到了/usr/local/bin/python2.7下面去了,后面Python 2.7相关的库也将安装到这里

$ ls -ltr /usr/local/bin/python*

检查一下Python2.6是不是还在/usr/bin/下面

$ ls -ltr /usr/bin/python*

检查一下系统路径变量PATH,保证/usr/local/bin在/usr/bin之前,然后将Python的软链接指向2.7。

$ echo $PATH
/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
$ ln -s /usr/local/bin/python2.7 /usr/local/bin/python

这样子普通用户登录的时候就可以使用Python2.7了,而root用户(sudo)仍然使用Python2.6,yum等才不会出错。

$ which python
/usr/local/bin/python
$ python -V
Python 2.7.11

$ sudo -s
which python
#/usr/bin/python
python -V
#Python 2.6.6
exit

安装Python 2.7的包管理工具

$ wget https://bitbucket.org/pypa/setuptools/raw/bootstrap/ez_setup.py
$ sudo /usr/local/bin/python2.7 ez_setup.py
$ sudo /usr/local/bin/easy_install-2.7 pip

检查一下是不是对了

$ which pip
/usr/local/bin/pip

$ which easy_install
/usr/local/bin/easy_install

在普通用户下面操作使用easy_install 就是安装到Python2.7的目录下面去了

$ easy_install requests

这时候会报错,因为相关的Python 2.7目录并没有写的权限

$ sudo chmod 664 /usr/local/bin

或者

sudo /usr/local/bin/easy_install-2.7 requests

注意:如果是在root用户或者sudo命令下,使用的仍然是Python 2.6,所以必须要指明使用那个版本的easy_install。
接下来就可以愉快的使用pip安装Python2.7相关的库了。

参考链接:
Installing python 2.7 on centos 6.3. Follow this sequence exactly for centos machine only
How To Set Up Python 2.7.6 and 3.3.3 on CentOS 6.4
How to install Python 2.7 and Python 3.3 on CentOS 6
Centos 6.4 python 2.6 升级到 2.7
CENTOS 6.5 安装 Python 2.7 总结
Difference in details between “make install” and “make altinstall”
Common Python Tools: Using virtualenv, Installing with Pip, and Managing Packages

分布式系统

好久以前写的了。在InfoQ上学习了分布式监控系统的设计与实现微信朋友圈技术之道,在架构设计的基础思想并不复杂。
首先是微信朋友圈技术之道,介绍了微信朋友圈团队的开发,团队仅4个人,因为他们站在巨人的肩膀上:
基础环境
全部才用C++开发
混合部署普通服务器
海量带宽

强大的基础设施
腾讯CDN,图片和视频上传、存储、分发
RPC框架
Key-Value(KV)存储系统
强大、方便、灵活的部署系统
强大的RPC框架
C++的框架
支持protobuf描述接口
支持进程/线程/协程多种模式:支持数十万的并发协程,方便编写和调试“同步”的网络调用和服务
从CGI到叶子服务器,全系统透明支持过载保护和QOS
为每次CGI调用自动生成全系统调用关系图
每个服务自动内建500多个监控项
高性能Key-value存储系统
三机一组,三机间支持数据一致性,容忍一台机器出错、自动切换
三机分布在一个数据中心的三个独立园区,在任意一个园区提供本区读写服务,容忍一个园区网络隔离

微信架构
接入层,维持长连接,避免重复连接开销;推送消息
逻辑层,注册/登录,消息,朋友圈,LBS;批处理,群聊,通知,好友推荐
存储代理层,账号,消息,关系链,朋友圈,群管理
存储层,Key-value存储
性能水平扩展(sharding)
相册,按照用户做水平扩展
发表,按照发表key做水平扩展
评论,按照评论key做水平扩展
时间线,按照用户做水平扩展

发表流程
上传图片到CDN,查找最近节点
调用朋友圈CGI
添加新发表
在相册增加新发表索引
添加时间线更新任务,批处理,给好友时间线添加新索引
返回发表成功

浏览流程
调用朋友圈CGI
拉取时间线
拉取新发表元数据
拉取CDN上的图片

数据单副本、索引写扩散、检查更新单读取
数据(发表)单副本:减少内存开销
索引写扩散、检查更新单读取:减少检查更新时的读扩散
写比较慢,失败可以重试;读不可等待

赞、评论与浏览
往赞、评论表插入发表索引和对应赞、评论

微信部署、接入与容灾
1地3园区对等部署,对等接入(电信、移动、联通);数据对等分布于同步;对等服务
容灾,任何两个园区都可以提供全量无损服务
数据中心分布:上海、深圳、香港、加拿大
通信优先专线服务;其次公网(加密)
各数据中心可独立提供服务
各数据中心通过idc queue异步同步写入(自动重试)

核心数据读写分析
相册与发表
本地idc写入,单向同步到其他idc
key的全局唯一性保证无冲突(根据idc预先生成)
时间线
本地idc只保存本地用户时间线的key,无需同步

跨洋同步的挑战和应对
挑战:大带宽延迟、丢包、乱序、低可靠性(专心中断)
应对:延迟、丢包、乱序达到一定程度自动从tcp切换到udp;专线中断自动切换到公网(AES加密)

评论、赞写冲突
因果一致性(不同数据中心保证key不重复,比如不同idc取模)

参考链接:
分布式监控系统的设计与实现
微信朋友圈技术之道

MySql 慢日志分析

最近老是碰上MySql报错:1203:User already has more than ‘max_user_connections’ active,之前都没出现过,感觉应该是慢查询导致的。向运维拷贝慢日志分析,慢日志开、启配置参考这里
拷贝出来的日志很大,需要按故障时间点做一下切割,以便缩小排查范围。按照这里提供的cutlogbytime.pl脚本运行却报错

[[email protected] mysql-log-filter-1.9]$ ./cutlogbytime.pl slow.log 1443103200 1443117600 > yestoday.log
: command not foundline 1:
: command not foundline 4:
./cutlogbytime.pl: line 5: use: command not found
: command not foundline 5:
./cutlogbytime.pl: line 6: use: command not found
: command not foundline 6:
: command not foundline 7:
'/cutlogbytime.pl: line 8: syntax error near unexpected token `{
'/cutlogbytime.pl: line 8: `if (@ARGV<2){

去掉顶行的空格后再运行,还是报错

[[email protected] mysql-log-filter-1.9]$ ./cutlogbytime.pl slow.log 1443103200 1443117600 > today.log
-bash: ./cutlogbytime.pl: /usr/bin/perl^M: bad interpreter: No such file or directory

最后参考stackoverflow上面的答案更改运行方式为Perl(而不是shell),就可以了。

[[email protected] mysql-log-filter-1.9]$ perl cutlogbytime.pl slow.log 1443103200 1443117600 > today.log

利用mysqlslowdump(perl脚本)来分析日志,-s参数表示排序方式:r表示影响行数(Rows),t表示耗时(Time),c表示查询次数(Count)

[[email protected] mysql-log-filter-1.9]$  perl mysqldumpslow.pl -s r -t 10 today4.log

Reading mysql slow query log from today4.log
Count: 1  Time=190.48s (190s)  Lock=0.00s (0s)  Rows=21829854.0 (21829854), xx[xxxx]@[192.168.10.139]
  SELECT /*!N SQL_NO_CACHE */ * FROM `errormessage`

Count: 32791  Time=40.95s (1342865s)  Lock=0.05s (1512s)  Rows=1.0 (32791), xx[xxxx]@10hosts
  select  *  from connectinfo where  ID=N  and AppType=N  ORDER BY CreateDatetime DESC LIMIT N

Count: 3  Time=3.71s (11s)  Lock=0.02s (0s)  Rows=300.0 (900), xx[xxxx]@2hosts
  select SeverName from errormessage where  ID='S'  and ServerType=N  and level=N  and MsgType <= N

第一个语句返回行数21829854,查看具体慢日志,之后需要插入这张表的进程均处于等待状态。

# Time: 150924  1:03:12
# [email protected]: xx[xxxx] @  [192.168.10.139]  Id: 1493761
# Query_time: 190.479062  Lock_time: 0.000000 Rows_sent: 21829854  Rows_examined: 21829854
SET timestamp=1443027792;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `errormessage`;
# Time: 150924  1:03:14
# [email protected]: xx[xxxx] @  [192.168.10.168]  Id: 1498010
# Query_time: 59.669817  Lock_time: 57.159403 Rows_sent: 0  Rows_examined: 0
SET timestamp=1443027794;
insert into errormessage (`ID`,`ServerType`,`MsgType`,`Level`,`dev`,`content`,`EventTime`,`SeverName`) values ( '1217', '3', '4', '4', '827', 'erc:start erc error,songid=46243,keymd5=ee1275b26762e85a7f00e9890bdc092e,ercmd5=abbc3ea9102dbd003b7aa0547dcbf6fa', '2015-09-23 21:49:27', '192.168.15.117');
# [email protected]: xx[xxxx] @  [192.168.10.205]  Id: 1494756
# Query_time: 157.211158  Lock_time: 154.673647 Rows_sent: 0  Rows_examined: 0
SET timestamp=1443027794;
insert into errormessage (`ID`,`ServerType`,`MsgType`,`Level`,`dev`,`content`,`EventTime`,`SeverName`) values ( '865', '3', '1', '2', '106', '检测正常!', '2015-09-24 01:01:18', '192.168.0.33');
# [email protected]: xx[xxxx] @  [192.168.10.213]  Id: 1496479
# Query_time: 100.733230  Lock_time: 98.210902 Rows_sent: 0  Rows_examined: 0
SET timestamp=1443027794;
insert into errormessage (`ID`,`ServerType`,`MsgType`,`Level`,`dev`,`content`,`EventTime`,`SeverName`) values ( '2472', '3', '2', '4', '809', 'videoseripnoconfig', '2015-09-24 01:02:26', '192.168.0.18');

分析这几天的日志,发现故障时间点附近都是这个语句引起后面的SQL堵塞。原来是每天早上1点开始备份并同步全表数据,锁住了这个表导致后面的所有这个表的insert操作处于等待状态。mysqldump应该使用–single-transaction来避免锁表,类似下面这个

mysqldump –uuser -p --skip-opt -q -R  --single-transaction --default-character-set=utf8 --master-data=2  --create-option --no-autocommit –S ${sock} -B ${DBName}  > backup.sql

但是这样仍然是全表扫描进行备份,如果能够增量备份的话,影响要小很多,或者数据做冷热/新旧之分,定期将新(每天)/热数据转入旧(历史)/冷数据中。后面运维的解决方案是:升级数据库机器从虚拟机变为实体机,配置从机,并从从机进行备份同步。

上面mysqlslowdump使用影响行数来排序,事实上用另外两个类型(时间,次数)分析结果是connectinfo比较频繁,一直以来都认为是这个表的操作引起的。这里还尝试了其他工具来分析,使用mysqlsla.pl进行分析,相关参数选项参考这里

[[email protected] mysql-log-filter-1.9]$  perl mysqlsla.pl today.log
Auto-detected logs as slow logs
Report for slow logs: today4.log
60.57k queries total, 17 unique
Sorted by 't_sum'
Grand Totals: Time 5.38M s, Lock 3.22M s, Rows sent 21.86M, Rows Examined 184.46M


______________________________________________________________________ 001 ___
Count         : 25.59k  (42.24%)
Time          : 3905525.574451 s total, 152.643069 s avg, 113.07488 s to 2720.338946 s max  (72.64%)
  95% of Time : 3260112.482495 s total, 134.12789 s avg, 113.07488 s to 282.366041 s max
Lock Time (s) : 3168076.975558 s total, 123.820721 s avg, 108.548105 s to 311.639359 s max  (98.45%)
  95% of Lock : 2961933.212121 s total, 121.860167 s avg, 108.548105 s to 123.487106 s max
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 54 avg, 0 to 4.92k max  (0.75%)
Database      :
Users         :
        [email protected] 192.168.10.147 : 10.65% (2724) of query, 10.26% (6215) of all users
        [email protected] 192.168.10.209 : 10.33% (2643) of query, 10.16% (6156) of all users
        [email protected] 192.168.10.205 : 10.16% (2599) of query, 9.97% (6036) of all users
        [email protected] 192.168.10.211 : 10.13% (2591) of query, 9.98% (6042) of all users
        [email protected] 192.168.10.207 : 9.93% (2541) of query, 9.95% (6024) of all users
        [email protected] 192.168.10.161 : 9.83% (2515) of query, 9.84% (5960) of all users
        [email protected] 192.168.10.149 : 9.81% (2510) of query, 9.95% (6028) of all users
        [email protected] 192.168.10.215 : 9.76% (2498) of query, 9.85% (5963) of all users
        [email protected] 192.168.10.168 : 9.71% (2485) of query, 9.69% (5868) of all users
        [email protected] 192.168.10.213 : 9.69% (2480) of query, 9.66% (5851) of all users

Query abstract:
SET timestamp=N; UPDATE connectinfo SET devicetag='S', connectipaddress='S', updatedatetime=now() WHERE ID=N AND apptype=N;

Query sample:
SET timestamp=1443027797;
update connectinfo set DeviceTag='1070A416AF000000', ConnectIPAddress='60.174.116.165', UpdateDatetime=now() where ID=5358 and AppType=0;

______________________________________________________________________ 002 ___
Count         : 32.79k  (54.14%)
Time          : 1344378.871914 s total, 40.99841 s avg, 2.000747 s to 1944.548192 s max  (25.01%)
  95% of Time : 587407.556704 s total, 18.85678 s avg, 2.000747 s to 233.465042 s max
Lock Time (s) : 1512.917798 s total, 46.138 ms avg, 76 ▒s to 114.302 ms max  (0.05%)
  95% of Lock : 1414.978902 s total, 45.423 ms avg, 76 ▒s to 50.514 ms max
Rows sent     : 1 avg, 1 to 1 max  (0.15%)
Rows examined : 4.92k avg, 4.92k to 4.92k max  (87.41%)
Database      :
Users         :
        [email protected] 192.168.10.209 : 10.24% (3359) of query, 10.16% (6156) of all users
        [email protected] 192.168.10.149 : 10.16% (3331) of query, 9.95% (6028) of all users
        [email protected] 192.168.10.147 : 10.11% (3315) of query, 10.26% (6215) of all users
        [email protected] 192.168.10.211 : 10.03% (3288) of query, 9.98% (6042) of all users
        [email protected] 192.168.10.207 : 10.02% (3285) of query, 9.95% (6024) of all users
        [email protected] 192.168.10.161 : 9.97% (3268) of query, 9.84% (5960) of all users
        [email protected] 192.168.10.215 : 9.96% (3266) of query, 9.85% (5963) of all users
        [email protected] 192.168.10.205 : 9.92% (3254) of query, 9.97% (6036) of all users
        [email protected] 192.168.10.168 : 9.86% (3234) of query, 9.69% (5868) of all users
        [email protected] 192.168.10.213 : 9.73% (3191) of query, 9.66% (5851) of all users

Query abstract:
SET timestamp=N; SELECT * FROM connectinfo WHERE ID=N AND apptype=N ORDER BY createdatetime DESC LIMIT N;

Query sample:
SET timestamp=1443027795;
select  *  from connectinfo where  ID=7646  and AppType=0  ORDER BY CreateDatetime DESC LIMIT 1;

______________________________________________________________________ 003 ___
Count         : 842  (1.39%)
Time          : 66663.314786 s total, 79.172583 s avg, 2.011408 s to 673.604537 s max  (1.24%)
  95% of Time : 56684.989954 s total, 70.944919 s avg, 2.011408 s to 193.623235 s max
Lock Time (s) : 48221.988255 s total, 57.27077 s avg, 69 ▒s to 185.402303 s max  (1.50%)
  95% of Lock : 40627.196184 s total, 50.847555 s avg, 69 ▒s to 166.67704 s max
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 0 avg, 0 to 0 max  (0.00%)
Database      :
Users         :
        [email protected] 192.168.10.207 : 11.64% (98) of query, 9.95% (6024) of all users
        [email protected] 192.168.10.205 : 11.28% (95) of query, 9.97% (6036) of all users
        [email protected] 192.168.10.213 : 10.93% (92) of query, 9.66% (5851) of all users
        [email protected] 192.168.10.161 : 10.45% (88) of query, 9.84% (5960) of all users
        [email protected] 192.168.10.149 : 10.33% (87) of query, 9.95% (6028) of all users
        [email protected] 192.168.10.211 : 9.74% (82) of query, 9.98% (6042) of all users
        [email protected] 192.168.10.147 : 9.38% (79) of query, 10.26% (6215) of all users
        [email protected] 192.168.10.215 : 9.38% (79) of query, 9.85% (5963) of all users
        [email protected] 192.168.10.168 : 9.03% (76) of query, 9.69% (5868) of all users
        [email protected] 192.168.10.209 : 7.84% (66) of query, 10.16% (6156) of all users

Query abstract:
SET timestamp=N; INSERT INTO errormessage (id,servertype,msgtype,level,dev,content,eventtime,severname) VALUES ( 'S', 'S', 'S', 'S', 'S', 'S', 'S', 'S')1;

使用mysqlsla可以看SQL语句的执行数量/比例,影响行数,用户,占比等。,从这里看很可能认为是connectinfo表(95%以上)引起,SHOW PROCESSLIST也是如此 。
由于这个文件我是单独下载回来的,运行mysqlsla.pl时候碰到很多错误,逐个安装解决了

[[email protected] mysql-log-filter-1.9]$  perl mysqlsla.pl today.log
Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at mysqlsla.pl line 2092.
BEGIN failed--compilation aborted at mysqlsla.pl line 2092.
#解决方法
[[email protected] mysql-log-filter-1.9]$ sudo yum install perl-Time-HiRes

[[email protected] mysql-log-filter-1.9]$ perl -MCPAN -e 'install DBI'
Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
BEGIN failed--compilation aborted.

#解决方法
[[email protected] mysql-log-filter-1.9]$ sudo yum install perl-DBI

正确的方法是应该检测对应根目录下面的Makefile.PL,比如

[[email protected] percona-toolkit-2.2.15]$ perl Makefile.PL
#如果报以下错误,需要先安装对应模块,简单点就是
#sudo yum install perl-devel
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1.
BEGIN failed--compilation aborted at Makefile.PL line 1.

然后安装对应模块,参考这里
使用Percona公司的工具pt-query-digest来分析也得到了同mysqlsla类似的结果

[[email protected] bin]$ pt-query-digest ../../today4.log

# 9.8s user time, 700ms system time, 21.05M rss, 73.66M vsz
# Current date: Mon Oct  5 05:52:01 2015
# Hostname: vagrant-centos64.vagrantup.com
# Files: ../../today.log
# Overall: 60.57k total, 17 unique, 4.54 QPS, 402.68x concurrency ________
# Time range: 2015-09-23 22:17:29 to 2015-09-24 02:00:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        5376198s      2s   2720s     89s    258s    118s     57s
# Lock time        3217840s       0    312s     53s    118s     60s    48ms
# Rows sent         20.85M       0  20.82M  361.00    0.99  84.46k    0.99
# Rows examine     175.91M       0  20.82M   2.97k   4.71k  84.48k   4.71k
# Query size         7.85M      64     597  135.90  151.03   27.56  112.70

# Profile
# Rank Query ID           Response time      Calls R/Call   V/M   Item
# ==== ================== ================== ===== ======== ===== ========
#    1 0xF1132168DB0BFC57 3905525.5745 72.6% 25586 152.6431 61.61 UPDATE connectinfo
#    2 0xD4B317E755A0ABD7 1344378.8719 25.0% 32791  40.9984 30... SELECT connectinfo
#    3 0xE23849EE6FB19DAE   66663.3148  1.2%   842  79.1726 62.99 INSERT errormessage
...

# Query 1: 7.52 QPS, 1.15kx concurrency, ID 0xF1132168DB0BFC57 at byte 16243195
# This item is included in the report because it matches --limit.
# Scores: V/M = 61.61
# Time range: 2015-09-24 01:03:17 to 02:00:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         42   25586
# Exec time     72 3905526s    113s   2720s    153s    271s     97s    124s
# Lock time     98 3168077s    109s    312s    124s    118s     14s    118s
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0   1.33M       0   4.80k   54.39       0  504.65       0
# Query size    48   3.78M     149     157  154.94  151.03    0.52  151.03
# String:
# Hosts        192.168.10.147 (2724/10%)... 9 more
# Users        gate
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS LIKE 'connectinfo'\G
#    SHOW CREATE TABLE `connectinfo`\G
update connectinfo set DeviceTag='10705BDDCD000000', ConnectIPAddress='115.231.63.78', UpdateDatetime=now() where ID=6912 and AppType=0\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  DeviceTag='10705BDDCD000000', ConnectIPAddress='115.231.63.78', UpdateDatetime=now() from connectinfo where  ID=6912 and AppType=0\G

PS:这个问题解决后没几天,数据库又出问题,以为又是慢SQL什么的,结果是交换机网口问题。。。数据传输太慢,导致主从不同步。。。

参考链接:
mysql 慢日志分析
慢日志按时间截取
MySQL 之 slow log
MySQL优化—工欲善其事,必先利其器(2)
日志常用统计技巧
性能优化之MySQL优化(一)
mysqlsla的安装与使用
安装DBI组件。 Can’t locate DBI.pm in @INC-mysql接口
Issue 12: Can’t locate Time/HiRes.pm
打开MySQL的慢查询记录
“Can’t locate ExtUtils/MakeMaker.pm” while compile git
analysing slow MySQL queries with pt-query-digest
mysqldump备份原理
mysqldump –single-transaction, yet update queries are waiting for the backup
mysql 利用binlog增量备份,还原实例
MySQl备份恢复策略(完全+增量备份策略)