Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6, 10.11, 11.4, 11.8
-
Unexpected results
-
Description
Hello
I'll start out by saying I'm not 100 % sure of this, and I'm sorry if I'm wasting anyones time.
When looking at sys.innodb_buffer_stats_by_table and sys.innodb_buffer_stats_by_schema two of the columns are, pages_hashed and pages_old
Pulling up the CREATE VIEW for that view shows how each of those are gathered
SELECT IF(LOCATE('.', ibp.table_name) = 0, 'InnoDB System', REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', 1), '`', '')) AS object_schema, |
REPLACE(SUBSTRING_INDEX(ibp.table_name, '.', -1), '`', '') AS object_name, |
format_bytes(SUM(IF(ibp.compressed_size = 0, 16384, compressed_size))) AS allocated, |
format_bytes(SUM(ibp.data_size)) AS data, |
COUNT(ibp.page_number) AS pages, |
COUNT(IF(ibp.is_hashed = 'YES', 1, NULL)) AS pages_hashed, |
COUNT(IF(ibp.is_old = 'YES', 1, NULL)) AS pages_old, |
ROUND(SUM(ibp.number_records)/COUNT(DISTINCT ibp.index_name)) AS rows_cached |
FROM information_schema.innodb_buffer_page ibp |
WHERE table_name IS NOT NULL |
GROUP BY object_schema, object_name |
ORDER BY SUM(IF(ibp.compressed_size = 0, 16384, compressed_size)) DESC; |
They both reference information_schema.innodb_buffer_page
pages_old checks if is_old is equal to 'YES', but the is_old is not a string but an int:
https://github.com/MariaDB/server/blob/212fad1b7e26f80c65299179ed320b766570d251/storage/innobase/handler/i_s.cc#L118
Running the following, seems to give a good indication that the "logic" has been flipped, in either INNODB_BUFFER_PAGE or the sys views
SELECT IS_OLD, if(`IS_OLD` = 'YES',1,NULL) AS `pages_old` FROM `INNODB_BUFFER_PAGE`; |
And the same is true for IS_HASHED vs. pages_hashed
Again, I'm not 100% sure of this, and I'm very sorry if this ends up wasting anyones time
Attachments
Issue Links
- is caused by
-
MDEV-19940 Clean up INFORMATION_SCHEMA.INNODB_ tables
-
- Closed
-
- relates to
-
MDEV-37323 Default value of 16kb for innodb page size in sys views
-
- Open
-