--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: TABLE OBJECT_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: FILE OBJECT_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: 0 CREATED_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