Until now there was no way to answer the question without manual investigation. As it turned out a small change can make a big difference.
There are two ways to know how many records an InnoDB page stores. The index page has a header PAGE_N_RECS – this is a number of records the page stores. Obviously it doesn’t count any deleted records. The second method to confirm how many records are in the page is to follow records pointers – and count them.
As you might know, records inside an InnoDB page are organized in an unidirectional list. The list is sorted by primary key and starts with the internal record
infinum and ends with another internal record
supremum. If you follow record pointers from the infinum and end up with the supremum record then two conclusions can be make:
This is what
constraints_parser does before it tries to find any records in the page:
./constraints_parser -5f pages-actor/FIL_PAGE_INDEX/0-1599/00000000-00000003.page -V
Checking a page
Infimum offset: 0x63
Supremum offset: 0x70
Next record at offset: 0x7F (127)
Next record at offset: 0xA8 (168)
Next record at offset: 0xCE (206)
Next record at offset: 0x70 (112)
Page is good
Format: COMPACT, Records list: Valid, Expected records: (200 200)
Here you can see “Expected records”: 200 in the header and 200 records were found by pointers.
Then the contraints_parser tries to recover the records. It checks whether fields have permitted values, etc. In the end it knows how many records were actually recovered.
This statistics are printed along with the recovered records:
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (200 200)
000000001917 DB000001720110 actor 1 "PENELOPE" "GUINESS" "2006-02-15 04:34:33"
000000001917 DB00000172011B actor 2 "NICK" "WAHLBERG" "2006-02-15 04:34:33"
000000001917 DB000001720126 actor 3 "ED" "CHASE" "2006-02-15 04:34:33"
000000001917 DB000001720131 actor 4 "JENNIFER" "DAVIS" "2006-02-15 04:34:33"
000000001917 DB0000017209E4 actor 200 "THORA" "TEMPLE" "2006-02-15 04:34:33"
-- Page id: 3, Found records: 200, Lost records: NO, Leaf page: YES
From the output above we know that 200 records were expected, 200 – recovered. So, no records were lost.
It’s worth noting that records are stored in the leaf pages only, so if an index has more than one page no records will be found in the root page.
For the root page it will print “Leaf page: NO” and it can be ignored.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.