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
Last updated
Was this helpful?