[MDEV-22343] Remove SYS_TABLESPACES and SYS_DATAFILES Created: 2020-04-22 Updated: 2023-12-21 Resolved: 2020-11-11 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Storage Engine - InnoDB |
| Fix Version/s: | 10.6.0 |
| Type: | Task | Priority: | Major |
| Reporter: | Marko Mäkelä | Assignee: | Marko Mäkelä |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | performance | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
The InnoDB internal tables SYS_TABLESPACES and SYS_DATAFILES as well as the INFORMATION_SCHEMA views INNODB_SYS_TABLESPACES and INNODB_SYS_DATAFILES were introduced in MySQL 5.6 for no good reason when the InnoDB support for the DATA DIRECTORY table attribute was introduced. The file system should be the authoritative source of information on data files. Storing information about file system paths in the file system (symlinks, or even the .isl files that were unfortunately chosen as the solution) is sufficient. If information is additionally stored in some hidden tables inside the InnoDB system tablespace, everything unnecessarily becomes more complicated, because more copies of data mean more opportunity for the copies to be out of sync, and because modifying the data in the system tablespace in the desired way might not be possible at all without modifying the InnoDB source code. So, the copy in the system tablespace basically is a redundant, non-authoritative source of information. For compatibility with older versions, we might want to preserve the INFORMATION_SCHEMA views, but make them reflect the fil_system cache. |
| Comments |
| Comment by Marko Mäkelä [ 2020-04-22 ] |
|
Removing the code to create or access the unnecessary data dictionary tables will also remove some dependencies on the InnoDB internal SQL parser, which would be removed in MDEV-11655. |
| Comment by Marko Mäkelä [ 2020-11-11 ] |
|
We will stop creating or accessing the system tables SYS_TABLESPACES and SYS_DATAFILES. We will also remove the view INFORMATION_SCHEMA.INNODB_SYS_DATAFILES. The view INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES will be repurposed to directly reflect fil_system.space_list. The column PAGE_SIZE, which would always contain the value of the GLOBAL read-only variable innodb_page_size, is removed. The column ZIP_PAGE_SIZE, which would actually contain the physical page size of a page, is renamed to PAGE_SIZE. Finally, a new column FILENAME is added, as a replacement of SYS_DATAFILES.PATH. |