Streaming, Faster Exporting and Instant Importing of Tables

The doublewrite buffer depends on the space_id for identifying pages. Thus, we cannot make the space_id in data files entirely “don’t care”. It seems that unless MDEV-11659 adds some extra metadata to the doublewrite buffer, before flushing we should write the current space_id to the files. For example, if a file with space_id=15 gets imported into an InnoDB instance as space_id=10, all pages would initially carry the space_id 10 in each page header at byte offset 34. Every time a page is flushed to the data file, the new space_id 15 would be written. The true space_id would always be present in the tablespace header (page 0).

Being able to quickly import InnoDB tables would be a great feature as this could enable almost instant cloning of whole databases on CoW filesystems

In MDEV-11657 I suggested the idea of splitting the InnoDB undo log into tablename.ibu files. If we go down that route, EXPORT would merely have to merge the change buffer and prevent any purge activity while the .ibd and .ibu files are copied. Then, IMPORT could apply the undo logs. It would be possible to EXPORT while active transactions exist on the table. Those transactions would be rolled back by the IMPORT.

If we can fix MDEV-13603, then thanks to MDEV-12288 we should always have DB_TRX_ID=0 when importing tables. This would mean that we should only have problems with FIL_PAGE_SPACE_ID or FIL_PAGE_LSN when importing. As far as I understand, the PAGE_MAX_TRX_ID in secondary index leaf pages only matters for performance. If we have some PAGE_MAX_TRX_ID in the future, then when accessing records in that page, we will always have to look up the corresponding record in the primary key index.

The FIL_PAGE_SPACE_ID we could fix by removing most reads of that field from our code, and passing fil_space_t* in all calls and data structures. Then, we would have the "real" tablespace ID only in the first page, at byte offset 38 (right after the FIL_SPACE_PAGE_ID). The FIL_PAGE_LSN we can avoid adjusting if we somewhere keep track of the maximum LSN of the file. When importing, we would only let log_sys->lsn jump forward to this number.

After MDEV-12266 removed many fil_space_t lookups, it should be feasible to remove references to the tablespace ID on the file pages.

In the exporting instance, it is not strictly necessary to keep track of the maximum LSN and transaction ID of each file. We can simply record the maximum LSN and TRX_ID of the exporting server, and on import, ensure that the importing server is not behind those numbers.

Interesting feature. The alter table import tablespace also rewrites Index Ids at least in mysql 5.7. IndexIDs can also be different between the importing and exporting server.

laxmanprabhu, you are right, the PAGE_INDEX_ID in each index page header would be adjusted as well. But, I think that we could relax the checks around those somehow. One idea would be that we would require each page of an index to contain the same PAGE_INDEX_ID as the root page does. It would not necessarily have to match the dict_index_t::id. As far as I can tell, the main use of the identifier is the adaptive hash index, which I would remove in MDEV-20487.

In MDEV-4259 I wrote down an idea how to store the index metadata inside the .ibd files, to eventually allow the InnoDB data dictionary tables to be removed. The directory inside the .ibd files would store the root page numbers for each secondary index. (For the clustered index, it is always page number 3.)

With all this in place, when creating an index, we could just assign a random PAGE_INDEX_ID value, to allow us to catch corruption where an index page is pointing to a page in a different index.

marko,

With the changes that you're envisioning, would the .cfg file be completely unnecessary, or would it still be needed for some reason? (i.e. see MDEV-20974)

GeoffMontee, yes, the .cfg files would no longer be read or written. Currently, the .cfg files store information about secondary index root page numbers, plus some InnoDB data dictionary information that is derived from the original .frm file.

Some existing code tries to guess the root page numbers when the .cfg file is not available. Since the aim of this task is instantaneous operation, we can only read at most a small number of pages of the data file, and hence we should store the information somewhere in the .ibd file.

MDEV-15225 would allow the original .frm file to be used when importing tables, thus making most of the .cfg file redundant. Until we extend the .ibd file format to store information about the secondary index root page numbers (which I would do as part of this task), the .cfg files would still be needed.

MDEV-20974 was closed as a duplicate of this task, since this task will remove the need for .cfg files.

Making export wait for purge would also fix MDEV-25265.

While testing MDEV-18543, mleich got the server to crash during a rename operation in the following scenario that involves any ALTER TABLE that uses ALGORITHM=COPY:

1. ALTER TABLE…ALGORITHM=COPY is almost finished, and renames the old copy of the table to a different name, for the purpose of dropping it.
2. The test environment copies a file by that name into the data directory.
3. ALTER TABLE…ALGORITHM=COPY attempts to rename the intermediate copy of the table to the final name, but it finds that a file by that name meanwhile exists.
4. (ALTER TABLE…ALGORITHM=COPY would have dropped the original table that had been renamed.)

To prevent this scenario, we could introduce a separate PREPARE IMPORT step that would acquire an exclusive metadata lock on the table name and ensure that no table by that name exists. So, the workflow would be as follows:

1. PREPARE IMPORT tablename;
2. copy the tablename.* files into the data directory
3. IMPORT tablename;

After MDEV-27735 removes the code that writes change buffer records, the change buffer bitmap pages will be unused. I think that we can extend the MDEV-19534 innodb_checksum_algorithm=full_crc32 format as follows:

1. A bit in FSP_SPACE_FLAGS would indicate the new format.
2. FIL_PAGE_SPACE_ID in each page header and BTR_EXTERN_SPACE_ID in each BLOB pointer will be ignored, or they may be validated against the contents of that field in page 0.
3. Only FSP_SPACE_ID in page 0 will contain the tablespace identifier. This field would be updated when importing a tablespace.
4. The change buffer bitmap pages (pages 1+n*innodb_page_size) will be initialized with NUL bytes.
5. In page 1 we would store a transactional (undo-logged) ROW_FORMAT=REDUNDANT record, comprising:
   • the root page numbers of all secondary indexes (could be a VARBINARY column, with a length that is a multiple of 4 bytes)
   • server ID, table dictionary version (see MDEV-4259)
   • possibly, something that identifies the MariaDB Server version that last modified this record

We should also store the maximum FIL_PAGE_LSN and transaction ID somewhere, along with the purge status. These fields would be cleared by the special slow export step. If we do not know the maximum LSN or transaction ID, we will still be able to import the tablespace without writing much to it; we’d merely have to read many pages.

This should make the .cfg files redundant; we will only need .frm and .ibd files for exporting and importing tablespaces.

MDEV-26137 has been filed for an SQL level change that would allow InnoDB tables to be implicitly imported or discovered. One would just issue a statement like SELECT * FROM tablename or CHECK TABLE tablename after copying some files to the data directory:

• tablename.frm (the table definition, with the correct timestamp format (MDEV-15225) and whatnot)
• tablename.ibd (the InnoDB data copied between FLUSH TABLES tablename FOR EXPORT and UNLOCK TABLES
• tablename.cfg (additional InnoDB metadata generated by FLUSH TABLES tablename FOR EXPORT)

The scope of this ticket would be a change of the .ibd file format so that:

• no .cfg file will be needed (the index root page numbers will be explicitly stored in the .ibd file), and
• the file can be imported instantly, without rewriting all pages.

I propose that any other changes, such as introducing a "streaming" variant of EXPORT or IMPORT that would transfer the contents of .frm and .ibd files over a socket connection, would be outside the scope of this ticket.

Will this mechanism apply to a PARTITIONed table? And/or a single PARTITION?

With MDEV-26137 merged, do we think that this enhancement might make 11.3 now? This is a very exciting feature for lots of use cases for us.

And one question, would this feature only work for NEW tables created with a version of MariaDB with this feature, since it's a format change to innodb? In other words, for tables created prior to this enhancement, you'd still need a .cfg file? That's still great, just thinking through how to use this properly.

rjasdfiii, the design of partitioned tables is somewhat complicated design, and it could become even more so after MDEV-22168 is implemented.

bjquinn, it should be possible to import tables without a .cfg file already now. Under some circumstances, secondary indexes in the imported file would be ignored. The clustered index root page number in .ibd files is always 3 in MariaDB or MySQL up to 5.7.

The required .ibd file format changes are somewhat challenging to implement, even though MDEV-29694 freed up some space by removing the change buffer and thus making the change buffer bitmap pages (page number 1+n*innodb_page_size) completely unused.

I think that the changes of dictionary information (secondary index root page numbers and index identifiers) will need to be covered with an undo log record. A thinkable solution would be a pseudo ‘table’ starting at page 1, complete with DB_TRX_ID, DB_ROLL_PTR to allow rollback.

marko, thank you. What I had in mind was using this in conjunction with MDEV-26137 to simply rsync an .ibd + .frm file in alone, w/o a .cfg file, and just expect them to show up. If I'm reading MDEV-26137 right, the way to get the table to show up is to try to open it, is there any other way to force a directory scan to pick up all new tables?

Anyway, the limitations I understand right now on just copying in .ibd/.frm's are the following. Please clarify if I'm missing something:

• Need 11.2.1 RC, which is not yet released
• Source table needs to be created in 11.2.1 or later? Or at least needs to have at one point been running on an 11.2.1 server, or perhaps needed an OPTIMIZE TABLE while running on 11.2.1+ to get the latest table format?
• Need the .ibd and .frm, but not .cfg. Just rsync over the .ibd and .frm, no need to FLUSH TABLE ... FOR EXPORT on the source server (though obviously preferred if possible, otherwise it's like you're getting a copy of the table with READ UNCOMMITTED), also no need to ALTER TABLE DISCARD/IMPORT TABLESPACE.
• Partitioned table will not work
• Will lose all indexes other than the primary key under certain circumstances (which indexes, which circumstances?)
• Is there a way to get a .cfg file w/o FLUSH TABLE ... FOR EXPORT?
• If any of the required criteria is not met, you will still not need to DISCARD/IMPORT tablespace, but you will need the .cfg (i.e. FLUSH TABLE ... FOR EXPORT was run and you still have the .cfg file from that)

bjquinn, at some point in MDEV-26137 there was a thought that user-copied data files would be imported automatically, just like they will be for the MyISAM storage engine. For now, that plan was abandoned, and a statement like ALTER TABLE t2 IMPORT TABLESPACE will be necessary. Unlike MyISAM, InnoDB still needs to update its internal data dictionary tables to be able to use the table in transactions.

The last substantial changes to InnoDB data file formats were made in MariaDB Server 10.4, by MDEV-12026 and MDEV-15562. MDEV-29694 removed the InnoDB change buffer in MariaDB Server 11.0, but that did not really change the .ibd file format or affect importing tablespaces. MariaDB Server 11.2 should be able to import any old data files (from MySQL up to 5.7 or any MariaDB version).

The "instant IMPORT" that I want to do in this ticket would require a file format change. Importing older-format files would still involve reading and rewriting each page in the .ibd file.

Yeah, I had followed a few of these tickets and thought I'd seen some stuff that I couldn't find anymore, maybe that's because MDEV-26137 changed.

So if I understand correctly, MDEV-26137 allows you to just copy the .ibd/.frm/.cfg in place and do an ALTER TABLE t2 IMPORT TABLESPACE, even though t2 doesn't exist in the internal InnoDB data dictionary? So no need for CREATE TABLE. But SELECT * FROM or CHECK TABLE would not be sufficient alone (as was initially planned).

Is there a ticket I should follow for the auto-import feature dropped from MDEV-26137?

What are the current limitations around importing a table without a .cfg file? Does this ticket still intend to resolve those situations?

Thanks!

Hey @marko just seeing if you had an update on my questions above. Just trying to see if I can properly understand the implications of this feature and what the limitations are, including what the limitations are in 11.2 before this ticket, MDEV-11658, is resolved. Thanks!

We've been on MariaDB 10.5 or earlier at multiple sites for a while now, and we were looking into upgrading to later versions, in particular to start benefiting from these improvements in copying around InnoDB data. One of our major use cases is a large development database that needs to be refreshed frequently and quickly either from the production data or from zfs snapshots taken over time of historical data. Besides the fact that we don't always want current data, the data diverges at this point while doing development. We have multiple dev databases on the same server. Anyway, all of these tickets like this one about streamlining this process are very interesting to us. The holy grail would be the original intent of this ticket (instant imports by just rsync'ing an .ibd and .frm file into a database folder), though anything that streamlines the current process would be welcomed. However, we started testing latest 11.x, and noticed that we've actually regressed in functionality because now since 10.5.13, I believe, it refuses to import tablespace for tables with secondary indexes. That actually worked fine for us before but this new restriction is a regression for us. Any thoughts?

I think that an auto-import on DML, without involving any DDL statement, would be problematic for locking and logging.

The fix of the crashing bug MDEV-26131 was to refuse to import secondary indexes if a .cfg file is missing. The .cfg file would be created by FLUSH TABLES…FOR EXPORT.

Yes, but for our use case, creating a .cfg file is impractical. We can't lock the tables on the entire production system in order to make a copy of it or to snapshot it regularly in order to have selective copies we can make later on by time/date. This mechanism worked perfectly well for us until the fix in question broke our process.

This is also why I was asking previously about the elimination of the .cfg requirement, which was mentioned as one of the outcomes of the resolution of this particular ticket.

I can live without auto import, but I can't require locking tables in order to make the copies I need, that's actually a regression from our existing process.

@BJQuinn - Instead of speeding up dump/srestore,... Have you tried LVM? Put the database in a disk subsystem, then "snapshot" it whenever you want to capture it. The snapshot is virtually instantaneous. You could either keep multiple copies on the same server or leisurely rsync them to some other server (and destroy the snapshot). Since LVM employs "copy-on-write", each snapshot grows only as the two copies diverge.

I'm not using dump/restore, I'm copying the raw .ibd/.frm files (w/o .cfg files since I can't afford to lock the production database regularly), regularly creating a .sql file of the structure of all the tables, then on demand, running a script to create a new database, create all the empty tables from their definitions, then discarding tablespaces on all tables in the database, copy .ibd/.frm files in from either the production database or a snapshot via zfs, and then importing the tablespace for all the tables. This is orders of magnitude faster than dump/restore. This still works on < 10.5.13, and I guess would still work on >= 10.5.13 as long as you only had a primary index and no others. In 10.5.13 there was a limitation added that now prevents tables with secondary indexes from being imported this way, unless you got the .cfg file using flush table for export, which locks the table.

This JIRA issue at one point intended to eliminate .cfg files altogether, which I would assume would mean a .cfg file could not be required to import the tablespace for tables. Alternatively, I could accept an option where I could get a .cfg or equivalent w/o locking the source table.

@BJQuinn – LVM should be faster and less invasive that that. But it does snapshot the entire filesystem. So it is better to have that entire database isolated on its own mount. COW is the secret to its speed – and that depends on whether the OS and filesystem support COW.

Thanks for offering some ideas! For various other purposes, I prefer zfs over LVM. But the problem isn't how to get the snapshotted data, I can get that. It's how to import it into a different database on the same mariadb instance. If I snapshot the entire volume (which is what I'm doing with zfs currently as well), then I either have to selectively copy files off the volume to a new database in a running server, at which point I run into the problem I'm describing. Or I go ahead and take the entire volume, or the entire /var/lib/mysql folder at least, ibdata file and all, and copy it over. But then that wouldn't allow me to load data into an existing instance of mariadb, it would require wiping out the entire server.

@BJQuinn - Sorry, I forgot to mention that it would have to be a separate instance of MariaDB, optionally even a different version (if not too different).

Thanks, yes, what I'm trying to do is create on demand, quick clones of databases on the same database instance. This worked pretty well before 10.5.13, and this JIRA issue seemed to be poised to make it work even better, at least at one point.

Any updates on workarounds for this? This is getting to be more and more difficult to work with as we upgrade more servers past 10.5.13. I would be happy with any of the following solutions:

• an option that doesn't require .cfg files for tables with secondary indexes
• an override option for the "fix" in MDEV-26131
• a way to get a .cfg file that doesn't require locking the table in question
• any other ideas anyone has

We have some databases that are many terabytes. mysqldump and similar aren't workable for that size. We often need just certain tables to be copied, or certain databases, and we often need them copied from one database to another on the same server. There's really no alternative to copying .ibd files around at the filesystem level for this use case, and I can't lock these tables to get a .cfg file the standard way. This all worked before 10.5.13 and was not undocumented behavior. I gained interest in this ticket and MDEV-11658 which held promise to streamlining this purpose even further, but I'm now in a situation where my existing process doesn't work at all.

Thanks!

Sorry, there has been no update on this. Designing and implementing a file format change is challenging. When it comes to partitioned tables, MDEV-10568 is an omission compared to MySQL 5.7.

The only alternative that we currently have is a faster load of SQL dumps, which was implemented in MDEV-515 and MDEV-24621. MDEV-16281 could do more in that area.

Understood, thank you for the feedback!

In the meantime, could we get a way to override MDEV-26131? I understand getting a new improved way to do this is a lot of work. But optionally providing a way to override MDEV-26131 should be simpler, and should also be considered a feature regression. I know there were some reasons to implement MDEV-26131, but we have copied countless tables with secondary indexes using this mechanism and never ran into the issue that caused that fix to be put in place, so it's pure regression for us.

I'd expect that internally InnoDB wouldn't need a table lock to create a .cfg file. But a lock might be needed so that you wouldn't copy a table which is being actively modified at the same moment.

Also, perhaps, it might be possible to recreate secondary indexes without a cfg file — that is, InnoDB cannot use them, but perhaps it can drop and create them again.

Generally we're pulling .ibd files from filesystem snapshots. So: the file is frozen in time and not actively changing during the copy, but obviously a frozen in time copy might have stuff that's not flushed to disk, etc. But that's fine for our purposes.

If we could get a .cfg file somehow without the lock, that would at least get us back to where we were. We would still be very excited for further improvements in JIRA tickets like this, but at least we'd be back to the functionality we had before.

I'm also now running into the issue that the ".cfg file or no indexes" rule from MDEV-26131 doesn't play nicely with MDEV-26137 since all I have is an .ibd file, I can't drop the indexes it wants me to drop, so I can't import the file at all.

Is there any way we can get an optional workaround for MDEV-26131? I'm running into this problem constantly now.

Would the following be an acceptable solution?

1. CREATE TABLE without any indexes.
2. ALTER TABLE…DISCARD TABLESPACE;
3. Copy the .ibd file to the data directory.
4. ALTER TABLE…IMPORT TABLESPACE;
5. ALTER TABLE…FORCE, ADD UNIQUE INDEX(u), ADD INDEX(h), ADD INDEX(j);

The last step should copy the data of the old file and create the secondary indexes. If you do not need the indexes, you could omit the last step, but then the space that is occupied by the indexes would not be reclaimed.

Thanks for the suggestion! I have in fact tried that successfully in some cases. The problem lies with larger tables where re-adding the indexes becomes very time consuming, on the order of hours or longer. One of our primary goals for shuttling around .ibd files like this is to make fast copies of large datasets. Rewriting all their indexes takes the "fast" part out of it.