MySQL: Reset SYS – Performance Schema Summary

O banco performance_schema foi criado para trazer informações de baixo nível a respeito de desempenho do MySQL, e o banco sys é mais recente (adicionado no MySQL 5.7.7) e traz as informações do perfomance_schema de forma sumarizada.

Isto é útil pois traz as informações em formato agregado, como abaixo:

mysql> SELECT object_schema, object_name, rows_full_scanned from sys.schema_tables_with_full_table_scans WHERE rows_full_scanned > 1000000 ORDER BY rows_full_scanned;
+---------------+-----------------------+-------------------+
| object_schema | object_name           | rows_full_scanned |
+---------------+-----------------------+-------------------+
| forum         | phpbb_search_wordlist |           1003347 |
| zabbix        | hosts                 |           3332517 |
| MonitORA      | EmailsParaEnvio       |           7150953 |
| zabbix        | escalations           |           9691834 |
| zabbix        | alerts                |          14223029 |
| MonitORA      | Chamados              |          15438518 |
| zabbix        | triggers              |          15957954 |
| MonitORA      | Tramites_Chamados     |          16192035 |
| zabbix        | event_recovery        |          29597398 |
| zabbix        | history_log           |          41492516 |
| blog          | wp_posts              |          48438898 |
| zabbix        | history_text          |          48987581 |
| zabbix        | events                |          65402951 |
| zabbix        | trends                |          81661995 |
| zabbix        | history               |         144542471 |
| zabbix        | graphs                |         155184379 |
| zabbix        | trends_uint           |         227359895 |
| blog          | wp_options            |         262954671 |
| zabbix        | history_uint          |         360164821 |
| zabbix        | hostmacro             |        3786653828 |
+---------------+-----------------------+-------------------+
20 rows in set (0,06 sec)

mysql>

Mas este tipo de informação só será útil se você souber desde quando são coletadas. Para isso, as configurações e as informações resumidas do banco sys podem ser reiniciadas com o procedimento abaixo.

mysql> call sys.ps_setup_reset_to_default(1);
+--------------------------------------------------------------------------------------------------------------------------+
| status                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------+
| Resetting: setup_actors
DELETE FROM performance_schema.setup_actors WHERE NOT (HOST = '%' AND USER = '%' AND ROLE = '%') |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

+-----------------------------------------------------------------------------------------------------------------+
| status                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------+
| Resetting: setup_actors
INSERT IGNORE INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES') |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0,06 sec)

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| status                                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Resetting: setup_instruments
UPDATE performance_schema.setup_instruments SET ENABLED = sys.ps_is_instrument_default_enabled(NAME), TIMED = sys.ps_is_instrument_default_timed(NAME) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,06 sec)

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| status                                                                                                                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Resetting: setup_consumers
UPDATE performance_schema.setup_consumers SET ENABLED = IF(NAME IN ('events_statements_current', 'events_transactions_current', 'global_instrumentation', 'thread_instrumentation', 'statements_digest'), 'YES', 'NO') |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,12 sec)

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| status                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Resetting: setup_objects
DELETE FROM performance_schema.setup_objects WHERE NOT (OBJECT_TYPE IN ('EVENT', 'FUNCTION', 'PROCEDURE', 'TABLE', 'TRIGGER') AND OBJECT_NAME = '%'AND (OBJECT_SCHEMA = 'mysql'AND ENABLED = 'NO'AND TIMED = 'NO' ) OR (OBJECT_SCHEMA = 'performance_schema' AND ENABLED = 'NO'AND TIMED = 'NO' ) OR (OBJECT_SCHEMA = 'information_schema' AND ENABLED = 'NO'AND TIMED = 'NO' ) OR (OBJECT_SCHEMA = '%'AND ENABLED = 'YES' AND TIMED = 'YES')) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,12 sec)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Resetting: setup_objects
INSERT IGNORE INTO performance_schema.setup_objects VALUES ('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') |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,12 sec)

+-------------------------------------------------------------------------------+
| status                                                                        |
+-------------------------------------------------------------------------------+
| Resetting: threads
UPDATE performance_schema.threads SET INSTRUMENTED = 'YES' |
+-------------------------------------------------------------------------------+
1 row in set (0,12 sec)

Query OK, 0 rows affected (0,12 sec)

mysql> call sys.ps_truncate_all_tables(1);
+------------------------------------------------------------------+
| status                                                           |
+------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_stages_history |
+------------------------------------------------------------------+
1 row in set (0,01 sec)

+-----------------------------------------------------------------------+
| status                                                                |
+-----------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_stages_history_long |
+-----------------------------------------------------------------------+
1 row in set (0,06 sec)

+-------------------------------------------------------------------------------------------+
| status                                                                                    |
+-------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_stages_summary_by_account_by_event_name |
+-------------------------------------------------------------------------------------------+
1 row in set (0,12 sec)

+----------------------------------------------------------------------------------------+
| status                                                                                 |
+----------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_stages_summary_by_host_by_event_name |
+----------------------------------------------------------------------------------------+
1 row in set (0,20 sec)

+------------------------------------------------------------------------------------------+
| status                                                                                   |
+------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_stages_summary_by_thread_by_event_name |
+------------------------------------------------------------------------------------------+
1 row in set (0,21 sec)

+----------------------------------------------------------------------------------------+
| status                                                                                 |
+----------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_stages_summary_by_user_by_event_name |
+----------------------------------------------------------------------------------------+
1 row in set (0,22 sec)

+---------------------------------------------------------------------------------------+
| status                                                                                |
+---------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_stages_summary_global_by_event_name |
+---------------------------------------------------------------------------------------+
1 row in set (0,28 sec)

+----------------------------------------------------------------------+
| status                                                               |
+----------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_statements_history |
+----------------------------------------------------------------------+
1 row in set (0,29 sec)

+---------------------------------------------------------------------------+
| status                                                                    |
+---------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_statements_history_long |
+---------------------------------------------------------------------------+
1 row in set (0,29 sec)

+-----------------------------------------------------------------------------------------------+
| status                                                                                        |
+-----------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_statements_summary_by_account_by_event_name |
+-----------------------------------------------------------------------------------------------+
1 row in set (1,31 sec)

+--------------------------------------------------------------------------------+
| status                                                                         |
+--------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_statements_summary_by_digest |
+--------------------------------------------------------------------------------+
1 row in set (1,44 sec)

+--------------------------------------------------------------------------------------------+
| status                                                                                     |
+--------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_statements_summary_by_host_by_event_name |
+--------------------------------------------------------------------------------------------+
1 row in set (1,52 sec)

+---------------------------------------------------------------------------------+
| status                                                                          |
+---------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_statements_summary_by_program |
+---------------------------------------------------------------------------------+
1 row in set (1,53 sec)

+----------------------------------------------------------------------------------------------+
| status                                                                                       |
+----------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_statements_summary_by_thread_by_event_name |
+----------------------------------------------------------------------------------------------+
1 row in set (1,54 sec)

+--------------------------------------------------------------------------------------------+
| status                                                                                     |
+--------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_statements_summary_by_user_by_event_name |
+--------------------------------------------------------------------------------------------+
1 row in set (1,54 sec)

+-------------------------------------------------------------------------------------------+
| status                                                                                    |
+-------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_statements_summary_global_by_event_name |
+-------------------------------------------------------------------------------------------+
1 row in set (1,54 sec)

+------------------------------------------------------------------------+
| status                                                                 |
+------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_transactions_history |
+------------------------------------------------------------------------+
1 row in set (1,55 sec)

+-----------------------------------------------------------------------------+
| status                                                                      |
+-----------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_transactions_history_long |
+-----------------------------------------------------------------------------+
1 row in set (1,58 sec)

+-------------------------------------------------------------------------------------------------+
| status                                                                                          |
+-------------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_transactions_summary_by_account_by_event_name |
+-------------------------------------------------------------------------------------------------+
1 row in set (1,76 sec)

+----------------------------------------------------------------------------------------------+
| status                                                                                       |
+----------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_transactions_summary_by_host_by_event_name |
+----------------------------------------------------------------------------------------------+
1 row in set (1,77 sec)

+------------------------------------------------------------------------------------------------+
| status                                                                                         |
+------------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_transactions_summary_by_thread_by_event_name |
+------------------------------------------------------------------------------------------------+
1 row in set (1,77 sec)

+----------------------------------------------------------------------------------------------+
| status                                                                                       |
+----------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_transactions_summary_by_user_by_event_name |
+----------------------------------------------------------------------------------------------+
1 row in set (1,78 sec)

+---------------------------------------------------------------------------------------------+
| status                                                                                      |
+---------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_transactions_summary_global_by_event_name |
+---------------------------------------------------------------------------------------------+
1 row in set (1,78 sec)

+-----------------------------------------------------------------+
| status                                                          |
+-----------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_waits_history |
+-----------------------------------------------------------------+
1 row in set (1,78 sec)

+----------------------------------------------------------------------+
| status                                                               |
+----------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_waits_history_long |
+----------------------------------------------------------------------+
1 row in set (1,83 sec)

+------------------------------------------------------------------------------------------+
| status                                                                                   |
+------------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_waits_summary_by_account_by_event_name |
+------------------------------------------------------------------------------------------+
1 row in set (1,92 sec)

+---------------------------------------------------------------------------------------+
| status                                                                                |
+---------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_waits_summary_by_host_by_event_name |
+---------------------------------------------------------------------------------------+
1 row in set (1,96 sec)

+-----------------------------------------------------------------------------+
| status                                                                      |
+-----------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_waits_summary_by_instance |
+-----------------------------------------------------------------------------+
1 row in set (1,96 sec)

+-----------------------------------------------------------------------------------------+
| status                                                                                  |
+-----------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_waits_summary_by_thread_by_event_name |
+-----------------------------------------------------------------------------------------+
1 row in set (2,07 sec)

+---------------------------------------------------------------------------------------+
| status                                                                                |
+---------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_waits_summary_by_user_by_event_name |
+---------------------------------------------------------------------------------------+
1 row in set (2,07 sec)

+--------------------------------------------------------------------------------------+
| status                                                                               |
+--------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name |
+--------------------------------------------------------------------------------------+
1 row in set (2,07 sec)

+-----------------------------------------------------------------------+
| status                                                                |
+-----------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.file_summary_by_event_name |
+-----------------------------------------------------------------------+
1 row in set (2,32 sec)

+---------------------------------------------------------------------+
| status                                                              |
+---------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.file_summary_by_instance |
+---------------------------------------------------------------------+
1 row in set (2,32 sec)

+------------------------------------------------------------------------------------+
| status                                                                             |
+------------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.memory_summary_by_account_by_event_name |
+------------------------------------------------------------------------------------+
1 row in set (2,32 sec)

+---------------------------------------------------------------------------------+
| status                                                                          |
+---------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.memory_summary_by_host_by_event_name |
+---------------------------------------------------------------------------------+
1 row in set (2,33 sec)

+-----------------------------------------------------------------------------------+
| status                                                                            |
+-----------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.memory_summary_by_thread_by_event_name |
+-----------------------------------------------------------------------------------+
1 row in set (2,33 sec)

+---------------------------------------------------------------------------------+
| status                                                                          |
+---------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.memory_summary_by_user_by_event_name |
+---------------------------------------------------------------------------------+
1 row in set (2,33 sec)

+--------------------------------------------------------------------------------+
| status                                                                         |
+--------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.memory_summary_global_by_event_name |
+--------------------------------------------------------------------------------+
1 row in set (2,33 sec)

+---------------------------------------------------------------------------+
| status                                                                    |
+---------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.objects_summary_global_by_type |
+---------------------------------------------------------------------------+
1 row in set (2,33 sec)

+-------------------------------------------------------------------------+
| status                                                                  |
+-------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.socket_summary_by_event_name |
+-------------------------------------------------------------------------+
1 row in set (2,33 sec)

+-----------------------------------------------------------------------+
| status                                                                |
+-----------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.socket_summary_by_instance |
+-----------------------------------------------------------------------+
1 row in set (2,33 sec)

+----------------------------------------------------------------------------------+
| status                                                                           |
+----------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage |
+----------------------------------------------------------------------------------+
1 row in set (2,33 sec)

+----------------------------------------------------------------------------+
| status                                                                     |
+----------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table |
+----------------------------------------------------------------------------+
1 row in set (2,34 sec)

+------------------------------------------------------------------------------+
| status                                                                       |
+------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.table_lock_waits_summary_by_table |
+------------------------------------------------------------------------------+
1 row in set (2,34 sec)

+---------------------+
| summary             |
+---------------------+
| Truncated 44 tables |
+---------------------+
1 row in set (2,34 sec)

Query OK, 0 rows affected (2,34 sec)

mysql>

E agora eu posso passar a acompanhar os indicadores de performance “do zero”.

mysql> SELECT object_schema, object_name, rows_full_scanned from sys.schema_tables_with_full_table_scans ORDER BY rows_full_scanned;
+---------------+--------------+-------------------+
| object_schema | object_name  | rows_full_scanned |
+---------------+--------------+-------------------+
| zabbix        | globalmacro  |                 1 |
| zabbix        | expressions  |                 4 |
| zabbix        | actions      |                 5 |
| zabbix        | media_type   |                 5 |
| zabbix        | config       |                 7 |
| zabbix        | acknowledges |                 8 |
| zabbix        | dbversion    |                14 |
| zabbix        | scripts      |                24 |
| zabbix        | interface    |                56 |
| blog          | wp_terms     |                65 |
| zabbix        | escalations  |               212 |
| zabbix        | hosts        |               250 |
| zabbix        | graphs       |              6096 |
| blog          | wp_options   |             13284 |
| zabbix        | hostmacro    |            157406 |
+---------------+--------------+-------------------+
15 rows in set (0,08 sec)

mysql>

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.