WIP MDEV-32067 InnoDB linear read ahead had better be logical
The linear read-ahead (enabled by nonzero innodb_read_ahead_threshold)
only works if index leaf pages or undo log pages have been allocated
on adjacent page numbers. That is not always the case. We will introduce
a logical read-ahead for accessing leaf-level B-tree pages. It is
enabled by default by setting innodb_read_ahead_threshold>0.
buf_page_get_low(): Do not invoke buf_page_t::set_accessed(),
buf_page_make_young_if_needed(), or buf_read_ahead_linear().
We will invoke them in those callers of buf_page_get_gen() or
buf_page_get() where it makes sense: the access is not
one-time-on-startup and the page and not going to be freed soon.
We will not invoke linear read-ahead in functions that would
essentially allocate or free pages, because allocated pages
should be reinitialized by buf_page_create(), eliminating the
need to read anything from the file system. Likewise, freeing
pages should not involve accessing any sibling pages, except
for freeing singly-linked lists of BLOB pages.
buf_read_ahead_logical(): A new function, currently invoked in
btr_cur_t::search_leaf() and when opening the leftmost leaf page
in btr_cur_t::open_leaf().
We will not invoke buf_read_ahead_logical() in
btr_cur_t::pessimistic_search_leaf() or in a pessimistic operation
of btr_cur_t::open_leaf(), because it is assumed that pessimistic
operations should be preceded by optimistic operations, which should
already have invoked read-ahead.
TODO: In btr_cur_t::search_leaf(), instead of reading ahead up all
leaf pages from the key onwards (up to children[16] pages), try to
limit the read-ahead to the range of interest, by referring to the
end_key that is passed to handler::read_range_first().
MDEV-32049 Deadlock due to log_free_check() in trx_purge_truncate_history()
The function log_free_check() is not supposed to be invoked while
the caller is holding any InnoDB synchronization objects, such as
buffer page latches, tablespace latches, index tree latches, or
in this case, rseg->mutex (rseg->latch in 10.6 or later).
A hang was reported in 10.6 where several threads were waiting for
an rseg->latch that had been exclusively acquired in
trx_purge_truncate_history(), which invoked log_free_check() inside
trx_purge_truncate_rseg_history(). Because the threads that were
waiting for the rseg->latch were holding exclusive latches on some
index pages, log_free_check() was unable to advance the checkpoint
because those index pages could not be written out.
trx_purge_truncate_history(): Invoke log_free_check() before
acquiring the rseg->mutex and invoking trx_purge_free_segment().
trx_purge_free_segment(): Do not invoke log_free_check() in order
to avoid a deadlock.
MDEV-32029 Assertion failures in log_sort_flush_list upon crash recovery
In commit 0d175968d1181a0308ce6caccc2e4fbc972ca6c6 (MDEV-31354)
we only waited that no buf_pool.flush_list writes are in progress.
The buf_flush_page_cleaner() thread could still initiate page writes
from the buf_pool.LRU list while only holding buf_pool.mutex, not
buf_pool.flush_list_mutex. This is something that was changed in
commit a55b951e6082a4ce9a1f2ed5ee176ea7dbbaf1f2 (MDEV-26827).
log_sort_flush_list(): Wait for the buf_flush_page_cleaner() thread to
be completely idle, including LRU flushing.
buf_flush_page_cleaner(): Always broadcast buf_pool.done_flush_list
when becoming idle, so that log_sort_flush_list() will be woken up.
Also, ensure that buf_pool.n_flush_inc() or
buf_pool.flush_list_set_active() has been invoked before any page
writes are initiated.
buf_flush_try_neighbors(): Release buf_pool.mutex here and not in the
callers, to avoid code duplication. Make innodb_flush_neighbors=ON
obey the innodb_io_capacity limit.
MDEV-30986 Slow full index scan for I/O bound case
buf_page_init_for_read(): Test a condition before acquiring a latch,
not while holding it.
buf_read_ahead_linear(): Do not use a memory transaction, because it
could be too large, leading to frequent retries.
Release the hash_lock as early as possible.
MDEV-31303 Key not used when IN clause has both signed and usigned values
Summary:
This patch enables possible index optimization when
the WHERE clause has an IN condition of the form:
signed_or_unsigned_column IN (signed_or_unsigned_constant, signed_or_unsigned_constant [,signed_or_unsigned_constant]*)
when the IN list constants are of different signess, e.g.:
WHERE signed_column IN (signed_constant, unsigned_constant ...)
WHERE unsigned_column IN (signed_constant, unsigned_constant ...)
Details:
In a condition like:
WHERE unsigned_predicant IN (1, LONGLONG_MAX + 1)
comparison handlers for individual (predicant,value) pairs are
calculated as follows:
* unsigned_predicant and 1 produce &type_handler_newdecimal
* unsigned_predicant and (LONGLONG_MAX + 1) produce &type_handler_slonglong
The old code decided that it could not use bisection because
the two pairs had different comparison handlers.
As a result, bisection was not allowed, and, in case of
an indexed integer column predicant the index on the column was not used.
The new code catches special cases like:
signed_predicant IN (signed_constant, unsigned_constant)
unsigned_predicant IN (signed_constant, unsigned_constant)
It enables bisection using in_longlong, which supports a mixture
of predicant and values of different signess.
In case when the predicant is an indexed column this change
automatically enables index range optimization.
Thanks to Vicențiu Ciorbaru for proposing the idea and for preparing MTR tests.
MDEV-32028 InnoDB scrubbing doesn't write zero while freeing the extent
Problem:
========
InnoDB fails to mark the page status as FREED during
freeing of an extent of a segment. This behaviour affects
scrubbing and doesn't write all zeroes in file even though
pages are freed.
Solution:
========
InnoDB should mark the page status as FREED before
reinitialize the extent descriptor entry.
MDEV-31835 Remove unnecesary extra HA_EXTRA_IGNORE_INSERT call
- HA_EXTRA_IGNORE_INSERT call is being called for every inserted row,
and on partitioned tables on every row * every partition.
This leads to slowness during load..data operation
- Under bulk operation, multiple insert statement error handling
will end up emptying the table. This behaviour introduced by the
commit 8ea923f55b7666a359ac2c54f6c10e8609d16846 (MDEV-24818).
This makes the HA_EXTRA_IGNORE_INSERT call redundant. We can
use the same behavior for insert..ignore statement as well.
- Removed the extra call HA_EXTRA_IGNORE_INSERT as the solution
to improve the performance of load command.
