Panoramica sul Performance Schema
Instroduzione
Il Performance Schema è una funzionalità che serve a monitorare le performance del server, introdotta in MariaDB 5.5.3.
E' implementato come Storage Engine, pertanto compare nella lista degli Storage Engine disponibili.
SHOW ENGINES; +--------------------+---------+----------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------+--------------+------+------------+ | ... | | | | | | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ... | | | | | | +--------------------+---------+----------------------------------+--------------+------+------------+
Tuttavia PERFORMANCE_SCHEMA non è un normale Storage Engine per registrare i dati, ma è un meccanismo per implementare la funzionalità Performance Schema.
Lo Storage Engine contiene un database chiamato performance_schema, che a sua volta contiene un certo numero di tabelle che possono essere interrogate con normali istruzioni SQL, che restituiscono informazioni specifiche riguardanti le performance.
USE performance_schema;
SHOW TABLES; +----------------------------------------------------+ | Tables_in_performance_schema | +----------------------------------------------------+ | accounts | | cond_instances | | events_stages_current | | events_stages_history | | events_stages_history_long | | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | | events_statements_current | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | events_waits_current | | events_waits_history | | events_waits_history_long | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | host_cache | | hosts | | mutex_instances | | objects_summary_global_by_type | | performance_timers | | rwlock_instances | | session_account_connect_attrs | | session_connect_attrs | | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | | socket_instances | | socket_summary_by_event_name | | socket_summary_by_instance | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | | threads | | users | +----------------------------------------------------+
Attivare il Performance Schema
Per default, il performance schema è disabilitato. Per conoscerne lo stato si può controllare la variabile di sistema performance_schema.
SHOW VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+
Il performance schema non può essere attivato a runtime - deve essere attivato all'avvio del server impostando la riga seguente nel file di configurazione my.cnf.
performance_schema=on