43 millions d'insertions en 4 minutes 47 dans postgresql dans une machine virtuelle kvm !
Je suis en train d'insérer des "zillions" de points provenant d'un campagne de mesure lidar dans une base de données postgresql/postgis et le taux d'insertion/sec est "abassourdissant". Bon il est vrai qu'il a fallu un peu "adapter" les paramètres du fichier de configuration postgresql.conf pour ces opérations d'insertions massives et il est aussi vrai que l'utilisation de la bonne méthode d'insertion est déterminante.Mais tout de même pour un GNU/Linux Debian 6.0.5 Squeeze 64 bit tournant dans une machine virtuelle kvm sur un host physique Proxmox "ça décoiffe sec" les amis.
Tout commence par la compilation de la dernière version de liblas la version 1.7.0 en ce moment.
Ensuite on rentre dans le vif du sujet je convertis mes fichiers las en csv avec l'utilitaire las2txt :
las2txt --parse MxyzcipnreadRGB --precision 3 3 3 3 -i 1243-14-d_color_ortho_r1.las -o 1243-14-d_color_ortho_r1.csv
Ensuite avec un wc -l 1243-14-d_color_ortho_r1.csv je découvre qu'il y a 43'362'150 lignes dans ce fichiers qui ressemble en gros à cela :
head 1243-14-d_color_ortho_r1.csv
0,536876.270,153160.410,476.670,13,4,65,1,1,0,-10,0,32,47,50
1,536876.510,153160.480,476.970,13,1,65,1,1,0,-10,1,34,49,49
2,536876.120,153160.640,477.010,13,3,65,1,1,0,-10,1,30,42,43
3,536875.530,153161.370,477.050,13,6,65,1,1,0,-10,0,36,51,53
4,536875.840,153161.250,477.550,13,12,65,1,1,0,-10,0,79,107,116
Il faudra une table pour réceptionner ces données je la crée à l'aide de la requête sql ci-après:
CREATE TABLE public.l3d_lidar_1243_14_d_color_ortho ( id integer NOT NULL DEFAULT 0, x double precision NOT NULL DEFAULT 0.0, y double precision NOT NULL DEFAULT 0.0, z double precision NOT NULL DEFAULT 0.0, classification smallint NOT NULL DEFAULT 0, intensity smallint DEFAULT 0, point_source_id smallint DEFAULT 0, number_of_returns smallint DEFAULT 0, return_number smallint DEFAULT 0, flightline_edge smallint DEFAULT 0, scan_angle_rank smallint DEFAULT 0, scan_direction smallint DEFAULT 0, red_color smallint DEFAULT 0, green_color smallint DEFAULT 0, blue_color smallint DEFAULT 0, CONSTRAINT pk_l3d_lidar_1243_14_d_color_ortho PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE public.l3d_lidar_1243_14_d_color_ortho OWNER TO postgres;Puis vient l'insertion dans la base de données que j'effectue en tant que postgres avec un "su postgres"
time psql -c "COPY l3d_lidar_1243_14_d_color_ortho FROM '/tmp/1243-14-d_color_ortho_r1.csv' USING DELIMITERS ',' CSV;" goeland
real 4m47.795s
user 0m0.024s
sys 0m0.008s
Ensuite je rajoute le champ géométrie et je l'alimente en créant un point avec les valeurs x,y
psql -c "SELECT AddGeometryColumn('','l3d_lidar_1243_14_d_color_ortho','the_geom',21781,'POINT',2);" goeland
time psql -c "UPDATE l3d_lidar_1243_14_d_color_ortho SET the_geom=ST_GeomFromText('POINT(' || x || ' ' || y || ')',21781);" goeland
qui me retourne :
UPDATE 43362150
real 11m43.924s
user 0m0.024s
sys 0m0.012s
finalement je crée les index dont le fameux index spatial gist sur le champ the_geomet je lance un VACUUM ANALYSE pour mettre a jours les statistiques
time psql -c "CREATE INDEX idx_gist_l3d_lidar_1243_14_d_color_ortho_the_geom ON l3d_lidar_1243_14_d_color_ortho USING gist(the_geom);" goeland
CREATE INDEX
real 13m17.049s
user 0m0.032s
sys 0m0.000s
time psql -c "CREATE INDEX idx_l3d_lidar_1243_14_d_color_ortho_classification ON l3d_lidar_1243_14_d_color_ortho USING btree(classification);" goeland
CREATE INDEX
real 1m35.588s
user 0m0.028s
sys 0m0.008s
time psql -c "CREATE INDEX idx_l3d_lidar_1243_14_d_color_ortho_intensity ON l3d_lidar_1243_14_d_color_ortho USING btree(intensity);" goeland
CREATE INDEX
real 4m8.840s
user 0m0.028s
sys 0m0.004s
time psql -c "CREATE INDEX idx_l3d_lidar_1243_14_d_color_ortho_z ON l3d_lidar_1243_14_d_color_ortho USING btree(z);" goeland
CREATE INDEX
real 3m50.480s
user 0m0.032s
sys 0m0.000s
time psql -c "VACUUM ANALYZE l3d_lidar_1243_14_d_color_ortho;" goeland
VACUUM
real 0m17.309s
user 0m0.036s
sys 0m0.000s
Commentaires