Find Current Log Sequence Number in MySQL 5.0.X

innodbMySQLmysql-5mysql-5.0

I would like to test the optimal value for iblog files on my mysql which is 5.0.77
I found the below procedure in mysqlperformance blog:
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

mysql> pager grep sequence
PAGER set to 'grep sequence'

mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 84 3836410803
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row in set (0.05 sec)

mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83471203 | 
+------------+

But on mysql 5.0.X, I can't see the log sequence number under "show innodb stauts" output.
Shall some one please let me know a way to know current log sequence number in mysql 5.0.x

Best Answer

I actually wrote about this back on August 27, 2012 : Proper tuning for 30GB InnoDB table on server with 48GB RAM. Here is an excerpt from my past answer (under the section entitled Log File Size):

EXCERPT

5MB is the default size for innodb_log_file_size. Percona's mysqperformanceblog.com gave two good articles on computing the right size for your particular MySQL instance:

Basically, the blog recommends measuring how many bytes are written to the InnoDB Log Files in one hour. This is what I run to figure that out

SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL_HR,@MB_WL_HR,@GB_WL_HR;

END OF EXCERPT

Where do you go from here?

Run these commands every hour for a week. When you ascertain the highest number for @MB_WL_HR, take half of it and resize innodb_log_file_size to it : See my post How to safely change MySQL innodb variable 'innodb_log_file_size'?

CONCLUSION

Since you can figure out the proper InnoDB Log File Size in this manner, there is no need to pool the show engine innodb status\G for anything, especially since you said it is not available in MySQL 5.0.

Keep in mind that my answer works for MySQL 5.1/5.5. So, how do you did this for MySQL 5.0?

It's a good thing the status variable Innodb_os_log_written exists in MySQL 5.0.

Looking back, here is your original question:

mysql> pager grep sequence
PAGER set to 'grep sequence'

mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 84 3836410803
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row in set (0.05 sec)

mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83471203 | 
+------------+

With MySQL 5.0 you have to do this:

mysql> show global status like 'Innodb_os_log_written'; select sleep(60); show global status like 'Innodb_os_log_written';

Then subtract and multiply like you did before:

mysql> show global status like 'Innodb_os_log_written'; select sleep(60); show global status like 'Innodb_os_log_written';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 500034464256 |
+-----------------------+--------------+
1 row in set (0.01 sec)

+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.01 sec)

+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 500047017984 |
+-----------------------+--------------+
1 row in set (0.01 sec)

mysql> select (500047017984 - 500034464256) / 1024 / 1024 as MB_per_min;
+-------------+
| MB_per_min  |
+-------------+
| 11.97216797 |
+-------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

Related Question