一.数据库性能评测关键指标
1.IOPS：每秒处理的IO请求次数，这跟磁盘硬件相关，DBA不能左右，但推荐使用SSD。
2.QPS：每秒查询次数，可以使用show status或mysqladmin extended-status命令来查看QPS值，如下所示。
mysql> show global status like 'Questions';--QPS=Questions/Uptime（show status like 'Uptime'）这是一个全局的平均值
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions | 80 |
+---------------+-------+
也可以自动获取这个，每隔1S获取一次，使用mysqladmin -r -i参数来实现，还可以同时获取每秒查询，更新的次数，如下所示。
root@zhumuxian-machine:/# mysqladmin -u root -p extended-status -r -i 1 | grep -E 'Questions|Com_select|Com_update'
Enter password:
| Com_select | 1 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Questions | 81 |
--这个命令会每隔一秒获取当前的Questions,Com_select,Com_update的值，并且将当前的输出值减去上次获取的值。grep命令的E参数指定正则表达式
使用mysqlslap(系统自带压力测试工具)命令来模拟多个并发客户端访问Mysql服务，接着查看QPS值（不要关闭上面会话中执行的mysqladmin）
root@zhumuxian-machine:/home/zhumuxian# mysqlslap --no-defaults -p --query='select * from test.stu' --number-of-queries=100000 -c 10 -i 10 --create-schema=test --模拟在test库下，10个客户端同时连接mysql服务器，总计进行100万次查询
| Questions | 3305 |
| Questions | 45405 |
| Questions | 40648 |
| Questions | 27976 |
| Questions | 41340 |
| Questions | 40961 |
| Questions | 35475 |
| Questions | 41397 |
| Questions | 38478 |
由上面的结果可以看出QPS还是可以的，毕竟我的机子才2G内存2.0GHZ CPU啊。其实这么高的QPS，是因为stu表的数据才几条而已，查询都是在查询缓存里面了，不过还可以使用mysqlslap命令的其它参数来自动生成SQL语句，然后再查询测试，如下所示。
root@zhumuxian-machine:/home/zhumuxian# mysqlslap --no-defaults -p --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number=100000 --auto-generate-sql-unique-query-number=10000 -c 20 --commit=10000 --create-schema=test
--模拟20个客户端连接，总计执行200万次查询
| Questions | 27 |
| Questions | 21 |
| Questions | 748 |
| Questions | 2715 |
| Questions | 1653 |
| Questions | 26013 |
| Questions | 24173 |
| Questions | 24295 |
| Questions | 21250 |
| Questions | 15643 |
3.TPS：每秒事务数，官方没有给出TPS参数，但一般都是使用以下公式进行计算的：
TPS = (Com_commit+Com_rollback)/seconds
--Seconds是由我们自己定义的，如果使用Uptime就是MYSQL该生命周期的一个平均值
使用mysqladmin和mysqlslap组合来计算和查看TPS，如下所示。
root@zhumuxian-machine:/home/zhumuxian# mysqlslap --no-defaults -p --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number=10000 --auto-generate-sql-unique-query-number=1000 -c 10 --commit=1 --create-schema=test
--模拟进行了10万次提交
Com_commit | 32 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_commit | 190 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_commit | 192 |
二.TPCC测试
TPCC是一套基准测试程序，由TPC推出的。我使用的是TPCC-MYSQL ，由Percona基于TPCC开发出来的，是模拟一个电商业务，安装步骤如下。
--因为tpcc-mysql源码放在Bazaar上，它是一个分布式的版本控制系统，需要先安装bzr命令
apt-get install bzr
--下载源码
bzr branch lp:~percona-dev/perconatools/tpcc-mysql
--编译
cd tpcc-mysql
make
--如果这时候出现/bin/sh: 1: mysql_config: not found错误，可以使用apt-get install libmysqlclient-dev解决，接着make
--如果又出现/usr/bin/ld: cannot find -lz错误，安装apt-get install zlib1g-dev即可，继续make，成功之后在TPCC-MYSQ目录下会生成两个可执行文件，
root@zhumuxian-machine:/home/zhumuxian/tpcc-mysql# ls tpcc*
tpcc_load tpcc_start
接着按照说明文档创建数据库和导入预定脚本，如下所示。
--创建tpcc_test库
root@zhumuxian-machine:/home/zhumuxian/tpcc-mysql# mysqladmin -p create tpcc_test
--导入创建表SQL文件
root@zhumuxian-machine:/home/zhumuxian/tpcc-mysql# mysql -p tpcc < create_table.sql
--导入创建索引SQL文件
root@zhumuxian-machine:/home/zhumuxian/tpcc-mysql# mysql -p tpcc < add_fkey_idx.sql
--初始化数据,使用tpcc_load命令,具体用法可参考tpcc_load --help
root@zhumuxian-machine:/# /home/zhumuxian/tpcc-mysql/tpcc_load 127.0.0.1 tpcc_test root 123456 10
*************************************
*** ###easy### TPC-C Data Loader ***
*************************************
<Parameters>
[server]: 127.0.0.1
[port]: 3306
[DBname]: tpcc_test
[user]: root
[pass]: 123456
[warehouse]: 10
TPCC Data Load Started...
Loading Item
---只截取部分信息，太多了--
使用tpcc_start进行测试，如下：
root@zhumuxian-machine:/# /home/zhumuxian/tpcc-mysql/tpcc_start -h localhost -d tpcc_test -u root -p 123456 -w 10 -c 10 -r 100 -l 200 -f /home/zhumuxian/tpcc_test.log
--注释：-w(warehouse) -c(connections) -r(warntime预热时间) -l(run time)
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value 'localhost'
option d with value 'tpcc_test'
option u with value 'root'
option p with value ''
option w with value ''
option c with value ''
option r with value ''
option l with value ''
option f with value '/home/zhumuxian/tpcc_test.log'
<Parameters>
[server]: localhost
[port]: 3306
[DBname]: tpcc_test
[user]: root
[pass]: 123456
[warehouse]: 10
[connection]: 10
[rampup]: 100 (sec.)
[measure]: 200 (sec.) RAMP-UP TIME.(100 sec.) --只截取部分信息---
测试结果信息输出如下，截取部分：
10, 15(7):7.971|8.304, 22(0):1.602|1.693, 3(0):0.741|0.764, 2(0):9.421|9.553, 2(2):19.999|27.620
20, 25(17):8.037|8.375, 22(0):1.405|1.620, 2(0):0.518|0.574, 1(0):0.000|6.744, 3(3):19.999|25.810
30, 16(9):7.792|7.855, 17(0):1.479|1.643, 2(0):0.420|0.655, 3(0):7.372|7.753, 1(1):0.000|24.702
--这个结果以逗号分开，分为6项，依次为操作时间，创建订单，支付订单，查询订单，发货以及查询库存。
--每一项都有4个属性值，分别为成功执行的事务数，延迟执行的事务数，90%事务的响应时间，最大响应时间
--拿第一条分析，创建订单，共操作15次，延迟7次，90%事务时间7.971S，最大响应时间8.304S
---------------------各线程数据量汇总-------------------------
<Raw Results>
[] sc:256 lt:288 rt:0 fl:0 --sc 成功数量 lt 延迟数量 rt 重试数量 fl 失败数量
[] sc:547 lt:0 rt:0 fl:0
[] sc:55 lt:0 rt:0 fl:0
[] sc:53 lt:0 rt:0 fl:0
[] sc:12 lt:44 rt:0 fl:0
in 200 sec.
--------------------不同业务事务数占比-----------------------
[transaction percentage]
Payment: 43.59% (>=43.0%) [OK]
Order-Status: 4.38% (>= 4.0%) [OK]
Delivery: 4.22% (>= 4.0%) [OK]
Stock-Level: 4.46% (>= 4.0%) [OK]
---------------------TmpC指标----------------------
<TpmC>
163.200 TpmC --每分钟处理的事务数
三.MySQL SERVER参数配置优化
MySQL实例是由一组后台线程，一些内存块和若干个服务线程组成。默认情况下，mysql后台有八个线程，1个主线程，4个IO线程，1个锁线程，1个错误监控线程，1个PURGE线程，可以通过show engine innodb status查看这些线程的状态。
1.调整连接相关参数
调整max_connections，提高并发连接，根据mysql服务器的配置和性能来设置，在linux平台下，设置为500-1000问题不大，默认值为151。设置方法如下：
mysql> set global max_connections=1000; --临时设置
Query OK, 0 rows affected (0.06 sec)
------在my.cnf配置文件中添加----
【mysqld】
max_connections = 1000
调整max_connect_errors，增大允许连接不成功的最大尝试次数，如果尝试连接的错误次数大于这个变量的值，mysql服务将拒绝新的连接，除非在会话中执行flush hosts或者mysqladmin flush-host，但这样的代价比较大，因此这个参数值一般要设置大点，建议为10万级以上，系统默认为100。设置方法与上面max_connections设置类似。
调整back_log，连接请求队列中存放的最大连接请求数量，默认值为50+（max_connections/5），如果短时间内有大量的连接请求，可以适当增加该参数的值。
禁止mysql服务的逆向解析SKIP-NAME-RESOLVE，默认为禁止。
2.调整文件相关参数
调整SYNC_BINLOG，该参数指定同步二进制日志到磁盘的频率，默认为0，由自身的缓存机制决定何时同步。设置为1，性能最低，安全最高，即提交一个事务同步一次。
调整EXPIRE_LOG_DAYS，该参数指定二进制日志的生命周期，超过了这个时间，将会自动删除二进制日志，默认为0，即从不删除二进制日志，须手动清理。一般情况下，该值设置为1-2周。设置方法都一样，在my.cnf文件中添加该参数并指定值即可。
调整MAX_BINLOG_SIZE，该参数指定二进制日志文件的大小，默认为1G。
3.调整缓存相关参数
binlog_cache_size：指定二进制日志事务缓冲区的大小，默认值为32KB，最好不要超过64MB，8M-16M即可满足绝大多数场景(不是本人得出)。
max_binlog_cache_size：指定二进制日志事务缓存区能够使用的最大内存大小，建议设置为binlog_cache_size的两倍即可。
binlog_stmt_cache_size：指定二进制日志非事务缓存区的大小，默认大小也为32KB，意见为binlog_cache_size一样。
thread_cache_size：指定缓存的线程数量，建议值300-500。
query_cache_size：指定用于缓存查询结果集的内存区大小，默认为1MB，最好不要超过256MB。
query_cache_limit：控制查询缓存，能够缓存单条SQL生成的最大结果集，默认值为1M，采用默认值就好。
query_cache_type：0 不使用查询缓存 1 缓存除SELECT SQL_NO_CACHE外的查询结果 2 只缓存SELECT SQL_CACHE的查询结果。
sort_buffer_size：指定单个session内存排序区的大小，默认为256KB，一般设置为1-4MB即可。
max_heap_table_size：指定内存表，即memory存储引擎表，最大可以占用内存的大小，默认为16MB。
4.InnoDB内存优化
innodb_buffer_pool_szie：指定InnoDB引擎专用缓存区大小，用来存储表对象数据和索引信息，默认为128MB，当然这个值越大越好，在OS和内存允许的情况下。
查看innodb buffer pool的使用情况，如下所示。
mysql> show global status like 'innodb_buffer%';
+---------------------------------------+-------------+
| Variable_name | Value |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | |
| Innodb_buffer_pool_bytes_data | |
| Innodb_buffer_pool_pages_dirty | |
| Innodb_buffer_pool_bytes_dirty | |
| Innodb_buffer_pool_pages_flushed | |
| Innodb_buffer_pool_pages_free | |
| Innodb_buffer_pool_pages_misc | |
| Innodb_buffer_pool_pages_total | |
| Innodb_buffer_pool_read_ahead_rnd | |
| Innodb_buffer_pool_read_ahead | |
| Innodb_buffer_pool_read_ahead_evicted | |
| Innodb_buffer_pool_read_requests | |
| Innodb_buffer_pool_reads | |
| Innodb_buffer_pool_wait_free | |
| Innodb_buffer_pool_write_requests | |
+---------------------------------------+-------------+
-----------------InnoDB缓存命中率计算公式------------
(-(innodb_buffer_pool_reads/innodb_buffer_pool_read_request))*
该值越小，说明命中率越低，是时候扩充内存，增加innodb_buffer_pool_size的大小
innodb_buffer_pool_instances：指定InnoDB缓存区分为几个区域来使用，默认为8。
innodb_thread_concurrency：指定InnoDB内部最大线程数，默认为0，由InnoDB自行管理。
innodb_flush_method：指定InnoDB刷新数据文件和日志文件的方式，默认调用fsync()函数。
innodb_log_buffer_size：指定InnoDB日志缓存区的大小。默认为8MB。
innodb_flush_log_at_trx_commit：指定InnoDB刷新日志缓存区中的数据到文件的方式，默认值为1，即只要提交事务或回滚事务，就将缓存区的数据刷新到日志文件中，并同步到文件系统中，若值为0，则每秒向日志文件写入一次并写入磁盘，若值为2，遇到事务提交时，将数据写入日志文件中，但并不立即触发文件系统的同步写入。
innodb_flush_log_at_timeout ：指定每隔N秒刷新日志。
5.MyISAM内存优化
key_buffer_size：指定MyISAM索引缓存区大小，默认为8MB。
key_cache_block_size ：指定索引缓存的块大小，默认为1KB。
read_buffer_size：指定顺序读时数据缓存的大小，默认为128KB，如果经常要进行顺序读取时，可以适当加大该值。
read_rnd_buffer_size ：指定随机读取时数据缓存区的大小。默认为256KB。
四.慢查询日志分析
1.使用mysql自带的mysqldumpslow工具分析，如下所示。
root@zhumuxian-machine:/data/mysql# mysqldumpslow mysql-slow.log Reading mysql slow query log from mysql-slow.log
Count: 2 Time=2.49s (4s) Lock=0.00s (0s) Rows=50.0 (100), root[root]@localhost
select * from customer where c_id=N
该工具提供的参数很少，最核心的参数就是-s，用来排序的，-r按照规则倒叙输出，-t用来控制输出的SQL数量。
2.使用第三方工具pt-query-digest进行分析
pt-query-digest来自于Percona Toolkit，安装方法如下：
root@zhumuxian-machine:/home/zhumuxian# wget percona.com/get/percona-toolkit.deb -----下载
root@zhumuxian-machine:/home/zhumuxian# dpkg -i percona-toolkit_2.2.14_all.deb -----安装
root@zhumuxian-machine:/home/zhumuxian# whereis pt-query-digest
pt-query-digest: /usr/bin/pt-query-digest /usr/bin/X11/pt-query-digest /usr/share/man/man1/pt-query-digest.1p.gz ----查看安装是否成功
使用pt-query-digest分析慢查询日志，如下所示：
root@zhumuxian-machine:/data/mysql# pt-query-digest mysql-slow.log
输出结果如下：
# 360ms user time, 0 system time, 17.94M rss, 25.71M vsz
# Current date: Sun Apr 26 11:43:57 2015
# Hostname: zhumuxian-machine
# Files: mysql-slow.log
# Overall: 2 total, 1 unique, 0.02 QPS, 0.04x concurrency ________________
# Time range: 2015-04-25 17:02:26 to 17:04:17
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 5s 1s 4s 2s 4s 2s 2s
# Lock time 508us 216us 292us 254us 292us 53us 254us
# Rows sent 100 0 100 50 100 70.71 50
# Rows examine 585.94k 292.97k 292.97k 292.97k 292.97k 0 292.97k
# Query size 78 38 40 39 40 1.41 39
//以上时概要信息，包括了总共有多少条查询，多少条不同的语句，QPS等信息
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0x4E195A4D8816B149 4.9706 100.0% 2 2.4853 1.59 SELECT customer
//对各类查询的执行情况进行分析，结果按总执行时长，从大到小排序
# Query 1: 0.02 QPS, 0.04x concurrency, ID 0x4E195A4D8816B149 at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.59
# Time range: 2015-04-25 17:02:26 to 17:04:17
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 100 2
# Exec time 100 5s 1s 4s 2s 4s 2s 2s
# Lock time 100 508us 216us 292us 254us 292us 53us 254us
# Rows sent 100 100 0 100 50 100 70.71 50
# Rows examine 100 585.94k 292.97k 292.97k 292.97k 292.97k 0 292.97k
# Query size 100 78 38 40 39 40 1.41 39
//列出1号查询详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计
如果要分析某个时间段产生的慢查询日志，可以使用--since和--until参数。
root@zhumuxian-machine:/data/mysql# pt-query-digest --since 2d mysql-slow.log
----分析2天之内产生的慢查询日志
----since 值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。
将分析结果导入其它文件中，不显示在终端上
root@zhumuxian-machine:/data/mysql# pt-query-digest --since 2d mysql-slow.log > slow.txt
分析2015-02-22到2015-03-22之间产生的慢查询
root@zhumuxian-machine:/data/mysql# pt-query-digest --since '2015-02-22' --until '2015-03-22' mysql-slow.log
使用--filter参数过滤条件只分析特定的慢查询
root@zhumuxian-machine:/data/mysql# pt-query-digest --filter '($event->{user}||"")=~m/root/i' mysql-slow.log
-----只分析root用户下的慢查询root@zhumuxian-machine:/data/mysql# pt-query-digest --filter '($event->{fingerprint}||"")=~m/select/i' mysql-slow.log
-----只分析SELECT语句的慢查询还有很多的参数可以使用，有用到的同学可自行参考官方文档。
五.获取当前MySQL服务所有的连接线程列表
使用show [full] processlist命令可以获取当前所有的线程列表，如下所示：
mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+-------+------------------+
| 135 | root | localhost | NULL | Query | 0 | init | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
使用kill命令，可以杀掉正在运行线程，如下所示：
mysql> kill 137;
Query OK, 0 rows affected (0.00 sec)
也可以不杀掉线程，只停止它的查询，
mysql> show processlist;
+-----+------+-----------+------+---------+------+------------------------------+-----------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+------------------------------+-----------------------------------------------+
| 135 | root | localhost | test | Query | 0 | init | show processlist |
| 139 | root | localhost | test | Query | 7 | Waiting for table level lock | insert into t1 values(100,4010404,'dsdsffef') |
+-----+------+-----------+------+---------+------+------------------------------+-----------------------------------------------+
2 rows in set (0.00 sec) mysql> kill query 139;
Query OK, 0 rows affected (0.00 sec) mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+-------+------------------+
| 135 | root | localhost | test | Query | 0 | init | show processlist |
| 139 | root | localhost | test | Sleep | 99 | | NULL |
+-----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)
六.应用层优化
1.尽量使用连接池，因为创建新的连接代价比较大，可以预先创建好适当的连接保存起来，应用需要时可以直接分配，当应用释放连接后，该连接返回给连接池。
2.尽量避免对同一数据做重复检索，比如你现在需要用户的年龄，就直接查询年龄，等会儿你有要性别，又去检索性别，其实都可以一次性获得，不要做重复的查询。
3.使用查询缓存，对相同的数据做检索时，mysql会直接从缓存中获取，查看是否开启缓存，如下所示：
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
4.在应用层增加CACHE层，比如将经常用到的数据放到文本中，或者弄个二级数据库，同步机制自己制定。
5.负载均衡，利用复制特性，将查询操作放到slave端进行，减轻主库的压力。