PERFORMANCE_SCHEMA数据库-CSDN博客

--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