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 these lines (if you are using 5.0 or 5.1 without Innodb plugin):

--- LOG --- Log sequence number 2380 1505869110 Log flushed up to 2380 1505868967 Last checkpoint at 2380 936944426 1 pending log writes, 0 pending chkp writes 532415047 log i/o's done, 544.17 log i/o's/second

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

select (( ( 2380 * 4 * 1024 * 1024 * 1024) + 1505869110 ) - ( ( 2380 * 4 * 1024 * 1024 * 1024) + 936944426 )) /1024/1024 "Space used in MB"; +------------------+ | Space used in MB | +------------------+ | 542.56885910 | +------------------+ 1 row in set (0.00 sec)

In this example 542 megabytes is more than 75% of the total log space, so the logsize (512MB) is small. Ensure that the amount of log space used never exceeds 75% of that value (542MB). Find the instructions here to add/resizeInnodb log files.

__Examine INNODB MONITOR output (i.e. SHOW ENGINE INNODB STATUS\G) and look at these lines:__

**There is slightly different method to calculate innodb log file space used (if you are using MySQL 5.5 or InnoDB plugin in MySQL 5.1):**```
---
LOG
---
Log sequence number 2388016708
Log flushed up to 2388016690
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".
```
SELECT (2388016708 - 2380597012)/1024/1024 "Space used in MB";
+------------------+
| Space used in MB |
+------------------+
| 7.0759735111 |
+------------------+
1 row in set (0.00 sec)
```

Based on the rules of algebra, isn't it simpler to have the formula simply be

ReplyDelete(Log sequence number - Last checkpoint at) /1024/1024

That appears to give the same result when I calculate it.

The reason you get the same result, because both values on the left are identical but its not always the case. If the numbers of the left are different then you won't get the same calculation e.g.

DeleteLog sequence number 153 78914

Log flushed up to 152 3788352838

Last checkpoint at 152 403429220