Vous êtes nouveau sur Developpez.com ? Créez votre compte ou connectez-vous afin de pouvoir participer !

Vous devez avoir un compte Developpez.com et être connecté pour pouvoir participer aux discussions.

Vous n'avez pas encore de compte Developpez.com ? Créez-en un en quelques instants, c'est entièrement gratuit !

Si vous disposez déjà d'un compte et qu'il est bien activé, connectez-vous à l'aide du formulaire ci-dessous.

Identifiez-vous
Identifiant
Mot de passe
Mot de passe oublié ?
Créer un compte

L'inscription est gratuite et ne vous prendra que quelques instants !

Je m'inscris !

Comparaison des performances de SIG (Système d'Information Géographique) : PostGreSQL/PostGIS vs SQL Server Spatial
Par Frédéric Brouard

Le , par MaitrePylos

0PARTAGES

12  1 
Comparatif sig et performances.



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)
.

Vous pouvez lire le comparatif ici : http://g-ernaelsten.developpez.com/t...-performances/

Et vous ?

Que pensez-vous de ce comparatif?
Utilisez-vous d’autres bases de données pour vos SIG? Lesquelles ?

Une erreur dans cette actualité ? Signalez-le nous !

Avatar de thewild
Nouveau membre du Club https://www.developpez.com
Le 27/04/2017 à 16:00
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://www.postgresql.org/docs/9.6/...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é.
6  1 
Avatar de thewild
Nouveau membre du Club https://www.developpez.com
Le 28/04/2017 à 13:43
Citation Envoyé par SQLpro Voir le message
Mais pour vous plaire
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.
4  0 
Avatar de thewild
Nouveau membre du Club https://www.developpez.com
Le 28/04/2017 à 12:15
Citation Envoyé par SQLpro Voir le message
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 :
Citation Envoyé par SQLpro Voir le message
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 ?
3  0 
Avatar de thewild
Nouveau membre du Club https://www.developpez.com
Le 28/04/2017 à 15:58
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).
4  1 
Avatar de csperandio
Membre habitué https://www.developpez.com
Le 27/04/2017 à 13:23
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" ?
2  0 
Avatar de eclesia
Rédacteur https://www.developpez.com
Le 27/04/2017 à 13:47
Dommage que SQL-Server ne respecte pas le standard pour les requetes SIG : ISO/IEC 13249-3 SQL/MM

+1 pour PostgreSQL
1  0 
Avatar de rupteur
Membre averti https://www.developpez.com
Le 28/04/2017 à 8:31
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.
2  1 
Avatar de SQLpro
Rédacteur https://www.developpez.com
Le 29/04/2017 à 7:32
Citation Envoyé par rupteur Voir le message
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.
Apparemment vous n'avez pas lu correctement le papier, car j'indique que je ferais ces tests à nouveau et sous Linux lorsque SQL Server disposera d'une version officielle sous Linux, ce qui ne saurait tarder... Les versions actuelles de SQL Server sous linux étant encore en béta...

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.

A +
1  0 
Avatar de alassanediakite
Membre expert https://www.developpez.com
Le 01/05/2017 à 20:30
Salut
Si...
Citation Envoyé par SQLpro Voir le message
Ce n'était pas le but du test. Donc, anecdotique...
Pourquois...
Citation Envoyé par SQLpro Voir le message
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...
@+
2  1 
Avatar de alassanediakite
Membre expert https://www.developpez.com
Le 02/05/2017 à 0:53
Salut
Citation Envoyé par SQLpro Voir le message
...alors que PostGreSQL ne sait pas transactionner les commandes DDL...
Avec pgAdmin 4 v1.4 (sortie le 13 avril!!!)
Code : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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.
@+
1  0