Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.4-1
Description
The current auto_increment behavior in the InnoDB engine is sub-optimal. As it currently functions, the auto_increment value is stored until the server shuts down or resets, and then is rebuilt based on values in the table when it starts up again. Furthermore, in 5.6 this ought to become even worse, because tables can be evicted from the InnoDB data dictionary cache. We may get a too low auto-increment value even without shutdown/restart. When a table is evicted, InnoDB will forget the current auto-increment value, and it will do SELECT MAX(auto_inc_column) next time when the table is accessed.
Attachments
Issue Links
- blocks
-
MDEV-11578 Remove the requirement to have an INDEX on AUTO_INCREMENT column
-
- Open
-
- causes
-
MDEV-20775 InnoDB: Failing assertion: !page_zip || page_zip_validate(page_zip, page, index)
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Epic Colour | ghx-label-3 | |
| Epic Name | Persistent auto increment for InnoDB | |
| Epic Status | To Do [ 10100 ] | |
| Issue Type | Epic [ 5 ] | Task [ 3 ] |
| Fix Version/s | 10.1.0 [ 12200 ] |
| Assignee | Jan Lindström [ jplindst ] |
| Labels | innodb |
| Assignee | Jan Lindström [ jplindst ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Fix Version/s | 10.1.1 [ 16100 ] | |
| Fix Version/s | 10.1.0 [ 12200 ] |
| Workflow | defaullt [ 38821 ] | MariaDB v2 [ 43750 ] |
| Fix Version/s | 10.1.2 [ 15801 ] | |
| Fix Version/s | 10.1 [ 16100 ] |
| Fix Version/s | 10.2.0 [ 14601 ] | |
| Fix Version/s | 10.1.2 [ 15801 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Workflow | MariaDB v2 [ 43750 ] | MariaDB v3 [ 63519 ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Description |
The current auto_increment behavior in the InnoDB engine is sub-optimal. As it currently functions, the auto_increment value is stored until the server shuts down or resets, and then is rebuilt based on values in the table when it starts up again. Furthermore, in 5.6 this ought to become even worse, because tables can be evicted from the InnoDB data dictionary cache. We may get a too low auto-increment value even without shutdown/restart. When a table is evicted, InnoDB will forget the current auto-increment value, and it will do SELECT MAX(auto_inc_column) next time when the table is accessed. Plan (in high level): --- create data dictionary table to store auto_increment column and its current value 1 week --- create functions to automatically create auto_increment object/drop auto increment object when 2 days table is created/dropped --- create a procedure to update persistent auto increment column values at dictionary 2 days --- create functions to read, store, peek and set auto_increment column value persistently 3 days --- create a function to migrate current dictionary to dictionary containing persistent auto increments 2 days --- create a function/tool/script to read current database and create necessary persistent auto_increment fields (this might not be 100% correct on all possible cases) 1 week --- add test cases to test suite + testing 1 week I might be here little bit pessimistic but those are not calendar days, they are work days i.e. total work cost ~5 weeks. Concerns: --- there is some cost to store auto-increment value to persistent storage and this cost will be then per row operation (inserts mostly) |
The current auto_increment behavior in the InnoDB engine is sub-optimal. As it currently functions, the auto_increment value is stored until the server shuts down or resets, and then is rebuilt based on values in the table when it starts up again. Furthermore, in 5.6 this ought to become even worse, because tables can be evicted from the InnoDB data dictionary cache. We may get a too low auto-increment value even without shutdown/restart. When a table is evicted, InnoDB will forget the current auto-increment value, and it will do SELECT MAX(auto_inc_column) next time when the table is accessed. |
| Assignee | Jan Lindström [ jplindst ] | Marko Mäkelä [ marko ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Sprint | 10.2.4-1 [ 121 ] |
| Rank | Ranked lower |
| Sprint | 10.2.4-1 [ 121 ] |
| Rank | Ranked higher |
| Sprint | 10.2.4-1 [ 121 ] |
| Rank | Ranked lower |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Summary | Persistent auto increment for InnoDB | Persistent AUTO_INCREMENT for InnoDB |
| Assignee | Marko Mäkelä [ marko ] | Jan Lindström [ jplindst ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Link | This issue blocks MDEV-11578 [ MDEV-11578 ] |
| Assignee | Jan Lindström [ jplindst ] | Marko Mäkelä [ marko ] |
| Assignee | Marko Mäkelä [ marko ] | Jan Lindström [ jplindst ] |
| Assignee | Jan Lindström [ jplindst ] | Marko Mäkelä [ marko ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Fix Version/s | 10.2.4 [ 22116 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-13094 [ MDEV-13094 ] |
| Link |
This issue is duplicated by |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link | This issue relates to MDEV-22357 [ MDEV-22357 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 63519 ] | MariaDB v4 [ 132311 ] |
| Link |
This issue relates to |