Données volumineuse MySQL

 

 Manipulation de données volumineuse MySQL

Mysql M Deux très grandes tables: une avec 1,4 milliards de lignes et une autre de 500 millions de lignes, plus quelques autres petites tables avec quelques centaines de milliers de lignes chacune. Sur le disque, il s’élève à environ un demi-téraoctet.

Le serveur MySQL est exécuté sur une machine très puissant, 64 bits avec 128G de RAM et un disque dur rapide. J’ai pensé que l’interrogation serait un jeu d’enfant.

Avant d’illustrer comment MySQL peut être bipolaire, la première chose à mentionner est que vous devez modifier la configuration du serveur MySQL et la taille de chaque cache.

Les variables de configuration MySQL

Les variables système peuvent être définis au démarrage du serveur en utilisant les options sur la ligne de commande ou dans un fichier d’options. La plupart d’entre eux peuvent être modifiés dynamiquement alors que le serveur est en cours d’exécution par le biais de la variables serveur système mysql, les noms des variables système MySql ne sont pas toujours évidentes. J’ai fini avec quelque chose comme ceci:

 

key_buffer_size = 1G sort_buffer_size = 16M tmp_table_size = 4G max_heap_table_size = 8G record_buffer = 512K record_rnd_buffer = 512K myisam_sort_buffer_size = 4G

 

MySQL fait un assez bon travail à la récupération de données à partir des tables individuelles lorsque les données sont correctement indexé. Par exemple, cette requête est un jeu d’enfant sur ma table :

 mysql> SELECT * FROM relations WHERE relation_type = 'INSIDE';

Nous avons un index pour la colonne relation_type. Il faut un certain temps pour transférer les données, car il récupère des millions d’enregistrements, mais la recherche proprement dite et la récupération est très rapide; des données commence en streaming immédiatement.

Mais ces requêtes sont ennuyeuses. Lors de l’exploration des données, nous voulons souvent des requêtes complexes qui impliquent plusieurs tables en même temps, donc en voici une :

 mysql> SELECT p.project_id, p.name, IFNULL (comptage (r.relation_id distinct), 0)
 DE relations que r 
          RIGHT JOIN sourcerer.projects comme p
          ON r.project_id = p.project_id
        OÙ p.source = 'Apache'
        GROUP BY p.project_id;
       ... Données ...
 133 lignes de jeu (1 min 33,94 sec)

La chose que j’ai fait avec cette requête était de rejoindre la table des relations (la table de la ligne 1B +) avec la table des projets (environ 175 000 mille lignes), sélectionner un sous-ensemble des projets (les projets Apache), et le groupe les résultats par Identifiant du projet , de sorte que j’ai un comptage du nombre de relations par projet sur cette collection particulière. Les champs joints sont indexés, le champ de la source n’est pas indexé. Comme on le voit, il a fallu 1 min et demi pour la requête à exécuter. OK, ce serait mauvais pour une requête en ligne, mais pas si mal pour un hors un. Passons à une question qui est légèrement différente:

mysql> SELECT p.project_id, p.name, IFNULL (comptage (r.relation_id distinct), 0)
 DE relations que r
 RIGHT JOIN sourcerer.projects comme p
          ON r.project_id = p.project_id
 OÙ p.source = 'Apache' ET r.relation_type = 'INSIDE'
 GROUP BY p.project_id;
 ...
  132 lignes de jeu (46 min 26.00 sec)

Whoa! J’ai ajouté une petite contrainte à des relations, en sélectionnant uniquement un sous-ensemble d’entre eux, et maintenant il prend 46 minutes pour cette requête à compléter! WTF?! C’est totalement contre-intuitive. Ajout d’une contrainte signifie que moins de dossiers seraient examinées, ce qui signifie un traitement plus rapide. Droite?

Regardons ce que «expliquer» dit. (Btw, «expliquer» est votre ami face WTFs avec MySQL). Voici la «expliquer» pour la première requête, l’un sans la contrainte sur la table de relations:

mysql> SELECT expliquer p.project_id, p.name, IFNULL (count (distinct r.relation_id), 0) à partir de relations que r RIGHT JOIN sourcerer.projects que p = ON r.project_id p.project_id OÙ p.source = 'Apache 'GROUP BY p.project_id;

  | Identifiant | select_type | Table | Type | possible_keys | clé | key_len | ref | lignes | extra |

  | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 173663 | Utilisation où; Utilisation filesort |
  | 1 | SIMPLE | r | ref | project_id | project_id | 8 | sourcerer.p.project_id | 8735 | |

 

Et voici le «l’explication» pour la deuxième requête, celle de la contrainte sur la table de relations:

mysql> SELECT EXPLIQUER p.project_id, p.name, IFNULL (comptage (r.relation_id distinct), 0) à partir de relations que r RIGHT JOIN sourcerer.projects que p = ON r.project_id p.project_id OÙ relation_type = 'INSIDE' ET p.source = GROUPE 'Apache' PAR p.project_id;

 | Identifiant | select_type | Table | Type | possible_keys | clé | key_len | ref | lignes | extra |

 | 1 | SIMPLE | r | ref | relation_type, project_id | relation_type | 2 | const | 508126553 | Utilisation où; l'aide temporaire; Utilisation filesort |
 | 1 | SIMPLE | p | eq_ref | project_id | project_id | 8 | sourcerer.r.project_id | 1 | Utilisation où |

Alors qu’est-ce qui se passe?

Selon l’explication, dans la première requête, la sélection sur la table des projets est faite en premier. Parce qu’il s’agit de seulement quelques centaines de milliers de lignes, la table résultante peut être conservé dans la mémoire; la jointure suivante entre la table résultante et la table très grandes relations sur le champ indexé est rapide. Nous sommes tous bien. Cependant, dans la seconde requête, l’explication nous dit que, d’abord, une sélection est faite sur la table de relations (en fait, les relations OÙ relation_type = ‘INSIDE’), le résultat de cette sélection est énorme (en millions de lignes), il ne rentre pas dans la mémoire, si MySQL utilise une table temporaire, la création de cette table prend un temps très long … catastrophe!

Ici, vous pouvez demander: mais pourquoi ne le planificateur de requêtes choisir de faire la sélection des projets de la première, tout comme il l’a fait sur la première requête? Ensuite, il devrait rejoindre que le tableau des grandes relations, tout comme il l’a fait avant, ce qui serait rapide, puis sélectionnez les relations à l’intérieur et à compter et le groupe des trucs. Je ne sais pas pourquoi le planificateur a fait la décision qu’il a prise. Mais essayons dire exactement ce que je viens de dire:

mysql> SELECT p.project_id, p.name, IFNULL (comptage (r.relation_id distinct), 0)
       FROM (SELECT * FROM projets OÙ source = 'Apache') AS p
       LEFT JOIN relations R sur (p.project_id = r.project_id)
       OÙ r.relation_type = 'INSIDE'
       GROUP BY p.project_id
       ...
       132 rows in set (57.26 sec)

OK, nous sommes de retour dans les affaires: 57 secondes!

Comme vous pouvez le voir, la ligne entre la voile en douceur et la catastrophe est très mince, et particulièrement avec de très grandes tables. De très petits changements dans la requête peuvent avoir des effets gigantesques de la performance. Vous avez toujours besoin de comprendre ce que prévoit le planificateur de requêtes à faire.

Maintenant, dans cet exemple particulier, nous pouvons aussi ajouter un index dans le champ de la table des projets source. Permettez-moi de le faire:

projets mysql> ALTER TABLE ADD source d'indice (source);
  Query OK, 173 663 lignes affectées, 4 avertissements (16,78 sec)
  Records: 173 663 doublons: 0 Avertissements: 0

 

Revenons à la requête lente et voir ce que le planificateur de requêtes veut faire maintenant:

mysql> SELECT expliquer p.project_id, p.name, IFNULL (count (distinct r.relation_id), 0)
        DE relations que r
        RIGHT JOIN sourcerer.projects comme p
        ON r.project_id = p.project_id
        OÙ p.source = 'Apache' ET r.relation_type = 'INSIDE'
        GROUP BY p.project_id;

 | Identifiant | select_type | Table | Type | possible_keys | clé | key_len | ref | lignes | extra |

 | 1 | SIMPLE | p | ref | project_id source | Source | 1003 | const | 227 | Utilisation où; Utilisation filesort |
 | 1 | SIMPLE | r | ref | relation_type, project_id | project_id | 8 | sourcerer.p.project_id | 8735 | Utilisation où |

Ah-ha! Maintenant, il a changé d'avis à propos de la table à traiter d'abord: il veut traiter les premiers projets. Cela semble bon, pas de tables temporaires partout, alors essayons-le:
  mysql> SELECT p.project_id, p.name, IFNULL (comptage (r.relation_id distinct), 0)
 DE relations que r
 RIGHT JOIN sourcerer.projects comme p
          ON r.project_id = p.project_id
 OÙ p.source = 'Apache' ET r.relation_type = 'INSIDE'
 GROUP BY p.project_id;
 ...
        132 lignes de jeu (1 min 15,71 sec)

 

OK, bon. L’index sur le champ source ne fait pas nécessairement une amélioration considérable de la performance sur la recherche de projets (après tout, ils semblent tenir dans la mémoire), mais le facteur dominant ici est que, à cause de cet index, le planificateur a décidé de traiter la table des projets de la première. Dans ce cas, qui fait la différence entre la voile en douceur et la catastrophe.

Certains de mes étudiants ont suivi une approche différente. Plutôt que de s’appuyer sur le processeur de requêtes MySQL pour l’assemblage et de contraindre les données, ils récupèrent les dossiers en vrac, puis ne le filtrage / traitement se Java ou programmes Python. Si vous n’êtes pas prêt à plonger dans les détails subtils de traitement des requêtes MySQL, qui est une variante de trop.

 

 

 

 

 

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

* Copy This Password *

* Type Or Paste Password Here *