0

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?

  • 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 Jan 11 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 Jan 11 at 21:28
  • @RickJames can you please explain what is innodb-buffer-pool-reads ? Because, I got diffrent explanation from different source. – Dinesh Kumar Jan 12 at 10:40
  • @RickJames the above measurement were taken after mysql was restarted. Hence no buffer pool cache. – Dinesh Kumar Jan 12 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 Jan 14 at 16:40

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.