1

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?

Improve this question
12
  • Restart mysqld and run the test again. This will force all the reads to come from disk, not just the buffer_pool (cache). Were there 19K read requests?
    – Rick James
    Commented Jan 11, 2019 at 21:10
  • While you are at it, do SHOW GLOBAL STATUS LIKE 'Inno%'; and SELECT name, type, count, FORMAT(avg_count, 1) AS per_second, comment FROM information_schema.innodb_metrics WHERE name LIKE '%innodb%';
    – Rick James
    Commented Jan 11, 2019 at 21:28
  • @RickJames can you please explain what is innodb-buffer-pool-reads ? Because, I got diffrent explanation from different source.
    – Dinesh Kumar
    Commented Jan 12, 2019 at 10:40
  • @RickJames the above measurement were taken after mysql was restarted. Hence no buffer pool cache.
    – Dinesh Kumar
    Commented Jan 12, 2019 at 10:41
  • 1
    That sounds "close". The Facebook link said more than anything I have ever found. Yet it was still vague. What is your goal? I would like a reliable way to measure disk activity for a single query. This STATUS seems at least close. The Handler% values are quite good at the 'row' level, but not 'disk' level.
    – Rick James
    Commented Jan 14, 2019 at 16:40

1 Answer 1

Reset to default
0

If you are using mysql8.0 version, you can refer to this link to determine https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html

Improve this answer
2
  • 1
    Link only answers get deleted. Answers should be complete by themselves.
    – Rohit Gupta
    Commented May 14 at 9:41
  • Please add the relevant descriptions from the page in your link to your answer, via the edit link. It is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.
    – Hannah Vernon ♦
    Commented May 14 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.