How to find out "InnoDB Write buffer efficiency"?

Question

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.

Answer 1

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

Answer 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).