GitLab

Since MariaDB 10.5, innodb_locks_unsafe_for_binlog option is removed.

Thanks to continuous performance improvements of InnoDB on recent MariaDB versions, the overall performance seems to be similar to 10.4 with innodb_locks_unsafe_for_binlog. Still, using READ COMMITTED transaction isolation level is better for performance, if using it is safe, like CMFActivity and possibly so-called data warehouse usage.

So this merge request extends the connection string syntax so that we can specify transaction isolation level per connector.

Also CMFActivity uses READ COMMITTED isolation level even without changing the connection string.

Here is the performance comparison using software/erp5/test/test/benchmarks.py in SlapOS repository.

(below, I use 45448334 as the base ERP5 revision. Deadlock count details below are taken with simple grep and they can be counted twice but the still you can see the trend).

• (ref: MariaDB 10.4)

     30 INSERT INTO category
     72 REPLACE INTO delivery

    606 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
     88 UPDATE query in CMFActivity

• (ref: MariaDB 11.8)

     35 INSERT INTO stock
     65 INSERT INTO `quantity_unit_conversion`
    270 DELETE FROM category
    301 REPLACE INTO delivery
  69387 INSERT INTO category

    202 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
    131 UPDATE query in CMFActivity

• !2151

     39 INSERT INTO stock
     28 INSERT INTO `quantity_unit_conversion`
    260 DELETE FROM category
    313 REPLACE INTO delivery
  73064 INSERT INTO category

    752 SELECT FOR UPDATE query in CMFActivity (outside SQLLock)
     72 UPDATE query in CMFActivity

So simply using READ COMMITTED isolation level in CMFActivity is not better on MariaDB 11.8. We need !2158 as well.

configuration advice to upgrade from MariaDB 10.4.

(see also slapos!1876)

• optimizer_use_condition_selectivity

The optimizer_use_condition_selectivity controls which statistics can be used by the optimizer when looking for the best query execution plan.

1. Use selectivity of predicates as in MariaDB 5.5.
2. Use selectivity of all range predicates supported by indexes.
3. Use selectivity of all range predicates estimated without histogram.
4. Use selectivity of all range predicates estimated with histogram.
5. Additionally use selectivity of certain non-range predicates calculated on record sample. From MariaDB 10.4.1, the default is 4. Until MariaDB 10.4.0, the default is 1.

In our case, we have a good plan with optimizer_use_condition_selectivity=1 but have a bad plan with optimizer_use_condition_selectivity=3 or 4.

• innodb_flush_method

innodb_flush_method is deprecated in MariaDB 11.0. If you want direct I/O bypassing kernel cache, you can use the following configuration.

innodb_data_file_write_through = ON
innodb_log_file_write_through = ON
innodb_data_file_buffering = OFF
innodb_log_file_buffering = OFF

(this MR supercedes !1997 (closed))