Postgresql et Ocaml: performances à l'insertion

Postgresql permet 2 types d'insertions: les insertions en pur SQL à l'aide d'un "insert", et les copies. À quel point les copies sont-elles plus efficaces que les insertions?

Voici quelques tests rapides via le mode immédiat d'Ocaml, en passant par le wrappeur Postgres. Ouvrons tout d'abord une connexion.

ocaml -I /usr/lib/ocaml/postgresql/ -I /usr/lib/ocaml/threads
# #load "unix.cma";;
# #load "threads.cma";;
# #load "bigarray.cma";;
# #load "postgresql.cma";;
# let connectionstring = "host=localhost dbname=Test";;
# let c = new Postgresql.connection ~conninfo:connectionstring ();;

Voici quelques fonctions de base qui servent respectivement à créer une séquence de a à b, et à mesurer le temps pour exécuter une fonction donnée. Notez que "seq" est un peu alambiquée pour permettre la tail-recursion (et donc de demander des sequences de 1M d'éléments sans dépassement de pile).

# let seq a b = 
   let rec do_seq a r = 
    if a > b then r else do_seq (a + 1) (a::r) 
   in List.rev(do_seq a []);;
# let time f e = 
   let t1 = Unix.gettimeofday() in f e; 
   let t2 = Unix.gettimeofday() in 
   t2 -. t1;;

Et enfin, les deux fonctions à tester, l'une faisant tourner n insertions dans une transaction, l'autre utilisant la copie.

# let run n = 
   ignore(c#exec "begin"); 
   List.iter 
    (fun e -> ignore(c#exec  
                     ~params:[|string_of_int e; "def"; "0.143"|] 
                     "insert into data values($1, $2, $3)")) 
    (seq 1 n); 
   ignore(c#exec "commit");;
# let batch n = 
   ignore(c#exec "copy data from stdin"); 
   List.iter 
    (fun e -> ignore(c#putline ((string_of_int e)^"\tdef\t0.143\n"))) 
    (seq 1 n); 
   ignore c#endcopy;;

Ces fonctions font strictement la même chose, c'est à dire insérer pour i de 1 à n, {i, "def", 0.143} dans une table ayant pour colonnes un entier, une chaîne, et un double.

L'on fait tourner...

# time run 100000;;
- : float = 13.3781638145446777
# time batch 100000;;
- : float = 0.458184957504272461

Ce qui nous donne un déjà très honorable 7500 insertions par seconde avec des insert, et un ahurissant 220000 insertions par seconde avec une copie. Les gains sont encore plus significatifs avec plus de lignes, la copie atteignant 240000 insertions par seconde pour 1 million d'éléments.

Notez tout de même que ma base est configurée avec fsync = off afin d'éviter les coûteuses synchronisations avec le disque. Quand bien même il est vrai qu'attendre que tout soit écrit aplanirait les différences, un cas d'utilisation standard d'insertions massives est la jointure avec une table temporaire pour faire un select, par exemple, pour lequel les écritures disques ne rentreront pas en compte.

Ce qui nous fait donc quand même une insertion par copie 30 fois plus rapide qu'une insertion en SQL pur.