All pages
Powered by GitBook
1 of 54

Sys Schema

This schema provides a simplified and user-friendly view of Performance Schema and Information Schema data, aiding in database diagnostics and performance tuning.

MariaDB starting with 10.6.0

The sys_schema is a collection of views, functions and procedures to help administrators get insight into database usage. The Performance Schema is required to be enabled in order to make use of the Sys Schema.

This article is currently incomplete.

Sys Schema sys_config Table

MariaDB starting with 10.6.0

The Sys Schema sys_config table was added in MariaDB 10.6.0. The sys_config table is also backported to MariaDB-10.5-enterprise.

The sys.sys_config table holds configuration options for the Sys Schema.

This is a persistent table (using the Aria storage engine), with the configuration persisting across upgrades (new options are added with INSERT IGNORE.

The table also has two related triggers, which maintain the user that INSERTs or UPDATEs the configuration - sys_config_insert_set_user and sys_config_update_set_user respectively.

Its structure is as follows:

+----------+--------------+------+-----+-------------------+-----------------------------+
| Field    | Type         | Null | Key | Default           | Extra                       |
+----------+--------------+------+-----+-------------------+-----------------------------+
| variable | varchar(128) | NO   | PRI | NULL              |                             |
| value    | varchar(128) | YES  |     | NULL              |                             |
| set_time | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| set_by   | varchar(128) | YES  |     | NULL              |                             |
+----------+--------------+------+-----+-------------------+-----------------------------+

Note, when functions check for configuration options, they first check whether a similar named user variable exists with a value, and if this is not set then pull the configuration option from this table in to that named user variable. This is done for performance reasons (to not continually SELECT from the table), however this comes with the side effect that once inited, the values last with the session, somewhat like how session variables are inited from global variables. If the values within this table are changed, they will not take effect until the user logs in again.

Options Included

Variable
Default Value
Description

statement_truncate_len

64

Sets the size to truncate statements to, for the format_statement function.

statement_performance_analyzer.limit

100

The maximum number of rows to include for the views that does not have a built-in limit (e.g. the 95th percentile view). If not set the limit is 100.

statement_performance_analyzer.view

NULL

Used together with the 'custom' view. If the value contains a space, it is considered a query, otherwise it must be an existing view querying the performance_schema.events_statements_summary_by_digest table.

diagnostics.allow_i_s_tables

OFF

Specifies whether it is allowed to do table scan queries on information_schema.TABLES for the diagnostics procedure.

diagnostics.include_raw

OFF

Set to 'ON' to include the raw data (e.g. the original output of "SELECT * FROM sys.metrics") for the diagnostics procedure.

ps_thread_trx_info.max_length

65535

Sets the maximum output length for JSON object output by the ps_thread_trx_info() function.

Notes

Some early versions of sys_config were stored in InnoDB format.

This page is licensed: CC BY-SA / Gnu FDL

Sys Schema Stored Functions

The following stored functions are available in the Sys Schema

extract_schema_from_file_name

Syntax

sys.extract_schema_from_file_name(path)

Description

extract_schema_from_file_name is a stored function available with the Sys Schema.

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.

Examples

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                                                                   |
+----------------------------------------------------------------------+

See also

  • extract_table_from_file_name()

This page is licensed: CC BY-SA / Gnu FDL

extract_table_from_file_name

Syntax

sys.extract_table_from_file_name(path)

Description

extract_table_from_file_name is a stored function available with the Sys Schema.

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.

Examples

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                                                                  |
+---------------------------------------------------------------------+

See also

  • extract_schema_from_file_name()

This page is licensed: CC BY-SA / Gnu FDL

format_path

Syntax

sys.format_path(path)

Description

format_path is a stored function available with the Sys Schema 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:

  • datadir

  • tmpdir

  • slave_load_tmpdir

  • innodb_data_home_dir

  • innodb_log_group_home_dir

  • innodb_undo_directory

  • basedir

Examples

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                                              |
+------------------------------------------------------------------+

This page is licensed: CC BY-SA / Gnu FDL

format_statement

Syntax

sys.format_statement(statement)

Description

Returns a reduced length string. The length is specified by the statement_truncate_len configuration option (default 64), and the removed part of the string (if any) is replaced with an ellipsis (three dots).

The function is intended for use in formatting lengthy SQL statements to a fixed length.

Examples

Default truncation length 64:

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 |
+-------------------------------------------------------------------+

Reducing the truncation length to 48:

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 |
+---------------------------------------------------+

This page is licensed: CC BY-SA / Gnu FDL

format_time

Syntax

sys.format_time(picoseconds)

Description

format_time is a stored function available with the Sys Schema. Given a time in picoseconds, returns a human-readable time value and unit indicator. Unit can be:

  • 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 MariaDB 11.0.2, but with the following differences:

  • Represents minutes as m rather than min.

  • Represent weeks.

Examples

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 |
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+

This page is licensed: CC BY-SA / Gnu FDL

list_add

Syntax

sys.list_add(list,value)

Description

list_add is a stored function available with the Sys Schema.

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.

Examples

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 |
+-----------------------------------------------------------------------+

See Also

  • list_drop

This page is licensed: CC BY-SA / Gnu FDL

list_drop

Syntax

sys.list_drop(list,value)

Description

list_drop is a stored function available with the Sys Schema.

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.

Examples

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 |
+-----------------------------------------------------------------------+

See Also

  • list_add

This page is licensed: CC BY-SA / Gnu FDL

ps_is_account_enabled

Syntax

sys.ps_is_account_enabled(host,user)

Description

ps_is_account_enabled is a stored function available with the Sys Schema.

It takes host and user arguments, and returns an ENUM('YES','NO') depending on whether Performance Schema instrumentation for the given account is enabled.

Examples

SELECT sys.ps_is_account_enabled('localhost', 'root');
+------------------------------------------------+
| sys.ps_is_account_enabled('localhost', 'root') |
+------------------------------------------------+
| YES                                            |
+------------------------------------------------+

This page is licensed: CC BY-SA / Gnu FDL

ps_is_consumer_enabled

Syntax

sys.ps_is_consumer_enabled(consumer)

Description

ps_is_consumer_enabled is a stored function available with the Sys Schema.

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.

Examples

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                                               |
+----------------------------------------------------+

See Also

  • Performance Schema setup_consumers Table

This page is licensed: CC BY-SA / Gnu FDL

ps_is_instrument_default_enabled

Syntax

sys.ps_is_instrument_default_enabled(instrument)

Description

ps_is_instrument_default_enabled is a stored function available with the Sys Schema.

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.

Examples

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                                         |
+--------------------------------------------+

This page is licensed: CC BY-SA / Gnu FDL

ps_is_instrument_default_timed

Syntax

sys.ps_is_instrument_default_timed(instrument)

Description

ps_is_instrument_default_timed is a stored function available with the Sys Schema.

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.

Examples

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                                       |
+------------------------------------------+

This page is licensed: CC BY-SA / Gnu FDL

ps_is_thread_instrumented

Syntax

sys.ps_is_thread_instrumented(connection_id)

Description

ps_is_thread_instrumented is a stored function available with the Sys Schema that returns whether or not Performance Schema instrumentation for the given connection_id is enabled.

  • YES - instrumentation is enabled

  • NO - instrumentation is not enabled

  • UNKNOWN - the connection ID is unknown

  • NULL - NULL value

Examples

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                                |
+-------------------------------------+

This page is licensed: CC BY-SA / Gnu FDL

ps_thread_account

Syntax

sys.ps_thread_account(thread_id)

Description

ps_thread_account is a stored function available with the Sys Schema that returns the account (username@hostname) associated with the given thread_id.

Returns NULL if the thread_id is not found.

Examples

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 page is licensed: CC BY-SA / Gnu FDL

ps_thread_id

Syntax

sys.ps_thread_id(connection_id)

Description

ps_thread_id is a stored function available with the Sys Schema that returns the thread_id associated with the given connection_id. If the connection_id is NULL, returns the thread_id for the current connection.

Examples

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 |
+------------------------+

This page is licensed: CC BY-SA / Gnu FDL

ps_thread_stack

Syntax

sys.ps_thread_stack(thread_id, verbose)

Description

ps_thread_stack is a stored function available with the Sys Schema that, for a given thread_id, returns all statements, stages, and events within the Performance Schema, as a JSON formatted stack.

The boolean verbose argument specifies whether or not to include file:lineno information in the events.

Examples

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": []}

This page is licensed: CC BY-SA / Gnu FDL

ps_thread_trx_info

Syntax

sys.ps_thread_trx_info(thread_id)

Description

ps_thread_trx_info is a stored function available with the Sys Schema.

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.

Examples

See Also

  • Sys Schema sys_config Table

This page is licensed: CC BY-SA / Gnu FDL

quote_identifier

Syntax

sys.quote_identifier(str)

Description

quote_identifier is a stored function available with the Sys Schema.

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.

Examples

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`                     |
+-----------------------------------------------------------+

This page is licensed: CC BY-SA / Gnu FDL

sys_get_config

Syntax

sys.sys_get_config(name,default)

Description

sys_get_config is a stored function available with the Sys Schema.

The function returns a configuration option value from the sys_config table. 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.

Examples

SELECT sys.sys_get_config('ps_thread_trx_info.max_length',NULL);
+----------------------------------------------------------+
| sys.sys_get_config('ps_thread_trx_info.max_length',NULL) |
+----------------------------------------------------------+
| 65535                                                    |
+----------------------------------------------------------+

See Also

  • Sys Schema sys_config Table

This page is licensed: CC BY-SA / Gnu FDL

format_bytes

Syntax

sys.format_bytes(double)

Description

format_bytes is a stored function available with the Sys Schema.

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 MariaDB 11.8, the built-in FORMAT_BYTES function can instead be used. The functions are similar, except that FORMAT_BYTES also displays exbibytes.

Examples

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                           |
+------------------------------------+------------------------------------+

This page is licensed: CC BY-SA / Gnu FDL

version_major

Syntax

sys.version_major()

Description

version_major is a stored function available with the Sys Schema.

It returns the MariaDB Server major release version.

Examples

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 |
+----------------+-------+-------+-------+

See Also

  • version_minor

  • version_patch

This page is licensed: CC BY-SA / Gnu FDL

version_minor

Syntax

sys.version_minor()

Description

version_minor is a stored function available with the Sys Schema.

It returns the MariaDB Server minor release version.

Examples

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 |
+----------------+-------+-------+-------+

See Also

  • version_major

  • version_patch

This page is licensed: CC BY-SA / Gnu FDL

version_patch

Syntax

sys.version_patch()

Description

version_patch is a stored function available with the Sys Schema.

It returns the MariaDB Server patch release version.

Examples

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 |
+----------------+-------+-------+-------+

See Also

  • version_major

  • version_minor

This page is licensed: CC BY-SA / Gnu FDL

Sys Schema Stored Procedures

This article is currently incomplete.

The following stored procedures are available in the Sys Schema.

create_synonym_db

Syntax

create_synonym_db(db_name,synonym)

# db_name (VARCHAR(64))
# synonym (VARCHAR(64))

Description

create_synonym_db is a stored procedure available with the Sys Schema.

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 performance_schema or information_schema databases.

Returns an error if the source database doesn't exist, or the synonym already exists.

Example

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       |
...

This page is licensed: CC BY-SA / Gnu FDL

optimizer_switch Helper Functions

Syntax

optimizer_switch_on()
optimizer_switch_off()
optimizer_switch_choice("on" | "off")

Description

The above procedures can be used to check which optimizer_switch options are on or off. The result set is sorted according to optimizer_switch option name.

Example

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  |
+-------------------------------+------+

Notes

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");

See Also

  • optimizer-switch Documentation for optimizer_switch

This page is licensed: CC BY-SA / Gnu FDL

ps_trace_statement_digest

Syntax

ps_trace_statement_digest(in_digest, in_runtime, in_interval, in_start_fresh, in_auto_enable)

Description

ps_trace_statement_digest is a stored procedure available with the Sys Schema.

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.

Examples

CALL sys.ps_trace_statement_digest('891ec6860f98ba46d89dd20b0c03652c', 5, 0.5, TRUE, TRUE);

This page is licensed: CC BY-SA / Gnu FDL

ps_trace_thread

Syntax

ps_trace_thread(thread_id, outfile, max_runtime, interval, start_fresh, auto_setup, debug)

Description

ps_trace_thread is a stored procedure available with the Sys Schema.

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.

  • 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.

Dumps all Performance Schema data for an instrumented thread to a .dot formatted graph file (for use with the DOT graph description language). All returned result sets should be used for a complete graph.

Session binary logging is disabled during execution, by adjusting the sql_log_bin session value (note the permissions required).

Examples

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 |
+-------------------------------------------------------------------+

This page is licensed: CC BY-SA / Gnu FDL

ps_truncate_all_tables

Syntax

ps_truncate_all_tables(bool display)

Description

ps_truncate_all_tables is a stored procedure available with the Sys Schema.

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 TRUNCATE TABLE statement before execution.

Examples

CALL sys.ps_truncate_all_tables(false);
+---------------------+
| summary             |
+---------------------+
| Truncated 44 tables |
+---------------------+
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 |
+---------------------+

This page is licensed: CC BY-SA / Gnu FDL

statement_performance_analyzer

Syntax

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')

Description

statement_performance_analyzer is a stored procedure available with the Sys Schema which returns a report on running statements.

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

table_exists

Syntax

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')

Description

table_exists is a stored procedure available with the Sys Schema.

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

Examples

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        |
+-------------+

This page is licensed: CC BY-SA / Gnu FDL

Sys Schema Views

This article is currently incomplete

host_summary and x$host_summary Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6

Description

The host_summary and x$host_summary views contain host activity information, grouped by host. The host_summary view is intended to be easier for human reading, while the x$host_summary view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

host

Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).

statements

Total number of statements for the host.

statement_latency

Total wait time of timed statements for the host.

statement_avg_latency

Average wait time per timed statement for the host.

table_scans

Total table scans for the host.

file_ios

Total file I/O events for the host.

file_io_latency

Total wait time of timed file I/O events for the host.

current_connections

Current connections for the host.

total_connections

Total connections for the host.

unique_users

Number of distinct users for the host.

current_memory

Current allocated memory for the host.

total_memory_allocated

Total allocated memory for the host.

Example

SELECT * FROM sys.host_summary\G
*************************** 1. row ***************************
                  host: localhost
            statements: 59
     statement_latency: 148.11 ms
 statement_avg_latency: 2.51 ms
           table_scans: 11
              file_ios: 2065
       file_io_latency: 79.57 ms
   current_connections: 1
     total_connections: 3
          unique_users: 1
        current_memory: -2672 bytes
total_memory_allocated: 0 bytes

SELECT * FROM sys.x$host_summary\G
*************************** 1. row ***************************
                  host: localhost
            statements: 98
     statement_latency: 160926285000
 statement_avg_latency: 1642104948.9796
           table_scans: 12
              file_ios: 2071
       file_io_latency: 79742533755
   current_connections: 1
     total_connections: 3
          unique_users: 1
        current_memory: -2672
total_memory_allocated: 0

This page is licensed: CC BY-SA / Gnu FDL

host_summary_by_file_io and x$host_summary_by_file_io Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6

Description

The host_summary_by_file_io and x$host_summary_by_file_io views summarize file I/O, grouped by host. Rows are sorted by descending total file I/O latency by default.

The host_summary_by_file_io view is intended to be easier for human reading, while the andx$host_summary_by_file_ioview provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

host

Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).

ios

Total file I/O events for the host.

ios_latency

Total wait time of timed file I/O events for the host.

Example

SELECT * FROM sys.host_summary_by_file_io\G
*************************** 1. row ***************************
      host: localhost
       ios: 6526
io_latency: 490.28 ms
*************************** 2. row ***************************
      host: background
       ios: 457
io_latency: 151.39 ms

SELECT * FROM sys.x$host_summary_by_file_io\G
*************************** 1. row ***************************
      host: localhost
       ios: 6532
io_latency: 490447878974
*************************** 2. row ***************************
      host: background
       ios: 457
io_latency: 151388125856

This page is licensed: CC BY-SA / Gnu FDL

host_summary_by_file_io_type and x$host_summary_by_file_io_type Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6

Description

The host_summary_by_file_io_type and x$host_summary_by_file_io_type views summarize file I/O, grouped by host and event type. Rows are sorted by host and descending total I/O latency by default. The host_summary_by_file_io_type view is intended to be easier for human reading, while the x$host_summary_by_file_io_type view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

host

Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).

event_name

File I/O event name.

total

Total number of occurrences of the file I/O event for the host.

total_latency

Total wait time of timed occurrences of the file I/O event for the host.

max_latency

Maximum single wait time of timed occurrences of the file I/O event for the host.

Example

SELECT * FROM sys.host_summary_by_file_io_type;
+------------+----------------------------------------------+-------+---------------+-------------+
| host       | event_name                                   | total | total_latency | max_latency |
+------------+----------------------------------------------+-------+---------------+-------------+
| background | wait/io/file/innodb/innodb_log_file          |    45 | 109.80 ms     | 26.48 ms    |
| background | wait/io/file/innodb/innodb_data_file         |   195 | 29.47 ms      | 1.23 ms     |
| background | wait/io/file/sql/global_ddl_log              |     4 | 4.45 ms       | 4.33 ms     |
...
| localhost  | wait/io/file/csv/data                        |     4 | 25.98 us      | 9.60 us     |
| localhost  | wait/io/file/partition/ha_partition::parfile |     1 | 14.19 us      | 14.19 us    |
| localhost  | wait/io/file/myisam/kfile                    |     1 | 11.95 us      | 11.95 us    |
+------------+----------------------------------------------+-------+---------------+-------------+

SELECT * FROM sys.x$host_summary_by_file_io_type;
+------------+----------------------------------------------+-------+---------------+-------------+
| host       | event_name                                   | total | total_latency | max_latency |
+------------+----------------------------------------------+-------+---------------+-------------+
| background | wait/io/file/innodb/innodb_log_file          |    45 |  109804643160 | 26478157582 |
| background | wait/io/file/innodb/innodb_data_file         |   195 |   29469738630 |  1226986584 |
| background | wait/io/file/sql/global_ddl_log              |     4 |    4447263252 |  4327780456 |
| localhost  | wait/io/file/csv/data                        |     4 |      25978718 |     9603922 |
| localhost  | wait/io/file/partition/ha_partition::parfile |     1 |      14191190 |    14191190 |
| localhost  | wait/io/file/myisam/kfile                    |     1 |      11954300 |    11954300 |
+------------+----------------------------------------------+-------+---------------+-------------+

This page is licensed: CC BY-SA / Gnu FDL

host_summary_by_stages and x$host_summary_by_stages Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The host_summary_by_stages and x$host_summary_by_stages views summarize statement stages, grouped by host. Rows are sorted by host and descending total latency by default. The host_summary_by_stages view is intended to be easier for human reading, while the x$host_summary_by_stages view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

host

Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).

event_name

Stage event name.

total

Total number of occurrences of the file stage event for the host.

total_latency

Total wait time of timed occurrences of the stage event for the host.

avg_latency

Average wait time per timed occurrence of the stage event for the host.

Example

SELECT * FROM sys.host_summary_by_stages\G
*************************** 1. row ***************************
         host: background
   event_name: stage/innodb/buffer pool load
        total: 1
total_latency: 3.75 ms
  avg_latency: 3.75 ms

SELECT * FROM sys.x$host_summary_by_stages\G
*************************** 1. row ***************************
         host: background
   event_name: stage/innodb/buffer pool load
        total: 1
total_latency: 3747098000
  avg_latency: 3747098000

This page is licensed: CC BY-SA / Gnu FDL

host_summary_by_statement_type and x$host_summary_by_statement_type Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The host_summary_by_statement_type and x$host_summary_by_statement_type views summarize information about executed statements, grouped by host and statement type. Rows are sorted by host and descending total latency by default.

The host_summary_by_statement_type view is intended to be easier for human reading, while the x$host_summary_by_statement_type view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

host

Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).

statement

Final component of the statement event name, for example create_table or select.

total

Total number of statement occurrences for the host.

total_latency

Total wait time of timed statements of the statement event for the host.

max_latency

Maximum single wait time of timed occurrences of the statement event for the host.

lock_latency

Total time spent by timed occurrences of the statement event for the host waiting for locks.

rows_sent

Total number of rows returned by occurrences of the statement event for the host.

rows_examined

Total number of rows read from storage engines by occurrences of the statement event for the host.

rows_affected

Total number of rows affected by occurrences of the statement event for the host.

full_scans

Total number of full table scans by occurrences of the statement event for the host.

Example

SELECT * FROM sys.host_summary_by_statement_type\G
*************************** 1. row ***************************
         host: localhost
    statement: create_table
        total: 18
total_latency: 366.93 ms
  max_latency: 48.02 ms
 lock_latency: 3.16 ms
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0
*************************** 2. row ***************************
         host: localhost
    statement: select
        total: 27
total_latency: 339.16 ms
  max_latency: 64.51 ms
 lock_latency: 205.61 ms
    rows_sent: 750599937895926
rows_examined: 13925
rows_affected: 0
   full_scans: 21

...

SELECT * FROM sys.x$host_summary_by_statement_type\G
*************************** 1. row ***************************
         host: localhost
    statement: create_table
        total: 18
total_latency: 366927804000
  max_latency: 48023563000
 lock_latency: 3156000000
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0
*************************** 2. row ***************************
         host: localhost
    statement: select
        total: 28
total_latency: 343873182000
  max_latency: 64507216000
 lock_latency: 205984000000
    rows_sent: 750678474440767
rows_examined: 14370
rows_affected: 0
   full_scans: 22

This page is licensed: CC BY-SA / Gnu FDL

innodb_buffer_stats_by_schema and x$innodb_buffer_stats_by_schema Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Querying these views can have a noticeable performance impact on a production server.

Description

The innodb_buffer_stats_by_schema and x$innodb_buffer_stats_by_schema views summarize information from the Information Schema INNODB_BUFFER_PAGE table, grouped by schema. Rows are sorted by descending buffer size by default.

The innodb_buffer_stats_by_schema view is intended to be easier for human reading, while the x$innodb_buffer_stats_by_schema view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

object_schema

Object schema name, or InnoDB System if the table belongs to the InnoDB storage engine.

allocated

Total number of bytes allocated for the schema.

data

Total number of data bytes allocated for the schema.

pages

Total number of pages allocated for the schema.

pages_hashed

Total number of hashed pages allocated for the schema.

pages_old

Total number of old pages allocated for the schema.

rows_cached

Total number of cached rows for the schema.

Example

SELECT * FROM sys.innodb_buffer_stats_by_schema\G
*************************** 1. row ***************************
object_schema: InnoDB System
    allocated: 160.00 KiB
         data: 6.21 KiB
        pages: 10
 pages_hashed: 10
    pages_old: 10
  rows_cached: 21
*************************** 2. row ***************************
object_schema: mysql
    allocated: 112.00 KiB
         data: 1.73 KiB
        pages: 7
 pages_hashed: 7
    pages_old: 7
  rows_cached: 5
*************************** 3. row ***************************
object_schema: test
    allocated: 64.00 KiB
         data: 0 bytes
        pages: 4
 pages_hashed: 4
    pages_old: 4
  rows_cached: 0

SELECT * FROM sys.x$innodb_buffer_stats_by_schema\G
*************************** 1. row ***************************
object_schema: InnoDB System
    allocated: 163840
         data: 6362
        pages: 10
 pages_hashed: 0
    pages_old: 0
  rows_cached: 21
*************************** 2. row ***************************
object_schema: mysql
    allocated: 114688
         data: 1775
        pages: 7
 pages_hashed: 0
    pages_old: 0
  rows_cached: 5
*************************** 3. row ***************************
object_schema: test
    allocated: 65536
         data: 0
        pages: 4
 pages_hashed: 0
    pages_old: 0
  rows_cached: 0

This page is licensed: CC BY-SA / Gnu FDL

innodb_buffer_stats_by_table and x$innodb_buffer_stats_by_table Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Querying these views can have a noticeable performance impact on a production server.

Description

The innodb_buffer_stats_by_table and x$innodb_buffer_stats_by_table views summarize information from the Information Schema INNODB_BUFFER_PAGE table, grouped by schema and table. Rows are sorted by descending buffer size by default.

The innodb_buffer_stats_by_table view is intended to be easier for human reading, while the x$innodb_buffer_stats_by_table view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

object_schema

Object schema name, or InnoDB System if the table belongs to the InnoDB storage engine.

object_name

Table name.

allocated

Total number of bytes allocated for the table.

data

Number of data bytes allocated for the table.

pages

Total number of pages allocated for the table.

pages_hashed

Total number of hashed pages allocated for the table.

pages_old

Total number of old pages allocated for the table.

rows_cached

Total number of cached rows for the table.

Example

SELECT * FROM sys.innodb_buffer_stats_by_table\G
*************************** 1. row ***************************
object_schema: mysql
  object_name: transaction_registry
    allocated: 64.00 KiB
         data: 0 bytes
        pages: 4
 pages_hashed: 4
    pages_old: 4
  rows_cached: 0
*************************** 2. row ***************************
object_schema: InnoDB System
  object_name: SYS_FOREIGN
    allocated: 48.00 KiB
         data: 0 bytes
        pages: 3
 pages_hashed: 3
    pages_old: 3
  rows_cached: 0
*************************** 3. row ***************************
object_schema: InnoDB System
  object_name: SYS_TABLES
    allocated: 32.00 KiB
         data: 1.07 KiB
        pages: 2
 pages_hashed: 2
    pages_old: 2
  rows_cached: 10

...

 SELECT * FROM sys.x$innodb_buffer_stats_by_table\G
*************************** 1. row ***************************
object_schema: mysql
  object_name: transaction_registry
    allocated: 65536
         data: 0
        pages: 4
 pages_hashed: 0
    pages_old: 0
  rows_cached: 0
*************************** 2. row ***************************
object_schema: InnoDB System
  object_name: SYS_FOREIGN
    allocated: 49152
         data: 0
        pages: 3
 pages_hashed: 0
    pages_old: 0
  rows_cached: 0
*************************** 3. row ***************************
object_schema: InnoDB System
  object_name: SYS_TABLES
    allocated: 32768
         data: 1100
        pages: 2
 pages_hashed: 0
    pages_old: 0
  rows_cached: 10
....

This page is licensed: CC BY-SA / Gnu FDL

innodb_lock_waits and x$innodb_lock_waits Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The innodb_lock_waits and x$innodb_lock_waits views summarize InnoDB locks that transactions are waiting upon, by default sorted in descending buffer size.

The innodb_lock_waits view is intended to be easier for human reading, while the x$innodb_lock_waits view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

wait_started

Time that lock wait began.

wait_age

TIME value for the length of the lock wait.

wait_age_secs

Seconds value for the length of the lock wait.

locked_table_schema

Schema containing the locked table.

locked_table_name

Name of the locked table.

locked_table_partition

Name of the locked partition, or NULL if none.

locked_table_subpartition

Name of the locked subpartition, or NULL if none.

locked_index

Name of the locked index.

locked_type

Type of the waiting lock.

waiting_trx_id

ID of the waiting transaction.

waiting_trx_started

Time that the waiting transaction started.

waiting_trx_age

TIME value for the length of time that the transaction has been waiting.

waiting_trx_rows_locked

Number of rows locked by the waiting transaction.

waiting_trx_rows_modified

Number of rows modified by the waiting transaction.

waiting_pid

Processlist ID of the waiting transaction.

waiting_query

Statement waiting for the lock.

waiting_lock_id

ID of the waiting lock.

waiting_lock_mode

Mode of the waiting lock.

blocking_trx_id

ID of the transaction blocking the waiting lock.

blocking_pid

Processlist ID of the blocking transaction.

blocking_query

Statement the blocking transaction is executing, or NULL if the session that issued the blocking query has become idle.

blocking_lock_id

ID of the lock blocking the waiting lock.

blocking_lock_mode

Mode of the lock blocking the waiting lock.

blocking_trx_started

Time the blocking transaction started.

blocking_trx_age

TIME value for how long the blocking transaction has been executing.

blocking_trx_rows_locked

Number of rows locked by the blocking transaction.

blocking_trx_rows_modified

Number of rows modified by the blocking transaction.

sql_kill_blocking_query

KILL statement that could be used to kill the blocking statement.

sql_kill_blocking_connection

KILL statement that could be used to kill the blocking statement session.

This page is licensed: CC BY-SA / Gnu FDL

io_by_thread_by_latency and x$io_by_thread_by_latency Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The io_by_thread_by_latency and x$io_by_thread_by_latency view summarize I/O consumers to display time waiting for I/O, grouped by thread. Rows are sorted by descending total I/O latency by default.

The io_by_thread_by_latency view is intended to be easier for human reading, while the x$io_by_thread_by_latency view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

user

The account associated with a foreground thread, or the background thread name

total

Total number of I/O events allocated for the thread.

total_latency

Total wait time of timed I/O events for the thread.

min_latency

Minimum single wait time of timed I/O events for the thread.

avg_latency

Average wait time per timed I/O event for the thread.

min_latency

Maximum single wait time of timed I/O events for the thread.

thread_id

Thread id.

processlist_id

Processlist id for foreground threads, or NULL for background threads.

Example

SELECT * FROM sys.io_by_thread_by_latency\G
*************************** 1. row ***************************
          user: main
         total: 378
 total_latency: 40.11 ms
   min_latency: 570.21 ns
   avg_latency: 206.02 us
   max_latency: 4.33 ms
     thread_id: 1
processlist_id: NULL
*************************** 2. row ***************************
          user: msandbox@localhost
         total: 521
 total_latency: 10.28 ms
   min_latency: 775.04 ns
   avg_latency: 21.79 us
   max_latency: 977.79 us
     thread_id: 89
processlist_id: 7

...

SELECT * FROM sys.x$io_by_thread_by_latency\G
*************************** 1. row ***************************
          user: main
         total: 378
 total_latency: 40106340880
   min_latency: 570208
   avg_latency: 206016046.6000
   max_latency: 4327780456
     thread_id: 1
processlist_id: NULL
*************************** 2. row ***************************
          user: msandbox@localhost
         total: 498
 total_latency: 9637694714
   min_latency: 775040
   avg_latency: 21364289.0000
   max_latency: 977787350
     thread_id: 89
processlist_id: 7

...

This page is licensed: CC BY-SA / Gnu FDL

io_global_by_file_by_bytes and x$io_global_by_file_by_bytes Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The io_global_by_file_by_bytes and x$io_global_by_file_by_bytes views summarize global I/O consumers showing I/O in bytes, grouped by file. Rows are sorted by descending total I/O (bytes read and written) by default.

The io_global_by_file_by_bytes view is intended to be easier for human reading, while the x$io_global_by_file_by_bytes view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

file

File path name.

count_read

Total number of read events for the file.

total_read

Total number of bytes read from the file.

avg_read

Average number of bytes per read from the file.

count_write

Total number of write events for the file.

total_written

Total number of bytes written to the file.

avg_write

Average number of bytes per write to the file.

total

Total number of bytes read and written for the file.

write_pct

Percentage of total I/O bytes that were writes.

Example

SELECT * FROM sys.io_global_by_file_by_bytes\G
...
*************************** 3. row ***************************
         file: @@datadir/ddl_recovery.log
   count_read: 0
   total_read: 0 bytes
     avg_read: 0 bytes
  count_write: 114
total_written: 220.17 KiB
    avg_write: 1.93 KiB
        total: 220.17 KiB
    write_pct: 100.00
*************************** 4. row ***************************
         file: @@datadir/ib_logfile0
   count_read: 6
   total_read: 66.50 KiB
     avg_read: 11.08 KiB
  count_write: 43
total_written: 81.00 KiB
    avg_write: 1.88 KiB
        total: 147.50 KiB
    write_pct: 54.92
...

SELECT * FROM sys.x$io_global_by_file_by_bytes\G
...
*************************** 3. row ***************************
         file: /home/ian/sandboxes/msb_10_6_19/data/ddl_recovery.log
   count_read: 0
   total_read: 0
     avg_read: 0.0000
  count_write: 114
total_written: 225459
    avg_write: 1977.7105
        total: 225459
    write_pct: 100.00
*************************** 4. row ***************************
         file: /home/ian/sandboxes/msb_10_6_19/data/ib_logfile0
   count_read: 6
   total_read: 68096
     avg_read: 11349.3333
  count_write: 43
total_written: 82944
    avg_write: 1928.9302
        total: 151040
    write_pct: 54.92
...

This page is licensed: CC BY-SA / Gnu FDL

io_global_by_file_by_latency and x$io_global_by_file_by_latency Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The io_global_by_file_by_latency and x$io_global_by_file_by_latency views summarize global I/O consumers to display time waiting for I/O, grouped by file. Rows are sorted by descending total latency by default.

The io_global_by_file_by_latency view is intended to be easier for human reading, while the x$io_global_by_file_by_latency view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

file

File path name.

total

Total number of I/O events for the file.

total_latency

Total wait time of timed I/O events for the file.

count_read

Total number of read I/O events for the file.

read_latency

Total wait time of timed read I/O events for the file.

count_write

Total number of write I/O events for the file.

write_latency

Total wait time of timed write I/O events for the file.

count_misc

Total number of other I/O events for the file.

misc_latency

Total wait time of timed other I/O events for the file.

Example

SELECT * FROM sys.io_global_by_file_by_latency\G
*************************** 1. row ***************************
         file: @@datadir/ddl_recovery.log
        total: 222
total_latency: 288.64 ms
   count_read: 0
 read_latency: 0 ps
  count_write: 114
write_latency: 2.59 ms
   count_misc: 108
 misc_latency: 286.05 ms
*************************** 2. row ***************************
         file: @@datadir/ib_logfile0
        total: 95
total_latency: 165.29 ms
   count_read: 6
 read_latency: 61.04 us
  count_write: 43
write_latency: 1.31 ms
   count_misc: 46
 misc_latency: 163.92 ms
...

SELECT * FROM sys.x$io_global_by_file_by_latency\G
*************************** 1. row ***************************
         file: /home/ian/sandboxes/msb_10_6_19/data/ddl_recovery.log
        total: 222
total_latency: 288641408158
   count_read: 0
 read_latency: 0
  count_write: 114
write_latency: 2594925264
   count_misc: 108
 misc_latency: 286046482894
*************************** 2. row ***************************
         file: /home/ian/sandboxes/msb_10_6_19/data/ib_logfile0
        total: 95
total_latency: 165291020006
   count_read: 6
 read_latency: 61040974
  count_write: 43
write_latency: 1310187820
   count_misc: 46
 misc_latency: 163919791212
...

This page is licensed: CC BY-SA / Gnu FDL

io_global_by_wait_by_bytes and x$io_global_by_wait_by_bytes Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The io_global_by_wait_by_bytes and x$io_global_by_wait_by_bytes views summarize global I/O consumers, displaying amount of I/O and time waiting for I/O, grouped by event. Rows are sorted by descending total I/O (bytes read and written) by default.

The io_global_by_wait_by_bytes view is intended to be easier for human reading, while the x$io_global_by_wait_by_bytes view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

event_name

I/O event name. The wait/io/file prefix is stripped.

total

Total number of occurrences of the I/O event.

total_latency

Total wait time of timed occurrences of the I/O event

min_latency

Minimum single wait time of timed occurrences of the I/O event.

avg_latency

Average wait time per timed occurrence of the I/O event.

max_latency

Maximum single wait time of timed occurrences of the I/O event.

count_read

Total number of read I/O events for the file.

total_read

Total number of bytes read for the I/O event.

avg_read

Average number of bytes per read for the I/O event.

count_write

Total number of write requests for the I/O event.

total_written

Number of bytes written for the I/O event.

avg_written

Average number of bytes per write for the I/O event.

total_requested

Total number of bytes (read and write) for the I/O event.

Example

SELECT * FROM sys.io_global_by_wait_by_bytes\G
*************************** 1. row ***************************
     event_name: innodb/innodb_data_file
          total: 220
  total_latency: 38.96 ms
    min_latency: 0 ps
    avg_latency: 177.09 us
    max_latency: 4.07 ms
     count_read: 174
     total_read: 4.73 MiB
       avg_read: 27.86 KiB
    count_write: 0
  total_written: 0 bytes
    avg_written: 0 bytes
total_requested: 4.73 MiB
*************************** 2. row ***************************
     event_name: aria/MAD
          total: 1107
  total_latency: 18.27 ms
    min_latency: 0 ps
    avg_latency: 16.50 us
    max_latency: 204.97 us
     count_read: 105
     total_read: 840.00 KiB
       avg_read: 8.00 KiB
    count_write: 0
  total_written: 0 bytes
    avg_written: 0 bytes
total_requested: 840.00 KiB

...

SELECT * FROM sys.x$io_global_by_wait_by_bytes\G
*************************** 1. row ***************************
     event_name: innodb/innodb_data_file
          total: 220
  total_latency: 38959722138
    min_latency: 0
    avg_latency: 177089374
    max_latency: 4065566778
     count_read: 174
     total_read: 4964352
       avg_read: 28530.7586
    count_write: 0
  total_written: 0
    avg_written: 0.0000
total_requested: 4964352
*************************** 2. row ***************************
     event_name: aria/MAD
          total: 1107
  total_latency: 18270683624
    min_latency: 0
    avg_latency: 16504546
    max_latency: 204973168
     count_read: 105
     total_read: 860160
       avg_read: 8192.0000
    count_write: 0
  total_written: 0
    avg_written: 0.0000
total_requested: 860160

...

This page is licensed: CC BY-SA / Gnu FDL

io_global_by_wait_by_latency and x$io_global_by_wait_by_latency Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The io_global_by_wait_by_latency and x$io_global_by_wait_by_latency views summarize global I/O consumers, displaying I/O and time waiting for I/O, grouped by event. Rows are sorted by descending total latency by default.

The io_global_by_wait_by_latency view is intended to be easier for human reading, while the x$io_global_by_wait_by_latency view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

event_name

I/O event name. The wait/io/file prefix is stripped.

total

Total number of occurrences of the I/O event.

total_latency

Total wait time of timed occurrences of the I/O event.

min_latency

Minimum single wait time of timed occurrences of the I/O event.

avg_latency

Average wait time per timed occurrence of the I/O event.

max_latency

Maximum single wait time of timed occurrences of the I/O event.

count_read

Total number of read request for the I/O event.

total_read

Total number of bytes read for the I/O event.

avg_read

Average number of bytes per read for the I/O event.

count_write

Total number of write requests for the I/O event.

total_written

Number of bytes written for the I/O event.

avg_written

Average number of bytes per write for the I/O event.

total_requested

Total number of bytes (read and write) for the I/O event.

Example

SELECT * FROM sys.io_global_by_wait_by_latency\G
*************************** 1. row ***************************
   event_name: sql/global_ddl_log
        total: 223
total_latency: 288.66 ms
  avg_latency: 1.29 ms
  max_latency: 26.07 ms
 read_latency: 0 ps
write_latency: 2.59 ms
 misc_latency: 286.07 ms
   count_read: 0
   total_read: 0 bytes
     avg_read: 0 bytes
  count_write: 114
total_written: 220.17 KiB
  avg_written: 1.93 KiB
*************************** 2. row ***************************
   event_name: innodb/innodb_log_file
        total: 95
total_latency: 165.29 ms
  avg_latency: 1.74 ms
  max_latency: 26.48 ms
 read_latency: 61.04 us
write_latency: 1.31 ms
 misc_latency: 163.92 ms
   count_read: 6
   total_read: 66.50 KiB
     avg_read: 11.08 KiB
  count_write: 43
total_written: 81.00 KiB
  avg_written: 1.88 KiB
...

SELECT * FROM sys.x$io_global_by_wait_by_latency\G
*************************** 1. row ***************************
   event_name: sql/global_ddl_log
        total: 223
total_latency: 288663966666
  avg_latency: 1294456930
  max_latency: 26072142152
 read_latency: 0
write_latency: 2594925264
 misc_latency: 286069041402
   count_read: 0
   total_read: 0
     avg_read: 0.0000
  count_write: 114
total_written: 225459
  avg_written: 1977.7105
*************************** 2. row ***************************
   event_name: innodb/innodb_log_file
        total: 95
total_latency: 165291020006
  avg_latency: 1739905288
  max_latency: 26478157582
 read_latency: 61040974
write_latency: 1310187820
 misc_latency: 163919791212
   count_read: 6
   total_read: 68096
     avg_read: 11349.3333
  count_write: 43
total_written: 82944
  avg_written: 1928.9302
...

This page is licensed: CC BY-SA / Gnu FDL

latest_file_io and x$latest_file_io Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The latest_file_io and x$latest_file_io views summarize file I/O activity, grouped by file and thread. Rows are sorted by most recent I/O by default.

The latest_file_io view is intended to be easier for human reading, while the x$latest_file_io view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

thread

Account associated with the thread for foreground threads (port number for TCP/IP connections), or thread name and thread ID for background threads.

total

Total number of occurrences of the I/O event.

file

File path name.

latency

Wait time of the file I/O event.

operation

Type of operation

requested

Number of bytes requested for the file I/O event.

This page is licensed: CC BY-SA / Gnu FDL

memory_by_host_by_current_bytes and x$memory_by_host_by_current_bytes Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The memory_by_host_by_current_bytes and x$memory_by_host_by_current_bytes summarize memory use grouped by host. Rows by default are sorted by descending amount of memory used.

The memory_by_host_by_current_bytes view is intended to be easier for human reading, while the x$memory_by_host_by_current_bytes view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

host

Host from which the client connected. If the HOST column in the underlying Performance Schema table is NULL, rows are assumed to be for background threads, and the background host name is used.

current_count_used

Current number of allocated memory blocks that have not yet been freed for the host.

current_allocated

Current number of allocated bytes that have not yet been freed for the host.

current_avg_alloc

Current number of allocated bytes per memory block for the host.

current_max_alloc

Largest single current memory allocation in bytes for the host.

total_allocated

Total memory allocation in bytes for the host.

This page is licensed: CC BY-SA / Gnu FDL

metrics Sys Schema View

MariaDB starting with 10.6

The Sys Schema was introduced in MariaDB 10.6

Description

The metrics view contains global status variables (as found in the Performance Schema global_status Table), InnoDB metrics (as found in the Information Schema INNODB_METRICS Table, current and total memory allocation, based on the Performance Schema memory instrumentation, as well the current time in human readable and Unix timestamp formats.

It contains the following columns:

Column
Description

Variable_name

The name of the metric. One of the VARIABLE_NAME column if a global_status table, the NAME column if an InnoDB metric, or a description for other metrics.

Variable_value

The metric value. One of the VARIABLE_VALUE column if a global status variable, the COUNT column for InnoDB metrics, the related column from the Performance Schema memory_summary_global_by_event_name table, the value of NOW(3) or UNIX_TIMESTAMP(NOW(3)).

Type

Metric type. One of Global Status, InnoDB Metrics - % (with % being the value of the SUBSYSTEM column in the INNODB_METRICS table, Performance Schema or System Time

Enabled

Whether the metric is enabled. Always YES for global status variables and the current time. For InnoDB metrics, YES only if the STATUS column of the INNODB_METRICS table, otherwise NO. For memory metrics: YES, NO or PARTIAL (for metrics where not all memory/% instruments are enabled). Performance Schema memory instruments are always enabled)

Example

SELECT * FROM sys.metrics\G
*************************** 1. row ***************************
 Variable_name: aborted_clients
Variable_value: 0
          Type: Global Status
       Enabled: YES
*************************** 2. row ***************************
 Variable_name: aborted_connects
Variable_value: 0
          Type: Global Status
       Enabled: YES

...

*************************** 578. row ***************************
 Variable_name: trx_undo_slots_used
Variable_value: 0
          Type: InnoDB Metrics - transaction
       Enabled: YES
*************************** 579. row ***************************
 Variable_name: NOW()
Variable_value: 2024-09-09 16:16:08.745
          Type: System Time
       Enabled: YES
*************************** 580. row ***************************
 Variable_name: UNIX_TIMESTAMP()
Variable_value: 1725891368.745
          Type: System Time
       Enabled: YES

This page is licensed: CC BY-SA / Gnu FDL

privileges_by_table_by_level Sys Schema View

MariaDB starting with 11.4

This Sys Schema view was introduced in MariaDB 11.4.0.

Description

Shows granted privileges broken down by table on which they allow access and level on which they were granted.

For example, if a user x has SELECT privilege granted ON db.*, this view will list all tables in the db schema with the user x having SELECT privilege on them. This is different from INFORMATION_SCHEMA.TABLE_PRIVILEGES, which only lists privileges granted on the table level.

Column
Description

TABLE_SCHEMA

Database name.

TABLE_NAME

Table name.

GRANTEE

Account name that was granted the privilege.

PRIVILEGE

Privilege, such as SELECT or DROP.

LEVEL

Privilege level, such as GLOBAL or SCHEMA.

Example

SELECT * FROM sys.privileges_by_table_by_level;
+--------------+------------+---------------------------+----------------+--------+
| TABLE_SCHEMA | TABLE_NAME | GRANTEE                   | PRIVILEGE      | LEVEL  |
+--------------+------------+---------------------------+----------------+--------+
...
| test         | t2         | 'root'@'localhost'        | SELECT         | GLOBAL |
| test         | t1         | 'root'@'localhost'        | SELECT         | GLOBAL |
| test         | t3         | 'root'@'localhost'        | SELECT         | GLOBAL |
| test         | t2         | 'root'@'localhost'        | INSERT         | GLOBAL |
| test         | t1         | 'root'@'localhost'        | INSERT         | GLOBAL |
| test         | t3         | 'root'@'localhost'        | INSERT         | GLOBAL |
| test         | t2         | 'root'@'localhost'        | UPDATE         | GLOBAL |
| test         | t1         | 'root'@'localhost'        | UPDATE         | GLOBAL |
| test         | t3         | 'root'@'localhost'        | UPDATE         | GLOBAL |
| test         | t2         | 'root'@'localhost'        | DELETE         | GLOBAL |
| test         | t1         | 'root'@'localhost'        | DELETE         | GLOBAL |
| test         | t3         | 'root'@'localhost'        | DELETE         | GLOBAL |
| test         | t2         | 'root'@'localhost'        | CREATE         | GLOBAL |
| test         | t1         | 'root'@'localhost'        | CREATE         | GLOBAL |
| test         | t3         | 'root'@'localhost'        | CREATE         | GLOBAL |
| test         | t2         | 'root'@'localhost'        | DROP           | GLOBAL |
| test         | t1         | 'root'@'localhost'        | DROP           | GLOBAL |
| test         | t3         | 'root'@'localhost'        | DROP           | GLOBAL |
| test         | t2         | 'root'@'localhost'        | REFERENCES     | GLOBAL |
| test         | t1         | 'root'@'localhost'        | REFERENCES     | GLOBAL |
| test         | t3         | 'root'@'localhost'        | REFERENCES     | GLOBAL |
| test         | t2         | 'root'@'localhost'        | INDEX          | GLOBAL |
| test         | t1         | 'root'@'localhost'        | INDEX          | GLOBAL |
| test         | t3         | 'root'@'localhost'        | INDEX          | GLOBAL |
| test         | t2         | 'root'@'localhost'        | ALTER          | GLOBAL |
| test         | t1         | 'root'@'localhost'        | ALTER          | GLOBAL |
| test         | t3         | 'root'@'localhost'        | ALTER          | GLOBAL |
| test         | t2         | 'root'@'localhost'        | SHOW VIEW      | GLOBAL |
| test         | t1         | 'root'@'localhost'        | SHOW VIEW      | GLOBAL |
| test         | t3         | 'root'@'localhost'        | SHOW VIEW      | GLOBAL |
| test         | t2         | 'root'@'localhost'        | TRIGGER        | GLOBAL |
| test         | t1         | 'root'@'localhost'        | TRIGGER        | GLOBAL |
| test         | t3         | 'root'@'localhost'        | TRIGGER        | GLOBAL |
| test         | t2         | 'root'@'localhost'        | DELETE HISTORY | GLOBAL |
| test         | t1         | 'root'@'localhost'        | DELETE HISTORY | GLOBAL |
| test         | t3         | 'root'@'localhost'        | DELETE HISTORY | GLOBAL |
| test         | t2         | 'PUBLIC'@''               | SELECT         | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | SELECT         | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | SELECT         | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | INSERT         | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | INSERT         | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | INSERT         | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | UPDATE         | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | UPDATE         | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | UPDATE         | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | DELETE         | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | DELETE         | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | DELETE         | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | CREATE         | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | CREATE         | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | CREATE         | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | DROP           | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | DROP           | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | DROP           | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | REFERENCES     | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | REFERENCES     | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | REFERENCES     | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | INDEX          | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | INDEX          | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | INDEX          | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | ALTER          | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | ALTER          | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | ALTER          | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | SHOW VIEW      | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | SHOW VIEW      | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | SHOW VIEW      | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | TRIGGER        | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | TRIGGER        | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | TRIGGER        | SCHEMA |
| test         | t2         | 'PUBLIC'@''               | DELETE HISTORY | SCHEMA |
| test         | t1         | 'PUBLIC'@''               | DELETE HISTORY | SCHEMA |
| test         | t3         | 'PUBLIC'@''               | DELETE HISTORY | SCHEMA |
+--------------+------------+---------------------------+----------------+--------+

See Also

  • GRANT (description of the privileges and how to grant them)

  • INFORMATION_SCHEMA.TABLE_PRIVILEGES

  • MDEV-24486

This page is licensed: CC BY-SA / Gnu FDL

schema_auto_increment_columns Sys Schema View

MariaDB starting with 10.6

The Sys Schema was introduced in MariaDB 10.6

Description

Information about AUTO_INCREMENT columns, sorted by descending usage ratio and maximum column value. Does not include columns in the mysql, sys, information_schema and performance_schema schemas.

Contains the following columns:

Column
Description

table_schema

Schema name containing the table with the auto_increment attribute.

table_name

Table containing the auto_increment attribute.

column_name

Name of the column containing the auto_increment attribute.

data_type

Data type of the auto_increment column, for example tinyint.

column_type

Data type of the auto_increment column, plus additional information for example tinyint(3) unsigned.

is_signed

1 if the column is signed,0 if not.

is_unsigned

1 if the column is unsigned,0 if it is.

max_value

Maximum possible value for the column, for example 255 for an unsigned tinyint.

auto_increment

Current auto_increment value for the column.

auto_increment_ratio

Ratio of used to maximum value for the auto_increment column.

Example

CREATE OR REPLACE TABLE animals (
    id TINYINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

 INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('fox'),('whale'),('ostrich');

SELECT * FROM sys.schema_auto_increment_columns\G
*************************** 1. row ***************************
        table_schema: test
          table_name: animals
         column_name: id
           data_type: tinyint
         column_type: tinyint(4)
           is_signed: 1
         is_unsigned: 0
           max_value: 127
      auto_increment: 7
auto_increment_ratio: 0.0551

This page is licensed: CC BY-SA / Gnu FDL

schema_object_overview Sys Schema View

MariaDB starting with 10.6

The Sys Schema was introduced in MariaDB 10.6

Description

A count of the number of objects within each schema, sorted by schema and object.

Contains the following columns:

Column
Description

db

Schema name

object_type

Object name

count

Count of the number of objects

Example

SELECT * FROM sys.schema_object_overview;
+--------------------+---------------+-------+
| db                 | object_type   | count |
+--------------------+---------------+-------+
| information_schema | SYSTEM VIEW   |    79 |
| mysql              | BASE TABLE    |    30 |
| mysql              | INDEX (BTREE) |    76 |
| mysql              | PROCEDURE     |     2 |
| mysql              | VIEW          |     1 |
| performance_schema | BASE TABLE    |    81 |
| sys                | BASE TABLE    |     1 |
| sys                | FUNCTION      |    22 |
| sys                | INDEX (BTREE) |     1 |
| sys                | PROCEDURE     |    26 |
| sys                | VIEW          |   100 |
+--------------------+---------------+-------+

This page is licensed: CC BY-SA / Gnu FDL

host_summary_by_statement_latency and x$host_summary_by_statement_latency Sys Schema Views

MariaDB starting with 10.6

These Sys Schema views were introduced in MariaDB 10.6.

Description

The host_summary_by_statement_latency and x$host_summary_by_statement_latency views summarize statement statistics, grouped by host. Rows are sorted by descending total latency by default. The host_summary_by_statement_latency view is intended to be easier for human reading, while the x$host_summary_by_statement_latency view provides the data in raw form, intended for tools that process the data.

They contain the following columns:

Column
Description

host

Host that the client connected from, or background for background threads (where the HOST column in the underlying Performance Schema table is NULL).

total

Total number of statements for the host.

max_latency

Maximum single wait time of timed statements for the host.

lock_latency

Total time spent by timed statements for the host waiting for locks.

total_latency

Total wait time of timed statements for the host.

rows_sent

Total number of rows returned by statements for the host.

rows_examined

Total number of rows read from storage engines by statements for the host.

rows_affected

Total number of rows affected by statements for the host.

full_scans

Total number of full table scans by statements for the host.

Example

SELECT * FROM sys.host_summary_by_statement_latency\G
*************************** 1. row ***************************
         host: localhost
        total: 1042
total_latency: 816.89 ms
  max_latency: 64.51 ms
 lock_latency: 215.64 ms
    rows_sent: 750599937895985
rows_examined: 13548
rows_affected: 6
   full_scans: 33
*************************** 2. row ***************************
         host: background
        total: 0
total_latency: 0 ps
  max_latency: 0 ps
 lock_latency: 0 ps
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0

SELECT * FROM sys.x$host_summary_by_statement_latency\G
*************************** 1. row ***************************
         host: localhost
        total: 1041
total_latency: 812132706000
  max_latency: 64507216000
 lock_latency: 215301000000
    rows_sent: 750599937895983
rows_examined: 13110
rows_affected: 6
   full_scans: 32
*************************** 2. row ***************************
         host: background
        total: 0
total_latency: 0
  max_latency: 0
 lock_latency: 0
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0

This page is licensed: CC BY-SA / Gnu FDL