| Bug #112075 | innodb_cached_indexes.n_cached_pages reports crazy high numbers | ||
|---|---|---|---|
| Submitted: | 15 Aug 2023 15:09 | Modified: | 22 Mar 3:33 |
| Reporter: | Jay Janssen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[16 Aug 2023 11:58]
MySQL Verification Team
Hi Mr. Janssen, Thank you for your bug report. However, please do note that this is a forum for the bugs with a fully repeatable test cases that always lead to the bug showing up. This is not a forum for asking questions. Regarding your questions, InnoDB pages can be both cached and removed from the cache. What you see is the number of times pages have been cached, while removal from the cache is not counted. Not a bug.
[16 Aug 2023 14:51]
Jay Janssen
At least the documentation is not very clear https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-cached-indexes-table.htm... says: ``` N_CACHED_PAGES The number of index pages cached in the InnoDB buffer pool. ``` It should read "The total number of index pages cached in the Innodb buffer pool since server startup for this specific index". As it reads currently, it makes it sound like that is the _current_ number of cached pages.
[16 Aug 2023 14:55]
MySQL Verification Team
Hi, The current explanation is very clear. It does NOT say that this is a CURRENT number of pages being cached.
[16 Aug 2023 17:09]
MySQL Verification Team
This is now a verified Documentation bug.
[22 Mar 3:33]
Philip Olson
Posted by developer: Thanks Jay for reporting this bug and offering a suggestion to improve the documentation, this is now fixed in the documentation source.
[22 Mar 11:14]
MySQL Verification Team
Thank you, Phillip.
Description: I was working on a query to evaluate cached % of tables like this: ``` -- Innodb cached data summarized by table select innodb_tables.name, sum(innodb_cached_indexes.n_cached_pages) as pages_cached, sum(innodb_index_stats.stat_value) as pages_total, sys.format_bytes(sum(innodb_cached_indexes.n_cached_pages) * 16384) as size_cached, sys.format_bytes(sum(innodb_index_stats.stat_value) * 16384) as size_total, concat(round((sum(innodb_cached_indexes.n_cached_pages) / sum(innodb_index_stats.stat_value)) * 100, 2), '%') as cached_pct from information_schema.innodb_tables join information_schema.innodb_indexes using (table_id) join information_schema.innodb_cached_indexes on innodb_indexes.space = innodb_cached_indexes.space_id and innodb_indexes.index_id = innodb_cached_indexes.index_id join mysql.innodb_index_stats on innodb_tables.name = concat(innodb_index_stats.database_name, "/", innodb_index_stats.table_name) and innodb_indexes.name = innodb_index_stats.index_name and innodb_index_stats.stat_name='size' group by innodb_tables.name; ``` For the most part the results here look sane, but I noticed some tables report very high numbers of cached pages on their indexes: ``` *************************** 1. row *************************** name: <table name> pages_cached: 9599944 pages_total: 11 size_cached: 146.48 GiB size_total: 176.00 KiB cached_pct: 87272218.18% 1 row in set (0.01 sec) ``` Here are the specific records from the innodb_cached_indexes table: ``` select * from information_schema.innodb_cached_indexes where space_id=62 and index_id in (238, 239, 240, 241); +----------+----------+----------------+ | SPACE_ID | INDEX_ID | N_CACHED_PAGES | +----------+----------+----------------+ | 62 | 238 | 7817308 | | 62 | 239 | 377888 | | 62 | 240 | 377527 | | 62 | 241 | 1025509 | +----------+----------+----------------+ 4 rows in set (0.01 sec) ``` This table has a high update rate, but I can't account for why the cached pages count would seemingly exceed the total Buffer pool size by so much. Is this a fault with my query, the stats, or my understanding of what N_CACHED_PAGES means? The manual states: https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-cached-indexes-table.htm... N_CACHED_PAGES The number of index pages cached in the InnoDB buffer pool. How to repeat: I can see this on other servers on some tables. Not sure how easy it is to reproduce.