一.数据库性能评测关键指标
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 '123456' option w with value '10' option c with value '10' option r with value '100' option l with value '200' 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> [0] sc:256 lt:288 rt:0 fl:0 --sc 成功数量 lt 延迟数量 rt 重试数量 fl 失败数量 [1] sc:547 lt:0 rt:0 fl:0 [2] sc:55 lt:0 rt:0 fl:0 [3] sc:53 lt:0 rt:0 fl:0 [4] 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 | 447 | | Innodb_buffer_pool_bytes_data | 7323648 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 1 | | Innodb_buffer_pool_pages_free | 7745 | | Innodb_buffer_pool_pages_misc | 0 | | Innodb_buffer_pool_pages_total | 8192 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 2945 | | Innodb_buffer_pool_reads | 448 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 1 | +---------------------------------------+-------------+ -----------------InnoDB缓存命中率计算公式------------ (1-(innodb_buffer_pool_reads/innodb_buffer_pool_read_request))*100
该值越小，说明命中率越低，是时候扩充内存，增加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端进行，减轻主库的压力。