4

Is there any way to find out that "InnoDB Write buffer efficiency" without tools like mysqltuner?

I can't find it in INNODB STATUS or information_schema.

2 Answers 2

3

Why do you need to know it? There is no actionable information in the statistic.

Make innodb_buffer_pool_size about 70% of available RAM (assuming more than 4GB of RAM); that will be optimal.

Look for slow queries and improve them; that will cut back on performance problems.

Here are some things you can get from SHOW GLOBAL STATUS and SHOW VARIABLES:

  • Innodb_pages_written / Innodb_buffer_pool_write_requests -- Write requests that had to hit disk -- <15% is 'good'
  • Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests -- Should not have to wait to get into the buffer pool. -- <1% is 'good'
  • Innodb_buffer_pool_pages_flushed / Uptime -- Writes actually going to disk
  • Innodb_buffer_pool_pages_flushed / max(Questions, Queries) -- Buffer pool writes per query
2

When you issue the command show engine innodb status\G you will see a fair amount of information. You want to look for something that looks like Buffer pool hit rate 900 / 1000 in the output.

Your efficiency rate will be the percentage of hits compared to the overall usage. In the example above, my efficiency is 90% (900/1000).

1
  • 1
    is that the hit rate for read or write or both together? I need it separate for read and write.
    – Heiko
    Jan 19, 2017 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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