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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.