本文共 4614 字，大约阅读时间需要 15 分钟。
--MySQL 5.5新增一个存储引擎：命名PERFORMANCE_SCHEMA ,主要用于收集数据库服务器性能参数performance_schema提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）Performance的开启很简单，在my.cnf中[mysqld]加入performanc_schema，检查性能数据库是否启动的命令:SHOW VARIABLES LIKE 'performance_schema';若是返回的 值为ON，则说明性能数据库正常开启状态。--Performance_timers指定mysql服务可用的监控周期，CYCLE表示按每秒检测2603393034次mysql> SELECT * FROM performance_timers;+-------------+-----------------+------------------+----------------+| TIMER_NAME | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |+-------------+-----------------+------------------+----------------+| CYCLE | 3389398009 | 1 | 38 || NANOSECOND | 1000000000 | 1 | 98 || MICROSECOND | 1000000 | 1 | 106 || MILLISECOND | 1000 | 1000 | 96 || TICK | 105 | 1 | 491 |+-------------+-----------------+------------------+----------------+--设置哪些事件能够被收集mysql> SELECT * FROM setup_consumers;+--------------------------------+---------+| NAME | ENABLED |+--------------------------------+---------+| events_stages_current | NO || events_stages_history | NO || events_stages_history_long | NO || events_statements_current | YES || events_statements_history | NO || events_statements_history_long | NO || events_waits_current | YES || events_waits_history | NO || events_waits_history_long | NO || global_instrumentation | YES || thread_instrumentation | YES || statements_digest | YES |+--------------------------------+---------+--查看系统正在等待的资源，如下OBJECT_NAME: /var/lib/mysql/test/t.ibd说明其在等待一个数据文件mysql> select * from events_waits_current\G;*************************** 2. row *************************** THREAD_ID: 25 EVENT_ID: 48565172 END_EVENT_ID: NULL EVENT_NAME: wait/io/table/sql/handler SOURCE: handler.cc:2666 TIMER_START: 1331820782467260 TIMER_END: NULL TIMER_WAIT: NULL SPINS: NULL OBJECT_SCHEMA: test OBJECT_NAME: t INDEX_NAME: idx_t OBJECT_TYPE: TABLEOBJECT_INSTANCE_BEGIN: 139955292366880 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL OPERATION: fetch NUMBER_OF_BYTES: NULL FLAGS: NULL*************************** 3. row *************************** THREAD_ID: 25 EVENT_ID: 48565173 END_EVENT_ID: NULL EVENT_NAME: wait/io/file/innodb/innodb_data_file SOURCE: fil0fil.cc:5625 TIMER_START: 1331820783520410 TIMER_END: NULL TIMER_WAIT: NULL SPINS: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: /var/lib/mysql/test/t.ibd INDEX_NAME: NULL OBJECT_TYPE: FILEOBJECT_INSTANCE_BEGIN: 139955747255232 NESTING_EVENT_ID: 48565172 NESTING_EVENT_TYPE: WAIT OPERATION: read NUMBER_OF_BYTES: 16384 FLAGS: NULL --查看系统中正在运行的sql，可发现其在做一个统计 mysql> select * from events_statements_current\G;*************************** 2. row *************************** THREAD_ID: 25 EVENT_ID: 40293117 END_EVENT_ID: 60439383 EVENT_NAME: statement/sql/select SOURCE: mysqld.cc:1157 TIMER_START: 1315332597974000 TIMER_END: 1352782771644000 TIMER_WAIT: 37450173670000 LOCK_TIME: 142000000 SQL_TEXT: select count(*) from t DIGEST: 2f0c57df096166b233e0fc64d317cc68 DIGEST_TEXT: SELECT COUNT ( * ) FROM `t` CURRENT_SCHEMA: test OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: NULL MYSQL_ERRNO: 0 RETURNED_SQLSTATE: NULL MESSAGE_TEXT: NULL ERRORS: 0 WARNINGS: 0 ROWS_AFFECTED: 0 ROWS_SENT: 1 ROWS_EXAMINED: 0CREATED_TMP_DISK_TABLES: 0 CREATED_TMP_TABLES: 0 SELECT_FULL_JOIN: 0 SELECT_FULL_RANGE_JOIN: 0 SELECT_RANGE: 0 SELECT_RANGE_CHECK: 0 SELECT_SCAN: 1 SORT_MERGE_PASSES: 0 SORT_RANGE: 0 SORT_ROWS: 0 SORT_SCAN: 0 NO_INDEX_USED: 1 NO_GOOD_INDEX_USED: 0 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL
转载地址：http://mmxna.baihongyu.com/