| 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 | ||
[22 Jan 2013 17:41]
MySQL Verification Team
Jeremy, 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
Description: 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! */ 3027 3028 reserve = 2 + ((size / FSP_EXTENT_SIZE) * 2) / 200; 3029 3030 if (n_free <= reserve + n_ext) { 3031 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 */ 3036 3037 reserve = 1 + ((size / FSP_EXTENT_SIZE) * 1) / 200; 3038 3039 if (n_free <= reserve + n_ext) { 3040 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.