What are the common system tables and views of mysql 01/18 Update SLTechnology News&Howtos

What are the common system tables and views of mysql

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you what are the common system tables and views about mysql. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

I. Common tables under information_schema

1.SCHEMATA

Information of all databases under the instance

* * 1. Row *

CATALOG_NAME: def

SCHEMA_NAME: information_schema

DEFAULT_CHARACTER_SET_NAME: utf8

DEFAULT_COLLATION_NAME: utf8_general_ci

SQL_PATH: NULL

2.TABLES

Provides details of tables and views

* * 298. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: wwj2

TABLE_NAME: t1

TABLE_TYPE: BASE TABLE

ENGINE: InnoDB

VERSION: 10

ROW_FORMAT: Dynamic

TABLE_ROWS: 3

AVG_ROW_LENGTH: 5461

DATA_LENGTH: 16384

MAX_DATA_LENGTH: 0

INDEX_LENGTH: 0

DATA_FREE: 0

AUTO_INCREMENT: 4

CREATE_TIME: 2018-04-17 10:44:04

UPDATE_TIME: NULL

CHECK_TIME: NULL

TABLE_COLLATION: utf8mb4_unicode_ci

CHECKSUM: NULL

CREATE_OPTIONS:

TABLE_COMMENT:

3.COLUMNS

Detailed tabular information

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: wwj

TABLE_NAME: t3

COLUMN_NAME: idt3

ORDINAL_POSITION: 1

COLUMN_DEFAULT: NULL

IS_NULLABLE: NO

DATA_TYPE: int

CHARACTER_MAXIMUM_LENGTH: NULL

CHARACTER_OCTET_LENGTH: NULL

NUMERIC_PRECISION: 10

NUMERIC_SCALE: 0

DATETIME_PRECISION: NULL

CHARACTER_SET_NAME: NULL

COLLATION_NAME: NULL

COLUMN_TYPE: int (11)

COLUMN_KEY: PRI

EXTRA:

PRIVILEGES: select,insert,update,references

COLUMN_COMMENT:

GENERATION_EXPRESSION:

4.STATISTICS

Index information of the table

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: wwj

TABLE_NAME: t3

NON_UNIQUE: 0

INDEX_SCHEMA: wwj

INDEX_NAME: PRIMARY

SEQ_IN_INDEX: 1

COLUMN_NAME: idt3

COLLATION: A

CARDINALITY: 8

SUB_PART: NULL

PACKED: NULL

NULLABLE:

INDEX_TYPE: BTREE

COMMENT:

INDEX_COMMENT:

* 2. Row * *

TABLE_CATALOG: def

TABLE_SCHEMA: wwj

TABLE_NAME: t3

NON_UNIQUE: 1

INDEX_SCHEMA: wwj

INDEX_NAME: idx-1

SEQ_IN_INDEX: 1

COLUMN_NAME: idt4

COLLATION: A

CARDINALITY: 8

SUB_PART: NULL

PACKED: NULL

NULLABLE:

INDEX_TYPE: BTREE

COMMENT:

INDEX_COMMENT:

5.USER_PRIVILEGES

User rights information

+-+

+-+

| | root'@'localhost' | def | SELECT | YES | |

| | root'@'localhost' | def | INSERT | YES | |

| | root'@'localhost' | def | UPDATE | YES | |

| | root'@'localhost' | def | DELETE | YES | |

| | root'@'localhost' | def | CREATE | YES | |

| | root'@'localhost' | def | DROP | YES | |

| | root'@'localhost' | def | RELOAD | YES | |

| | root'@'localhost' | def | SHUTDOWN | YES | |

| | root'@'localhost' | def | PROCESS | YES | |

| | root'@'localhost' | def | FILE | YES | |

| | root'@'localhost' | def | REFERENCES | YES | |

| | root'@'localhost' | def | INDEX | YES | |

| | root'@'localhost' | def | ALTER | YES | |

| | root'@'localhost' | def | SHOW DATABASES | YES | |

| | root'@'localhost' | def | SUPER | YES | |

| | root'@'localhost' | def | CREATE TEMPORARY TABLES | YES | |

| | root'@'localhost' | def | LOCK TABLES | YES | |

| | root'@'localhost' | def | EXECUTE | YES | |

| | root'@'localhost' | def | REPLICATION SLAVE | YES | |

| | root'@'localhost' | def | REPLICATION CLIENT | YES | |

| | root'@'localhost' | def | CREATE VIEW | YES | |

| | root'@'localhost' | def | SHOW VIEW | YES | |

| | root'@'localhost' | def | CREATE ROUTINE | YES | |

| | root'@'localhost' | def | ALTER ROUTINE | YES | |

| | root'@'localhost' | def | CREATE USER | YES | |

| | root'@'localhost' | def | EVENT | YES | |

| | root'@'localhost' | def | TRIGGER | YES | |

| | root'@'localhost' | def | CREATE TABLESPACE | YES | |

| | mysql.session'@'localhost' | def | SUPER | NO | |

| | mysql.sys'@'localhost' | def | USAGE | NO | |

| | REPL_USER'@'109.115.12.41' | def | REPLICATION SLAVE | NO | |

| | REPL_USER'@'109.115.12.42' | def | REPLICATION SLAVE | NO | |

| | REPL_USER'@'109.115.12.%' | def | REPLICATION SLAVE | NO | |

| | REPL_USER'@'109.115.12.%' | def | REPLICATION CLIENT | NO | |

| | root'@'%' | def | SELECT | NO | |

| | root'@'%' | def | INSERT | NO | |

| | root'@'%' | def | UPDATE | NO | |

| | root'@'%' | def | DELETE | NO | |

| | root'@'%' | def | CREATE | NO | |

| | root'@'%' | def | DROP | NO | |

| | root'@'%' | def | RELOAD | NO | |

| | root'@'%' | def | SHUTDOWN | NO | |

| | root'@'%' | def | PROCESS | NO | |

| | root'@'%' | def | FILE | NO | |

| | root'@'%' | def | REFERENCES | NO | |

| | root'@'%' | def | INDEX | NO | |

| | root'@'%' | def | ALTER | NO | |

| | root'@'%' | def | SHOW DATABASES | NO | |

| | root'@'%' | def | SUPER | NO | |

| | root'@'%' | def | CREATE TEMPORARY TABLES | NO | |

| | root'@'%' | def | LOCK TABLES | NO | |

| | root'@'%' | def | EXECUTE | NO | |

| | root'@'%' | def | REPLICATION SLAVE | NO | |

| | root'@'%' | def | REPLICATION CLIENT | NO | |

| | root'@'%' | def | CREATE VIEW | NO | |

| | root'@'%' | def | SHOW VIEW | NO | |

| | root'@'%' | def | CREATE ROUTINE | NO | |

| | root'@'%' | def | ALTER ROUTINE | NO | |

| | root'@'%' | def | CREATE USER | NO | |

| | root'@'%' | def | EVENT | NO | |

| | root'@'%' | def | TRIGGER | NO | |

| | root'@'%' | def | CREATE TABLESPACE | NO | |

+-+

6.SCHEMA_PRIVILEGES

Database permission information

+-+

+-+

| | 'mysql.sys'@'localhost' | def | sys | TRIGGER | NO | |

+-+

7.TABLE_PRIVILEGES

Table permission information

* 3. Row * *

GRANTEE: 'wwj'@'%'

TABLE_CATALOG: def

TABLE_SCHEMA: wwj

TABLE_NAME: t3

PRIVILEGE_TYPE: SELECT

IS_GRANTABLE: NO

8.COLUMN_PRIVILEGES

About column permission information

9.CHARACTER_SETS

Mysql available character set

10.TABLE_CONSTRAINTS

Constraint information for the table

11.KEY_COLUMN_USAGE

Describes the information for columns with constraints

* * 79. Row * *

CONSTRAINT_CATALOG: def

CONSTRAINT_SCHEMA: wwj2

CONSTRAINT_NAME: PRIMARY

TABLE_CATALOG: def

TABLE_SCHEMA: wwj2

TABLE_NAME: t1

COLUMN_NAME: id

ORDINAL_POSITION: 1

POSITION_IN_UNIQUE_CONSTRAINT: NULL

REFERENCED_TABLE_SCHEMA: NULL

REFERENCED_TABLE_NAME: NULL

REFERENCED_COLUMN_NAME: NULL

12.ROUTINES

Records the information of stored procedures and functions

13.VIEWS

The view information in the database is recorded, and show views permission is required.

* * 1. Row *

TABLE_CATALOG: def

TABLE_SCHEMA: sys

TABLE_NAME: host_summary

VIEW_DEFINITION: select if (isnull (`performance_ schema`.`collects`.`HOST`) AS `host`, sum (`stmt`.`total`) AS `statements`, `sys`.`format _ time` (sum (`stmt`.`total _ latency`) AS `statement_ latency`, `sys`.`format _ time` (ifnull (sum (sum (`stmt`.total _ latency`) / nullif (sum (`stmt`.`total`), 0) AS `statement_avg_ latency`, sum (`stmt`.`full _ scans`) AS `table_ latency`, sum (`io.`ios`) `AS _ latency`) `sys`.`format _ time` (sum (`io`.`io _ latency`) AS `unique_ latency`, sum (`performance_ schema`.`Secretts`.`CURRENT _ CONNECTIONS`) AS `sys`connections`, sum (`performance_ schema`.`collects`.`total _ CONNECTIONS`) AS `unique_ connections`, count (distinct `performance_ schema`.`accounts`.USER`) AS `unique_ users`, `sys.`format _ bytes` (sum (`mem.`current _ allocated`)) AS `current_ connections` `sys`.`format _ bytes` (sum (`mem`.`total _ allocated`)) AS `total_memory_ allocated` from (`performance _ schema`.`collects`join `sys`.x $host_summary_by_statement_ latency``stmt` on ((`performance schema``schema``accounts`.host`)) join `sys`.`x $host_summary_by_file_ io``io`io``io``io`on (`performance_ schema.`accounts`.`host`)) join `sys.`x $memory_by_host_by_current_ bytes`mem`mem`on ((`performance schema`.`host`)) ) group by if (isnull (`performance_ schema`.`recipts`.`HOST`) 'background', `performance_ schema`.`accounts`.`HOST`)

CHECK_OPTION: NONE

IS_UPDATABLE:

DEFINER: mysql.sys@localhost

SECURITY_TYPE: INVOKER

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

14.TRIGGERS

Provides information about triggers in the database

* * 1. Row *

TRIGGER_CATALOG: def

TRIGGER_SCHEMA: sys

TRIGGER_NAME: sys_config_insert_set_user

EVENT_MANIPULATION: INSERT

EVENT_OBJECT_CATALOG: def

EVENT_OBJECT_SCHEMA: sys

EVENT_OBJECT_TABLE: sys_config

ACTION_ORDER: 1

ACTION_CONDITION: NULL

ACTION_STATEMENT: BEGIN IF @ sys.ignore_sys_config_triggers! = true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER (); END IF; END

ACTION_ORIENTATION: ROW

ACTION_TIMING: BEFORE

ACTION_REFERENCE_OLD_TABLE: NULL

ACTION_REFERENCE_NEW_TABLE: NULL

ACTION_REFERENCE_OLD_ROW: OLD

ACTION_REFERENCE_NEW_ROW: NEW

CREATED: 2018-04-06 10 15 15 16 30

SQL_MODE:

DEFINER: mysql.sys@localhost

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: utf8_general_ci

II. Common tables under performance_schema

Reference: http://www.cnblogs.com/cchust/p/5022148.html

PERFORMANCE_SCHEMA is off by default. Need to set parameters: performance_schema can start this function, this parameter is static, can only be written in my.cnf can not be dynamically modified.

1. Configuration table

(1) set_actors

Used to configure monitoring for user dimensions, monitoring all user threads by default

+-+

+-+

| |% | YES | YES |

+-+

(2) setup_consumers

The table is used to configure the consumer type of the event, that is, which statistics the collected events will eventually be written to.

+-+ +

| | NAME | ENABLED |

+-+ +

| | events_stages_current | NO |

| | events_stages_history | NO |

| | events_stages_history_long | NO |

| | events_statements_current | YES |

| | events_statements_history | YES |

| | events_statements_history_long | NO |

| | events_transactions_current | NO |

| | events_transactions_history | NO |

| | events_transactions_history_long | NO |

| | events_waits_current | NO |

| | events_waits_history | NO |

| | events_waits_history_long | NO |

| | global_instrumentation | YES |

| | thread_instrumentation | YES |

| | statements_digest | YES |

+-+ +

(3) setup_instruments

The table is used to configure a specific instrument, which mainly consists of four categories: idle,stage/xxx,statement/xxx,wait/xxx.

-idle: indicates the time socket is idle

-stage: represents the statistics of each statement and each execution phase

-statement: information on the dimension of statistical statements

-wait: count all kinds of waiting events

(4) setup_objects

Used to configure monitoring objects. By default, all objects under mysql,information_schema,performance_schema are not monitored, and all objects under other DB are monitored.

+-+

+-+

| | EVENT | mysql |% | NO | NO | |

| | EVENT | performance_schema |% | NO | NO | |

| | EVENT | information_schema |% | NO | NO | |

| | EVENT |% |% | YES | YES | |

| | FUNCTION | mysql |% | NO | NO | |

| | FUNCTION | performance_schema |% | NO | NO | |

| | FUNCTION | information_schema |% | NO | NO | |

| | FUNCTION |% |% | YES | YES | |

| | PROCEDURE | mysql |% | NO | NO | |

| | PROCEDURE | performance_schema |% | NO | NO | |

| | PROCEDURE | information_schema |% | NO | NO | |

| | PROCEDURE |% |% | YES | YES | |

| | TABLE | mysql |% | NO | NO | |

| | TABLE | performance_schema |% | NO | NO | |

| | TABLE | information_schema |% | NO | NO | |

| | TABLE |% |% | YES | YES | |

| | TRIGGER | mysql |% | NO | NO | |

| | TRIGGER | performance_schema |% | NO | NO | |

| | TRIGGER | information_schema |% | NO | NO | |

| | TRIGGER |% |% | YES | YES | |

+-+

(5) setup_timers

Configure time units for each type of statistics

+-+ +

| | NAME | TIMER_NAME |

+-+ +

| | idle | MICROSECOND |

| | wait | CYCLE |

| | stage | NANOSECOND |

| | statement | NANOSECOND |

| | transaction | NANOSECOND |

+-+ +

2.instance table

(1) file_instances

The table records the objects with open files in the system, and open_count shows the number of current file openings.

+-+

+-+

+-+

(2) mutex_instances

All records in the system that use mutex objects are recorded in the table, and LOCKED_BY_THREAD_ID shows which thread is holding the mutex, or NULL if no thread holds it.

+-+

+-+

+-+

(3) rwlock_instances

Records all records that use read-write lock objects in the system

WRITE_LOCKED_BY_THREAD_ID is the thread_id holding the object. If no thread holds it, it is NULL.

READ_LOCKED_BY_COUNT records how many readers hold read locks at the same time.

+-+-

+-+-

| | wait/synch/rwlock/session/LOCK_srv_session_collection | 31736184 | NULL | 0 | |

+-+-

(4) socket_instances

Thread_id,socket_id,ip and port are recorded in the table, and other tables can be associated with socket_instance through thread_id to obtain IP-PORT information and be able to dock with the application.

Event_name consists of three main categories:

Wait/io/socket/sql/server_unix_socket, server unix listens for socket

Wait/io/socket/sql/server_tcpip_socket, server tcp listens for socket

Wait/io/socket/sql/client_connection, client socket

+-+

+-+

| | wait/io/socket/sql/server_tcpip_socket | 83218752 | 1 | 22 |:: | 3306 | ACTIVE |

| | wait/io/socket/sql/server_unix_socket | 83219072 | 1 | 24 | | 0 | ACTIVE |

| | wait/io/socket/sql/client_connection | 83221952 | 63 | 27 | | 0 | IDLE |

| | wait/io/socket/sql/client_connection | 83222272 | 64 | 29 | | 0 | IDLE |

| | wait/io/socket/sql/client_connection | 83222592 | 65 | 30 | | 0 | ACTIVE |

+-+

3.Wait Event table

Events_waits_current # records the events that the current thread is waiting for

Events_waits_history # records the last 10 events each thread has been waiting for

Events_waits_history_long # records 10000 recent events generated by all threads

Events in the history table are completed, and unfinished events are not added to the history table.

THREAD_ID: thread ID

EVENT_ID: the event ID of the current thread, and THREAD_ID make up a Primary Key.

END_EVENT_ID: when the event starts, this column is set to NULL. When the event ends, it is updated to the current event ID.

SOURCE: the source file when the event was generated

TIMER_START, TIMER_END, TIMER_WAIT: the time the event starts / ends and waits in picoseconds (picoseconds)

OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE depending on the situation

For synchronization objects (cond, mutex, rwlock), all three values are NULL

For file IO objects, OBJECT_SCHEMA is NULL,OBJECT_NAME and OBJECT_TYPE is FILE

For the SOCKET object, OBJECT_NAME is the IP: sock value of the socket

For the table I TABLE O object, OBJECT_SCHEMA is the SCHEMA name of the table, OBJECT_NAME is the table name, and OBJECT_TYPE is TABLE or TEMPORARY TABLE

NESTING_EVENT_ID: the parent event ID corresponding to this event

NESTING_EVENT_TYPE: parent event type (STATEMENT, STAGE, WAIT)

OPERATION: operation type (lock, read, write)

4.stage event table

Events_waits_current

Events_stages_history

Events_stages_history_long

The execution phase of the current thread is recorded in the table, and because you can know the execution time of each phase, you can get the time consumed by SQL in each phase through the stage table.

THREAD_ID: thread ID

EVENT_ID: event ID

END_EVENT_ID: the event ID that just ended

SOURCE: source location

TIMER_START, TIMER_END, TIMER_WAIT: the time the event starts / ends and waits in picoseconds (picoseconds)

NESTING_EVENT_ID: the parent event ID corresponding to this event

NESTING_EVENT_TYPE: parent event type (STATEMENT, STAGE, WAIT)

5.Statement Event

Events_statements_current

Events_statements_history

Events_statements_history_long

The Statments table records only the top-level requests, SQL statements or COMMAND, with one row for each statement, and is not listed separately for nested subqueries or stored procedures. The form of event_name is statement/sql/*, or statement/com/*

* * 8. Row *

THREAD_ID: 67

EVENT_ID: 96

END_EVENT_ID: 96

EVENT_NAME: statement/sql/select

SOURCE: socket_connection.cc:101

TIMER_START: 365640494222296000

TIMER_END: 365640496863080000

TIMER_WAIT: 2640784000

LOCK_TIME: 195000000

SQL_TEXT: select * from events_statements_history

DIGEST: 12bf4ba549a7a86ad0d382bf2cd93f91

DIGEST_TEXT: SELECT * FROM `events_statements_ roomy`

CURRENT_SCHEMA: performance_schema

OBJECT_TYPE: NULL

OBJECT_SCHEMA: NULL

OBJECT_NAME: NULL

OBJECT_INSTANCE_BEGIN: NULL

MYSQL_ERRNO: 0

RETURNED_SQLSTATE: NULL

MESSAGE_TEXT: NULL

ERRORS: 0

WARNINGS: 0

ROWS_AFFECTED: 0

ROWS_SENT: 12

ROWS_EXAMINED: 12

CREATED_TMP_DISK_TABLES: 0

CREATED_TMP_TABLES: 0

SELECT_FULL_JOIN: 0

SELECT_FULL_RANGE_JOIN: 0

SELECT_RANGE: 0

SELECT_RANGE_CHECK: 0

SELECT_SCAN: 1

SORT_MERGE_PASSES: 0

SORT_RANGE: 0

SORT_ROWS: 0

SORT_SCAN: 0

NO_INDEX_USED: 1

NO_GOOD_INDEX_USED: 0

NESTING_EVENT_ID: NULL

NESTING_EVENT_TYPE: NULL

NESTING_EVENT_LEVEL: 0

SQL_TEXT: recording SQL statements

DIGEST: a 32-bit string produced by MD5 SQL_TEXT. NULL if the statement_digest option is not turned on in the consumer table.

DIGEST_TEXT: replace the mid-value part of the statement with a question mark for SQL statement classification. NULL if the statement_digest option is not turned on in the consumer table.

CURRENT_SCHEMA: default database name

OBJECT_SCHEMA,OBJECT_NAME,OBJECT_TYPE: reserved fields, all NULL

ROWS_AFFECTED: number of impacts

ROWS_SENT: number of records returned

ROWS_EXAMINED: number of records read

CREATED_TMP_DISK_TABLES: number of physical temporary tables created

CREATED_TMP_TABLES: number of temporary tables created

When SELECT_FULL_JOIN:join, the first table is the number of full table scans

The number of reference tables scanned in range mode when SELECT_FULL_RANGE_JOIN:join

The number of first table scanned in range mode when SELECT_RANGE:join

The number of full table scans for the first epitope in SELECT_SCAN:join

SORT_ROWS: number of records sorted

NESTING_EVENT_ID,NESTING_EVENT_TYPE, reserved field, is NULL.

6.connection table

Users,hosts and account tables

Accounts contains information about hosts and users.

USER: user name

HOST: user's IP

7.Summary

The Summary table aggregates the statistics of each dimension, including table dimension, index dimension, session dimension, statement dimension and lock dimension.

(1) wait-summary table

Events_waits_summary_global_by_event_name

Scenario: aggregate by waiting event type, with one record for each event.

Events_waits_summary_by_instance

Scenario: aggregate by wait event object, the same kind of wait event, there may be multiple instances, each instance has a different memory address, so

Event_name+object_instance_begin uniquely determines a record.

Events_waits_summary_by_thread_by_event_name

Scenario: thread_id+event_name uniquely determines a record based on each thread and event.

COUNT_STAR: event count

SUM_TIMER_WAIT: total wait time

MIN_TIMER_WAIT: minimum waiting time

MAX_TIMER_WAIT: maximum waiting time

AVG_TIMER_WAIT: average waiting time

Stage-summary table

Events_stages_summary_by_thread_by_event_name

Events_stages_summary_global_by_event_name

Similar to the previous one

(3) statements-summary table

The events_statements_summary_by_thread_by_event_ name table and the events_statements_summary_global_by_event_name table are similar to the previous. For events_statements_summary_by_ digest tables

FIRST_SEEN_TIMESTAMP: the time when the first statement was executed

LAST_SEEN_TIMESTAMP: the time when the last statement was executed

Scenario: used to count top SQL in a certain period of time

(4). File Imax O summary table

File_summary_by_event_name [by event type]

File_summary_by_instance [by document]

Scenario: physical IO Dimension

FILE_NAME: specific file name, such as / u01/my3306/data/tcbuyer_0168/tc_biz_order_2695.ibd

EVENT_NAME: event name, such as wait/io/file/innodb/innodb_data_file

COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT

Statistical IO operation

COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ, SUM_NUMBER_OF_BYTES_READ

Statistical reading

COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE

Statistical writing

COUNT_MISC,SUM_TIMER_MISC,MIN_TIMER_MISC,AVG_TIMER_MISC,MAX_TIMER_MISC

Count other IO events, such as create,delete,open,close, etc.

(5). Table Imax O and Lock Wait Summaries- table

Table_io_waits_summary_by_table

According to wait/io/table/sql/handler, aggregate the Imax O operations of each table, [logical IO]

COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT

Statistical IO operation

COUNT_READ,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT

Statistical reading

COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE, MAX_TIMER_WRITE

Statistical writing

COUNT_FETCH,SUM_TIMER_FETCH,MIN_TIMER_FETCH,AVG_TIMER_FETCH, MAX_TIMER_FETCH

Same as reading

COUNT_INSERT,SUM_TIMER_INSERT,MIN_TIMER_INSERT,AVG_TIMER_INSERT,MAX_TIMER_INSERT

INSERT statistics, and corresponding DELETE and UPDATE statistics.

(6). Table_io_waits_summary_by_index_usage

Similar to table_io_waits_summary_by_table, statistics by index dimension

(7). Table_lock_waits_summary_by_table

Table lock wait events, including internal lock and external lock, are aggregated.

Internal lock is called through the SQL layer function thr_lock, and the operation value is:

Read normal

Read with shared locks

Read high priority

Read no insert

Write allow write

Write concurrent insert

Write delayed

Write low priority

Write normal

External lock calls the storage engine layer through the interface function handler::external_lock.

The value of the OPERATION column is:

Read external

Write external

(8) Connection Summaries

Events_waits_summary_by_account_by_event_name

Events_waits_summary_by_user_by_event_name

Events_waits_summary_by_host_by_event_name

Events_stages_summary_by_account_by_event_name

Events_stages_summary_by_user_by_event_name

Events_stages_summary_by_host_by_event_name

Events_statements_summary_by_account_by_event_name

Events_statements_summary_by_user_by_event_name

Events_statements_summary_by_host_by_event_name

(9). Socket-summaries

Socket_summary_by_instance

Socket_summary_by_event_name

Other tables

Performance_timers: statistical time units supported by the system

Threads: monitors the currently running thread on the server side

Http://www.cnblogs.com/cchust/p/5061131.html

3. Commonly used tables in MySQL database

1. User table: user column, permission column, security column, resource control column

2. Db table: user column, permission column

3. Table_ private table

4. Columns_ private table

5. Proc_ private table

4. Common tables in sys database

1.host view

(1) host_summary

Statistics of SQL execution information, file io information and connection information of host

? Host: listening on connected hosts

? Statements: the total number of statements executed by the current host

? Statement_latency: statement wait time (delay time)

? Statement_avg_latency: average delay time for execution of statements

? Table_scans: table scan times

? File_ios: total io time

? File_io_latency: file io latency

? Current_connections: current number of connections

? Total_connections: total number of links

? Unique_users: the number of unique users of this host

? Current_memory: the memory allocated by the current account

? Total_memory_allocated: the total amount of memory allocated by this host

(2) host_summary_by_file_io

The file io information of host is counted.

? host: host

? iOS: total number of IO events

? io_latency: total delay time of IO

(3) host_summary_by_file_io_type

Group according to host and event, and count the io information of files

? host: host

? event_name: IO event name

? total: events that occur on this host

? total_latency: the total delay time for IO events to occur on this host

? max_latency: the maximum delay time in the host IO event

(4) host_summary_by_stages

Total statement stage, grouped by host

? Host: host

? Event_name: name

? Total: total number of occurrences

? Total_latency: total delay time

? Avg_latency: average delay time

(5) host_summary_by_statement_latency

Statistics of the information of host and execution of SQL statements

? Host: host

? Total: the total number of statements for this host

? Total_latency: the total delay time of this host

? Max_latency: maximum delay time of the host

? Lock_latency: lock delay time waiting for the lock

? Rows_sent: the total number of rows returned by the host through the statement

? Rows_examined: the number of rows returned by statements on the storage engine

? Rows_affected: the total number of rows affected by the statement by this host

? Full_scans: the total number of statements scanned by the full table

(6). Host_summary_by_statement_type

Group according to host and sql types to count the execution information of SQL statements

? Host: host

? Statement: the last statement event name

? Total: total number of sql statements

? Total_latency: total delays of sql statements

? Max_latency: the maximum number of sql statement delays

? Lock_latency: total lock delay

? Rows_sent: the total number of rows returned by the statement

? Rows_examined: the total number of rows read through the sql statement of the storage engine

? Rows_affected: total number of rows affected by the statement

? Full_scans: the total number of statement events scanned by the full table

2.innodb view

(1) innodb_buffer_stats_by_schema

Count the innodb cache of innodb engine through database

? Object_schema: database name

? Allocated: the total number of bytes allocated to the current database

? Data: the number of bytes of data allocated to the current database

? Pages: total number of pages allocated to the current database

? Pages_hashed: the number of hash pages allocated to the current database

? Pages_old: the number of old pages allocated to the current database

? Rows_cached: the number of rows currently cached in the database

(2) innodb_buffer_stats_by_table

Innodb cache for each table innodb engine

? Object_schema: database name

? Object_name: table name

? Allocated: total number of bytes allocated to the table

? Data: the number of bytes of data allocated to the table

? Pages: the number of pages allocated to the table

? Pages_hashed: the number of hash pages allocated to the table

? Pages_old: the number of old pages allocated to the table

? Rows_cached: the number of row caches for the table

(3) innodb_lock_waits

Total innodb lock waiting

? Wait_started: the time the lock is waiting to occur

? Wait_age: how long has the lock been waiting

? Wait_age_secs: displays the time in seconds that the lock has been waiting (added this column in 5.7.9)

? Locked_table: locked table

? Locked_index: locked index

? Locked_type: lock type

? Waiting_trx_id: waiting transaction ID

? Waiting_trx_started: the time to wait for the transaction to start

? Waiting_trx_age: how long have you been waiting for the transaction

These are the common system tables and views of mysql shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.