mysql5.7.10 performance_schema_MySQL5.7-学习笔记(kylix)-2019.9.25-EnablePerformanceSchemaXXXEvent...

Performance

Schema Wait Event Tables

MySQL5.7默认没有开启等待事件收集，开启和关闭方式如下：

mysql> SELECT * FROM

performance_schema.setup_instruments

WHERE NAME LIKE

'wait/io/file/innodb%';

+--------------------------------------+---------+-------+

| NAME | ENABLED | TIMED |

+--------------------------------------+---------+-------+

|

wait/io/file/innodb/innodb_data_file | YES | YES |

|

wait/io/file/innodb/innodb_log_file |

YES |

YES |

|

wait/io/file/innodb/innodb_temp_file | YES | YES |

+--------------------------------------+---------+-------+

mysql> SELECT *

FROM performance_schema.setup_instruments

WHERE

NAME LIKE 'wait/io/socket/%';

+----------------------------------------+---------+-------+

| NAME | ENABLED | TIMED

|

+----------------------------------------+---------+-------+

|

wait/io/socket/sql/server_tcpip_socket | NO | NO |

|

wait/io/socket/sql/server_unix_socket |

NO |

NO |

|

wait/io/socket/sql/client_connection | NO | NO |

+----------------------------------------+---------+-------+

• Enable:

[mysqld]

performance-schema-instrument='wait/%=ON'

performance-schema-consumer-events-waits-current=ON

performance-schema-consumer-events-waits-history=ON

performance-schema-consumer-events-waits-history-long=ON

• Disable:

[mysqld]

performance-schema-instrument='wait/%=OFF'

performance-schema-consumer-events-waits-current=OFF

performance-schema-consumer-events-waits-history=OFF

performance-schema-consumer-events-waits-history-long=OFF

或者

• Enable:

UPDATE

performance_schema.setup_instruments

SET ENABLED =

'YES', TIMED = 'YES'

WHERE NAME =

'wait/%';

UPDATE

performance_schema.setup_consumers

SET ENABLED =

'YES'

WHERE NAME

LIKE 'events_waits%';

• Disable:

UPDATE

performance_schema.setup_instruments

SET ENABLED =

'NO', TIMED = 'NO'

WHERE NAME =

'wait/%';

UPDATE

performance_schema.setup_consumers

SET ENABLED =

'NO'

WHERE NAME

LIKE 'events_waits%';

Performance

Schema Stage Event Tables

MySQL5.7默认没有开启语句阶段收集，开启和关闭方式如下：

SELECT

*

FROM

performance_schema.setup_instruments

WHERE ENABLED='NO' AND NAME LIKE

"stage/%";

mysql> SELECT *

FROM

performance_schema.setup_consumers

WHERE NAME LIKE

'events_stages%';

+----------------------------+---------+

| NAME | ENABLED |

+----------------------------+---------+

|

events_stages_current | NO |

|

events_stages_history | NO |

| events_stages_history_long |

NO |

• Enable:

[mysqld]

performance-schema-instrument='stage/%=ON'

performance-schema-consumer-events-stages-current=ON

performance-schema-consumer-events-stages-history=ON

performance-schema-consumer-events-stages-history-long=ON

• Disable:

[mysqld]

performance-schema-instrument='stage/%=OFF'

performance-schema-consumer-events-stages-current=OFF

performance-schema-consumer-events-stages-history=OFF

performance-schema-consumer-events-stages-history-long=OFF

或者

• Enable:

UPDATE

performance_schema.setup_instruments

SET ENABLED =

'YES', TIMED = 'YES'

WHERE NAME =

'stage/%';

UPDATE

performance_schema.setup_consumers

SET ENABLED =

'YES'

WHERE NAME

LIKE 'events_stages%';

• Disable:

UPDATE

performance_schema.setup_instruments

SET ENABLED =

'NO', TIMED = 'NO'

WHERE NAME =

'stage/%';

UPDATE

performance_schema.setup_consumers

SET ENABLED =

'NO'

WHERE NAME

LIKE 'events_stages%';

Performance

Schema Transaction Tables

MySQL5.7默认没有开启事务收集，开启和关闭方式如下：

mysql> SELECT *

FROM

performance_schema.setup_instruments

WHERE NAME = 'transaction';

+-------------+---------+-------+

| NAME | ENABLED

| TIMED |

+-------------+---------+-------+

| transaction | NO | NO |

+-------------+---------+-------+

mysql> SELECT *

FROM

performance_schema.setup_consumers

WHERE NAME LIKE

'events_transactions%';

+----------------------------------+---------+

| NAME | ENABLED |

+----------------------------------+---------+

|

events_transactions_current | NO |

|

events_transactions_history | NO |

| events_transactions_history_long |

NO |

+----------------------------------+---------+

• Enable:

UPDATE

performance_schema.setup_instruments

SET ENABLED =

'YES', TIMED = 'YES'

WHERE NAME =

'transaction';

UPDATE

performance_schema.setup_consumers

SET ENABLED =

'YES'

WHERE NAME

LIKE 'events_transactions%';

• Disable:

UPDATE

performance_schema.setup_instruments

SET ENABLED =

'NO', TIMED = 'NO'

WHERE NAME =

'transaction';

UPDATE

performance_schema.setup_consumers

SET ENABLED =

'NO'

WHERE NAME

LIKE 'events_transactions%';

或者

• Enable:

[mysqld]

performance-schema-instrument='transaction=ON'

performance-schema-consumer-events-transactions-current=ON

performance-schema-consumer-events-transactions-history=ON

performance-schema-consumer-events-transactions-history-long=ON

• Disable:

[mysqld]

performance-schema-instrument='transaction=OFF'

performance-schema-consumer-events-transactions-current=OFF

performance-schema-consumer-events-transactions-history=OFF

performance-schema-consumer-events-transactions-history-long=OFF