On completion of trx_commit, the purge thread is signaled.
Check for invoking purge (till 10.5) is based on trx_sys.rseg_history_len.
This check was lately improved (in 10.6 with MDEV-25062) to use rseg->history_size.
For read-only workload there is no increase in history size this means on each
commit there is significant overhead to scan all 128 rollback segments to find out
there is no work to do.
It is advisable to skip the purge check completely if the transaction is read-only.
If the system has a parallel running read-write transaction it would cause the purge
thread to invoke whenever it commits ensuring no loss in functionality.
-----------
Patch developed accordingly has shown a 2-3% improvement for the read-only workload.
(will also submit the patch)
There is a trade-off here. Consider the following scenario:
A read-only transaction is created: START TRANSACTION WITH CONSISTENT SNAPSHOT would also explicitly create the read view upfront.
Another connection will run BEGIN; DELETE FROM t; COMMIT;
The read-only transaction is committed.
The decision to wake up purge even when committing a read-only transaction was made in order to ensure that purge will run whenever there is work to do (MDEV-11802).
If we can guarantee that purge will eventually run in such a scenario (say, something would wake up purge within a few seconds) then we can avoid the wake-up when committing read-only transactions.
Marko Mäkelä
added a comment - There is a trade-off here. Consider the following scenario:
A read-only transaction is created: START TRANSACTION WITH CONSISTENT SNAPSHOT would also explicitly create the read view upfront.
Another connection will run BEGIN; DELETE FROM t; COMMIT;
The read-only transaction is committed.
The decision to wake up purge even when committing a read-only transaction was made in order to ensure that purge will run whenever there is work to do ( MDEV-11802 ).
If we can guarantee that purge will eventually run in such a scenario (say, something would wake up purge within a few seconds) then we can avoid the wake-up when committing read-only transactions.
Before MDEV-25062 replaced the atomic variable trx_sys.rseg_history_len with 128 regular variables, reading the count was faster.
I prepared a fix that would avoid waking up purge if the transaction did not generate any undo log for purge. To ensure that purge will eventually be woken up (if the last transaction commits advanced the purge view but did not wake up purge due to the change), we can make srv_master_callback wake up purge approximately once per second.
Marko Mäkelä
added a comment - Before MDEV-25062 replaced the atomic variable trx_sys.rseg_history_len with 128 regular variables, reading the count was faster.
I prepared a fix that would avoid waking up purge if the transaction did not generate any undo log for purge. To ensure that purge will eventually be woken up (if the last transaction commits advanced the purge view but did not wake up purge due to the change), we can make srv_master_callback wake up purge approximately once per second.
krunalbauskar suggested that if we periodically wake up the purge subsystem anyway, it will not be necessary to wake up purge in trx_t::commit_cleanup() for any type of transaction. Common sense would suggest that not invoking trx_sys_t::history_exists() on any transaction commit should greatly improve performance on NUMA systems.
Marko Mäkelä
added a comment - krunalbauskar suggested that if we periodically wake up the purge subsystem anyway, it will not be necessary to wake up purge in trx_t::commit_cleanup() for any type of transaction. Common sense would suggest that not invoking trx_sys_t::history_exists() on any transaction commit should greatly improve performance on NUMA systems.
I have uploaded the original patch that I have tried just for reference.
As discussed with Marko I have tried the evaluate compress removal of purge invocation from the commit_cleanup but leaving the purge hooks that Marko added to srv_master_callback. This approach helps maintain the performance gain in the range of 2% as observed before. For read-write, the gain is limited but observed in the range of 1-2% (as expected).
Tried patch over and above branch bb-10.6-MDEV-26193
Krunal Bauskar
added a comment - - edited
I have uploaded the original patch that I have tried just for reference.
As discussed with Marko I have tried the evaluate compress removal of purge invocation from the commit_cleanup but leaving the purge hooks that Marko added to srv_master_callback. This approach helps maintain the performance gain in the range of 2% as observed before. For read-write, the gain is limited but observed in the range of 1-2% (as expected).
Tried patch over and above branch bb-10.6- MDEV-26193
diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc
index 6f2f92d8c0b..3763ed67c98 100644
--- a/storage/innobase/trx/trx0trx.cc
+++ b/storage/innobase/trx/trx0trx.cc
@@ -1388,13 +1388,10 @@ void trx_t::commit_cleanup()
mod_tables.clear();
assert_freed();
- const auto to_purge= undo_no;
trx_init( this );
mutex.wr_unlock();
ut_a(error_state == DB_SUCCESS);
- if (to_purge && !srv_read_only_mode)
- srv_wake_purge_thread_if_not_active();
}
/** Commit the transaction in a mini-transaction.
Bug
Major
There is a trade-off here. Consider the following scenario:
The decision to wake up purge even when committing a read-only transaction was made in order to ensure that purge will run whenever there is work to do (
MDEV-11802).If we can guarantee that purge will eventually run in such a scenario (say, something would wake up purge within a few seconds) then we can avoid the wake-up when committing read-only transactions.