Page Content

1 Availability

Any issue preventing further queries from running without admin intervention, on an otherwise well-dimensionned machine (enough disk & ram space, query workload not justifying a service loss).

1.1 Deadlock on MyISAM FullText

MariaDB version: 10.0.10-MariaDB-log

On a busy server occasionally exectuing fulltext SELECTs on MyISAM tables, there seems to be threshold effect when several DELETE/INSERT run in parallel, possibly in parallel with multiple SELECTs statements. Once the deadlock happens, there seems to be no automatic detection & recovery (at least, not within several minutes, which is already too much).

For reference, full_text table creation query is:

CREATE TABLE `full_text` (
  `uid` bigint(20) unsigned NOT NULL,
  `SearchableText` mediumtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`uid`),
  FULLTEXT KEY `SearchableText` (`SearchableText`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

1.1.1 First occurrence:

Several queries similar to row 10 below were in the same state.

*************************** 2. row ***************************
   id: 468934
state: Waiting for table level lock
 time: 249.3546780
 info: DELETE FROM full_text WHERE uid IN (195178889, 195178888)
[...]
*************************** 10. row ***************************
   id: 478384
state: FULLTEXT initialization
 time: 342.4968090
 info: SELECT DISTINCT
    catalog.path, catalog.uid, MATCH (`full_text`.`SearchableText`) AGAINST ('26/05/2016') AS `full_text_SearchableText__score__`
  FROM
    catalog AS `catalog` INNER JOIN full_text AS `full_text` ON `full_text`.`uid` = `catalog`.`uid`
  WHERE
    (
      `catalog`.`security_uid` IN (89529, 89776, 175948, 175951, 175960, [...])
      OR `catalog`.`viewable_owner` = '[redacted]'
      OR `catalog`.`viewable_associate` = '[redacted]'
    )
    AND `catalog`.`modification_date` >= "2016-05-25 22:00:00"
    AND `catalog`.`modification_date` < "2016-05-26 22:00:00"
    AND MATCH (`full_text`.`SearchableText`) AGAINST ('26/05/2016')
  LIMIT 1000

1.1.2 Second occurrence:

Several queries similar to row 44 below were in the same state.

*************************** 11. row ***************************
   id: 1539534
state: Waiting for table level lock
 time: 45.5764390
 info: REPLACE INTO full_text VALUES
(103025907, 'Abonnement mensuel avec facture électronique'),
(102496966, 'Abonnement mensuel avec facture électronique'),
(102856534, 'Abonnement mensuel avec facture électronique'),
(102242976, 'Forfait mensuel en cas de solde négatif'),
(103000028, 'Forfait mensuel en cas de solde négatif'),
(101925675, 'Abonnement mensuel avec facture électronique'),
(102591887, 'Forfait mensuel en cas de solde négatif'),
(101953931, 'Recharge 150.00 €'),
(102304295, 'Abonnement mensuel avec facture électronique')
[...]
*************************** 31. row ***************************
   id: 1539563
state: Waiting for table level lock
 time: 470.1234240
 info: DELETE FROM full_text WHERE uid IN (195931873, 195931872, 195931871, 195934422, 195934421, 195931877, 195931876, 195931875)
[...]
*************************** 44. row ***************************
   id: 1545561
state: FULLTEXT initialization
 time: 405.8365920
 info: SELECT DISTINCT
    catalog.path, catalog.uid, MATCH (`full_text`.`SearchableText`) AGAINST ('Confirmation d\'abonnement [redacted]') AS `full_text_SearchableText__score__`
  FROM
    catalog AS `catalog` INNER JOIN full_text AS `full_text` ON `full_text`.`uid` = `catalog`.`uid`
  WHERE
    MATCH (`full_text`.`SearchableText`) AGAINST ('Confirmation d\'abonnement [redacted]')
    AND (
      `catalog`.`security_uid` IN (89529, 89776, 175948, 175951, [...])
      OR `catalog`.`viewable_owner` = '[redacted]'
      OR `catalog`.`viewable_associate` = '[redacted]'
    )
  LIMIT 1000

1.2 MyISAM fulltext and Mroonga fulltext in single query cause crash

MariaDB version:
Mroonga version:

2 Consistency

Any issue where MariaDB does not provide enough consistency guarantees

2.1 innodb_locks_unsafe_for_binlogs causes unpredictable transaction isolation

In ERP5, enabling innodb_locks_unsafe_for_binlogs option makes a lot of difference, especially IIRC for DELETE queries (indexation of several tables involve a DELETE query before INSERTing). But that options is deprecated in MySQL (and a s a consequence in MariaDB) because it degrades transaction isolation in some cases, but how it degrades is racy. ERP5 expects REPEATABLE-READ isolation (and does configure MariaDB in that mode).

First, some initial setup of the two involved SQL command lines, and test table:

1> CREATE TABLE `foo` (
   `uid` int(10) unsigned NOT NULL DEFAULT '0',
   `value` int(10) unsigned DEFAULT NULL,
   PRIMARY KEY (`uid`),
   KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.08 sec)
1> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

  2> set autocommit=0;
  Query OK, 0 rows affected (0.00 sec)

Example giving the expected result, demonstrating that simply lowering transactional isolation level would loose some functionality:

1> select * from foo;
Empty set (0.00 sec)

  2> select * from foo;
  Empty set (0.00 sec)
  2> insert into foo values (1, 1);
  Query OK, 1 row affected (0.00 sec)
  2> commit;
  Query OK, 0 rows affected (0.00 sec)

1> select * from foo;
Empty set (0.00 sec)

But changing the transaction start boundary (by reordering selects) gives an incorrect result:

2> select * from foo;    
  Empty set (0.00 sec)

1> select * from foo;    
Empty set (0.00 sec)

  2> insert into foo values (1, 1);    
  Query OK, 1 row affected (0.00 sec)
  2> commit;    
  Query OK, 0 rows affected (0.00 sec)

1> select * from foo;    
+-----+-------+
| uid | value |
+-----+-------+
|   1 |     1 |
+-----+-------+
1 row in set (0.00 sec)

In the second example transaction isolation effectively became "READ-COMMITTED" instead of desired "REPETABLE-READ" - which justifies the deprecation.

I think there must be a way to avoid the cost of gap-locking without reducing transaction isolation. The way I understand above result is that commit visibility ordering depends on transaction start order, instead of transaction commit order. In ZODB, where there is the same need for REPEATABLE-READ isolation, this is solved by having 2 distinct values:

txn_time which tells up to which snapshot data can be read
tid, the transaction identifier, which is a new snapshot identifier assigned during the short serialised time at transaction commit boundary where the transaction becomes visible to any later snapshot

I do not know about InnoDB internals, so it's impossible for me to tell if there is any reason making this impossible. But if it is possible, I think such change would get rid of gap-locking need to preserve REPEATABLE-READ isolation level. Such change would then reverse the innodb_locks_unsafe_for_binlog deprecation and should even allow enabling it by default, improving performance overall.

3 Performance

Any issue where a query get a sub-optimal execution plan, or a supposedly optimal query plan do not yield a good enough performance.

3.1 Mroonga fulltext index cardinality is always 1

ERP5 allows users to search for documents by some string property values. Currently, there are two ways ERP5 makes these values available to SQL queries:

regular InnoDB BTree indexes querried with LIKE operator
fulltext indexes (either MyISAM or Mroonga)

There are several problems with this:

ERP5 proposes two radically different criterion syntaxes (_ and % wilcards for LIKE operator, vs. fulltext syntax), which confuses users
In addition to the differing syntax, result sets may be surprising to user, and hence misunderstood
As a result, users will tend to put % wilcards everywhere they can just to make LIKE operator be more tolerant

So overall, poor user experience and occasional disastrous performance. One could naively hope users will eventually discover the cause-effect relationship between wilcards and poor performance, but it never happened as far as we know. And teaching users to not use such feature never goes well.

So ERP5 needs to stop using InnoDB BTree indexes for any user-selected string criterion.

The resulting queries would look like:

SELECT ... FROM ... WHERE X.portal_type="Organisation" AND MATCH Y.title AGAINST "Nexedi" IN BOOLEAN MODE

(where X and Y may be the same table)

The first issue we face when X and Y are different tables is that joining with fulltext table results in disastrous execution plan when a non-fulltext criterion would be most discriminant: for example, "Nexedi" may be present in a lot of document titles (ex: "Nexedi Invoice 2016-ABCD" etc), but there would be only a handful of Organisation-type documents with such title. Still, MariaDB + Mroonga cause the fulltext condition to be applied first, causing disastrous performance as MariaDB spends time discarding when checking for portal_type criterion. This issue has been reported on the MariaDB issue tracker.

Another issue we face when X and Y would be the same table is that fulltext indexes cannot be composed with another column (this is even an SQL syntax limitation). So avoiding the join which causes the perfromance issue above does not yield any gain in practice.

3.2 InnoDB cluster and secondary indexes fragmentation

ERP5 inserts, deletes and updates millions of rows a day on a busy site, with InnoDB tablespaces in the hundreds of gigabytes range.

Restoring a full table dump sometimes yields an unexpectedly large space gain: a 350GB tablespace (9GB of gzipped SQL) would then fit in 150GB.

This may be the sign of index fragmentation, but there is no obvious instrumentation available to measure it. Runing an "ALTER TABLE" statement which involves table re-creation produces a table significantly smaller than the original table, but this can take a long time (several hours), and prevents detection before modification.

There are at least three things which can improve this issue:

avoiding fragmentation (probably the hardest to implement)
improve alter-table performance (already possible with percona toolkit's online schema change tool when not using triggers - and we never use triggers in ERP5, and possible in some cases with in-place ALTER TABLE algorithm)
provide some instrumentation inside MariaDB/InnoDB/XtraDB to track fragmentation effects, if possible at a low cost (page fill ratio encountered in the last N queries ?)

SOLUTION: MariaDB supports InnoDB defragmentation since 10.1.1 . This support requires actively triggering defragmentation each time it is needed, which is not very practical. More testing is needed.

3.3 [Started] Incorrect join order in query involving (mostly) stock and categories tables

A suboptimal execution plan which seems to be recurring in ERP5 is when involving a table where the most relevant criterion of the query (and which is efficient when querried alone) gets very poor performance when applying conditions comming from the categories table.

(do be expanded)

3.4 TokuDB does not fully make use of the available indexes

This is an issue detected with NEO, and reported to MariaDB's issue tracker .

Most Powerful Open Source ERP

MariaDB improvement efforts