Bug #68023 InnoDB reserves an excessive amount of disk space for write operations
Submitted: 3 Jan 2013 19:42 Modified: 22 Jan 2013 20:00
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:All OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, reservation, space

[3 Jan 2013 19:42] Jeremy Cole
When performing operations that are expected to expand a table (for example, allocate new pages due to a page split), InnoDB currently preallocates and reserves up to 1% of the total size of the tablespace as a measure to ensure that enough free extents (that is, disk space) are available for the operation and to ensure that if running out of disk space, these operations are preemptively failed as to reserve space for operations that end up freeing space (that is, delete data).

The percentage can be reasonable for tables smaller than a few gigabytes, but not for tables sized at tens of gigabytes or more, at which point the percentage won't correctly estimate the free space needed to perform operations and may cause an excessive amount of free extents to be preallocated.

How to repeat:
In fsp_reserve_free_extents in fsp/fsp0fsp.c, the following code is used to reserve space for write operations:

  3023		if (alloc_type == FSP_NORMAL) {
  3024			/* We reserve 1 extent + 0.5 % of the space size to undo logs
  3025			and 1 extent + 0.5 % to cleaning operations; NOTE: this source
  3026			code is duplicated in the function below! */
  3028			reserve = 2 + ((size / FSP_EXTENT_SIZE) * 2) / 200;
  3030			if (n_free <= reserve + n_ext) {
  3032				goto try_to_extend;
  3033			}
  3034		} else if (alloc_type == FSP_UNDO) {
  3035			/* We reserve 0.5 % of the space size to cleaning operations */
  3037			reserve = 1 + ((size / FSP_EXTENT_SIZE) * 1) / 200;
  3039			if (n_free <= reserve + n_ext) {
  3041				goto try_to_extend;
  3042			}
  3043		} else {
  3044			ut_a(alloc_type == FSP_CLEANING);
  3045		}

This reserves 2 extents + 1% of the tablespace size for normal insert/update operations, and 1 extent + 0.5% of the tablespace size for delete operations.

Suggested fix:
The amount of space consumed for this should be capped at some reasonable limit or alternately the space reservation can be disabled.
[22 Jan 2013 17:41] MySQL Verification Team

Your analysis is very correct and to the point. It also makes lot's of sense.

However, it is concluded that this bug has such a low priority that it's fix will be considered in the future.

Thank you for your contribution.
[22 Jan 2013 19:31] Davi Arnaut
Link to fix: https://github.com/twitter/mysql/commit/52c63a6cbe815c7a9882914b89e58f8b8e6b41a1

Apparently, "Contributions can be accepted to Open bugs only".
[22 Jan 2013 20:05] Davi Arnaut
Allow free space reservation percentage to be controlled and/or completely disabled.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: InnoDB-reserves-an-excessive-amount-of-space-in-larg.patch (application/octet-stream, text), 34.41 KiB.

[22 Jan 2013 20:07] Matthew Lord
Thank you, Jeremy and Davi! We really appreciate it.

I've marked this as verified now, and moved it over to the InnoDB devs for further examination.
[2 Feb 2013 12:40] MySQL Verification Team
We also have: http://bugs.mysql.com/bug.php?id=60593