4
4
Misc. INFORMATION_SCHEMA Tables
5
5
=================================
7
This page lists the ``INFORMATION_SCHEMA`` tables added to standard |MySQL| by |Percona Server| that don't exist elsewhere in the documentation.
12
.. table:: INFORMATION_SCHEMA.INNODB_LOCK_WAITS
14
:column REQUESTING_TRX_ID:
15
:column REQUESTED_LOCK_ID:
16
:column BLOCKING_TRX_ID:
17
:column BLOCKING_LOCK_ID:
19
.. table:: INFORMATION_SCHEMA.INNODB_LOCKS
21
:column LOCK_ID: Internal unique lock ID
22
:column LOCK_TRX_ID: ID of the transaction holding the lock
23
:column LOCK_MODE: Mode of the lock (shared, exclusive, …)
24
:column LOCK_TYPE: ``RECORD`` for a record lock and ``TABLE`` for a table lock
25
:column LOCK_TABLE: Name of the table holding the lock
26
:column LOCK_INDEX: If lock type is ``RECORD``, name of the index
27
:column LOCK_SPACE: If lock type is ``RECORD``, tablespace id of the locked record
28
:column LOCK_PAGE: If lock type is ``RECORD``, page number of the locked record
29
:column LOCK_REC: If lock type is ``RECORD``, heap number of the locked record
30
:column LOCK_DATA: If lock type is ``RECORD``, primary key of the locked record
32
This table contains information on each lock that is requested by any transaction and on each lock that is held by any transaction. You can get details about the transactions involved by joining INNODB_LOCKS with INNODB_TRX on TRX_ID.
34
This table was introduced by the |InnoDB| plugin and you can find the full documentation here.
36
.. table:: INFORMATION_SCHEMA.INNODB_TRX
38
:column TRX_ID: |InnoDB| internal unique transaction id
39
:column TRX_STATE: Execution state. Possible values are: ``Running``, ``Lock_wait``, ``Rolling_back``, ``Committing``
40
:column TRX_STARTED: Transaction start time
41
:column TRX_REQUESTED_LOCK_ID: Id of the lock the transaction has requested
42
:column TRX_WAIT_STARTED: Date and time when the transaction started waiting for a lock
43
:column TRX_WEIGHT: Weight of the transaction, ie approximate number of locked and modified rows
44
:column TRX_MYSQL_THREAD_ID: Thread id
45
:column TRX_QUERY: SQL query being executed
47
This table holds information on every transaction running in the |InnoDB| kernel. Contrary to the output of SHOW |InnoDB| STATUS, it doesn't show information on idle transactions.
49
This table was introduced by the |InnoDB| plugin and you can find the full documentation here.
54
Only the temporary tables that were explicitly created with `CREATE TEMPORARY TABLE` or `ALTER TABLE` are shown, and not the ones created to process complex queries.
10
Only the temporary tables that were explicitly created with `CREATE TEMPORARY TABLE` or `ALTER TABLE` are shown, and not the ones created to process complex queries.
56
12
.. table:: INFORMATION_SCHEMA.GLOBAL_TEMPORARY_TABLES
86
42
This table holds information on the temporary tables existing for the running connection.
44
Buffer Pool Data Structure Tables
45
=================================
47
The following tables provide various information about the contents of the |InnoDB| buffer pool.
49
.. table:: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES
51
:column PAGE_TYPE: Type of the page. Possible values: index, undo_log, inode, ibuf_free_list, allocated, bitmap, sys, trx_sys, fsp_hdr, xdes, blob, zblob, zblob2, unknown
52
:column SPACE_ID: Tablespace ID
53
:column PAGE_NO: Page offset within its tablespace
54
:column LRU_POSITION: Page position in the LRU list
55
:column FIX_COUNT: reference count of a page. It is incremented every time the page is accessed by |InnoDB|, and is 0 if and only if the page is not currently being accessed
56
:column FLUSH_TYPE: type of the last flush of the page (0:LRU 2:flush_list)
60
mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES LIMIT 20;
61
+-----------+----------+---------+--------------+-----------+------------+
62
| page_type | space_id | page_no | lru_position | fix_count | flush_type |
63
+-----------+----------+---------+--------------+-----------+------------+
64
| allocated | 0 | 7 | 3 | 0 | 2 |
65
| allocated | 0 | 1 | 4 | 0 | 0 |
66
| allocated | 0 | 3 | 5 | 0 | 0 |
67
| inode | 0 | 2 | 6 | 0 | 2 |
68
| index | 0 | 4 | 7 | 0 | 2 |
69
| index | 0 | 11 | 8 | 0 | 0 |
70
| index | 0 | 12956 | 9 | 0 | 0 |
71
| allocated | 0 | 5 | 10 | 0 | 2 |
72
| allocated | 0 | 6 | 11 | 0 | 2 |
73
| undo_log | 0 | 51 | 12 | 0 | 2 |
74
| undo_log | 0 | 52 | 13 | 0 | 2 |
75
| index | 0 | 8 | 14 | 0 | 0 |
76
| index | 0 | 288 | 15 | 0 | 0 |
77
| index | 0 | 290 | 16 | 0 | 2 |
78
| index | 0 | 304 | 17 | 0 | 0 |
79
| allocated | 0 | 0 | 18 | 0 | 2 |
80
| index | 0 | 10 | 19 | 0 | 0 |
81
| index | 0 | 12973 | 20 | 0 | 0 |
82
| index | 0 | 9 | 21 | 0 | 2 |
83
| index | 0 | 12 | 22 | 0 | 0 |
84
+-----------+----------+---------+--------------+-----------+------------+
85
20 rows in set (0.81 sec)
87
This table shows the characteristics of the allocated pages in buffer pool and current state of them.
89
.. table:: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX
91
:column index_id: index name
92
:column space_id: Tablespace ID
93
:column page_no: Page offset within its tablespace
94
:column n_recs: number of user records on page
95
:column data_size: sum of the sizes of the records in page
96
:column hashed: the block is in adaptive hash index (1) or not (0)
97
:column access_time: time of the last access to that page
98
:column modified: modified since loaded (1) or not (0)
99
:column dirty: modified since last flushed (1) or not (0)
100
:column old: is old blocks in the LRU list (1) or not (0)
101
:column lru_position: page position in the LRU list
102
:column fix_count: reference count of a page. It is incremented every time the page is accessed by |InnoDB|, and is 0 if and only if the page is not currently being accessed
103
:column flush_type: type of the last flush of the page (0:LRU 2:flush_list)
107
+----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
108
| index_id | space_id | page_no | n_recs | data_size | hashed | access_time | modified | dirty | old | lru_position | fix_count | flush_type |
109
+----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
110
| 39 | 0 | 5787 | 468 | 14976 | 1 | 2636182517 | 1 | 0 | 1 | 0 | 0 | 2 |
111
| 40 | 0 | 5647 | 1300 | 15600 | 1 | 2636182517 | 1 | 0 | 1 | 0 | 0 | 2 |
112
| 39 | 0 | 5786 | 468 | 14976 | 1 | 2636182516 | 1 | 0 | 1 | 0 | 0 | 2 |
113
| 40 | 0 | 6938 | 1300 | 15600 | 1 | 2636193968 | 1 | 0 | 1 | 0 | 0 | 2 |
114
| 39 | 0 | 5785 | 468 | 14976 | 1 | 2636182514 | 1 | 0 | 1 | 0 | 0 | 2 |
115
| 39 | 0 | 5784 | 468 | 14976 | 1 | 2636182512 | 1 | 0 | 1 | 0 | 0 | 2 |
116
| 40 | 0 | 5646 | 1300 | 15600 | 1 | 2636182511 | 1 | 0 | 1 | 0 | 0 | 2 |
117
| 39 | 0 | 7203 | 468 | 14976 | 1 | 2636193967 | 1 | 0 | 1 | 0 | 0 | 2 |
118
| 39 | 0 | 5783 | 468 | 14976 | 1 | 2636182507 | 1 | 0 | 1 | 0 | 0 | 2 |
119
| 39 | 0 | 5782 | 468 | 14976 | 1 | 2636182506 | 1 | 0 | 1 | 0 | 0 | 2 |
120
+----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
122
This table shows information about the index pages located in the buffer pool.
124
.. table:: INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_BLOB
126
:column space_id: tablespace ID
127
:column page_no: page offset within its tablespace
128
:column compressed: contains compressed data (1) or not (0)
129
:column part_len: data length in the page
130
:column next_page_no: page number of the next data
131
:column lru_position: page position in the LRU list
132
:column fix_count: reference count of a page. It is incremented every time the page is accessed by InnoDB, and is 0 if and only if the page is not currently being accessed
133
:column flush_type: type of the last flush of the page (0:LRU 2:flush_list)
137
mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES_BLOB LIMIT 20;
138
+----------+---------+------------+----------+--------------+--------------+-----------+------------+
139
| space_id | page_no | compressed | part_len | next_page_no | lru_position | fix_count | flush_type |
140
+----------+---------+------------+----------+--------------+--------------+-----------+------------+
141
| 1748 | 111 | 0 | 10137 | 0 | 263 | 0 | 2 |
142
| 1748 | 307 | 0 | 5210 | 0 | 1084 | 0 | 2 |
143
| 1748 | 1329 | 0 | 6146 | 0 | 4244 | 0 | 2 |
144
| 1748 | 1330 | 0 | 11475 | 0 | 4245 | 0 | 2 |
145
| 1748 | 1345 | 0 | 5550 | 0 | 4247 | 0 | 2 |
146
| 1748 | 1346 | 0 | 7597 | 0 | 4248 | 0 | 2 |
147
| 1748 | 3105 | 0 | 6716 | 0 | 8919 | 0 | 2 |
148
| 1748 | 3213 | 0 | 8170 | 0 | 9390 | 0 | 2 |
149
| 1748 | 6142 | 0 | 5648 | 0 | 19638 | 0 | 2 |
150
| 1748 | 7387 | 0 | 10634 | 0 | 24191 | 0 | 2 |
151
| 1748 | 7426 | 0 | 5355 | 0 | 24194 | 0 | 2 |
152
| 1748 | 7489 | 0 | 16330 | 7489 | 24196 | 0 | 2 |
153
| 1748 | 7490 | 0 | 7126 | 0 | 24197 | 0 | 2 |
154
| 1748 | 7657 | 0 | 13571 | 0 | 24681 | 0 | 2 |
155
| 1748 | 7840 | 0 | 11208 | 0 | 25737 | 0 | 2 |
156
| 1748 | 9599 | 0 | 11882 | 0 | 31989 | 0 | 2 |
157
| 1748 | 11719 | 0 | 7367 | 0 | 40466 | 0 | 2 |
158
| 1748 | 12051 | 0 | 11049 | 0 | 41441 | 0 | 2 |
159
| 1748 | 12052 | 0 | 16330 | 12052 | 41442 | 0 | 2 |
160
| 1748 | 12053 | 0 | 2674 | 0 | 41443 | 0 | 2 |
161
+----------+---------+------------+----------+--------------+--------------+-----------+------------+
162
20 rows in set (0.05 sec)
164
This table shows information from blob pages located in buffer pool.