What does 'innodb_buffer_pool_reads' and 'innodb_buffer_pool_read_requests' actually mean?
Mysql version: 5.7.18 (Innodb)
I have a table with the following specification
Structure:
CREATE TABLE `dummybigint10` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT 'A wonderful serenity has taken possession of my entire soul, like these sweet mornings of spring which I enjoy with my whole heart. I am alone, and feel the charm of existence in this spot, which was created for the bliss of souls like mine. I am so.',
`num1` bigint(20) DEFAULT NULL,
`num2` bigint(20) DEFAULT NULL,
`num3` bigint(20) DEFAULT NULL,
`num4` bigint(20) DEFAULT NULL,
`num5` bigint(20) DEFAULT NULL,
`num6` bigint(20) DEFAULT NULL,
`num7` bigint(20) DEFAULT NULL,
`num8` bigint(20) DEFAULT NULL,
`num9` bigint(20) DEFAULT NULL,
`num10` bigint(20) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=2163184 DEFAULT CHARSET=utf8
Row count: 1049088
The B+ tree of this table has 19104 leaf nodes (out of which 19075 used) and 20 internal nodes (out of which 20 is used). [Thanks to innodb_ruby project]
I fired a query to cause a full table scan as follows:
select count(*) from dummybigint10 where num9 is not null;
I observed for the values in 'innodb_buffer_pool_reads from performance_schema.
Observed values are:
innodb_buffer_pool_reads: 98
Some resource available online say that, 'innodb_buffer_pool_reads' is the number of pages which are loaded by Innodb from disk, which in my case , a full table scan on the given table has to cause at least 19104 but the actual value reported by Innodb is 98. I think that resource is misleading the interpretation of innodb_buffer_pool_reads. Can any one explain what does innodb_buffer_pool_reads actually mean?
mysql performance innodb
asked 3 mins ago
Dinesh KumarDinesh Kumar
133112
add a comment |
Mysql version: 5.7.18 (Innodb)
I have a table with the following specification
Structure:
CREATE TABLE `dummybigint10` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT 'A wonderful serenity has taken possession of my entire soul, like these sweet mornings of spring which I enjoy with my whole heart. I am alone, and feel the charm of existence in this spot, which was created for the bliss of souls like mine. I am so.',
`num1` bigint(20) DEFAULT NULL,
`num2` bigint(20) DEFAULT NULL,
`num3` bigint(20) DEFAULT NULL,
`num4` bigint(20) DEFAULT NULL,
`num5` bigint(20) DEFAULT NULL,
`num6` bigint(20) DEFAULT NULL,
`num7` bigint(20) DEFAULT NULL,
`num8` bigint(20) DEFAULT NULL,
`num9` bigint(20) DEFAULT NULL,
`num10` bigint(20) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=2163184 DEFAULT CHARSET=utf8
Row count: 1049088
The B+ tree of this table has 19104 leaf nodes (out of which 19075 used) and 20 internal nodes (out of which 20 is used). [Thanks to innodb_ruby project]
I fired a query to cause a full table scan as follows:
select count(*) from dummybigint10 where num9 is not null;
I observed for the values in 'innodb_buffer_pool_reads from performance_schema.
Observed values are:
innodb_buffer_pool_reads: 98
Some resource available online say that, 'innodb_buffer_pool_reads' is the number of pages which are loaded by Innodb from disk, which in my case , a full table scan on the given table has to cause at least 19104 but the actual value reported by Innodb is 98. I think that resource is misleading the interpretation of innodb_buffer_pool_reads. Can any one explain what does innodb_buffer_pool_reads actually mean?
mysql performance innodb
asked 3 mins ago
Dinesh KumarDinesh Kumar
133112
add a comment |
Mysql version: 5.7.18 (Innodb)
I have a table with the following specification
Structure:
CREATE TABLE `dummybigint10` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT 'A wonderful serenity has taken possession of my entire soul, like these sweet mornings of spring which I enjoy with my whole heart. I am alone, and feel the charm of existence in this spot, which was created for the bliss of souls like mine. I am so.',
`num1` bigint(20) DEFAULT NULL,
`num2` bigint(20) DEFAULT NULL,
`num3` bigint(20) DEFAULT NULL,
`num4` bigint(20) DEFAULT NULL,
`num5` bigint(20) DEFAULT NULL,
`num6` bigint(20) DEFAULT NULL,
`num7` bigint(20) DEFAULT NULL,
`num8` bigint(20) DEFAULT NULL,
`num9` bigint(20) DEFAULT NULL,
`num10` bigint(20) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=2163184 DEFAULT CHARSET=utf8
Row count: 1049088
The B+ tree of this table has 19104 leaf nodes (out of which 19075 used) and 20 internal nodes (out of which 20 is used). [Thanks to innodb_ruby project]
I fired a query to cause a full table scan as follows:
select count(*) from dummybigint10 where num9 is not null;
I observed for the values in 'innodb_buffer_pool_reads from performance_schema.
Observed values are:
innodb_buffer_pool_reads: 98
Some resource available online say that, 'innodb_buffer_pool_reads' is the number of pages which are loaded by Innodb from disk, which in my case , a full table scan on the given table has to cause at least 19104 but the actual value reported by Innodb is 98. I think that resource is misleading the interpretation of innodb_buffer_pool_reads. Can any one explain what does innodb_buffer_pool_reads actually mean?
mysql performance innodb
asked 3 mins ago
Dinesh KumarDinesh Kumar
133112
Mysql version: 5.7.18 (Innodb)
I have a table with the following specification
Structure:
CREATE TABLE `dummybigint10` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT 'A wonderful serenity has taken possession of my entire soul, like these sweet mornings of spring which I enjoy with my whole heart. I am alone, and feel the charm of existence in this spot, which was created for the bliss of souls like mine. I am so.',
`num1` bigint(20) DEFAULT NULL,
`num2` bigint(20) DEFAULT NULL,
`num3` bigint(20) DEFAULT NULL,
`num4` bigint(20) DEFAULT NULL,
`num5` bigint(20) DEFAULT NULL,
`num6` bigint(20) DEFAULT NULL,
`num7` bigint(20) DEFAULT NULL,
`num8` bigint(20) DEFAULT NULL,
`num9` bigint(20) DEFAULT NULL,
`num10` bigint(20) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=2163184 DEFAULT CHARSET=utf8
Row count: 1049088
The B+ tree of this table has 19104 leaf nodes (out of which 19075 used) and 20 internal nodes (out of which 20 is used). [Thanks to innodb_ruby project]
I fired a query to cause a full table scan as follows:
select count(*) from dummybigint10 where num9 is not null;
I observed for the values in 'innodb_buffer_pool_reads from performance_schema.
Observed values are:
innodb_buffer_pool_reads: 98
Some resource available online say that, 'innodb_buffer_pool_reads' is the number of pages which are loaded by Innodb from disk, which in my case , a full table scan on the given table has to cause at least 19104 but the actual value reported by Innodb is 98. I think that resource is misleading the interpretation of innodb_buffer_pool_reads. Can any one explain what does innodb_buffer_pool_reads actually mean?
mysql performance innodb
mysql performance innodb
asked 3 mins ago
Dinesh KumarDinesh Kumar
133112
asked 3 mins ago
Dinesh KumarDinesh Kumar
133112
asked 3 mins ago
Dinesh KumarDinesh Kumar
133112
asked 3 mins ago
Dinesh KumarDinesh Kumar
133112
asked 3 mins ago
Dinesh KumarDinesh Kumar
133112
133112
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226866%2fwhat-does-innodb-buffer-pool-reads-and-innodb-buffer-pool-read-requests-actu%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226866%2fwhat-does-innodb-buffer-pool-reads-and-innodb-buffer-pool-read-requests-actu%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
