[MDEV-5004] Support parallel read transactions on the same snapshot Created: 2013-09-10 Updated: 2021-03-25 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | None |
| Fix Version/s: | None |
| Type: | Task | Priority: | Major |
| Reporter: | Will Bryant | Assignee: | Unassigned |
| Resolution: | Unresolved | Votes: | 7 |
| Labels: | None | ||
| Issue Links: |
|
||||||||||||||||
| Description |
|
It would be very useful to be able to make a connection share the transaction snapshot of another connection that is already open and has started a consistent read transaction. This would not allow them to share the set of modifications (ie. inserts/updates/deletes not yet committed) - just the same query snapshot. It could even be read-only. If the transaction isolation levels are repeatable read, and no changes have been made, it means you can get perfectly consistent queries across the connections. The motivation is that then we can do parallel SQL backup dumps, parallel database resyncs, and parallel reporting queries. I would use all three personally. PostgreSQL 9.3 has introduced this feature using the function pg_export_snapshot to get the revision number and SET TRANSACTION SNAPSHOT <snapshot ID> to use it in another session (personally I think it would be more natural to pass it in the BEGIN/START TRANSACTION statement). They use it for parallel pg_dump which is good, but I would also use it in my apps as it is very hard to make full use of a modern database server with only one client thread and some big client jobs still take hours in a single thread. From what I understand of how InnoDB works, I think this is doable? The engine status shows lines like this: Trx read view will not see trx with id >= 1CE54868, sees < 1CE54864 So we would need to be able to copy those values across to another connection/transaction. Thoughts? |
| Comments |
| Comment by Sergei Golubchik [ 2013-09-10 ] |
|
This is part of the XA standard, but we don't support it yet. The syntax already exists, XA START, but the implementation in the storage engines is missing. Neither InnoDB nor XtraDB support it, as far as I know. |
| Comment by Arjen Lentz [ 2014-08-14 ] |
|
I discussed this with Jeremy Cole some time ago. It would be very beneficial for a number of applications, including (for instance) parallel mysqldump. |
| Comment by Will Bryant [ 2015-03-01 ] |
|
Some relevant work has been done in MySQL 5.7: http://dev.mysql.com/worklog/task/?id=7193 From that WL it seems they intend to support full XA, which is far more powerful than what I was suggesting here (which is only sharing the read snapshot). But either would be great for me! And yeah, indeed the first application in postgresql was for parallel dump (in pg_dump). |
| Comment by Marko Mäkelä [ 2021-03-25 ] |
|
arjen, I do not think that it would be feasible to make multiple threads share the same transaction object inside InnoDB. It would require more frequent use of trx_t::mutex and could cause a performance regression for the basic case when each thread is executing only a single transaction. In InnoDB, cloning any active read view should be straightforward. MDEV-14090 has been filed for a somewhat related bug that currently XA START does not even create a read view; for InnoDB, it will happen lazily when the first index record is about to be accessed. Fixing MDEV-16610 would require an even broader change: creating a read view that corresponds to any time in the recent past, or cloning the read view of another server. A special mechanism would be needed to prevent the purge of transaction history from ‘devouring’ recent read views prematurely. Normally, the purge_sys.view may advance as soon as the oldest active read view in the local server is disposed of. When using distributed read views across multiple servers, we should take other servers into account. Not every transaction would access every server. |