Bulk INSERT TUNNING

Bulk INSERT TUNNING - SQL/NoSQL - Programmation

Marsh Posté le 14-09-2005 à 17:11:40    

Bonjours, les big boss.
Voila g un souci je récupère un fichier plat de 4.000.000 de lignes, je le traite et je l'injecte dans une base de données postgres. Actuellement je mets plus de 11h à l'injection...
 
Je possède deux tables une table trap_snmp comportant 11 champs et une clef étrangère pointant vers ma table description. Ces deux tables ne possèdent pas tout de suite d'index (sauf clef primaire) pour ne pas retarder l'injection , les indexes seront fixés apres:
 

Citation :


--
--Création de la table description
--
CREATE TABLE Description  
(ID_description serial unique not null,PRIMARY KEY (ID_description),
description varchar(300) unique);
--
--Création de la table trap_snmp
--
CREATE TABLE trap_snmp  
(ID_trap serial unique not null,PRIMARY KEY (ID_trap),
type_req varchar(6),
alrmid int,
cri int,
id_desc int,
first_occ timestamp,
last_occ timestamp,
count int DEFAULT NULL,
equipement varchar(40),
Quadrigramme varchar(4),
Contrat varchar(40),
Nnm varchar(30),
Tk_stat_ch timestamp,
Foreign key (id_desc) REFERENCES description(id_description));


 
Pour optimiser tout ca g décider de mettre en place:
 
1->Lecture par bloc de 100.000 lignes pour ne pas charger en mémoire tout le fichier plat.
 

Citation :


Commentaire:
je ne sais pas quel procéder est le plus rapide la fonction read ou passer par la variable courante $_ .
reference:http://articles.mongueurs.net/magazines/perles/perles-07.html


 
2->traitement des champs à récupèrer dans le fichier plat pour les 100.000 lignes.
 
3->Injection des champs dans trap_snmp sans provisionner tout de suite description  ni récupèrer la clef primaire id_description de la table description pour le champ id_desc.
 

Citation :


Commentaire:
Il faut vérifier l'existence du champs description de la table description pour éviter les doublons mais pas immédiatement car les indexes se régénèrent à chaque injection et retardent le provisionning. Il faut générer les indexes pour les requetes SELECT.


 
4->Création des indexes sur trap_snmp:id_desc, description:id_description et description: description
 
5->provisionning de la table description + vérification de la présence des doublons.
 
<>->gestion possible avec les threads si la lecture par blocs fonctionnent.
 
Je désirerais avoir toutes suggestions pour tunner mon injection, réussir à lire par blocs et voir threader
 
Cordialement.


Message édité par ypnoize le 14-09-2005 à 17:25:59
Reply

Marsh Posté le 14-09-2005 à 17:11:40   

Reply

Marsh Posté le 14-09-2005 à 18:31:11    

Je comprends pas trop, dans ton fichier tu as des infos pour charger la table trap_snmp uniquement, ou aussi des descriptions à charger dans la table description ?
 
Les bulk inserts de 1000 ou 10.000 sont déjà largement suffisants.
Quels sont traitements à faire sur les infos du fichier ?

Reply

Marsh Posté le 14-09-2005 à 22:41:45    

J'ai rencontré un problème similaire au tiens (insertions en grand nombre) mais sous Oracle. Je ne sais pas si ça peut t'aider sous postgress, mais j'ai comparé les perfs de différentes méthode pour faire des insert dans ce topic:
http://forum.hardware.fr/hardwaref [...] tm#t787194


---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack
Reply

Marsh Posté le 14-09-2005 à 23:20:45    

Sous Oracle on peut en général se ramener à utiliser SQL Loader ... donc ça charge très vite les données en base ;)

Reply

Marsh Posté le 14-09-2005 à 23:25:05    

Beegee a écrit :

Sous Oracle on peut en général se ramener à utiliser SQL Loader ... donc ça charge très vite les données en base ;)


Sauf quand on ne peut pas [:dawa]


---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack
Reply

Marsh Posté le 15-09-2005 à 08:26:12    

C'est quand même rare ...
 
Si le fichier a une structure ne correspondant à aucune table, on peut en créer une exprès, ou traiter le fichier (en Perl ou autre) pour le rendre plus exploitable.

Reply

Marsh Posté le 15-09-2005 à 10:26:18    

Et si les données que tu dois insérer ne sont pas dans un fichier, tu fais comment ? ;)


---------------
"I wonder if the internal negative pressure in self pumping toothpaste tubes is adjusted for different market altitudes." John Carmack
Reply

Marsh Posté le 15-09-2005 à 10:36:04    

Tu fais un commit a chaque X lignes insérées ?
 
Sans l'injection dans description ton traitement prends combien de temps ?

Reply

Marsh Posté le 15-09-2005 à 10:42:02    

Beegee a écrit :

Je comprends pas trop, dans ton fichier tu as des infos pour charger la table trap_snmp uniquement, ou aussi des descriptions à charger dans la table description ?
 
Les bulk inserts de 1000 ou 10.000 sont déjà largement suffisants.
Quels sont traitements à faire sur les infos du fichier ?


 
je charge à la fois la table trap_snmp et la table description.Le traitement c du parsing et du découpage pour récupérer les champs à charger.

Reply

Marsh Posté le 15-09-2005 à 10:51:15    

jogrey a écrit :

Tu fais un commit a chaque X lignes insérées ?
 
Sans l'injection dans description ton traitement prends combien de temps ?


 
pour 1000 lignes à traiter, je suis à 12s mais le problème c que je dois récupérer l'id description.
 
Pour l'instant:
1->création des indexes  
2->injection dans table trap_snmp et description avec vérification des doublons sur description
 
A terme je voudrais suivre ce plan:
1->injection dans table trap_snmp
2->création de l'index sur description
3->vérification des doublons et injection dans description
4->création de l'index sur id_desc
5->récupération de l'id_desc

Reply

Marsh Posté le 15-09-2005 à 10:51:15   

Reply

Marsh Posté le 15-09-2005 à 10:52:31    

ypnoize a écrit :

pour 1000 lignes à traiter, je suis à 12s mais le problème c que je dois récupérer l'id description.
 
Pour l'instant:
1->création des indexes  
2->injection dans table trap_snmp et description avec vérification des doublons sur description
 
A terme je voudrais suivre ce plan:
1->injection dans table trap_snmp
2->création de l'index sur description
3->vérification des doublons et injection dans description
4->création de l'index sur id_desc
5->récupération de l'id_desc


 
d'apres toi le commit je devrais le faire tous les combien de lignes?

Reply

Marsh Posté le 15-09-2005 à 11:22:31    

Tu peux donner le format des lignes dans ton fichier d'entrée ?
 
Tu pourrais ajouter une colonne Description dans la table trap_snmp, et faire:
1- chargement des lignes du fichier dans trap_snmp.
2- Mise à jour de la table description pour qu'elle contienne toutes les descriptions possibles (celles qui y sont déjà, plus les distinctes qui sont dans trap_snmp).
3- Création de l'index sur la table description.
4- Mise à jour des id de description dans trap_snmp (simple UPDATE).
5- Enlever la colonne description dans la table trap_snmp.
 
Ce n'est qu'un moyen parmi d'autres ... tu pourrais aussi, dans un script, prendre ton fichier en entrée, et générer les INSERTs dans la table description, et ensuite, mettre en cache ces informations pour faire les bulk INSERTs dans trap_snmp ...

Reply

Marsh Posté le 15-09-2005 à 11:33:15    

Beegee a écrit :

Tu peux donner le format des lignes dans ton fichier d'entrée ?
 
Tu pourrais ajouter une colonne Description dans la table trap_snmp, et faire:
1- chargement des lignes du fichier dans trap_snmp.
2- Mise à jour de la table description pour qu'elle contienne toutes les descriptions possibles (celles qui y sont déjà, plus les distinctes qui sont dans trap_snmp).
3- Création de l'index sur la table description.
4- Mise à jour des id de description dans trap_snmp (simple UPDATE).
5- Enlever la colonne description dans la table trap_snmp.
 
Ce n'est qu'un moyen parmi d'autres ... tu pourrais aussi, dans un script, prendre ton fichier en entrée, et générer les INSERTs dans la table description, et ensuite, mettre en cache ces informations pour faire les bulk INSERTs dans trap_snmp ...


 
ouai c une bonne idée je vais essayer comme ca.
 
Voila une ligne de mon fichier en entré: (champs en gras,champs description)  
 
UPDATE: 38814130,"xxx-xxx-sup-05",3,"Data Warehouse Maintenance Program Error, Return Code: 2, Error: Lock of "/var/opt/OV/share/databases/snmpCollect//ovcolexportPid" failed..    Another instance of ovcolsum, ovcoldelsql or ovcoltosql may be running..    If this is not the case, remove /var",08/02/05 12:03:00,09/01/05 14:29:00,1389,"xxxx-sup-05","","","","Client xxx","","xxx-sup-05",09/01/05 14:29:00,"","",0,"",""


Message édité par ypnoize le 15-09-2005 à 11:35:59
Reply

Marsh Posté le 15-09-2005 à 11:52:31    

Pas terrible comme format, le séparateur (la virgule) et le charactère servant à encadrer (de temps en temps !) les valeurs (le guillemet) sont tous les deux utilisés eux-mêmes dans les valeurs !
 
T'as intérêt à blinder ton code ...

Message cité 1 fois
Message édité par Beegee le 15-09-2005 à 12:17:43
Reply

Marsh Posté le 15-09-2005 à 11:58:27    

Beegee a écrit :

Pas terrible comme format, le séparateur (la virgule) et le charactère servant à encadrer (de temps en temps !) les valuers (le guillemet) sont tous les deux utilisés eux-mêmes dans les valeurs !
 
T'as intérêt à blinder ton code ...


 
heyheyhey....
$str =~ /(?:(?<=,)|^)(".*?"|[^,]*)(?:(?=,)|$)/g;


Message édité par ypnoize le 15-09-2005 à 12:00:37
Reply

Marsh Posté le 16-09-2005 à 16:24:25    

l'instruction COPY sur postgres est l'équivalent de SQL_loader sur ORACLE ;)
Pour injecter 4.000.000 de lignes je mets environ 1h avec le calcul de l'index multiple ,la différence est flagrante.
Pour plus d'info:http://www.postgresql.org/docs/8.0/interactive/populate.html
 
merci les gars.


Message édité par ypnoize le 21-09-2005 à 13:55:59
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed