How to calculate InnoDB log files usage.

In this example, I would demonstrate how to check the amount of InnoDB log file space in use,
follow these steps ( checking current usage at peak times):
Examine INNODB MONITOR output (i.e. SHOW ENGINE INNODB STATUS\G)
and look at LOG section.

---
LOG
---
Log sequence number 5428 3708369329
Log flushed up to   5428 3708369329
Last checkpoint at  5428 834766592
0 pending log writes, 0 pending chkp writes


The innodb log file usage can be calculated by the following formula:

Last sequence number is ((5428 * 4 * 1024*1024*1024)+3708369329) = 23316790852017
Last checkpoint at is ((5428 * 4 * 1024*1024*1024)+834766592) = 23313917249280

So the actual innodb log file usuage is = 23316790852017 - 23313917249280 = 2873602737 = which is about 2740 MB.

innodb_log_file_size = 1048576000
innodb_log_files_in_group = 4

Total log files size = 1048576000 * 4 = 4000MB

The above calculations applicable only for MySQL versions < 5.5 version
and for MySQL version > 5.5 use the following calculations.


---
LOG
---
Log sequence number 2735016708
Log flushed up to   2734016690
Last checkpoint at  2380597012

Perfrom following calulation (formula) to know log space used. The values to use in calculation are "Log sequence number" and "Last checkpoint at".

mysql> SELECT (2735016708 - 2380597012)/1024/1024 "Space used in MB";
+------------------+
| Space used in MB |
+------------------+
|     338.00096130 |
+------------------+
1 row in set (0.01 sec)



MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers