[MDEV-28540] Deprecate and ignore the parameter innodb_prefix_index_cluster_optimization Created: 2022-05-11  Updated: 2022-06-07  Resolved: 2022-06-06

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 10.10.0

Type: Task Priority: Major
Reporter: Marko Mäkelä Assignee: Marko Mäkelä
Resolution: Fixed Votes: 1
Labels: performance

Issue Links:
Relates
relates to MDEV-6929 Port Facebook Prefix Index Queries Op... Closed
relates to MDEV-21212 buf_page_get_gen -> buf_pool->stat.n_... Closed

 Description   

The parameter innodb_prefix_index_cluster_optimization enables an optimization that was added in MDEV-6929. Should the optimization turn out to be incorrect and broken beyond repair in our stress testing, it will have to be reverted. If it is correct, there should be no point to disable it by default.

Related to this, the counters Innodb_secondary_index_triggered_cluster_reads and Innodb_secondary_index_triggered_cluster_reads_avoided are determining the usefulness of this optimization. If we hard-wire the optimization to enabled or disabled, these counters should not serve any useful purpose. As noted in MDEV-21212, frequently updating global counters is not for free.



 Comments   
Comment by Marko Mäkelä [ 2022-05-13 ]

A section of the test innodb.monitor revealed that this feature is actually buggy:

create table monitor_test(a char(3), b int, c char(2),
primary key (a(1), c(1)), key(b)) engine = innodb;
insert into monitor_test values("13", 2, "aa");
select a from monitor_test where b < 3;

This would incorrectly return the prefix '1' of the string '13'. The fix is simple:

diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
index 01cf3b107a8..8afabf6220c 100644
--- a/storage/innobase/row/row0sel.cc
+++ b/storage/innobase/row/row0sel.cc
@@ -4202,7 +4202,9 @@ bool row_search_with_covering_prefix(
 	const dict_index_t*	index = prebuilt->index;
 	ut_ad(!dict_index_is_clust(index));
 
-	if (dict_index_is_spatial(index)) {
+	/* In ha_innobase::build_template() we choose to access the
+	whole row when using exclusive row locks. */
+	if (prebuilt->select_lock_type == LOCK_X || !index->is_btree()) {
 		return false;
 	}
 

Comment by Marko Mäkelä [ 2022-05-14 ]

I conducted a quick performance test on NVMe, using Sysbench oltp_update_index, 30-second runs, 8×100,000 rows, 8GiB buffer pool:

revision 20 40 80 160
experiment: disable some non-InnoDB counters 42572.27 84046.06 148651.85 158132.39
MDEV-28540 remove 2 counters 42612.63 87023.75 149086.85 159434.07
MDEV-28539 remove several counters 44296.74 85968.64 148615.14 145086.68
baseline 43836.00 86088.89 149828.29 153944.74

This test is too short to draw any conclusions, but it does look somewhat promising.

Generated at Thu Feb 08 10:01:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.