MySQL Bugs: #78105: The effect of low innodb_undo

Bug #78105	The effect of low innodb_undo_logs value is not clear
Submitted:	17 Aug 2015 13:05	Modified:	7 Jul 2016 19:51
Reporter:	Laurynas Biveinis (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	doc, innodb_available_undo_logs, innodb_undo_logs

Description:
It is not entirely clear what happens if one sets innodb_undo_logs to a low values.

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_undo_logs says, for example

 "32 undo logs are reserved for use by temporary tables and are hosted in the temporary table tablespace (ibtmp1). To allocate additional undo logs for data-modifying transactions that generate undo records, innodb_undo_logs must be set to a value greater than 32 if undo logs are stored in the system tablespace only."

But https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespace.html says

"The first undo log (rollback segment) always resides in the system tablespace (when undo tablespaces are present, this undo log is inactive).

Undo logs 2 to 33 reside in the shared temporary tablespace (ibtmp1)."

The latter page would suggest that the former saying "innodb_undo_logs must be set to a value greater than 33 ... " would make more sense, but it's not entirely clear.

So let's test --innodb-undo-logs=32 by initialising a new instance with it:

SELECT @@innodb_undo_logs;
@@innodb_undo_logs
32
SHOW STATUS LIKE 'Innodb_available_undo_logs';
Variable_name	Value
Innodb_available_undo_logs	33

First, innodb_available_undo_logs > innodb_undo_logs is a surprising relationship and maybe should be documented as a possibility (while on the topic of innodb_available_undo_logs, maybe https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Innodb_availa... should mention that 32 of them are for temp tables only).

Second, if that extra undo log is the system tablespace one, which is always present if no separate undo tablespaces configured, then maybe https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_undo_logs should be rephrased to account for it? Or, if it's really a case that --innodb-undo-logs must be set to a higher value, and given that its low setting only applies to creating new instances, make its too-low value a fatal error at server startup?

How to repeat:
See above

Suggested fix:
Clarify docs

Posted by developer:
 
When innodb_undo_logs is set to 32 or less, InnoDB assigns one undo log
to the system tablespace and 32 to the temporary tablespace (ibtmp1).

More specifically, as provided by the Developer of this feature:

* If value = 1, then 1 redo (or normal) rollback segment is created and 32 non-redo rollback segments are created.
* If value = 1 - 32, then 1 redo (or normal) rollback segment is created and 32 non-redo rollback segments are created.
* If value > 32, then "value minus 32" redo rollback segments are created and 32 non-redo rollback segments are created. 

The following documentation has been updated:

http://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespace.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_undo_logs
http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Innodb_availab...

Changes should appear online soon.
Thank you for the bug report.