Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore sys_schema stored functions in MariaDB Server. These functions simplify querying performance and configuration data, offering a user-friendly interface for database diagnostics.
Convert bytes to a human-readable string. This function formats a numeric byte count into units like KiB, MiB, GiB, up to EiB.
sys.format_bytes(double)format_bytes is a available with the .
Given a byte count, returns a string consisting of a value and the units in a human-readable format. The units will be in bytes, KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), or PiB (pebibytes).
The binary prefixes (kibi, mebi, gibi, tebi and pebi) were created in December 1998 by the International Electrotechnical Commission to avoid possible ambiguity, as the widely-used prefixes kilo, mega, giga, tera and peta can be used to refer to both the power-of-10 decimal system multipliers and the power-of-two binary system multipliers.
From , the built-in function can instead be used. The functions are similar, except that FORMAT_BYTES also displays exbibytes.
This page is licensed: CC BY-SA / Gnu FDL
This function checks if a given Performance Schema instrument is enabled by default, returning YES or NO.
sys.ps_is_instrument_default_enabled(instrument)ps_is_instrument_default_enabled is a available with the .
It returns YES if the given Performance Schema instrument is enabled by default, and NO if it is not, does not exist, or is a NULL value.
This page is licensed: CC BY-SA / Gnu FDL
It takes host and user arguments, and returns an ENUM('YES','NO') depending on whether Performance Schema instrumentation for the given account is enabled.
This page is licensed: CC BY-SA / Gnu FDL
sys.ps_is_account_enabled(host,user)SELECT sys.ps_is_account_enabled('localhost', 'root');
+------------------------------------------------+
| sys.ps_is_account_enabled('localhost', 'root') |
+------------------------------------------------+
| YES |
+------------------------------------------------+It takes a list to be modified and a value to be dropped from the list, returning the resulting value. This can be used, for example, to remove a value from a system variable taking a comma-delimited list of options, such as sql_mode.
The related function list_add can be used to add a value to a list.
This page is licensed: CC BY-SA / Gnu FDL
It returns the MariaDB Server minor release version.
This page is licensed: CC BY-SA / Gnu FDL
sys.version_minor()The format_path function replaces specific system paths in a given string with their corresponding system variable names for better readability.
sys.format_path(path)format_path is a available with the that, given a path, returns a modified path after replacing subpaths matching the values of various system variables with the variable name.
The system variables that are matched are, in order:
This page is licensed: CC BY-SA / Gnu FDL
This function returns the user account (username@hostname) associated with a specific Performance Schema thread ID.
This function formats and truncates an SQL statement string to a specified length, making it suitable for display in summary views.
sys.list_drop(list,value)SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
SET @@sql_mode = sys.list_drop(@@sql_mode, 'NO_ENGINE_SUBSTITUTION');
SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER |
+-----------------------------------------------------------------------+SELECT VERSION(),
sys.version_major() AS major,
sys.version_minor() AS minor,
sys.version_patch() AS patch;
+----------------+-------+-------+-------+
| VERSION() | major | minor | patch |
+----------------+-------+-------+-------+
| 10.8.2-MariaDB | 10 | 8 | 2 |
+----------------+-------+-------+-------+SELECT sys.format_bytes(1000),sys.format_bytes(1024);
+------------------------+------------------------+
| sys.format_bytes(1000) | sys.format_bytes(1024) |
+------------------------+------------------------+
| 1000 bytes | 1.00 KiB |
+------------------------+------------------------+
SELECT sys.format_bytes(1000000),sys.format_bytes(1048576);
+---------------------------+---------------------------+
| sys.format_bytes(1000000) | sys.format_bytes(1048576) |
+---------------------------+---------------------------+
| 976.56 KiB | 1.00 MiB |
+---------------------------+---------------------------+
SELECT sys.format_bytes(1000000000),sys.format_bytes(1073741874);
+------------------------------+------------------------------+
| sys.format_bytes(1000000000) | sys.format_bytes(1073741874) |
+------------------------------+------------------------------+
| 953.67 MiB | 1.00 GiB |
+------------------------------+------------------------------+
SELECT sys.format_bytes(1000000000000),sys.format_bytes(1099511627776);
+---------------------------------+---------------------------------+
| sys.format_bytes(1000000000000) | sys.format_bytes(1099511627776) |
+---------------------------------+---------------------------------+
| 931.32 GiB | 1.00 TiB |
+---------------------------------+---------------------------------+
SELECT sys.format_bytes(1000000000000000),sys.format_bytes(1125899906842624);
+------------------------------------+------------------------------------+
| sys.format_bytes(1000000000000000) | sys.format_bytes(1125899906842624) |
+------------------------------------+------------------------------------+
| 909.49 TiB | 1.00 PiB |
+------------------------------------+------------------------------------+SELECT sys.ps_is_instrument_default_enabled('statement/sql/select');
+--------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('statement/sql/select') |
+--------------------------------------------------------------+
| YES |
+--------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_enabled('memory/sql/udf_mem');
+------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('memory/sql/udf_mem') |
+------------------------------------------------------------+
| NO |
+------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_enabled('memory/sql/nonexistent');
+----------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('memory/sql/nonexistent') |
+----------------------------------------------------------------+
| NO |
+----------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_enabled(NULL);
+--------------------------------------------+
| sys.ps_is_instrument_default_enabled(NULL) |
+--------------------------------------------+
| NO |
+--------------------------------------------+ps - picoseconds
ns - nanoseconds
us - microseconds
ms - milliseconds
s - seconds
m - minutes
h - hours
d - days
w - weeks
This function is very similar to the FORMAT_PICO_TIME function introduced in , but with the following differences:
Represents minutes as m rather than min.
Represent weeks.
This page is licensed: CC BY-SA / Gnu FDL
It returns YES if the given Performance Schema instrument is timed by default, and NO if it is not, does not exist, or is a NULL value.
This page is licensed: CC BY-SA / Gnu FDL
sys.ps_is_instrument_default_timed(instrument)The function is intended for use in formatting lengthy SQL statements to a fixed length.
Default truncation length 64:
Reducing the truncation length to 48:
This page is licensed: CC BY-SA / Gnu FDL
sys.format_statement(statement)SELECT sys.format_statement(
'SELECT field1, field2, field3, field4, field5, field6 FROM table1'
) AS formatted_statement;
+-------------------------------------------------------------------+
| formatted_statement |
+-------------------------------------------------------------------+
| SELECT field1, field2, field3, ... d4, field5, field6 FROM table1 |
+-------------------------------------------------------------------+It quotes a string to produce a result that can be used as an identifier in an SQL statement. The string is returned enclosed by backticks ("") and with each instance of backtick ("") doubled. If the argument is NULL, the return value is the word "NULL" without enclosing backticks.
This page is licensed: CC BY-SA / Gnu FDL
sys.quote_identifier(str)It returns a JSON object with information about the thread specified by the given thread_id. This information includes:
the current transaction;
executed statements (derived from the Performance Schema events_transactions_current Table and the Performance Schema events_statements_history Table (full data will only returned if the consumers for those tables are enabled).
The maximum length of the returned JSON object is determined by the value of the ps_thread_trx_info.max_length sys_config option (by default 65535). If the returned value exceeds this length, a JSON object error is returned.
This page is licensed: CC BY-SA / Gnu FDL
Given a file path, it returns the schema (database) name. The file name is assumed to be within the schema directory, and therefore the function will not return the expected result with partitions, or when tables are defined using the DATA_DIRECTORY table option.
The function does not examine anything on disk. The return value, a VARCHAR(64), is determined solely from the provided path.
This page is licensed: CC BY-SA / Gnu FDL
sys.extract_schema_from_file_name(path)SELECT sys.ps_thread_account(sys.ps_thread_id(CONNECTION_ID()));
+----------------------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(CONNECTION_ID())) |
+----------------------------------------------------------+
| msandbox@localhost |
+----------------------------------------------------------+
SELECT sys.ps_thread_account(sys.ps_thread_id(2042));
+-----------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(2042)) |
+-----------------------------------------------+
| NULL |
+-----------------------------------------------+
SELECT sys.ps_thread_account(sys.ps_thread_id(NULL));
+-----------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(NULL)) |
+-----------------------------------------------+
| msandbox@localhost |
+-----------------------------------------------+This function returns the patch release version number of the MariaDB Server instance, such as the 4 in 10.6.4.
sys.version_patch()version_patch is a stored function available with the .
It returns the MariaDB Server patch release version.
This page is licensed: CC BY-SA / Gnu FDL
SELECT @@tmpdir;
+------------------------------------+
| @@tmpdir |
+------------------------------------+
| /home/ian/sandboxes/msb_10_8_2/tmp |
+------------------------------------+
SELECT sys.format_path('/home/ian/sandboxes/msb_10_8_2/tmp/testdb.ibd');
+------------------------------------------------------------------+
| sys.format_path('/home/ian/sandboxes/msb_10_8_2/tmp/testdb.ibd') |
+------------------------------------------------------------------+
| @@tmpdir/testdb.ibd |
+------------------------------------------------------------------+sys.format_time(picoseconds)SELECT
sys.format_time(43) AS ps,
sys.format_time(4321) AS ns,
sys.format_time(43211234) AS us,
sys.format_time(432112344321) AS ms,
sys.format_time(43211234432123) AS s,
sys.format_time(432112344321234) AS m,
sys.format_time(4321123443212345) AS h,
sys.format_time(432112344321234545) AS d,
sys.format_time(43211234432123444543) AS w;
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+
| ps | ns | us | ms | s | m | h | d | w |
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+
| 43 ps | 4.32 ns | 43.21 us | 432.11 ms | 43.21 s | 7.20 m | 1.20 h | 5.00 d | 71.45 w |
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+SELECT sys.ps_is_instrument_default_timed('statement/sql/select');
+------------------------------------------------------------+
| sys.ps_is_instrument_default_timed('statement/sql/select') |
+------------------------------------------------------------+
| YES |
+------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_timed('memory/sql/udf_mem');
+----------------------------------------------------------+
| sys.ps_is_instrument_default_timed('memory/sql/udf_mem') |
+----------------------------------------------------------+
| NO |
+----------------------------------------------------------+
SELECT sys.ps_is_instrument_default_timed('memory/sql/nonexistent');
+-------------------------------------------------------------+
| sys.ps_is_instrument_default_timed('memory/sql/udf_memsds') |
+-------------------------------------------------------------+
| NO |
+-------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_timed(NULL);
+------------------------------------------+
| sys.ps_is_instrument_default_timed(NULL) |
+------------------------------------------+
| NO |
+------------------------------------------+SET @sys.statement_truncate_len = 48;
SELECT sys.format_statement(
'SELECT field1, field2, field3, field4, field5, field6 FROM table1'
) AS formatted_statement;
+---------------------------------------------------+
| formatted_statement |
+---------------------------------------------------+
| SELECT field1, field2, ... d5, field6 FROM table1 |
+---------------------------------------------------+SELECT sys.quote_identifier("Identifier with spaces");
+------------------------------------------------+
| sys.quote_identifier("Identifier with spaces") |
+------------------------------------------------+
| `Identifier with spaces` |
+------------------------------------------------+
SELECT sys.quote_identifier("Identifier` containing `backticks");
+-----------------------------------------------------------+
| sys.quote_identifier("Identifier` containing `backticks") |
+-----------------------------------------------------------+
| `Identifier`` containing ``backticks` |
+-----------------------------------------------------------+sys.ps_thread_trx_info(thread_id)SELECT sys.extract_schema_from_file_name('/usr/local/mysql/data/db/t1.ibd');
+----------------------------------------------------------------------+
| sys.extract_schema_from_file_name('/usr/local/mysql/data/db/t1.ibd') |
+----------------------------------------------------------------------+
| db |
+----------------------------------------------------------------------+NULL, returns the thread_id for the current connection.This page is licensed: CC BY-SA / Gnu FDL
sys.ps_thread_id(connection_id)YES - instrumentation is enabled
NO - instrumentation is not enabled
UNKNOWN - the connection ID is unknown
NULL - NULL value
This page is licensed: CC BY-SA / Gnu FDL
It takes a list to be modified and a value to be added to the list, returning the resulting value. This can be used, for example, to add a value to a system variable taking a comma-delimited list of options, such as sql_mode.
The related function list_drop can be used to drop a value from a list.
This page is licensed: CC BY-SA / Gnu FDL
The boolean verbose argument specifies whether or not to include file:lineno information in the events.
This page is licensed: CC BY-SA / Gnu FDL
sys.ps_thread_stack(thread_id, verbose)Given a file path, it returns the table name.
The function does not examine anything on disk. The return value, a VARCHAR(64), is determined solely from the provided path.
This page is licensed: CC BY-SA / Gnu FDL
This function returns the major version number of the MariaDB Server instance (e.g., 10 from 10.6.4).
sys.version_major()version_major is a stored function available with the .
It returns the MariaDB Server major release version.
This page is licensed: CC BY-SA / Gnu FDL
SELECT * FROM performance_schema.threads\G
*************************** 13. row ***************************
THREAD_ID: 13
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 3
PROCESSLIST_USER: msandbox
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 0
PROCESSLIST_STATE: Sending data
PROCESSLIST_INFO: SELECT * FROM performance_schema.threads
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 24379
SELECT sys.ps_thread_id(3);
+---------------------+
| sys.ps_thread_id(3) |
+---------------------+
| 13 |
+---------------------+
SELECT sys.ps_thread_id(NULL);
+------------------------+
| sys.ps_thread_id(NULL) |
+------------------------+
| 13 |
+------------------------+sys.ps_is_thread_instrumented(connection_id)SELECT sys.ps_is_thread_instrumented(CONNECTION_ID());
+------------------------------------------------+
| sys.ps_is_thread_instrumented(CONNECTION_ID()) |
+------------------------------------------------+
| YES |
+------------------------------------------------+
SELECT sys.ps_is_thread_instrumented(2042);
+-------------------------------------+
| sys.ps_is_thread_instrumented(2042) |
+-------------------------------------+
| UNKNOWN |
+-------------------------------------+
SELECT sys.ps_is_thread_instrumented(NULL);
+-------------------------------------+
| sys.ps_is_thread_instrumented(NULL) |
+-------------------------------------+
| NULL |
+-------------------------------------+sys.list_add(list,value)SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
SET @@sql_mode = sys.list_add(@@sql_mode, 'NO_ZERO_DATE');
SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+SELECT sys.ps_thread_stack(13, FALSE) AS thread_stack\G
*************************** 1. row ***************************
thread_stack: {"rankdir": "LR","nodesep": "0.10",
"stack_created": "2022-03-28 16:01:06",
"mysql_version": "10.8.2-MariaDB",
"mysql_user": "msandbox@localhost",
"events": []}sys.extract_table_from_file_name(path)SELECT sys.extract_table_from_file_name('/usr/local/mysql/data/db/t1.ibd');
+---------------------------------------------------------------------+
| sys.extract_table_from_file_name('/usr/local/mysql/data/db/t1.ibd') |
+---------------------------------------------------------------------+
| t1 |
+---------------------------------------------------------------------+SELECT VERSION(),
sys.version_major() AS major,
sys.version_minor() AS minor,
sys.version_patch() AS patch;
+----------------+-------+-------+-------+
| VERSION() | major | minor | patch |
+----------------+-------+-------+-------+
| 10.8.2-MariaDB | 10 | 8 | 2 |
+----------------+-------+-------+-------+It returns an ENUM('YES','NO') depending on whether Performance Schema instrumentation for the given consumer is enabled, and NULL if not given a valid consumer name.
This page is licensed: CC BY-SA / Gnu FDL
SELECT VERSION(),
sys.version_major() AS major,
sys.version_minor() AS minor,
sys.version_patch() AS patch;
+----------------+-------+-------+-------+
| VERSION() | major | minor | patch |
+----------------+-------+-------+-------+
| 10.8.2-MariaDB | 10 | 8 | 2 |
+----------------+-------+-------+-------+sys.ps_is_consumer_enabled(consumer)SELECT sys.ps_is_consumer_enabled('global_instrumentation');
+------------------------------------------------------+
| sys.ps_is_consumer_enabled('global_instrumentation') |
+------------------------------------------------------+
| YES |
+------------------------------------------------------+
SELECT sys.ps_is_consumer_enabled('events_stages_current');
+-----------------------------------------------------+
| sys.ps_is_consumer_enabled('events_stages_current') |
+-----------------------------------------------------+
| NO |
+-----------------------------------------------------+
SELECT sys.ps_is_consumer_enabled('nonexistent_consumer');
+----------------------------------------------------+
| sys.ps_is_consumer_enabled('nonexistent_consumer') |
+----------------------------------------------------+
| NULL |
+----------------------------------------------------+The sys_get_config function retrieves the value of a specified configuration option from the sys_config table, or a user variable if set.
sys.sys_get_config(name,default)sys_get_config is a available with the .
The function returns a configuration option value from the . It takes two arguments; name, a configuration option name, and default, which is returned if the given option does not exist in the table.
Both arguments are VARCHAR(128) and can be NULL. Returns NULL if name is NULL, or if the given option is not found and default is NULL.
This page is licensed: CC BY-SA / Gnu FDL
SELECT sys.sys_get_config('ps_thread_trx_info.max_length',NULL);
+----------------------------------------------------------+
| sys.sys_get_config('ps_thread_trx_info.max_length',NULL) |
+----------------------------------------------------------+
| 65535 |
+----------------------------------------------------------+