Migrer des LOB-segments d’Oracle vers PostGreSQL

Par Olivier JOLYDéveloppement, PHP, SQL, Support Niveau 3, Tuning
html

Depuis des années, ora2pg permet la migration de données d’Oracle vers PostGreSQL. Une des ombres au tableau de cet outil est le traitement des LOBs (Large OBjects) et plus particulièrement de BLOBs (Binary Large OBjects), qui même optimisé reste particulièrement lent.

J’ai récemment testé l’aventure sur une table contenant 8 millions de lignes pour 27 Go de BLOB-segments. Avec ora2pg l’extraction a pris, en utilisant le parallélisme que perl me permet sur un serveur de 4 vCPU et 32 Go de mémoire, près de 5 jours.

J’ai lancé la commande ora2pg avec les option -J 2 -j 4 et utilisé le paramétrage suivant :

DATA_LIMIT     1000
BLOB_LIMIT      300
CLOB_AS_BLOB    1
LONGREADLEN     1M
#LONGTRUNCOK    0 (default)
USE_LOB_LOCATOR  1
LOB_CHUNK_SIZE 512k

J’ai ensuite effectué l’export avec SQL*Plus, version 19 et tsv en 3 heures. Bien sûr il faut encore retraiter le fichier par la suite pour mettre ‘\\x’ devant chaque lob pour expliquer à PostGreSQL qu’on lui donne la valeur de ces BLOBs en hexadécimal. On verra plus loin que ce retraitement est assez rapide.

J’ai utilisé le paramétrage sqlplus (et le script) suivant 

col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set markup csv on delimiter "&TAB" quote off

set heading off termout off echo off verify off
set feed off timi off

set null \N
set numwidth 128
set long 1000000 longc 500000

set arraysize 5000
set rowprefetch 2500
set lobprefetch 32767

spool lob_table_data.tsv

select /*+ parallel(8) */ * from schema.lob_table ;

spool off
  • Les maximums pour long et longc[huncksize] sont 2 000 000 000 (donc on ne peut pas utiliser cette technique pour des lobs qui feraitent plus de deux milliards d’octets soit 1 907 Mo maximum). Dans ma table la taille maximum d’un blob était d’un million d’octet, j’ai donc utilisé cette valeur (c’est important d’utiliser la plus petite valeur possible)
  • Le maximum d’arraysize est 5000, on l’utilise on sait qu’on va remonter un maximum de données
  • Le maximum de numwidth est 128, on l’utilise aussi
  • Le maximum de lobprefetch est 32767; je sais que j’ai jusqu’à un million de caractères dans un lob …. je mets le maximum
  • Postgres, lorsd’un chargement direct depuis un fichier TSV reconnait \N comme valeur nulle, on le précise aussi

Pour le retraitement du fichier j’ai utilisé le code PHP suivant que j’ai lancé en ligne de commande.

<?php

$file=__DIR__ . '/lob_table_data.tsv' ;

$handle = fopen($file, "r");
$DEB="SET client_encoding TO 'UTF8';
SET synchronous_commit TO off;


COPY lob_table (id,lob) FROM STDIN;".PHP_EOL;

$FIN='\\.'.PHP_EOL;

echo 'BEGIN;'.PHP_EOL;
echo $DEB ;

$cpt=0;

while ( $cline = fgets($handle) )
{
  list ($k, $v) = explode(chr(9),$cline);
  echo $k.chr(9).'\\\\x'.$v;
  $cpt++;

  if ( $cpt == 300 ) {
    $cpt=0;
    echo $FIN.PHP_EOL.$DEB ;
  }
}

fclose($handle);

echo $FIN;
echo "COMMIT;";
?>

Au total une vingtaine de minutes pour retraiter les quelques 8 millions de lignes, donc même en ajoutant une heure pour transf&érer les fichiers du serveur de bases de données vers le serveur d’injection dans PostGreSQL on en a pour un grand maximum de 5 heures là où ora2pg avait pris prèsd de 5 jours. Un gain de temps non négligeable.

PS: Pour calculer la taille maximum d’un lob dans une table

With t as ( select dbms_lob.getlength(lob) sz from lob_table )
select max(sz) maxi
     , min(sz) mini
     , avg(sz) moyenne
     , round(sum(sz)/1024/1204/1204,2) Tot_Go
  from t ;