Insertion par batch en php [Résolu]

[Jacobian]

Bonjour tous le monde,
je cherche des retours d'experience concernant les batches PHP
voici mon probleme:
j'ai 300M de fichier sql chaque fichier contient des insertions dans 3 tables:
- country
- city
- region

pour cela, je veux optimiser mon programme suivant:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
foreach ($sel_country as $id_country => $name_country){
	foreach ($country[$id_country]['part'] as $part_number => $part_country){
					//path to sql file
					$db_file = "/".$part_country.".sql";
					$f_sql = fopen($db_file,"r");
					while (!feof($f_sql)) {
						$strSQL = fgets($f_sql);           		
						//insert data
					          $model->executeQuery($strSQL))					
					}
					//close the file
					fclose($f_sql);
			}
		}

conteneu des fichiers:

Code :

1
2
3
4
5
 
INSERT INTO  country (id, name, id_country) VALUES (1,'Afghanistan',0)
INSERT INTO  region (id, id_country, name) VALUES (1,1,'Velayat-e Badakhshan')
INSERT INTO  city (id, id_country, name, zip_code, region_code, id_region, lat, lon) VALUES (1,1,'Ab Gach','','',1,36.9833332,72.7000000)
.......

[estofilo]

Si le SGBD supporte les transactions, c.a.d en gros si ce n'est pas mysql avec le moteur MyISAM, il faudrait grouper les INSERT dans une transaction. Une transaction par fichier SQL, par exemple, ça serait assez simple au vu du découpage.

Dans le cas contraire à chaque INSERT, le SGBD attend que les données soient bien physiquement sur disque avant de continuer, ce qui ralentit énormément.

[Jacobian]

oui j'ai bien mySql avec le moteur myISam,
comment faire pour englober un fichier par transaction ?

[estofilo]

Avec MyISAM ce n'est pas faisable ni nécessaire car ce moteur n'a pas de transaction.

Pour aller plus vite il aurait fallu que les données soient au format brut au lieu d'être déjà en insert SQL, et les importer avec la commande LOAD DATA ou le programme mysqlimport qui sont spécialement prévus pour l'import de données en masse.

Une autre option serait en principe de traiter plusieurs fichiers en parallèle, mais avec MyISAM ça risque de ne rien donner comme accélération puisque que lors d'un INSERT le moteur verrouille toute la table.

Bref, rien de positif :-(

[Jacobian]

ok je vais changer en InnoDB,
est ce qu'il faut aussi changer le format des fichier insert ??

[estofilo]

Les INSERTs ont l'air complètement standard, il ne devrait pas y avoir de problème avec.

En revanche j'ai oublié quelque chose précédemment: s'il y a des index sur les tables, il est bon pour le temps d'exécution de les supprimer, pour les recréer une fois que l'import est terminé.
Et c'est encore plus vrai pour une insertion en parallèle s'il y a des index uniques.

[Jacobian]

le soucis avec load data infile est qu'il permet pas l'import dans plusieurs table est c'est ça que j'aime pas,
d'autre proposition ?

[stealth35]

y'a jamais de saut ligne dans tes insères ?

[Jacobian]

si j'ai bien compris non j'ai pas de saut de ligne dans mes fichiers mes par contre j'ai retour a la ligne

[Benjamin Delespierre]

Envoyé par Jacobian

ok je vais changer en InnoDB,
est ce qu'il faut aussi changer le format des fichier insert ??

Hey, ne va pas changer le moteur de stockage juste parce que c'est plus pratique. Il faut un raison technique valide pour faire ça.

[Jacobian]

Hey, ne va pas changer le moteur de stockage juste parce que c'est plus pratique. Il faut un raison technique valide pour faire ça.

je suis prêt a faire une refonte de tous le system si la solution que vous me proposer va amélioré les performance.

jusqu'a présent j'ai pas une réponse claire et pointue

[Benjamin Delespierre]

Le choix du moteur de stockage va dépendre de plusieurs paramètres: le volume de données à stocker, la présence de clés étrangères, le temps d'accès etc.

Généralement, et sauf motivation particulière, on mets les tables en InnoDB pour avoir:
- le renforcement de sécurité sur les clés étrangères
- les dépendances et les modifications en cascade
- les transactions
En revanche, si ta table contiens un nombre très important de donnés (supérieur à plusieurs millions de tuples) il vaut mieux mettre du MyIsam sinon la taille en mémoire va exploser avec les index. Egalement, si tu cherches à faire des recherches fulltext, seul MyIsam possède l'index qui te permet de le faire.

Renseigne toi plus sur le site de MySQL pour déterminer le moteur qu'il te faut: http://dev.mysql.com/doc/refman/5.0/...e-engines.html, http://www.mysql.fr/why-mysql/white-...sam_innodb.php

[Jacobian]

j'ai fait un test si j'ai met:

- MyIsam le temps d’exécution il est entre 20 et 30 min
- si je met innoDb le temps est doublé en 2 à savoir plus que 40 min

donc pour ses table la j'adopte comme moteur myIsam, cela dit j'aimerais bien des idées sur comment optimisé mon code au début

[stealth35]

y'a quoi derrière le executeQuery ?

[Benjamin Delespierre]

Envoyé par Jacobian

j'ai fait un test si j'ai met:

- MyIsam le temps d’exécution il est entre 20 et 30 min
- si je met innoDb le temps est doublé en 2 à savoir plus que 40 min

donc pour ses table la j'adopte comme moteur myIsam, cela dit j'aimerais bien des idées sur comment optimisé mon code au début

Y'a juste un truc que j'ai pas tellement compris, cette action tu dois l'effectuer souvent ?

Si c'est une action que tu dois effectuer une fois de temps en temps, le temps d'import export importe moins que la rapidité des requêtes effectuées par le système sur ces tables...

Egalement, si tu as un fichier de données (volumineux certes) tu peux l'importer dans un base de données en utilisant la console:

Code :

mysql -u xxx -p yyy database < fichier.sql

[Jacobian]

- executeQuery comme sont nom l'indique il lance une requête sql
- en fait c'est un system qui offre aux utilisateurs un ensemble de pays qu'il veux installer comme il peux les réinstallé
si l’utilisateur clic sur check All alors la il faut attendre

[stealth35]

Envoyé par Jacobian

- executeQuery comme sont nom l'indique il lance une requête sql

je m'en doutais pas du-tout ...
montre le code, on ne sait pas ce que t'as mis dedans

[Jacobian]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
public function executeQuery($query)
	{
		$db = JFactory::getDBO();
		$db->setQuery($query);
 
		if (!$db->Query()) {
			return false;
		}
 
		return true;
	}

[stealth35]

arf c'est du Joomla, je comprend mieux

[Jacobian]

non ce n'est pas de joomla mais c'est du PHP

j'attend vos lumieres les gas?