mysql performance-m6米乐安卓版下载

mysql的 performance_schema是一个监视mysql服务器执行的最基本特性。提供了一种在运行时检查mysql内部执行的方法：监视事件，收集事件把数据保存到内存表中（不使用持久的磁盘存储），通过这些数据,更加积极主动发现问题、解决问题。

对于检测点的信息performance_schema提供配置信息表，允许更改监视配置。使用表而不是单独的变量作为设置信息，提供了更高度的灵活性。
目前提供5个配置信息：

mysql> show tables like 'setup%';
 --------------------------------------- 
| tables_in_performance_schema (setup%) |
 --------------------------------------- 
| setup_actors                          |
| setup_consumers                       |
| setup_instruments                     |
| setup_objects                         |
| setup_threads                         |
 --------------------------------------- 
5 rows in set (0.01 sec)

• setup_actors:如何初始化监视新的前台线程
• setup_consumers:事件信息可以发送和存储到的目的地
• setup_instruments:可以收集事件的被检测对象的类
• setup_objects:应该监视哪些对象
• setup_threads:检测线程的名称和属性

表包含那些用户的前台服务线程（与客户端连接相关联的线程）启用监视和历史事件日志记录的信息。
就是说在性能模式下，与setup_actors表的行信息进行匹配用户线程，才能监视和事件记录。
因此默认情况下，所有前台线程都会启用监视和历史事件收集，此表的最大行100行（默认）。要更改表大小，在服务启动时修改performance_schema_setup_actors_size系统变量。

mysql> select * from performance_schem.setup_actors;
 ------ ------ ------ --------- --------- 
| host | user | role | enabled | history |
 ------ ------ ------ --------- --------- 
| %    | %    | %    | yes     | yes     |
 ------ ------ ------ --------- --------- 
1 row in set (0.01 sec)
mysql> insert into setup_actors(host,user,role,enabled,history) 
               values('127.0.0.1','dba','selecct','yes','yes');
query ok, 1 row affected (0.00 sec)
mysql> select * from setup_actors;
 ----------- ------ -------- --------- --------- 
| host      | user | role   | enabled | history |
 ----------- ------ -------- --------- --------- 
| %         | %    | %      | yes     | yes     |
| 127.0.0.1 | dba  | select | yes     | yes     |
 ----------- ------ -------- --------- --------- 
2 rows in set (0.00 sec)

备注：目前role末使用，同时setup_actors表允许使用truncate table 或 delete语句删除行。

具体消费记录信息，包含事件stage，statements，transaction，wait等方面记录消费信息。其中global_instrumentation和thread_instrumention是必须打开的前提，要不其他消费无法记录。

mysql> select * from performance_schema.setup_consumers;
 ---------------------------------- --------- 
| name                             | enabled |
 ---------------------------------- --------- 
| events_stages_current            | no      |
| events_stages_history            | no      |
| events_stages_history_long       | no      |
| events_statements_cpu            | no      |
| events_statements_current        | yes     |
| events_statements_history        | yes     |
| events_statements_history_long   | no      |
| events_transactions_current      | yes     |
| events_transactions_history      | yes     |
| events_transactions_history_long | no      |
| events_waits_current             | no      |
| events_waits_history             | no      |
| events_waits_history_long        | no      |
| global_instrumentation           | yes     |
| thread_instrumentation           | yes     |
| statements_digest                | yes     |
 ---------------------------------- --------- 
16 rows in set (0.01 sec)
#通过update进行修改
mysql> update performance_schema.setup_consumers
    -> set enabled = 'no'
    -> where name like '%history%';
query ok, 2 rows affected (0.00 sec)
rows matched: 8  changed: 2  warnings: 0

备注：
对setup_consumers表的修改会立即影响监视。同事表不允许使用truncate table。

setup_instruments表列出了可以收集事件的对象的类。目前包含1259个默认：788打开，471关闭。
每个对象表提供了一行。

mysql> select name,enabled from performance_schema.setup_instruments where enabled='no';
 ---------------------------------------------------------------------------- --------- 
| name                                                                       | enabled |
 ---------------------------------------------------------------------------- --------- 
| wait/synch/mutex/pfs/lock_pfs_share_list                                   | no      | 
| wait/synch/prlock/sql/mdl_lock::rwlock                                     | no      |   
| wait/synch/sxlock/innodb/undo_spaces_lock                                  | no      |
| wait/synch/cond/sql/page::cond                                             | no      |
| wait/synch/cond/sql/tc_log_mmap::cond_active                               | no      |
| stage/sql/freeing items                                                    | no      |
| stage/sql/fulltext initialization                                          | no      |
| stage/sql/opening tables                                                   | no      |
| stage/sql/waiting for gtid to be committed                                 | no      |
| stage/semisync/reading semi-sync ack from slave                            | no      |
。。。
 ---------------------------------------------------------------------------- --------- 
471 rows in set (0.00 sec)

mysql> select name,enabled from performance_schema.setup_instruments where enabled='yes';
 -------------------------------------------------------------------------------- --------- 
| name                                                                           | enabled |
 -------------------------------------------------------------------------------- --------- 
| wait/io/file/sql/binlog                                                        | yes     |
| wait/io/file/sql/binlog_cache                                                  | yes     |
| transaction                                                                    | yes     |
| memory/performance_schema/mutex_instances                                      | yes     |
| memory/performance_schema/rwlock_instances                                     | yes     |
| memory/performance_schema/cond_instances 
| memory/sql/servers_cache                                                       | yes     |
| memory/sql/relay_log_info::mta_coor                                            | yes     |
| error                                                                          | yes     |
。。。
 -------------------------------------------------------------------------------- --------- 

在ysql 8.0.27版本迭代中，这些对象操作也可以对应os层的线程

shell# ps -c mysqld h -o "pid tid cmd comm"
    pid     tid cmd                         command
 128626  128626 /opt/idc/mysql8.0/bin/mysql mysqld
 128626  128629 /opt/idc/mysql8.0/bin/mysql ib_io_ibuf
 128626  128630 /opt/idc/mysql8.0/bin/mysql ib_io_rd-1
 128626  128631 /opt/idc/mysql8.0/bin/mysql ib_io_rd-2
 128626  128634 /opt/idc/mysql8.0/bin/mysql ib_io_wr-1
 128626  128635 /opt/idc/mysql8.0/bin/mysql ib_io_wr-2
 128626  128638 /opt/idc/mysql8.0/bin/mysql ib_pg_flush_co
 128626  128639 /opt/idc/mysql8.0/bin/mysql ib_log_checkpt
 128626  128640 /opt/idc/mysql8.0/bin/mysql ib_log_fl_notif
 128626  128641 /opt/idc/mysql8.0/bin/mysql ib_log_flush
 128626  128642 /opt/idc/mysql8.0/bin/mysql ib_log_wr_notif
 128626  128643 /opt/idc/mysql8.0/bin/mysql ib_log_writer
 128626  128644 /opt/idc/mysql8.0/bin/mysql ib_log_files_g
 128626  128645 /opt/idc/mysql8.0/bin/mysql ib_srv_lock_to
 128626  128646 /opt/idc/mysql8.0/bin/mysql ib_srv_err_mon
 128626  128647 /opt/idc/mysql8.0/bin/mysql ib_srv_mon
 128626  128648 /opt/idc/mysql8.0/bin/mysql ib_buf_resize
 128626  128649 /opt/idc/mysql8.0/bin/mysql ib_src_main
 128626  128650 /opt/idc/mysql8.0/bin/mysql ib_dict_stats
 128626  128651 /opt/idc/mysql8.0/bin/mysql ib_fts_opt
 128626  128652 /opt/idc/mysql8.0/bin/mysql xpl_worker-1
 128626  128653 /opt/idc/mysql8.0/bin/mysql xpl_worker-2
 128626  128654 /opt/idc/mysql8.0/bin/mysql xpl_accept-1
 128626  128658 /opt/idc/mysql8.0/bin/mysql ib_buf_dump
 128626  128659 /opt/idc/mysql8.0/bin/mysql ib_clone_gtid
 128626  128660 /opt/idc/mysql8.0/bin/mysql ib_srv_purge
 128626  128661 /opt/idc/mysql8.0/bin/mysql ib_srv_wkr-1
 128626  128662 /opt/idc/mysql8.0/bin/mysql ib_srv_wkr-2
 128626  128663 /opt/idc/mysql8.0/bin/mysql ib_srv_wkr-3
 128626  128664 /opt/idc/mysql8.0/bin/mysql sig_handler
 128626  128666 /opt/idc/mysql8.0/bin/mysql xpl_accept-2
 128626  128667 /opt/idc/mysql8.0/bin/mysql gtid_zip
 128626  128668 /opt/idc/mysql8.0/bin/mysql con_admin-0
 128626  128669 /opt/idc/mysql8.0/bin/mysql connection

控制performance_schema是否监视特定对象。包含event，runction，procedure，table，trigger对象。

mysql> select * from performance_schema.setup_objects;
 ------------- -------------------- ------------- --------- ------- 
| object_type | object_schema      | object_name | enabled | timed |
 ------------- -------------------- ------------- --------- ------- 
| event       | mysql              | %           | no      | no    |
| event       | performance_schema | %           | no      | no    |
| event       | information_schema | %           | no      | no    |
| event       | %                  | %           | yes     | yes   |
| function    | mysql              | %           | no      | no    |
| function    | performance_schema | %           | no      | no    |
| function    | information_schema | %           | no      | no    |
| function    | %                  | %           | yes     | yes   |
| procedure   | mysql              | %           | no      | no    |
| procedure   | performance_schema | %           | no      | no    |
| procedure   | information_schema | %           | no      | no    |
| procedure   | %                  | %           | yes     | yes   |
| table       | mysql              | %           | no      | no    |
| table       | performance_schema | %           | no      | no    |
| table       | information_schema | %           | no      | no    |
| table       | %                  | %           | yes     | yes   |
| trigger     | mysql              | %           | no      | no    |
| trigger     | performance_schema | %           | no      | no    |
| trigger     | information_schema | %           | no      | no    |
| trigger     | %                  | %           | yes     | yes   |
 ------------- -------------------- ------------- --------- ------- 
20 rows in set (0.00 sec)

备注：
允许对setup_objects表执行truncate table和delete。

对于mysql内部线程的监控指标。

 mysql> select name,enabled,history ,properties  from performance_schema.setup_threads;
 ----------------------------------------------------- --------- --------- ------------ 
| name                                                | enabled | history | properties |
 ----------------------------------------------------- --------- --------- ------------ 
| thread/performance_schema/setup                     | yes     | yes     | singleton  |
| thread/sql/bootstrap                                | yes     | yes     | singleton  |
| thread/sql/manager                                  | yes     | yes     | singleton  |
| thread/sql/main                                     | yes     | yes     | singleton  |
| thread/sql/one_connection                           | yes     | yes     | user       |
| thread/sql/signal_handler                           | yes     | yes     | singleton  |
| thread/sql/compress_gtid_table                      | yes     | yes     | singleton  |
| thread/sql/parser_service                           | yes     | yes     | singleton  |
| thread/sql/admin_interface                          | yes     | yes     | user       |
| thread/mysys/thread_timer_notifier                  | yes     | yes     | singleton  |
| thread/sql/event_scheduler                          | yes     | yes     | singleton  |
| thread/sql/event_worker                             | yes     | yes     |            |
| thread/innodb/log_archiver_thread                   | yes     | yes     | singleton  |
| thread/innodb/page_archiver_thread                  | yes     | yes     | singleton  |
| thread/innodb/buf_dump_thread                       | yes     | yes     | singleton  |
| thread/innodb/clone_ddl_thread                      | yes     | yes     | singleton  |
| thread/innodb/clone_gtid_thread                     | yes     | yes     | singleton  |
| thread/innodb/ddl_thread                            | yes     | yes     |            |
| thread/innodb/dict_stats_thread                     | yes     | yes     | singleton  |
| thread/innodb/io_ibuf_thread                        | yes     | yes     | singleton  |
| thread/innodb/io_read_thread                        | yes     | yes     |            |
| thread/innodb/io_write_thread                       | yes     | yes     |            |
| thread/innodb/buf_resize_thread                     | yes     | yes     | singleton  |
| thread/innodb/log_files_governor_thread             | yes     | yes     | singleton  |
| thread/innodb/log_writer_thread                     | yes     | yes     | singleton  |
| thread/innodb/log_checkpointer_thread               | yes     | yes     | singleton  |
| thread/innodb/log_flusher_thread                    | yes     | yes     | singleton  |
| thread/innodb/log_write_notifier_thread             | yes     | yes     | singleton  |
| thread/innodb/log_flush_notifier_thread             | yes     | yes     | singleton  |
| thread/innodb/recv_writer_thread                    | yes     | yes     | singleton  |
| thread/innodb/srv_error_monitor_thread              | yes     | yes     | singleton  |
| thread/innodb/srv_lock_timeout_thread               | yes     | yes     | singleton  |
| thread/innodb/srv_master_thread                     | yes     | yes     | singleton  |
| thread/innodb/srv_monitor_thread                    | yes     | yes     | singleton  |
| thread/innodb/srv_purge_thread                      | yes     | yes     | singleton  |
| thread/innodb/srv_worker_thread                     | yes     | yes     |            |
| thread/innodb/trx_recovery_rollback_thread          | yes     | yes     |            |
| thread/innodb/page_flush_thread                     | yes     | yes     |            |
| thread/innodb/page_flush_coordinator_thread         | yes     | yes     | singleton  |
| thread/innodb/fts_optimize_thread                   | yes     | yes     | singleton  |
| thread/innodb/fts_parallel_merge_thread             | yes     | yes     |            |
| thread/innodb/fts_parallel_tokenization_thread      | yes     | yes     |            |
| thread/innodb/srv_ts_alter_encrypt_thread           | yes     | yes     | singleton  |
| thread/innodb/parallel_read_thread                  | yes     | yes     |            |
| thread/innodb/parallel_rseg_init_thread             | yes     | yes     |            |
| thread/innodb/meb::redo_log_archive_consumer_thread | yes     | yes     | singleton  |
| thread/myisam/find_all_keys                         | yes     | yes     |            |
| thread/mysqlx/acceptor_network                      | yes     | yes     |            |
| thread/mysqlx/worker                                | yes     | yes     | user       |
| thread/semisync/ack_receiver                        | yes     | yes     | singleton  |
| thread/sql/replica_io                               | yes     | yes     |            |
| thread/sql/replica_sql                              | yes     | yes     |            |
| thread/sql/replica_worker                           | yes     | yes     |            |
| thread/sql/replica_monitor                          | yes     | yes     | singleton  |
 ----------------------------------------------------- --------- --------- ------------ 
54 rows in set (0.00 sec)

对于mysql的performance_schema特性来说，监视和收集是通过修改源代码来添加检测来实现的。与复制或事件调度程序等其他特性不同，performance_schema没有单独的线程，不会导致服务行为发生变化，即使performance_schema内部失败，服务代码的执行也会正常进行。特别是performance_schema表都有索引，这使得优化器可以访问执行计划，而不是全表扫描。

但还是要注意下资源占有问题（比如：内存，cpu等），按照目前经验performance_schema全部打影响性能比率还是比较大。所以基础配置打开就可以。

https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-setup-tables.html