Frédéric Brouard a réalisé un test de comparaison de performance des cartouches spatiales dans deux moteurs de base de données.
Le présent test consiste à comparer la performance d'une dizaine de requêtes identiques sur le plan logique, consacrées au traitement spatial des données entre PostgreSQL et Microsoft SQL Server dont le SIG de chacun propose les mêmes fonctionnalités du standard OGC.
Les requêtes ont porté sur les données spatiales disponibles librement auprès de l'État français en matière de géographie politique et routière (GEOFLA et ROUTE 500).
Bonjour,
je réagis ici car je suis assez choqué par le contenu de ce comparatif.
Comme csperandio l'a très justement fait remarquer, le seul intérêt de cet article est de comparer les configurations "out of the box" des deux serveurs, certainement pas leurs performances en situation de production.
L'utilisation de données spatiales en devient tout à fait anecdotique.
Pour information, la configuration par défaut de PostgreSQL est extrêmement conservatrice en terme de ressources (work_mem = 4MB, shared_buffers = 128MB). Totalement inadaptée à la manipulation de données spatiales.
Quelques minutes de recherche sur internet auraient suffit à l'auteur pour y trouver les paramètres à ajuster ainsi que les valeurs recommandées pour un environnement de production.
Je remarque aussi un très grand déséquilibre dans la maîtrise des deux SGBD.
Deux exemples au hasard :
Pour SQL Server, nous avons créé une base de nom DB_GEO en mode de récupération simple afin d'éviter une journalisation continue et sans purge des transactions.
Et pourquoi ne pas avoir fait de même pour PostgreSQL, qui journalise en continu par défaut ? Parce que l'auteur ne savait pas que c'était le cas, ni qu'il état possible de désactiver la journalisation dans PostgreSQL je suppose.
NOTA : la commande PG dump est bloquante, car elle pose un verrou de type lecture (AccessShareLock) pendant toute la durée de la sauvegarde, verrou qui peut ne pas être acquis si une transaction est en cours. Ceci empêche donc les sauvegardes à chaud contrairement à SQL Server qui effectue un snapshot de la base, ce qui n'empêche nullement des transactions en cours, ni le démarrage de nouvelles transactions au cours de la sauvegarde
C'est FAUX !! ACCESS SHARE n'est bloqué que par ACCESS EXCLUSIVE, qui n'est posé que par les requête de modification de structure de la base de données (DDL). Une telle affirmation sans vérification est assez scandaleuse ! Un minimum de lecture de la documentation de PostgreSQL serait la bienvenue (une simple recherche Google aussi, car cette documentation est très bien indexée). https://ww...t-locking.html
Et une autre remarque qui pourrait être risible si elle n'était aussi affligeante :
les commandes de sauvegardes et de restaurations de PostGreSQL sont très mal documentées et peu d'exemples montrent comment faire telle ou telle sauvegarde et encore moins, restauration. Nous avons perdu plus de quatre heures en tentatives diverses et essais infructueux avant de trouver la bonne syntaxe par tâtonnement.
4h ? Pour au final utiliser les paramètre par défaut de pg_dump ? --blobs est inclu par défaut, comme le précise la lecture de la documentation de pg_dump, lecture qui doit prendre 10 minutes au maximum. 4h de tâtonnement pour cela, ça laisse songeur, voire dubitatif...
Au mieux, pour la restauration, PostGreSQL met deux fois plus de temps que SQL Server en mode non compressé en utilisant huit threads.
Deux fois plus de temps pour une base de données journalisée contre une base de données non journalisée ? Quelle incroyable surprise !!!
Il serait de bon aloi que l'auteur corrige son article, ou mieux le réalise dans des conditions un tant soit peu proches de la réalité.
Mais ce n'est pas pour me plaire, c'est pour donner un intérêt à votre comparatif (dommage de n'avoir pas fait dès le départ, ça aurait évité ce genre d'échange).
Merci d'avoir fait ces changements, le test en est bien plus instructif.
Pour information et dans l'éventualité où vous effectueriez d'autres comparatifs de ce genre dans le futur, sachez qu'il est déconseillé de mettre une valeur aussi haute pour shared_buffers sous Windows. 512MB c'est en général le maximum recommandé.
Cela n'a pas d'importance dans le cas présent bien-sûr.
Une dernière question, qu'entendez-vous par "les fonctions PostGIS ne peuvent être utilisées directement comme expression logique d'un prédicat" ?
La syntaxe "WHERE ST_IsValid(thegeom) = false" est peu orthodoxe, on lui préfère en général "WHERE NOT ST_IsValid(thegeom)".
Pour ce qui est des résultats du test, je constate que PostgreSQL a encore beaucoup de progrès à faire dans la parallélisation de ses requêtes. Ceci est encore plus vrai pour PostGIS, ses fonctions ayant pour l'instant un coût par défaut qui ne permet pas au planificateur de requête de faire les bons choix.
Donc vous me faites un procès imbécile ! (J'en suis habitué de la part de la communauté PG qui voit toujours d'un mauvais œil toute critique envers PG !)
J'admets la critique, mais je remarque que vous évitez la principale qui vous est faite : l'utilisation de la configuration par défaut des deux SGBD.
Vous semblez être un spécialiste de SQL Server, il aurait été de bon aloi de demander de l'aide à un spécialiste de PostgreSQL (ce que je ne suis pas) pour configurer votre serveur de test.
Au passage, merci d'avoir pris la peine de répondre.
Edit :
Envoyé par SQLpro
Il est facile de démontrer le blocage et je vous invite à faire une maquette sur le sujet.
Je viens de faire un test. Je dumpe une base de donnée contenant une grosse table, et je peux sans problème en modifier le contenu. Seules les requêtes DDL sont bloquées. Pourriez-vous être plus spécifique ?
Je me permets de continuer la discussion.
Vous avez choisi de ne faire retourner que les plus grosses requêtes. C'est un choix arbitraire, les requêtes les plus longues ne sont pas forcément les plus exécutés. A l'inverse, les requêtes courtes peuvent être exécutées très souvent. Bref, pour comparer les serveurs, la différence se fait en ratio, pas en temps absolu.
J'ai exécuté toutes vos requêtes sur ma machine de développement. CPU légèrement moins bon en single thread (i3 2125), 8Go de RAM, un vieux SSD Kingston (je ne sais pas ce que ça vaut par rapport à un array RAID 10 de disques magnétiques, mais les tables doivent toutes être dans le cache).
Q1: 2700ms (deux fois moins que sur votre serveur, 5 fois plus que SQL Server)
Q2 : 86 ms (moitié moins que SQL Server sur votre serveur)
Q3 : même résultat que vous, la requête est CPU bound, nos processeurs semblent équivalent (10 fois plus lent que SQL Server)
Q4 : 6800ms, 30% mieux que vous, 3x plus rapide que SQL Server
Q5 : 30s, pas de changement
Q6 : gain x2 grace au parallélisme (2x plus lent que SQL Server). Notez que PostGIS a une fonction ST_ContainsProperly qui remplit exactement le rôle de NOT ST_Intersects(ST_Boundary(), ...). Exécution en 900ms avec cette fonction. Que donne SQL Server ?
Q7 : Idem que vous, mais réécrivez la requête avec un CTE "WITH D AS (SELECT CODE_DEPT, ST_Centroid(geom) AS GEOM FROM S_GEO.DEPARTEMENT)", et la requête est 4x plus rapide que sur SQL Server (71ms). Je me demande ce que vous obtenez avec ce CTE sur SQL Server.
Q8 : 196ms, gain de temps x2 (oui, les paramètres de configuration font une différence quand le serveur fait un quicksort en mémoire plutôt que sur le disque), un peu mieux que SQL Server...
Q9, avec le parallélisme activé (Workers Launched: 4) : 292ms, 2x mieux que SQL Server
La restauration prend 30s sur ma machine de développement. Nul doute que sur votre serveur, avec un maintenance_work_mem à une valeur correcte, vous obtiendrez des temps comparables à SQL Server.
La conclusion ne me parait plus aussi nette, même si sur certaines requêtes la différence en faveur de SQL Server est très grande (10x). Sur certaines elle est importante en faveur de PostgreSQL (3x ou 4x).
J'ai une interrogation: quelle configuration l'auteur a fait sur PostgreSQL ? et sur SQL Server ?
Par exemple, sur certaines requêtes, on signale que SQL Server fait du parallélisme à la différence de PostgreSQL. Mais la version 9.6.x n'active pas le parallélisme par défaut et certains paramètres de base sont assez limités.
A moins que le but était de montrer les performances des 2 serveurs "out of the box" ?
Je trouve dommage d'élaborer et de mettre en place des tests aussi évolués sans tenir compte des spécifications de chaque SGBD.
Déjà si SQL Server tourne sous Windows, pourquoi ne pas avoir installé PostgreSQL sous linux ?
Comme cité plus haut, les paramètres par défaut, limitent volontairement l'allocation des ressources, et une meilleure configuration aurait (peut être) équilibré la donne.
Pourquoi ne pas configurer SQL Server de façon optimale et demander à une personne compétente de faire de même avec PostgreSQL ?
La question n'est pas de savoir lequel des SGBD sortira vainqueur mais au moins, il y aurait une vision claire des performances de chacun.
Je trouve dommage d'élaborer et de mettre en place des tests aussi évolués sans tenir compte des spécifications de chaque SGBD.
Déjà si SQL Server tourne sous Windows, pourquoi ne pas avoir installé PostgreSQL sous linux ?
Comme cité plus haut, les paramètres par défaut, limitent volontairement l'allocation des ressources, et une meilleure configuration aurait (peut être) équilibré la donne.
Pourquoi ne pas configurer SQL Server de façon optimale et demander à une personne compétente de faire de même avec PostgreSQL ?
La question n'est pas de savoir lequel des SGBD sortira vainqueur mais au moins, il y aurait une vision claire des performances de chacun.
D'autre part, hormis le parallélisme qui peut jouer dans certains cas, le réglage par défaut de PG sous Windows, n'est pas un handicap pour ce test. En effet la faible taille des données de la base est compatible avec les réglages mémoire par défaut.
Ce n'était pas le but du test. Donc, anecdotique...
Pourquois...
Envoyé par SQLpro
Pour info et après différentes sources scrutées, voici les opérations bloquant la sauvegarde dans PostGreSQL :
Toutes opérations DDL : CREATE, ALTER et DROP
TRUNCATE TABLE
Le rafraichissement des vues matérialisées
Les opérations de maintenance d'index et de stats
les opérations de gestion du stockage
...
En sus, lorsque des transactions ont été démarrées au niveau d'isolation SERIALIZABLE avant de lancer la sauvegarde, les tables verrouillées ne figurent pas dans la sauvegarde (à confirmer).
Pour information toujours, aucune de ces opérations n'est bloquante pour SQL Server lors des sauvegardes.
A +
Je voudrais juste éclairer ceux qui un temps soit peu accordent de l'importance aux propos de QUI ON SAIT..
Par rapport au post précédent...
Ce que dit la doc de PostgreSQL sur les commandes bloquant la sauvegarde (à savoir avec dump)...
Les sauvegardes créées par pg_dump sont cohérentes, ce qui signifie que la sauvegarde représente une image de la base de données au moment où commence l'exécution de pg_dump. pg_dump ne bloque pas les autres opérations sur la base lorsqu'il fonctionne (sauf celles qui nécessitent un verrou exclusif, comme la plupart des formes d'ALTER TABLE.)
Pour le rafraichissement de vue matérialisée, la doc ne fait aucunement cas de blocage. Tout débutant en relationnel sait qu'un SELECT simple ne bloque pas un autre SELECT.
Pour la maintenance d'index: la doc est claire, REINDEX autorise la lecture donc le dump, DROP INDEX pose un verrou exclusif.
Ceci est valable pour la maintenance des statistiques qui ne font que le lire, donc pas de blocage pour la lecture.
Pour le nettoyage régulier, pas de blocage de lecture sauf avec l'option FULL
Mais MONSIEUR QUI ON SAIT n'accorde aucune crédibilité a la doc de PostgreSQL. Pire encore, il refuse l'orthographe officiel "PostgreSQL" en mettant toujours "PostGreSQL".
Par ailleurs, celui qui veut apprendre PostgreSQL, pose des questions, mais ne critique pas pour...
@+
begin transaction;
create table t1(c int primary key);
create table t2(c int primary key);
commit transaction;
--12 secs COMMIT Query returned successfully in 12 secs.
begin transaction;
create table t3(c int primary key);
drop table t2;
alter table t1 add column c1 char(2);
commit transaction;
--2 secs COMMIT Query returned successfully in 2 secs.
begin transaction;
alter table t3 add column c2 char(1);
insert into t1(c, c1) values(1,'aa');
commit transaction;
--1 secs COMMIT Query returned successfully in 1 secs.
begin transaction;
alter table t3 add column c3 char(1);
insert into t1(c, c1) values(2,'ba');
rollback transaction;
--1 secs ROLLBACK Query returned successfully in 1 secs.
Comparatif sig et performances.
Envoyé par SQLpro
