WL#6658: Implement update_time for InnoDB tables
Status: Complete — Priority: High
The column INFORMATION_SCHEMA.TABLES.update_time is always NULL for InnoDB tables. It is supposed to contain the timestamps of last update (or insert or delete). This functionality has been missing in InnoDB so far and people have been asking for it, see BUG#2681 ABILITY TO DETERMINE WHEN DB WAS LAST MODIFIED (GENERIC METHOD). Last update time may be a bit fuzzy when MVCC is considered: (N., wall clock, SQL command) 1. 13:15 BEGIN; 2. 13:20 UPDATE t1 SET c1=1; (a transaction in InnoDB is started) 3. 13:25 UPDATE t2 SET c2=2; 4. 13:30 COMMIT; We consider that table t2 was last updated at 13:30, by the commit. This WL will deal with the in-memory maintenance of update_time and will have the limitation that the values will be lost when the server is restarted or the table evicted from the InnoDB data dictionary cache. We would like to make update_time persistent in follow up work, timeline not decided.
Overview of the changes needed to keep an in-memory timestamps: * Add a timestamp member to dict_table_t: update_time * update_time: During commit, see which tables were modified by the transaction and update their timestamp members (dict_table_t::update_time). * In ha_innobase::info(), copy the timestamp to the mysql-visible struct. * In ha_partition::info() aggregate the values and ship the biggest numbers to the upper layer. If calling time(3) during each commit turns out to be too expensive, we may use the timestamp which is already stored in trx->start_time, but then we will provide the timestamp when the transaction which modified the table started, 13:20 in the above example in "High-Level Description".
A list of tables which were modified by a transaction can be retrieved from the undo log, something amongst the lines of: trx_undo_rec_t* r = trx_undo_get_first_rec( trx->insert_info->space, trx->insert_info->zip_size, trx->insert_info->hdr_page_no, trx->insert_info->hdr_offset, RW_S_LATCH, mtr); trx_undo_rec_get_pars(r, ..., &table_id); But that would be too slow. A more effective way is to store the list of modified tables as the undo is created. Then traversing this list will be quick during transaction commit time. Add a "list of modified tables" member to each trx_t object. That list would better not contain duplicate entries so we can use std::set for that purpose. During trx_undo_report_row_operation() insert the table into the "list of modified tables" by the current transaction (std::set::insert()). trx_commit() is also called during rollback (!), so we choose to plant the update of table->update_time in trx_commit_for_mysql(). There we traverse the list of modified tables and update each one's dict_table_t::update_time member. If a table cannot be evicted from the BP while a trx is running on it, then we can store pointers to dict_table_t object in the list (std::set<dict_table_t*>). If the table object may be destroyed, then we need to store just dict_table_t::id in the list (std::set<table_id_t>) and during commit, try to get the table if it is in BP.
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.