30 mins avec JSON en MySQL

novembre 17, 2015

Read this post in English

Note: Cet article est inspiré de la présentation MySQL 5.7 + JSON de Morgan Tocker.

Comme vous le savez MySQL 5.7 est GA. Cette nouvelle mouture de la base de données open source la plus populaire au monde a plus de 150 nouvelles fonctionnalités. L’une d’entre elle est un type de données JSON natif ainsi que les fonctions JSON associées.

Prenons 30 minutes pour voir ce que cela donne…

 

Récupérer des documents JSON

Commençons pas récupérer des données au format JSON. Mirco Zeiss fournit un gros fichier JSON qui représente San Francisco (SF Open Data) : https://github.com/zemirco/sf-city-lots-json

Pour pouvoir manipuler ces données avec MySQL, quelques modifications sont nécessaires:

Ça à l’air bon !

Note: La taille des documents JSON stockés dans une colonne de type JSON est limitée par la valeur de la variable système max_allowed_packet. (Cette limitation ne s’applique que lorsque le serveur stocke les documents. En mémoire la taille des document peut donc être supérieure).

 

Notre document JSON sera stocké dans la table InnoDB features:

Une autre façon de stocker des documents JSON est de les mettre dans une colonne de type VARCHAR ou TEXT.

La table features_TEXT va nous permettre de comparer les performances des types JSON et TEXT.

Note: Dans cet exemple le type TEXT n’est pas suffisamment large pour géré nos données JSON . (ERROR 1406 (22001): Data too long for column ‘feature’ at row 17360). LONGTEXT fera donc l’affaire.

 

Insertion des données dans les tables

Note: Le temps d’exécution d’une même requête pouvant varier largement d’une exécution à l’autre, sur mon (vieux) portable avec une petite configuration pour MySQL (e.g. Buffer pool = 128Mo). J’ai donc exécuté les requêtes plusieurs fois en utilisant mysqlslap:

mysqlslap -c1 -i <N> { Concurrence = 1 / Itération > 20 (en fonction de la durée totale de la requête) }.

Le temps d’exécution sera donc représenté la plupart du temps sous la forme: « Minimum number of seconds to run all queries: 59.392 seconds » au lieu de « Query OK, 206560 rows affected (59.39 sec) ».

 

Copier les données JSON dans la table features

Minimum number of seconds to run all queries: 59.392 seconds

Copier les données JSON dans la table features_TEXT

Minimum number of seconds to run all queries: 39.784 seconds

Sur ma machine, charger 206560 enregistrements montre une différence de performance d’environ 40% en faveur du type TEXT par rapport au type JSON.

Cela s’explique par les fonctionnalités fournit par le type JSON de MySQL:

  • Validation automatique des documents JSON stockés. Tout document invalide produit une erreur.
  • Optimisation du stockage des données. Les documents JSON stockés dans des colonnes de type JSON sont convertis en un format interne qui permet un accès en lecture rapide.

Bien évidemment rien de comparable pour le type TEXT, ces fonctionnalités ont donc un coût lors de l’écriture dans la table, ce qui est plutôt logique.

Au niveau des méta-données:

Le point intéressant ici est que le type de données LONGTEXT consomme plus d’espace que le type JSON (au format de stockage optimisé) environ 20% plus.

 

Récupérer des données d’un document JSON

MySQL 5.7 fournit un ensemble de fonctions JSON.

Par exemple, JSON_EXTRACT renvoi des données d’un document JSON. A noter que, depuis MySQL 5.7.9 vous pouvez utiliser la syntaxe inlined JSON path expressions qui rend plus lisible les requêtes qui manipulent des données JSON:

e.g.

JSON_EXTRACT(col, « $.json_field ») est similaire à col->« $.json_field »

 

Table avec type de données JSON

Minimum number of seconds to run all queries: 4.470 seconds

Table avec type de données TEXT

Minimum number of seconds to run all queries: 29.365 seconds

Remarques

Récupérer les documents JSON implique, sans surprise, un Full Table Scan (FTS).

Cependant on peut constater la puissance du format interne JSON de MySQL qui permet un accès en lecture particulièrement rapide et efficace.

Dans cet exemple  le temps d’exécution est environ 7 fois plus rapide (4.470 s vs 29.365 s) avec le type de données JSON comparé aux même données stockées dans une colonne de type TEXT.

 

Colonne générée (Generated Column)

Une colonne de type JSON ne peut être indexée. CEPENDANT il est possible de contourner cette restriction en créant un index sur une generated column qui permet d’extraire une valeur scalaire de la colonne JSON. Les generated columns peuvent être stockée / matérialisée (STORED) ou virtuelle / non matérialisée (VIRTUAL).

Créer une generated column virtuelle (VIRTUAL) est très rapide car les valeurs de la colonne ne sont pas stockées mais calculées à la volée lors de la lecture des enregistrements immédiatement après tout trigger BEFORE. Seules les méta-données sont modifiées en d’autres termes, il n’y a pas de reconstruction de la table.

En production, c’est en général une colonne générée virtuelle qui sera pertinente.

Note: Le désavantage de cette approche est que certaines données sont stockées 2 fois; dans la colonne générée et dans l’index.

Les nouvelles structures de tables sont:

Au niveau des méta-données:

Remarques

Identique !

Comme on le pressentait la taille des données n’a pas changée.

 

Y a t’il une différence de temps d’exécution entre récupérer des documents JSON à partir de la colonne virtuelle et à partir de la fonction JSON_EXTRACT ?

Minimum number of seconds to run all queries: 2.790 seconds

Minimum number of seconds to run all queries: 25.933 seconds

Remarques

Évidemment le plan d’exécution (QEP) est le même: FTS.

Cependant 2 commentaires:

  • Le type de données JSON de MySQL est encore plus efficace que le type de données TEXT, dans cet exemple le temps d’exécution est environ 8 fois plus rapide avec JSON.
  • Un FTS sur une generated column virtuelle (colonne: feature_type) est plus performant que l’utilisation, dans la clause du SELECT, de la fonction json_extract sur le document JSON (de 4.470 à 2.790).

 

Créer un index sur une colonne générée

A partir de MySQL 5.7.8, InnoDB supporte les index secondaires sur les colonnes virtuelles.

Ajouter ou supprimer un index secondaire dans une colonne virtuelle est une opération qui ne nécessite pas de recopier la table (in-place operation).

La nouvelle structure des tables est:

Au niveau des méta-données:

Remarque

L’index sur la colonne feature_type est matérialisé. Sa taille est approximativement 6 Mo.

Grâce à l’index, la requête devrait être plus efficace:

Minimum number of seconds to run all queries: 0.178 seconds

Minimum number of seconds to run all queries: 0.178 seconds

Comme prévu, l’optimiseur utilise l’index (feature_type) et la temps d’exécution de la requête est vraiment meilleur, et ce pour les 2 tables (de 2.790 à 0.178 pour la colonne JSON).

 

Pour conclure

MySQL 5.7 implémente le type de données JSON, ainsi qu’un ensemble de fonctions qui permet de Créer, Chercher, Modifier des valeurs JSON mais également de Renvoyer des valeur d’attributs JSON. C’est une superbe fonctionnalité et je suis persuadé que les développeurs sauront en faire bon usage.

La colonne générée (Generated Columns) est également une fonctionnalité très utile. Elle peut être utilisée, entre autre, pour indexer une fonction, ou comme cache pour expressions souvent utilisées, ou pour indexer du contenu XML… ou comme nous venons de le voir pour indexer des documents JSON.

MySQL 5.7 est vraiment une superbe version !  Essayez la, elle mérite définitivement plus de 30 minutes.

Vous voulez en savoir plus sur MySQL 5.7?

 

Pour aller plus loin

MySQL Documentation

The JSON data type

https://dev.mysql.com/doc/refman/5.7/en/json.html

JSON Functions

https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

CREATE TABLE and Generated Columns

http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns

MySQL Server Blog

Native JSON Data Type and Binary Format

http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/

JSON functions

http://mysqlserverteam.com/json-labs-release-json-functions-part-1-manipulation-json-data/

http://mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/

Inline JSON Path Expressions in MySQL 5.7

http://mysqlserverteam.com/inline-json-path-expressions-in-mysql-5-7/

Getting Started With MySQL & JSON on Windows

http://mysqlserverteam.com/getting-started-with-mysql-json-on-windows/

Effective Functional Indexes in InnoDB

http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/

MySQL 5.7

What Is New in MySQL 5.7

https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

Complete list of new features in MySQL 5.7

http://www.thecompletelistoffeatures.com/

2

Slides du meetup

septembre 25, 2015

Le 17 septembre j’ai été invité par le MySQL User Group Fr pour parler d’un retour d’expérience sur une migration MySQL 5.5 vers 5.6.

Voici ma présentation:

Upgrade to MySQL 5.6 without downtime from Olivier DASINI

 

0

Meetup MySQL à Paris

septembre 14, 2015

Ce jeudi 17 septembre 2015, à l’initiative du MySQL User Group France (lemug.fr),  je vais présenter un retour d’expérience sur une migration MySQL 5.5 vers 5.6 dans les locaux de Dailymotion à Paris.

Le programme :

  • Aurélien Lequoy – DBA MySQL et CEO de Esysteme – présentera PmaControl son outil pour administrer et superviser MySQL.
  • Serge Frezefond – Architecte Cloud chez MariaDB Corp – parlera des nouveautés MariaDB 10.1 liées à la sécurité.
  • Olivier Dasini – Sr. MySQL DBA – Upgrade to MySQL 5.6 without downtime.

 

Infos et inscriptions:

http://www.lemug.fr/2015/invitation-meetup-pma-control-securite-bonnes-pratiques-le-17-septembre/

 

Et c’est gratuit !!! 🙂

Merci à Dailymotion & MariaDB Corp

0

UPDATE db SET age = age + 1 WHERE product=’MySQL’

janvier 7, 2014

2013 à été une année très riche en ce qui concerne l’écosystème MySQL / MariaDB / Percona Server. Voici un petit récap technique (incomplet) pour les 3 acteurs majeurs:

MySQL @Oracle

 

En bref

Oracle à repris la main et le lead technique en 2013 avec MySQL 5.6: http://dimitrik.free.fr/blog/archives/2013/02/mysql-performance-mysql-56-vs-mysql-55-vs-mariadb-55.html

 

Facebook migre vers la 5.6: https://github.com/facebook/mysql-5.6

 

De belle promesses avec MySQL 5.7: http://dimitrik.free.fr/blog/archives/2013/10/mysql-performance-the-road-to-500k-qps-with-mysql-57.html

 

Avec MySQL 5.7 & memcached: http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html

État des lieux

MySQL 5.6 GA depuis le 05/02/2013

Changes in MySQL 5.6.10 (2013-02-05, General Availability)

http://dev.mysql.com/downloads/mysql/

 

MySQL 5.7 DMR, dernière version le 03/12/2013

Changes in MySQL 5.7.1 (2013-04-23, Milestone 11)

http://dev.mysql.com/downloads/mysql/

 

MySQL cluster 7.3 GA depuis le 18/06/2013

Changes in MySQL Cluster NDB 7.3.2 (5.6.11-ndb-7.3.2) (2013-06-18, General Availability)

http://dev.mysql.com/downloads/cluster/

 

MySQL utilities GA dernière version le 02/08/2013

Changes in MySQL Utilities 1.3.4 (2013-08-02, General Availability)

http://dev.mysql.com/downloads/tools/

Événements français

Meetup Oracle/LeMug le 10 octobre 2013

http://dasini.net/blog/2013/10/01/mysql-techday-le-programme/

http://dasini.net/blog/2013/10/14/retour-sur-le-mysql-techday/

 

MariaDB & SkySQL

 

En bref

Monty Program Ab et SkySQL ont fusionnés: http://www.skysql.com/news-and-events/press-releases/skysql-merges-with-mariadb-developers

MariaDB Galera Cluster est passée GA, en attendant le tour de MariaDB 10.

Wikimedia migre en MariaDB 5.5: https://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariadb/

Google migre vers MariaDB 10: http://www.zdnet.com/google-quietly-dumps-oracle-mysql-for-mariadb-7000020670/

 

Des distribs Linux qui embarques MariaDB: https://mariadb.com/kb/en/distributions-which-include-mariadb/

Et une multitude de moteur de stockage: https://mariadb.com/kb/en/mariadb-storage-engines/

État des lieux

MariaDB 5.5 GA depuis le 23/05/2013

https://downloads.mariadb.org/

 

MariaDB Galera Cluster 5.5 GA depuis le 30/08/2013

https://downloads.mariadb.org/

 

MariaDB 10.0.7 Beta: dernière version le 27/12/2013

https://blog.mariadb.org/mariadb-10-0-7-now-available/

https://downloads.mariadb.org/

Événements français

Meetup SkySQL / LeMug: 17 décembre 2013

http://www.lemug.fr/2013/17-decembre-meet-up-la-nuit-mariadb/

 

Percona

 

En bref

Des outils (toolkit, XtraBackup,…) qui font le bonheur des DBA. A noter que le Q4 à vu la sortie en GA de Percona Server 5.6.

État des lieux

Percona Server 5.6 GA: depuis le 7/10/2013

http://www.percona.com/software/percona-server/ps-5.6

 

Percona Server 5.5 GA: dernière version le 20/12/2013

http://www.percona.com/downloads/Percona-Server-5.5/

 

Percona Server XtraDB Cluster 5.5 GA: dernière version le 3/12/2013

http://www.percona.com/software/percona-xtradb-cluster

 

Percona Server XtraDB Cluster 5.6 RC: dernière version le 18/12/2013

http://www.percona.com/doc/percona-xtradb-cluster/5.6/

 

XtraBackup

http://www.percona.com/software/percona-xtrabackup

 

Toolkit

http://www.percona.com/software/percona-toolkit

 

Monitoring Plugins

http://www.percona.com/software/percona-monitoring-plugins

 

Data Recovery Tool for InnoDB

http://www.percona.com/software/mysql-innodb-data-recovery-tools

Événements français

Pas de meetup Percona / LeMug en 2013. Il aurait dû avoir lieu fin 2013, mais un stupide problème de salle à fait capoter l’affaire. Ce n’est que partie remise.

N’hésitez pas à compléter si j’ai oublié des choses 🙂

Beaucoup de bonnes choses en 2013, c’est vraiment de bon augure pour 2014.

Je profite de ce “post” pour évidemment vous souhaiter une excellente année 2014. Que celle-ci soit remplie de bonheur, de santé, de réussite et de belles requêtes optimisées 🙂

 

SQLment votre,

 

Olivier DASINI

0

Retour sur le MySQL techday

octobre 14, 2013

Jeudi 10 octobre à eu lieu, dans les locaux d’Oracle, le MySQL techday organisé par Oracle et le MySQL User Group francophone.

Dans la continuité de MySQL Connect, les speakers nous ont offert des sessions de très grandes qualités sur les thèmes de la performance et de l’optimisation relative aux versions 5.6 et 5.7 de MySQL.

Les slides de Dimitri sont disponibles sur son blog: http://dimitrik.free.fr/blog/archives/2013/10/my-files-for-mysql-tech-day-paris-today.html

Ceux d’Arnaud Adant, sur le site de MySQL connect:

50 Tips for Boosting MySQL Performance

Enhancing Productivity with MySQL 5.6 New Features

 

Bonne dégustation !

 

A noter que c’est la 1ère fois en France (à ma connaissance) que les 3 grandes tendances de MySQL étaient représentées à un événement, à savoir SkySQL/MariaDB, Percona et bien sûr Oracle.
Prochain pari, des confs avec ces 3 tendances…
0

MySQL TechDay, le programme

octobre 1, 2013

Le 10 octobre 2013, Le MySQL User Group Francophone (lemug.fr) et Oracle MySQL vous invitent au MySQL Tech Day à Paris.

Début des réjouissances 10h30, avec un programme exceptionnel !!! :

  • 10h30 – Overview: MySQL Innovation @Oracle @MySQL Connect
    Xavier GERARD / Olivier ZEMRAG (MySQL France)
  • 11h00 – MySQL Performance: Latest improvements in MySQL 5.6 & 5.7
    Dimitri KRAVTCHUK (MySQL Performance Engineering leader)
  • 13h45 – MySQL Optimizer: What is new? what is coming?
    Guilhem BICHOT (MySQL Optimizer Engineering team)
  • 15h15 – 50 tips to boost MySQL Performance
    Arnaud ADANT (MySQL Support team)
  • 17h00 – MySQL Performance Schema: Overview / HOWTO
    • MySQL Performance Schema: Hands-on (live lab)
      Dimitri KRAVTCHUK (MySQL Performance Engineering leader)

 
Infos & inscriptions: http://www.lemug.fr/2013/mysql-tech-day-paris/

see you there 🙂

2

MySQL TechDay, le 10 Octobre 2013

août 26, 2013

Le 10 octobre 2013, Oracle et Le MySQL User Group Francophone (lemug.fr) vous invitent au MySQL Tech Day à Paris.

Une journée technique bien pleine où les différentes équipes MySQL chez Oracle vont nous présenter les améliorations présentes et futures des versions 5.6 et 5.7 de MySQL.

 

Au programme:

  • Overview: MySQL Innovation @Oracle

  • MySQL Performance: Latest improvements in MySQL 5.6 & 5.7

  • MySQL Optimizer: What is new, what is coming

  • 50 tips to boost MySQL Performance

  • MySQL Performance Schema: Overview / HOWTO

  • MySQL Performance Schema: Hands-on (live lab)

 

Bref du lourd, du très lourd !!!

 

Last but not least, c’est gratuit, mais les places sont limités…

 

Inscription et renseignements, veuillez contacter Alexis Bensa:

  • par email: alexis.bensa(@)oracle.com

  • téléphone: 01.57.60.27.96

Quand ?

Jeudi 10 octobre 2013, toute la journée

?

Cette rencontre aura lieu dans les locaux d’Oracle au

Portes de La Defense 15, boulevard Charles de Gaulle

Prix ?

Entrée libre

 

update: les horaires

Le communiqué officiel:


MySQL Tech Day @Paris


We’re happy to announce you that MySQL Tech Day will take place in Paris on Oct 10, 2013 in Oracle main office. It’ll be a full day event giving you an occasion to listen directly from Oracle developers about most of the improvements made recently within MySQL 5.6 and 5.7 development.

 

The agenda is the following :

  • Overview: MySQL Innovation @Oracle

  • MySQL Performance: Latest improvements in MySQL 5.6 & 5.7

  • MySQL Optimizer: What is new, what is coming

  • 50 tips to boost MySQL Performance

  • MySQL Performance Schema: Overview / HOWTO

  • MySQL Performance Schema: Hands-on (live lab)

 

Don’t miss such an opportunity to get most of the hot news about MySQL improvement directly from the source! It’ll be just two weeks after MySQL Connect, so there will be really a lot of news! So, book this date in your agenda right now and then don’t forget to register yourself for the event – attending is totally free, but places are limited, so registration is mandatory to secure enough seats.

 

To register : please, contact Alexis Bensa:

  • by email: alexis.bensa(@)oracle.com

  • or directly by phone: 01.57.60.27.96

 

Oracle main office address: Portes de La Defense 15, boulevard Charles de Gaulle.

 

Further details will be communicated over a time.

4

LeMug.fr à la radio

juillet 10, 2013
Tags: ,

L’association LeMug.fr (MySQL User Group Francophone) fut l’invité à l’émission Cyberculture de la radio Ici et Maintenant samedi 6 juillet pour parler des bases de données webs et principalement du sgbdr le plus utilisée sur internet: MySQL.

Le podcast de l’émission est disponible ici : Les bases de données webs

Source lemug.fr

 

0

#DBHangOpsFr du 20/06/2013, les backups avec MySQL

juin 25, 2013
Tags:

Si vous avez manqué 1er #DBHangOpsFr, voici la session de rattrapage.

Un grand merci à Cédric.

 

0

Utiliser une sous requête c’est mal ? (suite) part 1-3

mai 27, 2013

Comme promit, voici la suite de l’article Utiliser une sous-requête c’est mal ?

L’idée ici est de répondre aux interrogations de svar et d’en profiter pour explorer les nouvelles possibilités de la variante stable de MySQL qui possède l’optimiseur le plus avancé, c’est à dire MariaDB 5.5.

 

Préambule

En pré-requis, je vous invite à explorer la doc officielle de MySQL au sujet de la variable optimizer_switch, qui permet de sélectionner les algorithmes d’optimisation, ainsi que les différentes subtilités implémentées dans l’optimiseur de MariaDB.

 

La valeur par défaut sur MariaDB 5.5.30 de la variable optimizer_switch est:

 

mariadb5.5> SHOW VARIABLES LIKE ‘optimizer_switch’\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
      Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,
index_merge_intersection=on,index_merge_sort_intersection=off,
engine_condition_pushdown=off,index_condition_pushdown=on,
derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,
materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,
partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,
mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,
join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,
optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

Le truc super lisible en fait !

Mais comme je suis un mec super sympa, j’ai mis en gras les directives qui vont nous intéresser dans cet article.

 

Les patterns des tests

 

Pour atteindre les objectifs fixés, j’ai effectué 2 niveaux de tests. Le premier niveau va me fournir des données “objectives”. Il consiste à exécuter la requête avec différentes directives d’optimisation et d’analyser les “status” ainsi que les plans d’exécution associés.

Niveau stats

  1. SET optimizer_switch=’default’ : réinit des directives d’optimisation

  2. SET optimizer_switch=’valeur adéquate : choix des directives d’optimisation

  3. EXPLAIN EXTENDED : QEP et infos de réécritures de la requête

  4. SHOW WARNINGS : pour savoir comment l’optimiseur à réécrit la requête

  5. FLUSH STATUS : réinit des stats à 0

  6. exécution de la requête

  7. SHOW STATUS : pour récupérer les variables d’états, Handler* & Created_tmp

Avec le second niveau de tests, je vais récupérer une info “subjective”, le temps. Cela consiste à récupérer la durée minimale d’exécution de la requête avec les différentes directives d’optimisations à l’aide de l’outil mysqlslap.

Niveau temps

  1. SET GLOBAL optimizer_switch=’default’

  2. SET GLOBAL optimizer_switch=’valeur adéquate

  3. mysqlslap  -c1 -i200 -q »la requête«  : requête unitaire jouée plusieurs fois ie buffers chaud

 

A noter que le nombre d’itération (200) à été réduit à 5 lorsque la requête dure de l’ordre de la 10aine de minutes (bah oui, j’ai une vie aussi).

Contexte

 

Pour rappel, le problème initiale est une sous requête problématique en MySQL (Percona server) 5.5 :

 

SELECT DISTINCT OtherID
FROM MyTable
WHERE Status = 1
 AND Stage = 4
 AND Invisible = 0
 AND MainID IN
     (
     SELECT MainID
     FROM MyTable
     WHERE OtherID = 2779262
         AND Stage = 0
         AND Invisible = 0
     )
+———-+
| OtherID |
+———-+
7 rows in set (44 min 56.66 sec) <= A froid

L’idée ici n’est pas de comparer le temps d’exécution de la requête sur MariaDB par rapport à  Percona server (les serveurs et les paramètres sont différents), mais bien de voir et de comprendre les (nouveaux) choix de l’optimiseur de MariaDB 5.5.

Traditional behavior (IN to EXISTS strategy)

 

TEST 1

Traditionnellement l’optimiseur converti la sous-requête IN en EXIST. Pour simuler ce comportement il faut désactiver la matérialisation et la semi-jointure.

 

SET optimizer_switch=’materialization=off,semijoin=off’;

 

QEP

*************************** 1. row ***************************
         id: 1
select_type: PRIMARY
      table: MyTable
       type: ref
possible_keys: StageMainIDOtherID,…
        key: StageMainIDOtherID
    key_len: 1
        ref: const
       rows: 153455020
    filtered: 100.00
      Extra: Using where; Using temporary
*************************** 2. row ***************************
         id: 2
select_type: DEPENDENT SUBQUERY
      table: MyTable
       type: index_subquery
possible_keys: MainIDOtherIDStatus,…
        key: MainIDOtherIDStatus
    key_len: 8
        ref: func,const
       rows: 1
    filtered: 100.00
      Extra: Using where

Intuitivement on peut penser que la requête interne est exécutée en premier, puis qu’elle génère une liste d’ID (MainID) qui est ensuite passée dans la clause IN, du genre :

… MainID IN (123, 456, 789).

 

Eh ben non !

Comme convenu, le QEP est semblable à celui utilisé avec MySQL / percona server 5.5.

En fait, l’optimiseur transforme le clause IN en EXISTS. La table externe est lue la première sur environ 50% de ses lignes (rows: 153455020), ce qui dans notre cas fait beaucoup et est jointe avec la table interne.

La sous-requête dépend alors des données de la requête externe (select_type: DEPENDENT SUBQUERY).

La requête réécrite par l’optimiseur ressemble alors à peu près à :

 

SELECT DISTINCT OtherID
FROM MyTable AS MyExtTable
WHERE Status = 1
 AND Stage = 4
 AND Invisible = 0
 AND EXISTS
     (
     SELECT 1
     FROM MyTable AS MyInnTable
     WHERE OtherID = 2779262
         AND Stage = 0
         AND Invisible = 0
         AND MyExtTable.MainID = MyInnTable.MainID
     )
 
 

Optimizer rewriting

select distinct OtherID AS OtherID from MyTable
where ((Status = 1) and (Stage = 4) and (Invisible = 0) and <expr_cache><MainID>(<in_optimizer>(MainID,<exists>(<index_lookup>(<cache>(MainID) in MyTable on MainIDOtherIDStatus
where (((OtherID = 2779262) and (Stage = 0) and (Invisible = 0)) and (<cache>(MainID) = MainID) and (OtherID = 2779262) and (Stage = 0) and (Invisible = 0)))))))

 

Confirmation que l’on a bien une transformation IN to EXISTS.

Status

+—————————+———–+
| Variable_name             | Value     |
+—————————+———–+
| Created_tmp_disk_tables     | 0     |
| Created_tmp_tables            | 2     |
| Handler_mrr_key_refills     | 0         |
| Handler_mrr_rowid_refills   | 0         |
| Handler_prepare           | 0         |
| Handler_read_first        | 0         |
| Handler_read_key          | 44655686  |
| Handler_read_last         | 0         |
| Handler_read_next         | 153049748 |
| Handler_read_prev         | 0         |
| Handler_read_rnd          | 0         |
| Handler_read_rnd_deleted    | 0         |
| Handler_read_rnd_next     | 8         |
| Handler_tmp_write         | 206       |
| Handler_write             | 0         |
+—————————+———–+

 

Beaucoup de lecture dans l’index (Handler_read_key | 44655686). Le problème ici est le nombre élevé de lectures transverse de l’index (Handler_read_next | 153049748) qui est très coûteux, surtout si les IO sont sur disque, et c’est encore pire s’ils sont aléatoires ie random IO disk.

 

Contrairement à MySQL 5.5 où il n’y a qu’une seule table temporaire créée, ici il y en a 2 (Created_tmp_table | 2), l’une pour le dédoublonnage lié à la clause DISTINCT et l’autre… ben je ne sais pas :/

En profilant la requête avec la commande SHOW PROFILE, il y est bien noté que 2 tables temporaires sont supprimées, mais dans le même temps, une seule est créée…

Si quelqu’un à une explication, je suis preneur.

Bench

daz@sql:~/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i5 -q »SELECT DISTINCT OtherID  FROM MyTable  WHERE Status = 1  AND Stage = 4  AND Invisible = 0  AND MainID IN  ( SELECT MainID  FROM MyTable  WHERE OtherID = 2779262  AND Stage = 0  AND Invisible = 0  ); »

Benchmark

  Minimum number of seconds to run all queries: 550.444 seconds

 

Les meilleures performances de cette requête, dans ces conditions, sont de l’ordre de la 10aine de minutes, ce qui est rarement acceptable en OLTP.

Pour simuler l’”intelligence” de l’optimiseur de MySQL / Percona Server 5.5, il a fallu désactiver 2 directives d’optimisation (la matérialisation et la semi-jointure). Voyons ce qu’il en est en ne désactivant qu’une seule d’entre elles, à tour de rôle.

MATERIALIZATION strategy (disable semi-join)

 

TEST 2

Pour désactiver la semi-jointure, il suffit de mettre la directive suivante à OFF:

SET optimizer_switch=’semijoin=off’;

 

La stratégie d’optimisation utilisée par l’optimiseur est alors la matérialisation. En 2 mots, le résultat de la sous-requête est stocké dans une table temporaire qui contient un index unique. Cet index sert à dédoublonner les valeurs insérée et à récupérer les infos de manière performante (comme un index classique), qui vont alimenter la requête externe.

QEP

*************************** 1. row ***************************
         id: 1
select_type: PRIMARY
      table: MyTable
       type: ref
possible_keys: StageMainIDOtherID, …
        key: StageMainIDOtherID
    key_len: 1
        ref: const
       rows: 153455020
    filtered: 100.00
      Extra: Using where; Using temporary
*************************** 2. row ***************************
         id: 2
select_type: MATERIALIZED
      table: MyTable
       type: ref
possible_keys: MainIDOtherIDStatus,OtherIDStageStatusInvisibleMainID,StageMainIDOtherID, …
        key: OtherIDStageStatusInvisibleMainID
    key_len: 5
        ref: const,const
       rows: 235
    filtered: 100.00
      Extra: Using where; Using index

 

Là encore, selon le QEP la table externe est lue la première sur environ 50% de ses lignes  (rows: 153455020). Ce qui change là, c’est que la stratégie d’optimisation retenue est la matérialisation.

Autres différences avec le QEP originel, un covering index (Extra: Using where; Using index) sur les 235 (une estimation) enregistrements (rows: 235) de la table matérialisée.

Optimizer rewriting

select distinct OtherID AS OtherID from MyTable
where ((Status = 1) and (Stage = 4) and (Invisible = 0) and <expr_cache><MainID>(<in_optimizer>(MainID,MainID in
( <materialize> (select MainID from MyTable
where ((OtherID = 2779262) and (Stage = 0) and (Invisible = 0)) ), <primary_index_lookup>(MainID in <temporary table> on distinct_key where ((MainID = <subquery2>.MainID)))))))

 

Confirmation que l’on à bien la matérialisation de la sous-requête sous la forme d’une table temporaire indexée(<primary_index_lookup>MainID in <temporary table> on distinct_key).

A noter que l’index à également servi au dédoublonnage.

Status

+—————————+———–+
| Variable_name             | Value     |
+—————————+———–+
| Created_tmp_disk_tables     | 0     |
| Created_tmp_tables            | 3     |
| Handler_mrr_key_refills     | 0         |
| Handler_mrr_rowid_refills   | 0         |
| Handler_prepare           | 0         |
| Handler_read_first        | 0         |
| Handler_read_key          | 44655687  |
| Handler_read_last         | 0         |
| Handler_read_next         | 153049981 |
| Handler_read_prev         | 0         |
| Handler_read_rnd          | 0         |
| Handler_read_rnd_deleted    | 0         |
| Handler_read_rnd_next     | 8         |
| Handler_tmp_write         | 442       |
| Handler_write             | 0         |
+—————————+———–+

Niveau table temporaires, on a 1 table temporaire pour le DISTINCT + 1 table pour la matérialisation + 1 table temporaire mystère (Created_tmp_tables | 3).

Avec une table temporaire supplémentaire, il y a alors plus d’écriture (Handler_tmp_write | 442).

442 (Handler_tmp_write) – 235 (MATERIALIZED rows) = 207 (à rapporter au 206 du Handler_tmp_write du QEP originel)

 

A l’aide de toutes ces informations, lequel des 2 modes est le plus performant ?

D’un point de vu status, il y a 1 table temporaire de plus pour T2, mais les tables étant créées entièrement en mémoire, le coût reste donc faible. De plus le nombre de Handler_read_next est sensiblement égale. Même s’il n’est pas réparti de la même manière.

Bref, niveau status, exaequo entre T1 & T2

Avec explain, le nombre de rows de T2 est 235 fois plus important que pour T1. Cependant, T2 fournit une matérialisation et index covering index alors qu’avec T1 on est dans de la sous-requête corrélée !

Bref niveau explain, c’est pas clair non plus 😀 mais la matérialisation se faisant en mémoire, elle devrait être moins coûteuse.

Bench

daz@sql:~/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i5 -q »SELECT DISTINCT OtherID  FROM MyTable  WHERE Status = 1  AND Stage = 4  AND Invisible = 0  AND MainID IN  ( SELECT MainID  FROM MyTable  WHERE OtherID = 2779262  AND Stage = 0  AND Invisible = 0  ); »
Benchmark
  Minimum number of seconds to run all queries: 430.712 seconds

 

T2 s’exécute avec 2 minutes de moins que T1.

SEMIJOIN strategy (disable materialization)

 

TEST 3

Pour désactiver la matérialisation, il suffit de mettre la directive suivante à OFF:

SET optimizer_switch=’materialization=off’;

 

La stratégie d’optimisation utilisée par l’optimiseur est alors la semi-jointure (une sorte de jointure interne où seule une occurrence des valeurs jointes est récupérée).

QEP

*************************** 1. row ***************************
         id: 1
select_type: PRIMARY
      table: MyTable
       type: index
possible_keys: MainIDOtherIDStatus,OtherIDStageStatusInvisibleMainID,StageMainIDOtherID
        key: OtherIDStageStatusInvisibleMainID
    key_len: 15
        ref: NULL
       rows: 306910041
    filtered: 0.00
      Extra: Using where; Using index; Using temporary; LooseScan
*************************** 2. row ***************************
         id: 1
select_type: PRIMARY
      table: MyTable
       type: ref
possible_keys: MainIDOtherIDStatus,StageMainIDOtherID
        key: MainIDOtherIDStatus
    key_len: 4
        ref: MyTable.MainID
       rows: 1
    filtered: 100.00
      Extra: Using index condition; Using where

Le QEP obtenu est un full index scan aka FIS (type: index) avec covering index (Extra: Using index;). En général un FIS est plutôt une mauvaise nouvelle pour les perfs, sauf dans le cas d’un index covering.

L’algorithme utilisé par l’optimiseur pour la semi-jointure est le  LooseScan (Extra: LooseScan).

A noter l’utilisation de l’optimisation index condition pushdown ou ICP (Extra: Using index condition). On en y revient plus tard.

Optimizer rewriting

select distinct OtherID from MyTable semi join (MyTable)
where ((Status = 1) and (Stage = 4) and (Invisible = 0) and (OtherID = 2779262) and (Stage = 0) and (Invisible = 0)
and (MainID = MainID))

 

Confirmation que l’on a bien une transformation en semi-jointure.

Status

+—————————+———–+
| Variable_name             | Value     |
+—————————+———–+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
| Handler_mrr_key_refills   | 0         |
| Handler_mrr_rowid_refills | 0         |
| Handler_prepare           | 0         |
| Handler_read_first        | 1         |
| Handler_read_key          | 236       |
| Handler_read_last         | 0         |
| Handler_read_next         | 306909498 |
| Handler_read_prev         | 0         |
| Handler_read_rnd          | 0         |
| Handler_read_rnd_deleted  | 0         |
| Handler_read_rnd_next     | 8         |
| Handler_tmp_write         | 7         |
| Handler_write             | 0         |
+—————————+———–+

 

Les informations d’état sont assez claires (si si vous allez voir):

  • Le FIS est un parcours transverse complet de l’arbre d’index: (Handler_read_next | 306909498 et  Handler_read_first | 1)

  • La sous-requête elle, est responsable de: (Handler_read_key | 236)

  • Le distinct est responsable de la création de la table temporaire: (Created_tmp_tables | 1)

  • Remplissage de la table temporaire: (Handler_tmp_write | 7)

  • Lecture des données de la table temporaire: (Handler_read_rnd_next | 8)

 

T2 vs T3 ça donne quoi ?

Là encore ce n’est pas trivial de comparer les QEP et status des 2 requêtes. On peut cependant raisonnablement penser que le FIS avec covering index de T3 va générer moins de random IO que T2.

 

 

Bench

daz@sql:~/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i200 -q »SELECT DISTINCT OtherID  FROM MyTable  WHERE Status = 1  AND Stage = 4  AND Invisible = 0  AND MainID IN  ( SELECT MainID  FROM MyTable  WHERE OtherID = 2779262  AND Stage = 0  AND Invisible = 0  ); »
Benchmark
  Minimum number of seconds to run all queries: 130.531 seconds

 

T3 s’exécute avec 3m20 de moins que T2.

T3 s’exécute avec 5m20 de moins que T1.

Optimizer choice

 

TEST 4

Comme je l’ai mentionné en introduction, l’optimiseur de MariaDB est le plus avancé (en matière de possibilités d’optimisation) dans l’univers MySQL. Voyons comment cet optimiseur, avec les réglages par défaut, gère cette requête.

 

La commande suivante permet de réinitialiser, les directives d’optimisation:

SET optimizer_switch=’default’;

QEP

*************************** 1. row ***************************
         id: 1
select_type: PRIMARY
      table: <subquery2>
       type: ALL
possible_keys: distinct_key
        key: NULL
    key_len: NULL
        ref: NULL
       rows: 235
    filtered: 100.00
      Extra: Using temporary
*************************** 2. row ***************************
         id: 1
select_type: PRIMARY
      table: MyTable
       type: ref
possible_keys: MainIDOtherIDStatus,StageMainIDOtherID
        key: MainIDOtherIDStatus
    key_len: 4
        ref: MyTable.MainID
       rows: 1
    filtered: 100.00
      Extra: Using index condition; Using where
*************************** 3. row ***************************
         id: 2
select_type: MATERIALIZED
      table: MyTable
       type: ref
possible_keys: OtherIDStageStatusInvisibleMainID, StageMainIDOtherID, …
        key: OtherIDStageStatusInvisibleMainID
    key_len: 5
        ref: const,const
       rows: 235
    filtered: 100.00
      Extra: Using where; Using index; Distinct

La sous-requête est matérialisée et on a également une semi-jointure entre cette dernière est la table de la requête externe (MyTable).

Optimizer rewriting

select distinct OtherID AS OtherID from MyTable semi join (MyTable)
where ((Status = 1) and (Stage = 4) and (Invisible = 0) and (OtherID = 2779262) and (Stage = 0) and (Invisible = 0)
and (MainID = MainID))

 

L’information sur la semi-join est explicite (MyTable semi join (MyTable)… (MainID = MainID)).

Par contre, pas de trace ici de la matérialisation…

Status

+—————————+——-+
| Variable_name             | Value |
+—————————+——-+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 2     |
| Handler_mrr_key_refills   | 0     |
| Handler_mrr_rowid_refills | 0     |
| Handler_prepare           | 0     |
| Handler_read_first        | 0     |
| Handler_read_key          | 237   |
| Handler_read_last         | 0     |
| Handler_read_next         | 243   |
| Handler_read_prev         | 0     |
| Handler_read_rnd          | 0     |
| Handler_read_rnd_deleted  | 0     |
| Handler_read_rnd_next     | 245   |
| Handler_tmp_write         | 243   |
| Handler_write             | 0     |
+—————————+——-+

 

Niveau handler, que des petites valeurs, ça sent bon la perf tout ça !!!

Grosso-modo, ces infos nous disent :

  • 2 tables temporaires pour le distinct et la matérialisation : (Created_tmp_tables | 2)

  • La semi-jointure  : (Handler_read_next | 243)
  • La sous-requête elle, est responsable de : (Handler_read_key | 237)

  • Remplissage + lecture des tables temporaire : (Handler_read_rnd_next | 245 + Handler_tmp_write | 243)

Bench

daz@sql:~/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i200 -q »SELECT DISTINCT OtherID  FROM MyTable  WHERE Status = 1  AND Stage = 4  AND Invisible = 0  AND MainID IN  ( SELECT MainID  FROM MyTable  WHERE OtherID = 2779262  AND Stage = 0  AND Invisible = 0  ); »
Benchmark
  Minimum number of seconds to run all queries: 0.026 seconds

 

T4 est plus rapide que T1 de plus de 9 minutes.

T4 est plus rapide que T2 de plus de 7 minutes.

T4 est plus rapide que T3 de plus de 2 minutes.

En image, en temps relatif par rapport à T1 (la requête la plus longue) ça donne :

Avec les versions de MySQL / Percona server inférieures ou égales à 5.5 et les versions de MariaDB inférieures ou égales à 5.2 (de mémoire), les sous-requêtes peuvent poser de gros problèmes de performances. Il faut donc bien tester ces requêtes avant de les passer en prod.

 

Une bonne raison supplémentaire pour tester (et migrer) vers MariaDB 5.5 ou MySQL 5.6 (qui possède également les optimisations semijoin et materialization).

 

A suivre, dans la deuxième partie de cet article : Index Condition Pushdown et Multi Range Read…

0