Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore sys_schema stored procedures in MariaDB Server. These procedures simplify complex administrative and diagnostic tasks, offering streamlined access to performance and configuration insights.
The ps_trace_thread procedure captures a trace of Performance Schema instrumentation for a specific thread and dumps it to a .dot formatted graph file.
ps_trace_thread(thread_id, outfile, max_runtime, interval, start_fresh, auto_setup, debug)ps_trace_thread is a available with the .
Parameters:
thread_id INT: The thread to trace.
outfile VARCHAR(255): Name of the .dot file to be create.
max_runtime DECIMAL(20,2): Maximum time in seconds to collect data. Fractional seconds can be used, and NULL results in data being collected for the default sixty seconds.
Dumps all Performance Schema data for an instrumented thread to a .dot formatted graph file (for use with the ). All returned result sets should be used for a complete graph.
Session is disabled during execution, by adjusting the session value (note the permissions required).
This page is licensed: CC BY-SA / Gnu FDL
The create_synonym_db stored procedure creates a new database that contains views mirroring all tables from a source database, useful for creating aliases.
create_synonym_db(db_name,synonym)
# db_name (VARCHAR(64))
# synonym (VARCHAR(64))create_synonym_db is a available with the .
Takes a source database name db_name and synonym name and creates a synonym database with views that point to all of the tables within the source database. Useful for example for creating a synonym for the or databases.
Returns an error if the source database doesn't exist, or the synonym already exists.
This page is licensed: CC BY-SA / Gnu FDL
interval DECIMAL(20,2): Time in seconds to sleep between data collection. Fractional seconds can be used, and NULL results in the sleep being the default one second.
start_fresh BOOLEAN: Whether to reset all Performance Schema data before tracing.
auto_setup BOOLEAN: Whether to disable all other threads, enable all instruments and consumers, and reset the settings at the end of the run.
debug BOOLEAN: Whether to include file:lineno information in the graph.
Parameters:
in_digest VARCHAR(32): The statement digest identifier to analyze.
in_runtime INT: Specifies the duration to run the analysis in seconds.
in_interval DECIMAL(2,2): The analysis interval measured in seconds (including fraction values), at which snapshots are taken.
in_start_fresh BOOLEAN: Determines whether to truncate the Performance Schema events_statements_history_long and events_stages_history_long tables before starting.
in_auto_enable BOOLEAN: Determines whether to automatically enable required consumers.
This page is licensed: CC BY-SA / Gnu FDL
The following options from the sys_config table impact the output:
statement_performance_analyzer.limit - maximum number of rows (default 100) returned for views that have no built-in limit.
statement_performance_analyzer.view - custom query/view to be used (default NULL). If the statement_performance_analyzer.limit configuration option is greater than 0, there can't be a LIMIT clause in the query/view definition.
If the debug option is set (default OFF), the procedure will also produce debugging output.
This page is licensed: CC BY-SA / Gnu FDL
statement_performance_analyzer(in_action,in_table, in_views)
# in_action ENUM('snapshot', 'overall', 'delta', 'create_tmp',
'create_table', 'save', 'cleanup')
# in_table VARCHAR(129)
# in_views SET ('with_runtimes_in_95th_percentile', 'analysis',
'with_errors_or_warnings', 'with_full_table_scans',
'with_sorting', 'with_temp_tables', 'custom')This procedure truncates all Performance Schema summary tables, effectively resetting all aggregated performance statistics.
ps_truncate_all_tables(bool display)ps_truncate_all_tables is a available with the .
The procedure resets all aggregated instrumentation as a snapshot, producing a result set indicating the number of truncated tables. The boolean parameter display specifies whether to display each statement before execution.
This page is licensed: CC BY-SA / Gnu FDL
CALL sys.ps_trace_thread(25, CONCAT('/tmp/stack-', REPLACE(NOW(), ' ', '-'), '.dot'),
NULL, NULL, TRUE, TRUE, TRUE);
--------------------+
| summary |
+--------------------+
| Disabled 0 threads |
+--------------------+
+---------------------------------------------+
| Info |
+---------------------------------------------+
| Data collection starting for THREAD_ID = 25 |
+---------------------------------------------+
+-----------------------------------------------------------+
| Info |
+-----------------------------------------------------------+
| Stack trace written to /tmp/stack-2023-04-05-19:06:29.dot |
+-----------------------------------------------------------+
+-------------------------------------------------------------------+
| Convert to PDF |
+-------------------------------------------------------------------+
| dot -Tpdf -o /tmp/stack_25.pdf /tmp/stack-2023-04-05-19:06:29.dot |
+-------------------------------------------------------------------+
+-------------------------------------------------------------------+
| Convert to PNG |
+-------------------------------------------------------------------+
| dot -Tpng -o /tmp/stack_25.png /tmp/stack-2023-04-05-19:06:29.dot |
+-------------------------------------------------------------------+ps_trace_statement_digest(in_digest, in_runtime, in_interval, in_start_fresh, in_auto_enable)CALL sys.ps_trace_statement_digest('891ec6860f98ba46d89dd20b0c03652c', 5, 0.5, TRUE, TRUE);SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
CALL sys.create_synonym_db('performance_schema', 'perf');
+-----------------------------------------+
| summary |
+-----------------------------------------+
| Created 81 views in the `perf` database |
+-----------------------------------------+
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| perf |
| performance_schema |
| sys |
| test |
+--------------------+
SHOW FULL TABLES FROM perf;
+------------------------------------------------------+------------+
| Tables_in_perf | Table_type |
+------------------------------------------------------+------------+
| accounts | VIEW |
| cond_instances | VIEW |
| events_stages_current | VIEW |
| events_stages_history | VIEW |
| events_stages_history_long | VIEW |
...offsys.optimizer_switch_on() is a shortcut for sys.optimizer_switch_choice("on") .
sys.optimizer_switch_off() is a shortcut for sys.optimizer_switch_choice("off") .
optimizer-switch Documentation for optimizer_switch
This page is licensed: CC BY-SA / Gnu FDL
Given a database in_db_name and table name in_table_name, returns the table type in the OUT parameter out_table_type. The return value is an ENUM field containing one of:
'' - the table does not exist
'BASE TABLE' - a regular table
'VIEW' - a view
'TEMPORARY' - a temporary table
This page is licensed: CC BY-SA / Gnu FDL
CALL sys.ps_truncate_all_tables(false);
+---------------------+
| summary |
+---------------------+
| Truncated 44 tables |
+---------------------+optimizer_switch_on()
optimizer_switch_off()
optimizer_switch_choice("on" | "off")SELECT @@optimizer_switch\G
*************************** 1. row ***************************
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,
index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,
derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,
in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,
subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,
semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,
optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,
orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,
condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,
not_null_range_scan=off
call sys.optimizer_switch_on();
+---------------------------------+------+
| option | opt |
+---------------------------------+------+
| condition_pushdown_for_derived | on |
| condition_pushdown_for_subquery | on |
| condition_pushdown_from_having | on |
| derived_merge | on |
| derived_with_keys | on |
| exists_to_in | on |
| extended_keys | on |
| firstmatch | on |
| index_condition_pushdown | on |
| index_merge | on |
| index_merge_intersection | on |
| index_merge_sort_union | on |
| index_merge_union | on |
| in_to_exists | on |
| join_cache_bka | on |
| join_cache_hashed | on |
| join_cache_incremental | on |
| loosescan | on |
| materialization | on |
| optimize_join_buffer_size | on |
| orderby_uses_equalities | on |
| outer_join_with_cache | on |
| partial_match_rowid_merge | on |
| partial_match_table_scan | on |
| rowid_filter | on |
| semijoin | on |
| semijoin_with_cache | on |
| split_materialized | on |
| subquery_cache | on |
| table_elimination | on |
+---------------------------------+------+
call sys.optimizer_switch_off();
+-------------------------------+------+
| option | opt |
+-------------------------------+------+
| engine_condition_pushdown | off |
| index_merge_sort_intersection | off |
| mrr | off |
| mrr_cost_based | off |
| mrr_sort_keys | off |
| not_null_range_scan | off |
+-------------------------------+------+table_exists(in_db_name,in_table_name, out_table_type)
# in_db_name VARCHAR(64)
# in_table_name VARCHAR(64)
# out_table_type ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY')CALL sys.table_exists('mysql', 'time_zone', @table_type); SELECT @table_type;
+-------------+
| @table_type |
+-------------+
| BASE TABLE |
+-------------+
CALL sys.table_exists('mysql', 'user', @table_type); SELECT @table_type;
+-------------+
| @table_type |
+-------------+
| VIEW |
+-------------+CALL sys.ps_truncate_all_tables(true);
+------------------------------------------------------------------+
| status |
+------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.events_stages_history |
+------------------------------------------------------------------+
...
+------------------------------------------------------------------------------+
| status |
+------------------------------------------------------------------------------+
| Running: TRUNCATE TABLE performance_schema.table_lock_waits_summary_by_table |
+------------------------------------------------------------------------------+
+---------------------+
| summary |
+---------------------+
| Truncated 44 tables |
+---------------------+