D’Oracle à PostgreSQL...
Plus qu’au niveau du SGBDR c’est au niveau des développements applicatifs que les optimisations sont les plus spectaculaires. Ici mon travail consistait à migrer un batch de chargement d’une base de connaissance. Les tickets des 5 dernières années provenant des appels effectués au support sont quotidiennement chargés, organisés et indexés de manière à pouvoir effectuer des recherches sur le nom du produit, le client, la date etc. mais aussi de la recherche full text. Sont ainsi indexés le titre du ticket, la description du problème par le client, la solution qui lui avait été proposée ainsi que le suivi interne des échanges entre les différents niveaux de support. Il existe des moteurs d’indexation full text dédiés type Apache Lucene etc. mais pour traiter quelques dizaines de Go les capacité natives de PostgreSQL, Oracle ou SQL Server sont amplement suffisantes.
A l’origine Oracle Database XE était utilisé mais cette version gratuite d’Oracle Database ne permet de stocker que 11Go de données. Cette limite était atteinte alors même que les contenus longs étaient tronqués pour ne pas dépasser la limitation à 4000 octets du type VARCHAR2 en 11gR2. Le chargement prenait 1h30 et des essais avec Oracle Enterprise Edition donnaient le même temps. Individuellement chaque requête ne pouvait pas être optimisée. Les contraintes fixées étaient de faire travailler le moins possible la production transactionnelle tournant alors sous SQL Server 2000. Il fallait également charger l’intégralité des données à chaque fois de manière à simplifier la gestion. Les données de la base de connaissance ne devaient en effet pas nécessiter de sauvegardes. En cas de problème le serveur serait restauré avec une base vide et le traitement habituel serait relancé pour la réinitialiser.
Bref il ne fallait pas changer grand chose et pourtant après la migration vers PostgreSQL les temps d’interrogation de la base ont été meilleurs, notamment car Oracle XE ne peut utiliser qu’un CPU même sur une machine multiprocesseurs. Mais surtout le chargement a pu être bouclé en 20 minutes tout en récupérant l’intégralité des contenus dans des colonnes PostgreSQL de type TEXT, un VARCHAR sans limitation de taille. Alors PostgreSQL supérieur à Oracle Database ? C’est ce que j’aurais pu affirmer mais non ce n’est pas ça qui a permis de gagner 1h10.
Un coureur de semi-marathon court plus longtemps qu’un coureur de 5000m !
Zoomons sur la partie mise à jour de cette table telle qu’elle aurait été réalisée avec PostgreSQL en adaptant simplement le traitement requête par requête :
update TABLE_RECHERCHE set COL_X='CHAINE_X1' where COL_Y='CHAINE_Y1';
update TABLE_RECHERCHE set COL_X='CHAINE_X2' where COL_Y='CHAINE_Y2';
...
update TABLE_RECHERCHE set COL_X='CHAINE_Xn' where COL_Y='CHAINE_Yn';
update TABLE_RECHERCHE set COL_DOCUMENT=(setweight(to_tsvector(...),'A')
|| setweight(to_tsvector(...),'B')
|| ...
|| setweight(to_tsvector(...),'D'));
Quel est le problème de cette approche ? Elle oblige à balayer N fois la table TABLE_RECHERCHE alors que tout peut être fait dès sa création avec une requête de ce style :
Conclusion : tel spécialiste vous dira que l’insertion des données peut être plus rapide avec PostgreSQL qu’avec Oracle car le SGBDR ne fait pas de checksum par défaut et permet de désactiver complètement la journalisation. Tel autre vous dira que l’indexation avec Oracle 11gR2 est plus rapide qu’avec PostgreSQL avant la version 9.5. Mais tout cela ne compte pas beaucoup si le développeur fait des choix erronés. Ici les différentes mises à jour ne devaient pas faire partie du développement initial et, plutôt que de modifier le code de création de la table, le développeur avait ajouté des requêtes au fur et à mesure du besoin jusqu’à obtenir un résultat à la fois lent et coûteux en ressources.
create table TABLE_RECHERCHE as select
case when COL_Y = 'CHAINE_Y1' then 'CHAINE_X1'
when COL_Y = 'CHAINE_Y2' then 'CHAINE_X2'
...
when COL_Y = 'CHAINE_Yn' then 'CHAINE_Xn'
end as COL_X,
...,
(setweight(to_tsvector(...),'A') || setweight(to_tsvector(...),'B') || ... || setweight(to_tsvector(...),'D')) as COL_DOCUMENT
from ....
;
Les optimiseurs d’Oracle, de PostgreSQL ou de SQL Server travaillent requête par requête. Ils choisissent pour chaque requête le meilleur plan d’exécution en fonction des statistiques dont ils disposent. Ils peuvent éventuellement paralléliser l’exécution d’une requéte donnée si vous utilisez PostgreSQL-XL, PostgreSQL à partir de la version 9.6, Oracle Enterprise Edition ou SQL Server.
Cependant ils ne prendront jamais une initiative consistant à regrouper certaines opérations, à paralléliser certaines parties du traitement etc. Ils ne font en effet "que" ce que nous leur demandons et heureusement ! En aucun cas l’utilisation de tel ou tel SGBDR ne vous dispensera de réfléchir. Ici je n’ai même pas optimisé le chargement, sa durée pourrait encore être largement réduite mais elle a été jugée convenable et je ne suis pas allé plus loin.