Information schema hands-on

Posted on , updated on by Yannick Jaquier
MariaDB, MySQL ,

Table of contents

Preamble

Information Schema is a database accessible on all MariaDB/MySQL instances that provide access to instance metadata:

  • Information about the MariaDB/MySQL server
  • List of databases and tables of the instance
  • The data type of tables columns of other databases
  • Access privileges
  • Thread pool information
  • InnoDB information
  • In a more generic way of saying it: data dictionary and system catalog

Information Schema is also an alternative of the SHOW command to have an access to metadata information using the classical SELECT command (Codd’s rules). It is also advisable to be careful when joining Information Schema tables/views as performance can quickly drop/impact your instance (usage of EXPLAIN command is suggested).

My blog post will focus on the InnoDB part of Information Schema as well as few key tables. I will not review the metadata tables that are classical.

My test server has 64MB with 12 cores (24 threads) and is running with Red Hat Enterprise Linux release 8.7 (Ootpa). My MariaDB Community edition database is 10.11.7 and I’m using sysbench 1.0.20 to generate some workload to have some figures to report in my blog post.

Information Schema configuration

Information Schema is activated and configured by default. For most of the Information Schema tables you have nothing to do. The only small piece of configuration I have seen is for the InnoDB metrics where you can activate metrics individually by their name like:

MariaDB [(none)]> set global innodb_monitor_enable = 'innodb_dict_lru_count_active';
Query OK, 0 rows affected (0.000 sec)

Or by group using one of the subsystem group name like:

MariaDB [(none)]> set global innodb_monitor_enable = 'module_buffer';
Query OK, 0 rows affected (0.001 sec)
 
MariaDB [(none)]> show variables like 'innodb_monitor_enable';
+-----------------------+---------------+
| Variable_name         | Value         |
+-----------------------+---------------+
| innodb_monitor_enable | module_buffer |
+-----------------------+---------------+
1 row in set (0.001 sec)
 
MariaDB [(none)]> set global innodb_monitor_enable = 'module_buffer';
Query OK, 0 rows affected (0.001 sec)
 
MariaDB [performance_schema]> show variables like 'innodb_monitor_enable';
+-----------------------+--------------------+
| Variable_name         | Value              |
+-----------------------+--------------------+
| innodb_monitor_enable | module_buffer_page |
+-----------------------+--------------------+
1 row in set (0.001 sec)

Remark:
Yes, there is a small glitch in the official documentation and the value to set is module_buffer_page and not module_buf_page.

It means that if you want to set them at startup, same as binlog_ignore_db parameter, you need to set the parameter multiple time as it does not accept multiple values:

innodb_monitor_enable   = module_buffer
innodb_monitor_enable   = module_buffer_page

Then check in information_schema.innodb_metrics table with enabled and time_enabled columns if activation is active. You can also disable them using innodb_monitor_disable parameter.

Information Schema global status queries

The information_schema.global_status table is so huge that I will, for sure, not display all the variables. This is what you would display with a show [global] status command. Variables values are incremental since the instance has been started. Same as Performance Schema it is strongly suggested to display those figures with a graphical tool:

information_schema01
information_schema01

Aborted clients

With a security cap, number of unsuccessful tentative connection to your instance:

MariaDB [(none)]> select variable_name, variable_value from information_schema.global_status where variable_name = 'access_denied_errors';
+----------------------+----------------+
| variable_name        | variable_value |
+----------------------+----------------+
| ACCESS_DENIED_ERRORS | 12             |
+----------------------+----------------+
1 row in set (0.001 sec)

All clients traffic

Total traffic received and sent for all clients:

MariaDB [(none)]> select variable_name, round(variable_value/pow(1024,2),2) as MB from information_schema.global_status where variable_name in ('bytes_received', 'bytes_sent');
+----------------+-----------+
| variable_name  | MB        |
+----------------+-----------+
| BYTES_RECEIVED | 328131.94 |
| BYTES_SENT     | 518465.33 |
+----------------+-----------+
2 rows in set (0.001 sec)

Comparison of on-disk and memory temporary tables

select
  if(variable_name='created_tmp_disk_tables', 'number of on-disk temporary tables created', 'number of in-memory temporary tables created') as variable_name,
  variable_value
from information_schema.global_status
where variable_name in ('created_tmp_disk_tables', 'created_tmp_tables');
+----------------------------------------------+----------------+
| variable_name                                | variable_value |
+----------------------------------------------+----------------+
| number of on-disk temporary tables created   | 6654           |
| number of in-memory temporary tables created | 10339          |
+----------------------------------------------+----------------+
2 rows in set (0.002 sec)

Com_xxx and Handler_xxx variables

You have multiple tens of com_xxx and handler_xxx vairables. Com_xxx are more what you do, while handler_xxx also include the internal mechanism of MariaDB. Here a typical query:

select
  variable_name,
  variable_value
from information_schema.global_status
where variable_name in ('com_select','com_insert','com_update','com_delete','com_commit','handler_insert','handler_update','handler_delete','handler_commit');
+----------------+----------------+
| variable_name  | variable_value |
+----------------+----------------+
| COM_COMMIT     | 18647          |
| COM_DELETE     | 18170          |
| COM_INSERT     | 841875518      |
| COM_SELECT     | 829283225      |
| COM_UPDATE     | 167079068      |
| HANDLER_COMMIT | 1337774822     |
| HANDLER_DELETE | 18329          |
| HANDLER_UPDATE | 334047858      |
+----------------+----------------+
8 rows in set (0.002 sec)

InnoDB free and dirty space and data read and written

Check the official MariaDB documentation there is a bug with innodb_data_written with multiple version of MariaDB:

set @innodb_page_size=(select variable_value from  information_schema.global_variables where variable_name='innodb_page_size');
select
  variable_name,
  concat(cast(round(variable_value * @innodb_page_size / (1024 * 1024 * 1024),2) as char),' GB') as variable_value
from information_schema.global_status
where variable_name in ('innodb_buffer_pool_pages_free','innodb_buffer_pool_pages_dirty')
union
select
  variable_name,
  variable_value
from information_schema.global_status
where variable_name in ('innodb_data_pending_reads','innodb_data_pending_writes')
union
select
  variable_name,
  concat(cast(round(variable_value / (1024 * 1024 * 1024),2) as char),' GB') as variable_value
from information_schema.global_status
where variable_name in ('innodb_data_read','innodb_data_written');
+--------------------------------+----------------+
| variable_name                  | variable_value |
+--------------------------------+----------------+
| INNODB_BUFFER_POOL_PAGES_DIRTY | 10.22 GB       |
| INNODB_BUFFER_POOL_PAGES_FREE  | 88.83 GB       |
| INNODB_DATA_PENDING_READS      | 0              |
| INNODB_DATA_PENDING_WRITES     | 0              |
| INNODB_DATA_READ               | 7.89 GB        |
| INNODB_DATA_WRITTEN            | 3814.85 GB     |
+--------------------------------+----------------+
6 rows in set (0.002 sec)

InnoDB log buffer write rate

This interesting query is giving the InnoDB log buffer write rate:

select
  innodb_os_log_written_per_minute*60 as estimated_innodb_os_log_written_per_hour,
  concat(round(innodb_os_log_written_per_minute*60/1024/1024, 1), 'MB') as estimated_innodb_os_log_written_per_hour_mb
from
  (select sum(value) as innodb_os_log_written_per_minute from (
    select -variable_value as value
    from information_schema.global_status
    where variable_name = 'innodb_os_log_written'
    union all
    select sleep(60) from dual
    union all
    select variable_value
    from information_schema.global_status
    where variable_name = 'innodb_os_log_written'
  ) s1
) s2;
+------------------------------------------+---------------------------------------------+
| estimated_innodb_os_log_written_per_hour | estimated_innodb_os_log_written_per_hour_mb |
+------------------------------------------+---------------------------------------------+
|                               1518366720 | 1448.0MB                                    |
+------------------------------------------+---------------------------------------------+
1 row in set (1 min 0.002 sec)

Query cache statistics

select
  variable_name,
  variable_value
from information_schema.global_status
where variable_name like 'qcache%';
+-------------------------+----------------+
| variable_name           | variable_value |
+-------------------------+----------------+
| QCACHE_FREE_BLOCKS      | 2              |
| QCACHE_FREE_MEMORY      | 50305632       |
| QCACHE_HITS             | 333            |
| QCACHE_INSERTS          | 51716          |
| QCACHE_LOWMEM_PRUNES    | 0              |
| QCACHE_NOT_CACHED       | 97884          |
| QCACHE_QUERIES_IN_CACHE | 4              |
| QCACHE_TOTAL_BLOCKS     | 14             |
+-------------------------+----------------+
8 rows in set (0.001 sec)

Tables joins performance

Select_full_join and select_range_check should ideally be close to zero or you might need to review your tables indexes:

select
  variable_name,
  variable_value
from information_schema.global_status
where variable_name like 'select%';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| SELECT_FULL_JOIN       | 26254          |
| SELECT_FULL_RANGE_JOIN | 0              |
| SELECT_RANGE           | 115            |
| SELECT_RANGE_CHECK     | 0              |
| SELECT_SCAN            | 198201         |
+------------------------+----------------+
5 rows in set (0.001 sec)

Information Schema InnoDB queries

InnoDB buffer pool statistics

The InnoDB buffer pool statistics (not to say that other columns are not interesting so do not hesitate to check the official documentation):

set @innodb_page_size=(select variable_value from  information_schema.global_variables where variable_name='innodb_page_size');
select
  round(pool_size * @innodb_page_size / (1024 * 1024 * 1024),2) as pool_size_gb,
  round(free_buffers * @innodb_page_size / (1024 * 1024 * 1024),2) as free_buffers_gb,
  pending_flush_lru,
  pending_flush_list,
  number_pages_read,
  number_pages_get,
  number_pages_written
from information_schema.innodb_buffer_pool_stats;
 
+--------------+-----------------+-------------------+--------------------+-------------------+------------------+----------------------+
| pool_size_gb | free_buffers_gb | pending_flush_lru | pending_flush_list | number_pages_read | number_pages_get | number_pages_written |
+--------------+-----------------+-------------------+--------------------+-------------------+------------------+----------------------+
|         9.90 |            5.53 |                 0 |                  0 |            285337 |       1210678115 |              4302492 |
+--------------+-----------------+-------------------+--------------------+-------------------+------------------+----------------------+
1 row in set (0.000 sec)

InnoDB locks

You need to activate metrics first. For example, by default, lock_table_locks and lock_table_lock_waits wait events are not activated. I have decided, for easiness, to activate the whole lock subsystem:

MariaDB [(none)]> set global innodb_monitor_enable = 'module_lock';
Query OK, 0 rows affected (0.000 sec)
create database test
character set utf8mb4
collate utf8mb4_general_ci;
 
create table test.test01 (
	id int not null auto_increment,
	descr varchar(20) not null,
  primary key(id)
);
 
insert into test.test01(descr) values('One');

In one session I update the unique row with. Do not forget to disable autocommit first:

MariaDB [(none)]> set autocommit=0;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> update test.test01 set descr=’The one’ where id=1;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0

In a second session I update the same unique row. To have the time to perform your testing you also might want to increase the time in seconds that an InnoDB transaction waits for an InnoDB record lock (or table lock) before giving up with the error ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. I have set it to 15 minutes:

MariaDB [(none)]> set session innodb_lock_wait_timeout = 900;
Query OK, 0 rows affected (0.000 sec)
 
MariaDB [(none)]> update test.test01 set descr='The only one' where id=1;

Then you can start selecting in innodb_lock_waits, innodb_locks and innodb_trx tables:

MariaDB [(none)]> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
|          56731841 | 56731841:46:3:2   |        56731839 | 56731839:46:3:2  |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.001 sec)
 
MariaDB [(none)]> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 56731841:46:3:2 |    56731841 | X         | RECORD    | `test`.`test01` | PRIMARY    |         46 |         3 |        2 | 1         |
| 56731839:46:3:2 |    56731839 | X         | RECORD    | `test`.`test01` | PRIMARY    |         46 |         3 |        2 | 1         |
+-----------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.001 sec)
 
select
  trx_id,
  trx_state,
--   trx_started,
--   trx_requested_lock_id,
  trx_wait_started,
--   trx_weight,
  trx_mysql_thread_id,
  trx_query,
  prl.user,
  prl.host
--   trx_operation_state,
--   trx_tables_in_use,
--   trx_tables_locked,
--   trx_lock_structs,
--   trx_lock_memory_bytes,
--   trx_rows_locked,
--   trx_rows_modified,
--   trx_concurrency_tickets,
--   trx_isolation_level,
--   trx_unique_checks,
--   trx_foreign_key_checks,
--   trx_last_foreign_key_error,
--   trx_is_read_only,
--   trx_autocommit_non_locking
from information_schema.innodb_trx trx, information_schema.processlist prl
where trx.trx_mysql_thread_id = prl.id;
 
+----------+-----------+---------------------+---------------------+--------------------------------------------------------+---------+-----------+
| trx_id   | trx_state | trx_wait_started    | trx_mysql_thread_id | trx_query                                              | user    | host      |
+----------+-----------+---------------------+---------------------+--------------------------------------------------------+---------+-----------+
| 56731841 | LOCK WAIT | 2024-02-13 15:42:52 |                  33 | update test.test01 set descr='The only one' where id=1 | mariadb | localhost |
| 56731839 | RUNNING   | NULL                |                  31 | NULL                                                   | mariadb | localhost |
+----------+-----------+---------------------+---------------------+--------------------------------------------------------+---------+-----------+
2 rows in set (0.021 sec)

This query is also quite interesting:

select
  concat('thread ', b.trx_mysql_thread_id, ' from ', p.host) as who_blocks,
  if(p.command = "sleep", p.time, 0) as idle_in_trx,
  max(timestampdiff(second, r.trx_wait_started, current_timestamp)) as max_wait_time,
  count(*) as num_waiters
from information_schema.innodb_lock_waits as w
inner join information_schema.innodb_trx as b on b.trx_id = w.blocking_trx_id
inner join information_schema.innodb_trx as r on r.trx_id = w.requesting_trx_id
left join information_schema.processlist as p on p.id = b.trx_mysql_thread_id
group by who_blocks
order by num_waiters desc;
 
+--------------------------+-------------+---------------+-------------+
| who_blocks               | idle_in_trx | max_wait_time | num_waiters |
+--------------------------+-------------+---------------+-------------+
| thread 31 from localhost |        2811 |           836 |           1 |
+--------------------------+-------------+---------------+-------------+
1 row in set (0.022 sec)

Where I have been a bit disappointed is when I have tried to WRITE lock my test table table (part of the code of one of our application):

MariaDB [(none)]> lock table test.test01 write nowait;
Query OK, 0 rows affected (0.000 sec)

If I select in another session I obviosuly got stuck:

MariaDB [(none)]> select * from test.test01;

But it is strangely not reported in innodb_lock_waits, innodb_locks. I can only see the initial lock table transaction in innodb_trx:

select
  trx_id,
  trx_state,
--   trx_started,
--   trx_requested_lock_id,
  trx_wait_started,
--   trx_weight,
  trx_mysql_thread_id,
  trx_query,
  prl.user,
  prl.host
--   trx_operation_state,
--   trx_tables_in_use,
--   trx_tables_locked,
--   trx_lock_structs,
--   trx_lock_memory_bytes,
--   trx_rows_locked,
--   trx_rows_modified,
--   trx_concurrency_tickets,
--   trx_isolation_level,
--   trx_unique_checks,
--   trx_foreign_key_checks,
--   trx_last_foreign_key_error,
--   trx_is_read_only,
--   trx_autocommit_non_locking
from information_schema.innodb_trx trx, information_schema.processlist prl
where trx.trx_mysql_thread_id = prl.id;
 
+----------+-----------+------------------+---------------------+-----------+---------+-----------+
| trx_id   | trx_state | trx_wait_started | trx_mysql_thread_id | trx_query | user    | host      |
+----------+-----------+------------------+---------------------+-----------+---------+-----------+
| 56731843 | RUNNING   | NULL             |                  36 | NULL      | mariadb | localhost |
+----------+-----------+------------------+---------------------+-----------+---------+-----------+
1 row in set (0.021 sec)

I have tried to activate the module_metadata metrics subsystem but it did not change antyhting:

MariaDB [(none)]> set global innodb_monitor_enable = 'module_metadata';
Query OK, 0 rows affected (0.000 sec)

Any idea welcome…

The only option I have found is to select in processlist and see the thread waiting:

select
  id,
  user,
  host,
  db,
  command,
  time,
  state,
  info
from information_schema.processlist;
 
+----+----------+----------------------+--------------------+---------+-------+---------------------------------+------------------------------------------------------------------------------------+
| id | user     | host                 | db                 | command | time  | state                           | info                                                                               |
+----+----------+----------------------+--------------------+---------+-------+---------------------------------+------------------------------------------------------------------------------------+
| 38 | mariadb  | localhost            | NULL               | Query   |     0 | Filling schema table            | select id,user,host,db,command,time,state,info from information_schema.processlist |
| 37 | mariadb  | localhost            | NULL               | Query   |  1919 | Waiting for table metadata lock | select * from test.test01                                                          |
| 36 | mariadb  | localhost            | NULL               | Sleep   |  1932 |                                 | NULL                                                                               |
| 35 | yjaquier | 10.252.147.105:55081 | information_schema | Sleep   |     8 |                                 | NULL                                                                               |
| 30 | mariadb  | localhost            | NULL               | Sleep   | 13395 |                                 | NULL                                                                               |
| 22 | monyog   | 10.75.136.37:33096   | NULL               | Sleep   |   288 |                                 | NULL                                                                               |
|  6 | monyog   | 10.75.136.37:42824   | NULL               | Sleep   |    13 |                                 | NULL                                                                               |
+----+----------+----------------------+--------------------+---------+-------+---------------------------------+------------------------------------------------------------------------------------+
7 rows in set (0.021 sec)

When I release the lock in first session:

MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.000 sec)

I finally get the result in second session, check the timing. I hope it is taken into account in Performance Schema:

MariaDB [(none)]> select * from test.test01;
+----+-------+
| id | descr |
+----+-------+
|  1 | One   |
+----+-------+
1 row in set (33 min 35.705 sec)

InnoDB metrics

What you find in information_schema.innodb_metrics is directly dependent on what you have activated with innodb_monitor_enable initializtion parameter that we have seen in configuration chapter. Check the ENABLED column to understand if the counter is active or not (as well as TIME_ENABLED to see if it has been activated recently or not). Plenty of counters of information_schema.innodb_metrics table are overlapping with the ones of information_schema.global_status.

select
  name,
  concat(cast(round(count / (1024 * 1024 * 1024),2) as char),' GB') as value,
  comment
from information_schema.innodb_metrics
where name in ('buffer_data_reads','buffer_data_written')
and enabled = 1;
+---------------------+------------+-------------------------------------------------------+
| name                | value      | comment                                               |
+---------------------+------------+-------------------------------------------------------+
| buffer_data_reads   | 7.90 GB    | Amount of data read in bytes (innodb_data_reads)      |
| buffer_data_written | 3812.46 GB | Amount of data written in bytes (innodb_data_written) |
+---------------------+------------+-------------------------------------------------------+
2 rows in set (0.001 sec)

But againt it is strongly suggested to access information_schema.innodb_metrics with a graphical tool:

information_schema02
information_schema02

Information Schema remaining tables

Remains a bunch of interesting side tables…

Processlist

The information_schema.processlist can tell you which session are working or waiting. To display info column you need to use a graphical query tool::

select
  id,
  USER,
  HOST,
  db,
  command,
  time_ms,
  state
  -- ,info
from information_schema.processlist;
+--------+------------------+-----------------------------+--------------------+-------------+----------------+---------------------------------------------------------------+
| id     | USER             | HOST                        | db                 | command     | time_ms        | state                                                         |
+--------+------------------+-----------------------------+--------------------+-------------+----------------+---------------------------------------------------------------+
| 175710 | appmgr           | server05.domain.com:46124   | appprod            | Query       |      34094.027 | Waiting for table metadata lock                               |
| 175687 | appmgr           | server05.domain.com:46078   | appprod            | Query       |          0.061 | Optimizing                                                    |
| 169424 | root             | localhost                   | NULL               | Query       |         16.859 | Filling schema table                                          |
| 165719 | yjaquier         | 10.252.141.65:57196         | information_schema | Sleep       |       5803.706 |                                                               |
|   5230 | appmgr           | server05.domain.com:53278   | appqueue           | Sleep       |      36827.797 |                                                               |
|   2166 | appmgr           | server05.domain.com:49858   | appqueue           | Sleep       |      55126.404 |                                                               |
|     34 | replication_user | server06.domain.com:56142   | NULL               | Binlog Dump | 1715827482.757 | Master has sent all binlog to slave; waiting for more updates |
|     28 | root             | server07.domain.com:48428   | NULL               | Sleep       |      76649.646 |                                                               |
|     16 | event_scheduler  | localhost                   | NULL               | Daemon      | 1716538428.885 | Waiting on empty queue                                        |
|      6 | root             | server07.domain.com:57628   | NULL               | Sleep       |      27142.017 |                                                               |
+--------+------------------+-----------------------------+--------------------+-------------+----------------+---------------------------------------------------------------+
10 rows in set (0.018 sec)

Here I can see that session with id 175710 of appmgr user has already waited 34094 milliseconds or 34 seconds on a metadata lock to investigation the info column to see what SQL statement the session is doing can give some insight on what to tune…

Routines

Information_schema.routines helps you extract the sources of all procedures and functions of your databases:

select
  routine_type,
  routine_definition
from information_schema.routines
where routine_name='extract_schema_from_file_name'
and routine_schema='sys';
+--------------+------------------------------------------------------------------------------------------------------------+
| routine_type | routine_definition                                                                                         |
+--------------+------------------------------------------------------------------------------------------------------------+
| FUNCTION     | BEGIN
    RETURN LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(path, '\', '/'), '/', -2), '/', 1), 64);
END |
+--------------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.023 sec)

Users and Schemas privileges

select
  *
from information_schema.user_privileges
where grantee='''sbuser''@''localhost''';
+----------------------+---------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+----------------+--------------+
| 'sbuser'@'localhost' | def           | USAGE          | NO           |
+----------------------+---------------+----------------+--------------+
1 row in set (0.001 sec)
select
  *
from information_schema.schema_privileges
where grantee='''sbuser''@''localhost''';
+----------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+----------------------+---------------+--------------+-------------------------+--------------+
| 'sbuser'@'localhost' | def           | sbtest       | SELECT                  | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | INSERT                  | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | UPDATE                  | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | DELETE                  | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | CREATE                  | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | DROP                    | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | REFERENCES              | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | INDEX                   | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | ALTER                   | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | CREATE TEMPORARY TABLES | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | LOCK TABLES             | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | EXECUTE                 | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | CREATE VIEW             | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | SHOW VIEW               | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | CREATE ROUTINE          | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | ALTER ROUTINE           | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | EVENT                   | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | TRIGGER                 | NO           |
| 'sbuser'@'localhost' | def           | sbtest       | DELETE HISTORY          | NO           |
+----------------------+---------------+--------------+-------------------------+--------------+
19 rows in set (0.001 sec)

System variables

select
  variable_name,
  global_value,
  default_value
  -- ,variable_comment
from information_schema.system_variables
-- where global_value <> default_value
order by variable_name;
+------------------------------------------+--------------+---------------+
| variable_name                            | global_value | default_value |
+------------------------------------------+--------------+---------------+
| ALLOW_SUSPICIOUS_UDFS                    | OFF          | OFF           |
| ALTER_ALGORITHM                          | DEFAULT      | DEFAULT       |
| ANALYZE_SAMPLE_PERCENTAGE                | 100.000000   | 100.000000    |
| ARIA_BLOCK_SIZE                          | 8192         | 8192          |
| ARIA_CHECKPOINT_INTERVAL                 | 30           | 30            |
| ARIA_CHECKPOINT_LOG_ACTIVITY             | 1048576      | 1048576       |
| ARIA_ENCRYPT_TABLES                      | OFF          | OFF           |
| ARIA_FORCE_START_AFTER_RECOVERY_FAILURES | 0            | 0             |
| ARIA_GROUP_COMMIT                        | none         | none          |
| ARIA_GROUP_COMMIT_INTERVAL               | 0            | 0             |
.
.
.

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>