Pquoi Un SELECT * de plusieurs millions de lignes épuise la mémoire vive ?

[vincedo]

Bonjour,

Je bosse sur un script de migration qui convertit les données d'une application V1 vers une application V2.

En gros, voilà ce que fait mon script :

<?php
// Sélectionne les données à migrer.
$res = mysql_query("SELECT * FROM table_v1");

// Parcourt les données...
while ($o = mysql_fetch_object($res)) {

  // Crée un array destiné à stocker les données à migrer
  $object = array();
  $object['property1'] = $o->title;
  $object['property2'] = $o->description;

  // ici, déclare d'autres propriétés et effectue qq traitements.

  // Enregistre le résultat.
  mysql_query("INSERT INTO table_v2...");
}
?>

Si je limite le nombre d'enregistrements du SELECT à quelques milliers, tout se passe sans problème.

Mais si je ne limite pas (or, ma table contient 3,5 millions d'enregistrements), le script plante en disant qu'il n'a plus de mémoire. J'ai pourtant déjà augmenté la memory_limit dans php.ini à 250M... Et quand on fait une boucle avec un mysql_fetch_object, j'imagine que les données sont remontées partiellement, au fur et à mesure, pas toutes en même temps, non ?

Y a-t-il une autre façon d'écrire ce script pour qu'il ne consomme pas autant de mémoire ?

Merci

[Anonymus]

Oui, oublies ton tableau.

Bon, pour savoir s'il y a une autre possibilité d'écrire ce script, il faudrait que tu nous le passe entier, et non seulement un extrait.

Mais déjà, un tableau ($object['property1']=.. ) de plusieurs millions d'entrées plantera à coups surs.

Par contre, si c'est juste pour faire un insert derrière, a priori inutile de compléter le tableau, tu as juste à mettre les données retournées par le '$o'

Enfin, tu devrais jeter un oeil aux fonctions 'insert into' pour copier une table entière:

> INSERT INTO `base2`.`table2` SELECT * FROM `base1`.`table1`;

etc..

[Kioob]

Hello,

mysql_query() a la facheuse habitude de stocker en mémoire tout le résultat de la requete avant de poursuivre l'exécution du script... d'où cette forte consommation mémoire.

mysql_unbuffered_query() ne fait pas ça, mais en contrepartie tu ne peux pas executer d'autre requete tant que tu n'as pas fini de lire le contenu du dernier SELECT. Bref, vu ton code actuel, ça ne collerait pas non plus.

Visiblement, tu n'utilises que 2 des champs du select, donc tu peux déjà commencer par les lister dans ton select, plutot que de faire un "SELECT *" à la méthode "bourrin"

Reste la solution de scinder la requete en plusieurs étapes :

1) chargement de la table via : insert into tatable ( ... ) select a, b from ....

2) puis tu complète les champs manquants par "petit paquet" : "select id from tatable where champacompleter is null limit 0, 1000"

3) boucle là dessus, à coup de "update tatable set champacompleter = XXX where id = N"

Ce n'est pas forcément l'idéal non plus, mais sans plus d'indications sur ton traitement, je ne vois que ça.

De manière générale, l'idée est d'en faire le plus possible via MySQL.... si tu peux faire tout le traitement sans avoir à balancer un seul "SELECT" coté PHP, ça ira certainement beaucoup plus vite, et sans consommer de mémoire.

[vincedo]

Merci pour vos réponses.

@Anonymus

Je ne stocke pas tout le resultset dans un tableau, c'était juste une façon d'expliquer que je fais qq traitements sur les données remontées avant le INSERT. Je stocke le résultat de ces traitements dans un tableau (autrement dit, 1 tableau = 1 enregistrement, et c'est toujours le même tableau à chaque passage de la boucle).

@Kioob

Tu as raison, c'est pas recommandé de faire un SELECT * si on n'utilise que qq champs. Mais là encore, c'était juste un exemple pour expliquer mon pb. Dans mon code, je ne sélectionne que ce dont j'ai besoin.

Quelle solution ?

Déjà, le coupable est effectivement le nb d'enregistrements trop importants et le mysql_query() qui bufferise tous les résultats, d'où l'explosion de consommation mémoire.

mysql_unbuffered_query() est une super piste, je connaissais pas. Comme j'avais déjà décomposé mes requêtes dans une première tentative d'optimisation, je n'ai pas besoin de lancer de nouvelles requêtes tant que je boucle sur la première requête, je vais donc essayer ça.

Une autre solution -- trouvée après qq recherches -- est de limiter (via la clause LIMIT en SQL) le nb d'enregistrements remontés par chaque SELECT, et de répéter l'opération autant de fois que nécessaire jusqu'à ce que tous les enregistrements aient été traités.

Encore merci !

[Anonymus]

Y a-t-il une autre façon d'écrire ce script pour qu'il ne consomme pas autant de mémoire ?

oui, à condition de voir le 'bon' script

[Dadou]

Quand on utilise une telle requête on doit pas seulement changer la mémoire allouée, mais aussi de passer sur un serveur plus performant. Quand on commence à attaquer des bdd qui nous renvoient des millions de résultats, il faut avoir un "super serveur"

[Kioob]

Heureusement que non Dadou

La solution de l'upgrade à la place de la réflexion, ce n'est que du court terme... coté SQL il vaut toujours mieux régler le soucis de performance que de tenter de le cacher en optant pour un serveur 4 fois plus cher... J'ai vu des traitements SQL passés de 8 heures à 2 minutes après le travail d'un "vrai" développeur... et ce n'est certainement pas un changement de machine qui aurait réglé ça.

J'imagine bien un player DVD qui chercherait à charger les 4.7Go de la galette en mémoire pour lire le film : vaut il mieux corriger le code ou bien imposer 5Go de mémoire pour faire tourner le truc ?

Coté SGBD, heureusement qu'on a pas attendu les "octos xeon avec 12go de mémoire" pour traiter les tables de quelques millions d'enregistrements

[Portekoi]

Bonjour,

Un Insert dans une boucle, c'est ce qu'il y a de pire

Tu peux très bien traiter 100 000 lignes et recharger la page en transmettant le nombre d'enregistrements déjà traités.

++

[Dadou]

Heureusement que non Dadou

La solution de l'upgrade à la place de la réflexion, ce n'est que du court terme... coté SQL il vaut toujours mieux régler le soucis de performance que de tenter de le cacher en optant pour un serveur 4 fois plus cher... J'ai vu des traitements SQL passés de 8 heures à 2 minutes après le travail d'un "vrai" développeur... et ce n'est certainement pas un changement de machine qui aurait réglé ça.

Je n'ai pas dit qu'une optimisation de code n'était pas nécessaire, mais qu'a partir d'un moment il est nécessaire d'upgrader le serveur

J'imagine bien un player DVD qui chercherait à charger les 4.7Go de la galette en mémoire pour lire le film : vaut il mieux corriger le code ou bien imposer 5Go de mémoire pour faire tourner le truc ?

Coté SGBD, heureusement qu'on a pas attendu les "octos xeon avec 12go de mémoire" pour traiter les tables de quelques millions d'enregistrements

Non bien sûr mais pour de gros traitements on multipliait les serveurs.

Bien sûr la solution exprimée par vincedo de scinder la requête est la meilleure pour ses besoins de migration. Mais se pose la question d'après, il devra continuer en exploitation à faire du traitement sur une table de plusieurs millions de résultats, j'ai bien peur que quand les "utilisateurs / visiteurs" viendrons qu'il retombe vite avec les problèmes de performances.

[Phobos]

Bonjour,

Un Insert dans une boucle, c'est ce qu'il y a de pire

Tu peux très bien traiter 100 000 lignes et recharger la page en transmettant le nombre d'enregistrements déjà traités.

++

C'est moins pire que pas de boucle et une requète lourde à faire tomber le serveur

Cela revient au même de faire une boucle qui traite 100 000 lignes à chaque fois (pourquoi pas même avec un sleep() régulier) que de faire comme tu le décris.

[Portekoi]

Re,

Apparemment, il ne peut/veut pas faire de requetes imbriquées (insert into matable1 (val1, val2) select va1, val2 from matable2)

Et il ne peut pas non plus traiter la requête dans son intégralité.

Qu'il fasse un sleep ou un reload, cela revient au même. (d'ou mon post)

Et je le répète : mettre une requete dans une boucle est toujours mauvais pour le serveur (cela se ressent sur les grosses tables)

++

Portekoi