分类： 系统运维
2011-08-16 16:57:12
要想优化一条 Query，就须要清楚这条Query 的性能瓶颈到底在哪里，是消耗的 CPU 计算太多，还是需要的IO 操作太多？要想能够清楚地了解这些信息，在 MySQL 5.0 和 MySQL 5.1 正式版中已经非常容易做到，即通过 Query Profiler 功能。
MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条 Query 在整个执行过程中多种资源的消耗情况，如 CPU、IO、IPC、SWAP等，以及发生的 PAGE FAULTS、CONTEXT SWITCHE等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。下面看看 Query Profiler 的具体用法。
（1）通过执行”set profiling”命令，可以开启关闭 Query Profiler 功能。先开启 profiling 参数，如示例代码1-1所示：
root@localhost : (none) 10:53:11> SET profiling=1; Query OK, 0 rows affected (0.00 sec) |
（2）在开启 Query Profiler 功能之后，MySQL 就会自动记录所有执行的 Query 的profile 信息。下面执行 Query，如示例代码1-2所示：
root@localhost : test 07:43:18> SELECT status,count(*) -> FROM test_profiling GROUP BY status; +---------------- +-------------- + | status | count(*) | +---------------- +-------------- + | st_xxx1 | 27 | | st_xxx2 | 6666 | | st_xxx3 | 292887 | | st_xxx4 | 15 | +---------------- +-------------- + 5 rows in set (1.11 sec) |
（3）通过执行 “SHOW PROFILE” 命令获取当前系统中保存的多个 Query 的 profile 的概要信息，如示例代码1-3所示：
root@localhost : test 07:47:35> show profiles; +----------+------------+-----------------------------------------------------------+ | Query_ID | Duration |Query | +----------+------------+-----------------------------------------------------------+ | 1 | 0.00183100 |show databases | | 2 | 0.00007000 |SELECT DATABASE() | | 3 | 0.00099300 |desc test | | 4 | 0.00048800 |show tables | | 5 | 0.00430400 |desc test_profiling | | 6 | 1.90115800 |SELECT status,count(*) FROM test_profiling GROUP BY status | +----------+------------+-----------------------------------------------------------+ 3 rows in set (0.00 sec) |
（4）针对单个 Query 获取详细的 profile 信息。
在获取概要信息之后，就可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中详细的 profile 信息了，具体操作如示例代码1-4所示：
root@localhost : test 07:49:24> show profile cpu, block io for query 6; +--------------------- +---------- +---------- +----------- +-------------- +-------------- + | Status |Duration |CPU_user |CPU_system | Block_ops_in | Block_ops_out | +--------------------- +---------- +---------- +----------- +-------------- +-------------- + | starting | 0.000349 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Creating tmp table | 0.000035 | 0.000999 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Copying to tmp table | 1.900619 | 1.030844 | 0.197970 | 347 | 347 | | Sorting result | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | removing tmp table | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000029 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000001 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | +--------------------- +---------- +---------- +----------- +-------------- +-------------- + |
上面的例子获取了CPU 和 Block IO 的消耗，非常清晰，对于定位性能瓶颈非常适用。若希望得到其他的信息，都可以通过执行”SHOW PROFILE *** FOR QUERY n”来获取，各位朋友可以自行测试。
压力测试
mysqlslap --create-schema=IM --query="select work_company from work_experience where user_id='1010055' order by work_start_year desc ,work_start_month desc" --iterations=10000 -uroot -p -h 192.168.*.*
