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 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
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
This diagnostic procedure creates a report of the statements currently running or recently run on the server, aiding in performance troubleshooting.
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')statement_performance_analyzer is a available with the which returns a report on running statements.
The following options from the 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
This procedure traces a specific statement digest in the Performance Schema, capturing details about its execution for performance analysis.
ps_trace_statement_digest(in_digest, in_runtime, in_interval, in_start_fresh, in_auto_enable)ps_trace_statement_digest is a available with the .
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.
This page is licensed: CC BY-SA / Gnu FDL
The table_exists procedure checks for the existence of a specific table, view, or temporary table within a given database.
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')table_exists is a available with the .
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
This page is licensed: CC BY-SA / Gnu FDL
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.
CALL sys.ps_truncate_all_tables(false);
+---------------------+
| summary |
+---------------------+
| Truncated 44 tables |
+---------------------+These helper functions allow you to easily enable or disable specific optimizer_switch flags for the current session.
The above procedures can be used to check which optimizer_switch options are on or off. The result set is sorted according to option name.
sys.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") .
Documentation for optimizer_switch
This page is licensed: CC BY-SA / Gnu FDL
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
CALL sys.ps_trace_statement_digest('891ec6860f98ba46d89dd20b0c03652c', 5, 0.5, TRUE, TRUE);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 |
+-------------+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 |
...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 |
+---------------------+optimizer_switch_on()
optimizer_switch_off()
optimizer_switch_choice("on" | "off")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.
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 |
+-------------------------------------------------------------------+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 |
+-------------------------------+------+