InnoDB Temporary Tables just got faster
By krunal.bauskar on Sep 25, 2013
It all started with a goal to make InnoDB temporary tables more effective. Temporary table semantics are blessed with some important characteristics that can help us simplify lot of operations.
- Temporary tables are not visible across connections
- Temporary tables lifetime is limited to connection lifetime (unless user explicitly drops it).
What does this means in to InnoDB ?
- REDO logging can be avoided for temporary tables and related objects since temporary tables do not survive a shutdown or crash.
- Temporary table definitions can be maintained in-memory without persisting to the disk.
- Locking constraints can be relaxed since only one client can see these tables.
- Change buffering can be avoided since the majority of temporary tables are short-lived.
In order to implement these changes in InnoDB we took a bit different approach:
- We introduced a dedicated tablespace for housing temporary tables. In 5.7, all non-compressed temporary tables (irrespective of innodb-file-per-table setting) are housed in this new temporary tablespace (ibtmp1). Compressed temporary tables continue to get housed in their independent tablespace. This new temporary tablespace is re-created on each server restart and can be pre-extended before starting server. (For more about temporary tablespace check MySQL documentation).
- Dedicated rollback-segments (for housing undo logs) for temporary table objects.
- All related objects including rollback-segments have been moved to this new temporary tablespace. This helps in maintaining locality and importantly eliminating REDO logging associated with all such objects.
What have we achieved ? (Performance Gain w.r.t to 5.6)
- Create/Drop workload: (around 11x performance gain)
create n tables, drop n tables, create+drop n tables, create + drop n tables with significant number of keys.
- Insert/Update/Delete workload: (2-4x performance gain)
- insert workload: insert n entries: sorted, n entries: reverse sorted, n entries: random (total 3n entries)
- delete workload: delete n entries: first initiate insert workload to load table, delete n entries using primary key, delete n entries using secondary index, delete n entries using primary key such that complete table is empty.
- update workload: update n entries: first initiate insert workload to load table, update n entries using primary key, update n entries using secondary index, update all 3n entries 2 times. (no explicit key specified).
All testing done using sql-bench (sql-bench modified to accommodate temporary table use-case).
Summing it up....
The benchmarks above show that InnoDB temporary tables are faster in 5.7 (than in 5.6). We plan to further enhance temporary table performance.