CODEWITHSUNDEEP
mysqlperformanceoptimization
Jimmy P
Jimmy P

Reputation: 1892

MySQL: What is a sample page?

I recently encountered a problem where the EXPLAIN plan for a MySQL query had changed dramatically and was negatively impacting query run times. In our efforts to fix the problem, we considered increasing the innodb_stats_sample_page from the MySQL default of 8. During my search I've found literature on the pros and cons of doing that, as well as how to choose an optimal value for that variable, but I was unable to find any explanation on what a sample page actually is.

The purpose of them is clear:

The MySQL query optimizer uses estimated statistics about key distributions to choose the indexes for an execution plan, based on the relative selectivity of the index. Certain operations cause InnoDB to sample random pages from each index on a table to estimate the cardinality of the index. (This technique is known as random dives.) These operations include the ANALYZE TABLE statement, the SHOW TABLE STATUS statement, and accessing the table for the first time after a restart.

That page doesn't explain what a sample page actually is though. So, what exactly is a sample page?

Upvotes: 3

Views: 150

Answers (2)

Roberto
Roberto

Reputation: 2185

A page is a memory block in which multiple records (for a single table) are stored, the indexing algorithm can take a random page of the table (i.e. the records stored in any page of the table) to make some assumptions about all the records in the table. So it can guess how useful would be to use a specific index in that table without analyzing each record.

Check documentation for details about the page/block structure

Why a random page and not random records? Because a page is stored in continuous memory which can be accessed in one memory operation, so in a memory operation you can get more than one record.

How does it determine which page to analyze? Any page is taken, as there is no specific page which is a better sample than another page.

Will it always choose the same page if I don't change records in that table? No, it will not, it will take a different random page each time it tries to sample that table. But if there is no big differences between records, the different sampled pages could lead to the same indexes being used. But if there are big differences (e.g. Initially the table has null for a specific field and then half of it was added with non-null for that field) other index can be used.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

InnoDB stores all records inside a fixed-size unit which is commonly called a "page" (though InnoDB sometimes calls it a "block" instead). Currently all pages are the same size, 16KB.

https://dev.mysql.com/doc/internals/en/innodb-page-structure.html

Upvotes: 1

Related Questions