The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-21. For the latest, interactive version please visit https://mariadb.com/kb/.

Debug dell'ottimizzatore di MariaDB 5.3

Contents

  1. debug_optimizer_prefer_join_prefix
    1. Semi-join
  2. debug_optimizer_dupsweedout_penalized ==
  3. Approfondimenti

MariaDB 5.3 ha una patch per il debug dell'ottimizzatore. La patch è in

lp:maria-captains/maria/5.3-optimizer-debugging

Questa patch si trova in una #ifdef, ma c'è un #define diretto in mysql_priv.h, perciò compilando semplicemente l'albero si dovrebbe ottenere un binario con il debug dell'ottimizzatore abilitato.

La patch aggiunge due variabili di sistema:

  • @@debug_optimizer_prefer_join_prefix
  • @@debug_optimizer_dupsweedout_penalized

Esse sono sia di sessione sia globali e sono impostabili tramite la riga di comando del server.

debug_optimizer_prefer_join_prefix

Se questa variabile è diversa da NULL, specifica il prefisso di una join, sottoforma di lista separata da virgole degli alias delle tabelle:

set debug_optimizer_prefer_join_prefix='tbl1,tbl2,tbl3';

L'ottimizzatore fa del suo meglio per costruire un piano per la join che corrisponda al prefisso specificato. Per fare questo, confronta i prefissi delle join che sta considerando con @@debug_optimizer_prefer_join_prefix, e se il piano non corrisponde moltiplica il costo per un milione.

In questo modo è più o meno possibile decidere l'ordine delle join. Per esempio, si consideri la query seguente:

MariaDB [test]> explain select * from ten A, ten B, ten C;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
|  1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                    |
|  1 | SIMPLE      | B     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
|  1 | SIMPLE      | C     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
3 rows in set (0.00 sec)

E si richieda come ordine di join: C,A,B:

MariaDB [test]> set debug_optimizer_prefer_join_prefix='C,A,B';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from ten A, ten B, ten C;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
|  1 | SIMPLE      | C     | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                    |
|  1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
|  1 | SIMPLE      | B     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
3 rows in set (0.00 sec)

Ed ecco fatto.

Si noti che questo approccio è basato sullo sforzo minore:

  • Non è possibile forzare un ordine delle join che l'ottimizzatore considera non valido (per esempio, per "t1 LEFT JOIN t2" non è possibile ottenere un ordine di: t2,t1).
  • L'ottimizzatore esclude diversi piani e potrebbe scartare anche l'ordine di join richiesto, se valuta che sia un milione di volte più costoso di un altro.

Semi-join

E' anche possibile forzare un ordine con alcune join più alcune semi-join. Se lo si fa, è possibile che venga scelta una diversa strategia:

MariaDB [test]> set debug_optimizer_prefer_join_prefix=NULL;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from ten A where a in (select B.a from ten B, ten C where C.a + A.a < 4);
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                      |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
|  1 | PRIMARY     | A     | ALL  | NULL          | NULL | NULL    | NULL |   10 |                            |
|  1 | PRIMARY     | B     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where                |
|  1 | PRIMARY     | C     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; FirstMatch(A) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
3 rows in set (0.00 sec)

MariaDB [test]> set debug_optimizer_prefer_join_prefix='C,A,B';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from ten A where a in (select B.a from ten B, ten C where C.a + A.a < 4);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
|  1 | PRIMARY     | C     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Start temporary                                 |
|  1 | PRIMARY     | A     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (flat, BNL join) |
|  1 | PRIMARY     | B     | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; End temporary                      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
3 rows in set (0.00 sec)

La materializzazione delle semi-join è un caso particolare, perché "join prefix" non è esattamente ciò che compare nell'output di EXPLAIN. Per la materializzazione delle semi-join:

  • "<subqueryN>" non deve essere inserito in @@debug_optimizer_prefer_join_prefix.
  • Inserire invece le tabelle materializzate, dove si desidera avere la riga <subqueryN>.
  • Non è possibile in alcun modo controllare l'ordine delle join in una materializzazione annidata. Esempio: si desidera A-C-B-AA: 
MariaDB [test]> set debug_optimizer_prefer_join_prefix='A,C,B,AA';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select * from ten A, ten AA where A.a in (select B.a from ten B, ten C);
+----+-------------+-------------+--------+---------------+--------------+---------+------+------+------------------------------------+
| id | select_type | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra                              |
+----+-------------+-------------+--------+---------------+--------------+---------+------+------+------------------------------------+
|  1 | PRIMARY     | A           | ALL    | NULL          | NULL         | NULL    | NULL |   10 |                                    |
|  1 | PRIMARY     | <subquery2> | eq_ref | distinct_key  | distinct_key | 5       | func |    1 |                                    |
|  1 | PRIMARY     | AA          | ALL    | NULL          | NULL         | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
|  2 | SUBQUERY    | B           | ALL    | NULL          | NULL         | NULL    | NULL |   10 |                                    |
|  2 | SUBQUERY    | C           | ALL    | NULL          | NULL         | NULL    | NULL |   10 |                                    |
+----+-------------+-------------+--------+---------------+--------------+---------+------+------+------------------------------------+
5 rows in set (0.00 sec)

Ma si ottiene A-B-C-AA.

debug_optimizer_dupsweedout_penalized

Esistono quattro strategie di esecuzione delle semi-join:

  1. FirstMatch
  2. Materialization
  3. LooseScan
  4. DuplicateWeedout

Le prime tre strategie possono essere disabilitate attraverso altrettanti flag in @@optimizer_switch. DuplicateWeedout invece non ha alcun flag. Il motivo è che questa strategia può gestire tutti i tipi di subquery, con tutti i tipi di ordini delle join, e viene usata quando non è possibile sceglierne un'altra. (Prima o poi sarà possibile abilitare FirstMatch e disabilitare tutto il resto, ma per il momento non è così.)

Siccome non è possibile disabilitare DuplicateWeedout, vi sono casi in cui viene scelta al posto della strategia che si desidera. Ecco a cosa serve debug_optimizer_dupsweedout_penalized. Se si imposta:

MariaDB [test]> set debug_optimizer_dupsweedout_penalized=TRUE;

...il costo dei piani delle query che fanno uso di DuplicateWeedout viene moltiplicato per un milione. Questo non significa che ci si libera realmente di DuplicateWeedout — è ancora possibile che venga scelta in luogo di piani meno costosi, a causa del bug Bug #898747. Un rimedio parziale è eseguire:

MariaDB [test]> set optimizer_prune_level=0;

E' possibile utilizzare allo stesso tempo sia debug_optimizer_dupsweedout_penalized, sia debug_optimizer_prefer_join_prefix. In questo modo si dovrebbe riuscire a ottenere la strategia e l'ordine di join desiderati.

Approfondimenti

  • Alcuni esempi sono repesibili in mysql-test/t/debug_optimizer.test, nei sorgenti di MariaDB
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.