Vous n'êtes pas identifié(e).
Bonjour,
premier d'une longue série d'article sur le sujet, un comparatif entre SQL Server et PostGreSQL au sujet des performances des requêtes pour le DBA :
https://www.developpez.net/forums/d2101 … andes-dba/
Vos commentaires sont les bienvenus
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Bonjour SQLpro,
Merci pour votre article très intéressant qui montre que SQLserver est un SGBDR performant parmi les meilleurs des SGBDR propriétaires, il ne faut pas douter de ça.
Par contre je trouve que votre comparaison avec PostgreSQL est un peu biaisée par plusieurs choses :
- PostgreSQL fonctionne moins bien sur un serveur windows à cause de la gestion mémoire de cet OS (contrairement à sqlserver qui est optimisé pour cet OS). Il aurait fallu faire la comparaison SQLServer/windows, PostgreSQL/linux (ou pourquoi pas une comparaison sqlserver/linux vs postgresql/linux ?).
- la configuration PostgreSQL que vous appliquez aurait mérité un peu de parallélisme en configurant des parallel_workers
- en version 13, la vacuum des index est parallélisable.
Pour les commandes COPY, soyons indulgents avec postgresql : le moteur est encore jeune et toujours en cours d'amélioration, le parallélisme des processus de COPY arrive prochainement.
Et puis je suis sûr que si on creuse un peu, on trouverait des commandes SQL qui seraient plus rapides dans postgresql que dans sqlserver ;-) non ?
Dernière modification par ruizsebastien (02/02/2021 16:04:19)
Cordialement,
Sébastien.
Hors ligne
Pour les commandes COPY, soyons indulgents avec postgresql : le moteur est encore jeune et toujours en cours d'amélioration, le parallélisme des processus de COPY arrive prochainement.
Ben déjà c'est comparer des choux et des carottes, à moins que ça ait changé, en Sybase le BULK INSERT / BCP désactive par défaut les contraintes de la table. C'est pas les mêmes fonctionnements.
Autre exemple qu'on est dans la comparaison choux/carottes : Le gestion de l'isolation des transactions par défaut n'est pas du tout la même (verrouillage partagé/exclusif de pages par défaut en Sybase). En voici l'illustration simple :
1ère ligne : SQL-Server. Console de gauche, ouverture d'une transaction et UPDATE. Console de droite un simple SELECT : Il est en attente et restera en attente jusqu'au COMMIT du UPDATE.
2ème ligne : PgSQL. Console de gauche, ouverture d'une transaction et UPDATE. Console de droite un simple SELECT : Il renvoie les données parce que le UPDATE travaille sur son propre snapshot MVCC.
Bref, les deux fonctionnements ont chacun leur intérêt, mais on ne peut juste pas comparer.
Dernière modification par herve.lefebvre (02/02/2021 18:37:23)
Hors ligne
Bonjour,
Bonjour SQLpro,
Merci pour votre article très intéressant qui montre que SQLserver est un SGBDR performant parmi les meilleurs des SGBDR propriétaires, il ne faut pas douter de ça.
Par contre je trouve que votre comparaison avec PostgreSQL est un peu biaisée par plusieurs choses :
- PostgreSQL fonctionne moins bien sur un serveur windows à cause de la gestion mémoire de cet OS (contrairement à sqlserver qui est optimisé pour cet OS). Il aurait fallu faire la comparaison SQLServer/windows, PostgreSQL/linux (ou pourquoi pas une comparaison sqlserver/linux vs postgresql/linux ?).
je vous en prie, faites la preuve de ce que vous avancez. Pour ma part je n'ai jamais vu de différence significative à ce niveau... Vous avez tous les fichiers à votre disposition. Ayez le courage de me contredire avec des faits et non des mots. J'ai rarement vu un écart de plus de 8% sur quelques requêtes... Rien de plus !
- la configuration PostgreSQL que vous appliquez aurait mérité un peu de parallélisme en configurant des parallel_workers
- en version 13, la vacuum des index est parallélisable.
Aucune des requêtes que j'ai fait n'a montré dans son plan le moindre parallélisme en aucune manière. Raison pour laquelle je n'ai pas mentionné ce paramétrage que j'ai mis à 48 !
Le plan de requête de VACUUM n'est pas disponible contrairement à SQL Server qui permet de visualiser les plans de toutes les commandes... Mais si vous me dite qu'il a parallélisé, avec 48 cœurs de limite, alors c'est pathétique d'avoir un résultat de 30 fois plus lent... !
Pour les commandes COPY, soyons indulgents avec postgresql : le moteur est encore jeune et toujours en cours d'amélioration, le parallélisme des processus de COPY arrive prochainement.
C'est une bonne chose !
Et puis je suis sûr que si on creuse un peu, on trouverait des commandes SQL qui seraient plus rapides dans postgresql que dans sqlserver ;-) non ?
Oui, je vais publier d'autres articles, car c'est une série, et dans certains commandes PostGreSQL s'est révélé meilleur que SQL Server; ça reste néanmoins assez rare.
Là ou c'est le plus significatif c'est sur le spatial. Franchement 1/3 des requêtes PG sont plus rapide que SQL Server dans le benchmark qque j'avais effectué et qui mériterait d'être rafraichit avec les dernières version de deux SGBDR :
https://g-ernaelsten.developpez.com/tut … formances/
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Ben déjà c'est comparer des choux et des carottes, à moins que ça ait changé, en Sybase le BULK INSERT / BCP désactive par défaut les contraintes de la table. C'est pas les mêmes fonctionnements.
Tout d'abord Sybase et SQL Server sont deux produits différents. Plus de 30 ans de différence. PostGreSQL n'étais pas encore né, lorsque Microsoft a divergé de son achat de Sybase.
Mais vous avez partiellement raison sur ce point, cependant vous omettez de dire que la désactivation des contraintes de table ne concerne que :
1) les contraintes CHECK
2) les contraintes FOREIGN KEY
Les tables de notre tests n'ayant ni l'une ni l'autre, votre argument est donc NULL et non avenu !... Serait-ce du dénigrement ???
Autre exemple qu'on est dans la comparaison choux/carottes : Le gestion de l'isolation des transactions par défaut n'est pas du tout la même (verrouillage partagé/exclusif de pages par défaut en Sybase). ... bla bla bla ...
Ce dont vous parlez n'a aucun rapport et aucun intérêt avec les tests effectués dans cet article :
https://sqlpro.developpez.com/tutoriel/ … -pour-dba/
En effet :
1) Il n'y a aucune transaction explicite et aucun lot de plusieurs commandes passées simultanément. Donc votre démo ne sert a rien qu'a embrouiller les lecteurs;
2) Sybase n'est pas SQL Server... 30 ans de R&D d'écart. Comme si je disais PostGreSQL = Ingres !
3) SQL Server peut fairt du verrouillage optimiste de la même manière que Oracle ou PostGreSQL. C'est même conseillé dans la plupart des cas de figure !
Bref, commencez par lire, apprendre, vous former à SQL Server...
Mes sites :
https://sqlpro.developpez.com/
http://mssqlserver.fr/
Comme mon bouquin :
https://www.amazon.fr/SQL-Server-2014-a … 2212135920
peuvent vous y aider !
A +
Dernière modification par SQLpro (03/02/2021 13:04:48)
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Bonjour,
je vous en prie, faites la preuve de ce que vous avancez. Pour ma part je n'ai jamais vu de différence significative à ce niveau... Vous avez tous les fichiers à votre disposition. Ayez le courage de me contredire avec des faits et non des mots. J'ai rarement vu un écart de plus de 8% sur quelques requêtes... Rien de plus !
Bonjour, je n'ai pas de preuve la tout de suite car je ne travaille pas avec windows pour mes serveurs postgresql.
Par contre ce qui est sûr c'est qu'un postgresql installé sur windows demande des ajustements particuliers par rapport à une installation linux.
Avez-vous, pour votre benchmark, positionné les paramètres "shared_memory_type" et "dynamic_shared_memory_type" à la valeur "windows" ?
Avez-vous assigné le droit « Verrouiller les pages en mémoire » (Lock Pages in Memory) à l'utilisateur Windows qui fait tourner PostgreSQL ?
Il faut penser à mettre une valeur de max_wal_size suffisante, par rapport à shared_buffers, pour (dixit la doc) : étendre dans le temps les écritures de grandes quantités de données, nouvelles ou modifiées.
Si j'ai le temps, je referais vos benchmarks sur un linux, même si je n'aurais pas une machine avec 128G de ram tout de suite à disposition et que ce sera plutôt du PGS11.
Cordialement,
Sébastien.
Hors ligne
...
Avez-vous, pour votre benchmark, positionné les paramètres "shared_memory_type" et "dynamic_shared_memory_type" à la valeur "windows" ?
Non, car cela est fait automatiquement dans l'installeur de PG 13 pour Windows. Donc ces deux paramètres ont bien la valeur "windows"
Avez-vous assigné le droit « Verrouiller les pages en mémoire » (Lock Pages in Memory) à l'utilisateur Windows qui fait tourner PostgreSQL ?
Ce paramètre est très controversé. En tant que membre d'un groupe d'expert Microsoft SQL Server, nous ne recommandons pas ce paramétrage qui n'a pas d'influence significative au niveau des performances et possède de gros risques. Il n'est donc activé pour aucun process que ce soit PostGreSQL ou SQL Server...
Il faut penser à mettre une valeur de max_wal_size suffisante, par rapport à shared_buffers, pour (dixit la doc) : étendre dans le temps les écritures de grandes quantités de données, nouvelles ou modifiées.
max_wal_size est à 1 Go (1024 MB). Je n'ai pas réglé ce paramètre, pour PostGreSQL. En le passant à 10 Go et en le testant sur différentes requêtes, je n'ai vu aucune différence ce qui est bien normal car les écritures des CHECKPOINT sont asynchrones...
Peut être faudrait-il régler les min_wal_size qui est actuellement de 80 Mo...
Si j'ai le temps, je referais vos benchmarks sur un linux, même si je n'aurais pas une machine avec 128G de ram tout de suite à disposition et que ce sera plutôt du PGS11.
Volontiers mais pensez à les publier sur developpez.com puisque l'article original est sur ce média...
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Bonjour,
Profitant d'un petit temps, je viens juste de reproduire votre "test" sous Linux, sur ma machine perso. Elle est plus modeste et les données sont stockées sur un SSD en SATA, donc pas l'idéal.
Config matérielle :
AMD Ryzen 7 3700X 8-Core Processor
32 Go de RAM
Kernel Linux sans tuning particulier: MD Ryzen 7 3700X 8-Core Processor
La config de PostgreSQL 13 est un tunée :
postgres=# SELECT name, setting, unit FROM pg_settings WHERE source <> 'default' AND sourcefile = '/home/thomas/postgresql/v13/pgdata/postgresql.conf';
name | setting | unit
------------------------------+-------------------------+------
checkpoint_completion_target | 0.9 |
checkpoint_flush_after | 128 | 8kB
effective_io_concurrency | 1000 |
maintenance_io_concurrency | 1000 |
maintenance_work_mem | 1048576 | kB
max_wal_size | 2048 | MB
min_wal_size | 80 | MB
shared_buffers | 524288 | 8kB
wal_buffers | 16384 | 8kB
work_mem | 32768 | kBVous remarquerez donc l'existence d'un paramètre maintenance_work_mem.
Voici ce que j'obtiens. Le COPY me prend 15 secondes. Pour le VACUUM FULL, il prend 9 secondes avant création des index et 41 secondes après leur création. L'INSERT/SELECT passe à 20 secondes. Les collectes de stats sont plus rapides que sur SQL Server.
Ah oui, en parlant du VACUUM FULL. Depuis la version 8.4 sortie en l'an de grâce 2009, le VACUUM FULL reconstruit intégralement les index. Je ne comprends pas la pertinence de passer un REINDEX derrière, à part pour être sûr de biaiser le résultat ou de démontrer que vous n'êtes pas pertinents pour parler de PostgreSQL (je n'ai rien à dire quant à SQL Server, je ne connais pas ce SGBD).
Dans tous les cas, quand on vous dit que PostgreSQL est plus performance sous Linux, il serait peut-être temps de réaliser que ce n'est pas pour rien.
test=# \timing
Chronométrage activé.
test=# CREATE TABLE T_PERSONNE_PRS
(PRS_ID SERIAL PRIMARY KEY,
PRS_NOM VARCHAR(32),
PRS_PRENOM VARCHAR(25));
Temps : 25,752 ms
test=# CREATE COLLATION fr_ci_ai
(provider = icu,
locale = 'fr-u-ks-level1-kc-false',
deterministic=false);
Temps : 12,895 ms
test=# CREATE TABLE t_personne_ci_ai_prs
(prs_id INT PRIMARY KEY,
prs_nom VARCHAR(32) COLLATE fr_ci_ai,
prs_prenom VARCHAR(25) COLLATE fr_ci_ai);
Temps : 23,535 ms
test=# COPY T_PERSONNE_PRS
(PRS_ID, PRS_NOM , PRS_PRENOM)
FROM '/home/thomas/postgresql/v13/personnes.txt'
WITH (DELIMITER ',',
ENCODING 'WIN1252');
Durée : 15257,902 ms (00:15,258)
test=# VACUUM FULL t_personne_prs;
Durée : 8950,434 ms (00:08,950)
test=# CREATE INDEX X_1
ON T_PERSONNE_PRS (PRS_NOM);
Durée : 7126,100 ms (00:07,126)
test=# CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);
Durée : 7526,953 ms (00:07,527)
test=# CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);
Durée : 13931,502 ms (00:13,932)
test=# VACUUM FULL t_personne_prs;
Durée : 41881,512 ms (00:41,882)
test=# INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;
Durée : 20092,208 ms (00:20,092)
test=# ANALYZE T_PERSONNE_PRS (prs_nom);
Temps : 129,512 ms
test=# ANALYZE T_PERSONNE_PRS (prs_nom, prs_prenom);
Temps : 149,290 msThomas Reiss
Hors ligne
Oups, je n'ai qu'un degré de parallélisme de 2 pour les opérations de maintenance. Passons le à 8 :
(index droppés)
test=# SET max_parallel_maintenance_workers = 8;
test=# \timing
Chronométrage activé.
test=# CREATE INDEX X_1
ON T_PERSONNE_PRS (PRS_NOM);
CREATE INDEX
Durée : 4818,862 ms (00:04,819)
test=# CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);
CREATE INDEX
Durée : 5000,068 ms (00:05,000)
test=# CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);
CREATE INDEX
Durée : 11537,936 ms (00:11,538)
test=# VACUUM FULL t_personne_prs ;
VACUUM
Durée : 36408,680 ms (00:36,409)Thomas Reiss
Hors ligne
Dans tous les cas, quand on vous dit que PostgreSQL est plus performance sous Linux, il serait peut-être temps de réaliser que ce n'est pas pour rien.
J'ai exécuté les 2 sous Linux (Ryzen 3600, 16 Go RAM, HDD Barracuda 1To, sql-server 15.0.4083.2-15 ; postgresql 12.5-0ubuntu0.20.04.1)
BULK INSERT / COPY FROM : 28791 ms. / 14788,543 ms
rebuild / Vacuum + reindex : 13447 ms / 17958,908 ms + 6405,497 ms
CREATE INDEX X_1 : 7122 ms / 12037,500 ms
CREATE INDEX X_2 : 7105 ms / 11791,703 ms
CREATE INDEX X_3 : 14236 ms / 19126,317 ms
TABLE REBUILD / VACUUM FULL : 13925 ms / 56013,197 ms
INDEX REBUILD / REINDEX : 21529 ms / 48182,653 ms
INSERT SELECT : 34958 ms / 18661,224 ms
UPDATE STATISTICS X1 / ANALYZE : 411 ms / 149,122 ms
UPDATE STATISTICS X2 / ANALYZE : 512 ms / 170,565 ms
sp_updatestats / ANALYZE : 382 ms / 508,040 msL'ensemble des scripts et rapports d'exécution sont disponibles ici : http://dl.free.fr/voQm8nbdO
Les seules modif apportées sont la suppression de CODEPAGE = 'ACP' lors de l'import (non supporté sous Linux par sql-server) et l'ajout de ALTER SYSTEM SET max_parallel_maintenance_workers = 8; sur pgSQL.
Pour postgres, j'ai laissé le paramétrage à 120 Go de RAM sur ma machine à 16 Go (!) mais en fait pg ne dépasse pas les 5 Go pendant l'exécution du script.
Hors ligne
Dans tous les cas, quand on vous dit que PostgreSQL est plus performance sous Linux, il serait peut-être temps de réaliser que ce n'est pas pour rien.
Bon et bien, SQLpro, vous vouliez des preuves (sur la gestion de la mémoires de PostgreSQL sous windows), les 2 benchs ci-dessus parlent pour eux.
Pour éviter que les trolls se multiplient, je pourrais dire que, d'expérience, les 2 SGBDR sont performants chacun à leur manière.
Dans mon entreprise, nous avons les 3 grands SGBDR du marché : Oracle SQLServer et PostgreSQL.
Ils ont chacun leurs bon côtés, mais il faut avouer que d'un point de vue business, les coûts astronomiques en licences des SGBDR propriétaires sont un frein très important.
C'est pourquoi les SI se penchent de plus en plus vers PostgreSQL qui en plus de réduire de manière conséquente les coûts, permet d'avoir des performances au moins aussi bonnes que les autres. En plus la philosophie du monde du libre et de la communauté est franchement rafraîchissante comparée aux hotlines et autres système de ticketing des mastodontes.
Cordialement,
Sébastien.
Hors ligne
Hervé, sur le "rebuild / vacuum + reindex", c'est un VACUUM FULL ou un VACUUM simple ? parce qu'un VACUUM FULL intègre le REINDEX.
Guillaume.
Hors ligne
Hervé, sur le "rebuild / vacuum + reindex", c'est un VACUUM FULL ou un VACUUM simple ? parce qu'un VACUUM FULL intègre le REINDEX.
VACUUM FULL.
J'ai repris à la lettre les requêtes de https://sqlpro.developpez.com/tutoriel/ … -pour-dba/
Hors ligne
OK, c'était juste pour confirmation. Merci.
Guillaume.
Hors ligne
re bonjour,
Vos conditions de test appliquées à SQL Server divergent fortement de ce que j'ai donnée, et surtout, de la pratique. En effet, dans SQL Server, comme dans Oracle il est d'usage de dimensionner le stockage des données et du journal de transaction. Peut être ne le saviez vous pas ? Mais cela m'étonnerais vu que vous me dites que vous avez de l'Oracle et du SQL Server dans votre entreprise...
C'est pourquoi j'ai indiqué clairement dans l'article que la base avait été créée comme suit :
* fichier de données: 12 Gb ;
* fichier du journal de transaction : 8 Gb.
Votre script en free drive ne montre pas cela...
CREATE DATABASE test ON (NAME = test_ds, FILENAME = '/home/aegir/sqlserver/test_ds.dbf');
GOCela rejaillis immanquablement sur certains résultats, comme justement l'insertion depuis un fichier (BULK INSERT) ou l'insertion depuis une table (INSERT SELECT). Merci de refaire vos tests en appliquant de réglage ! Sans cela, l'autocroissance des fichiers (qui est à proscrire) va prendre du temps au détriment de l'opération...
Sur le VACUUM FULL, vous avez raison, mais le moins que l'on puisse dire c'est que la documentation n'est pas claire. Il faut lire toute la page pour s'en rendre compte.
1) l'entête de la doc précise au niveau du VACUUM FULL "rewrites the entire contents of the table" et donc ne parle pas des index.
2) Le paragraphe spécifique au VACUUM FULL indique que c'est la table qui est visée et ne parle pas des index...
Il faut aller lire l'entrée INDEX_CLEANUP pour vois que les index aussi sont pris en compte !
https://www.postgresql.org/docs/current/sql-vacuum.html
Bref, la documentation est à améliorer sérieusement !
J'avoue avoir raté ce point et je pense ne pas être le seul...
Cependant... VACUUM FULL ne fait pas la même chose que REINDEX TABLE... ou alors c'est que la doc est catastrophique dans ses explications...
En effet, VACUUM FULL ne fait que retirer les lignes fantômes, tandis que REINDEX répare la fragmentation.
1) Nulle part il n'est fait mention de la défragmentation d'index (appelés "bloat" dans PostGreSQL) dans la doc du VACUUM.
2) nulle part il n'est fait mention de la suppression des lignes fantômes (appelées "dead tuples" dans PostGreSQL) dans la doc de REINDEX
Je persiste donc à dire qu'il faut faire les deux ! Et que cela correspond exactement au ALTER TABLE ... REBUILD / ALTER INDEX ALL ... REBUILD de SQL Server qui reconstruit la table, ses index et recalcule les statistiques.
Vous dites :
"Dans tous les cas, quand on vous dit que PostgreSQL est plus performance sous Linux, il serait peut-être temps de réaliser que ce n'est pas pour rien."
Mais il n'y a aucune démo de cela dans vos tests.... c'est de l'affirmation gratuite sans intérêt et c'est dommage de vous dévaloriser alors que vous menez des tests intéressants.
Vous avez raison sur le fait que la maintenance nécessite un "max_parallel_maintenance_workers" plus élevé que 2. Je referais mes tests avec un 48...
Bref, d'après vos résultats et si l'on enlève les deux insertions, PostGreSQL ne fait mieux que sur les recalculs de statistiques... Cependant, vous ne donnez pas la mesure de l'échantillon statistique utilisé par PG... Quel est-il ? Car là j'ai un sérieux doute sur la grandeur de l'échantillon.... Autrement dit cet échantillon est-il comparable en terme de pourcentage à celui de SQL Server ?
Enfin vos tests sont realisés sur un PC de bureau doté de 8 cœurs. Dans la réalité de l'exploitation des bases de données dans le monde de l'entreprise, il est rare, si l'on veut servir de nombreux utilisateurs et avoir des performances, d'être aussi limité. C'est pourquoi j'ai travaillé sur une machine à 24 cœurs physiques ce qui avec l'hyperthreading donne 48 coeurs exploitables par le SGBDR.
Si l'on divise alors les temps de réponse de SQL Server par un facteur approchant (48 / 8 = 6, mais je ne retiendrais que 5 de fait de la Loi d'Amdhal), alors, nous devrions retomber sur des différences aussi significatives que celles que j'ai donné dans mon article initial...
Test SQL Server pondéré // PostGreSQL Gain (SQL Server)
---------------------------|--------------------------|-----------|--------------------
rebuild / Vacuum + reindex : 13 447 ms / 5 = 2 689 ms | 24 364 ms | 9 fois plus rapide
CREATE INDEX X_1 : 7 122 ms / 5 = 1 424 ms | 12 038 ms | 8 fois plus rapide
CREATE INDEX X_2 : 7 105 ms / 5 = 1 421 ms | 11 792 ms | 8 fois plus rapide
CREATE INDEX X_3 : 14 236 ms / 5 = 2 847 ms | 19 126 ms | 7 fois plus rapide
TBL REBUILD / VACUUM FULL : 13 925 ms / 5 = 2 785 ms | 56 014 ms | 20 fois plus rapide
INDEX REBUILD / REINDEX : 21 529 ms / 5 = 4 306 ms | 48 183 ms | 11 fois plus rapide
UPDATE STATS X1 / ANALYZE : 411 ms / 5 = 82 ms | 149 ms | 2 fois plus rapide
UPDATE STATS X2 / ANALYZE : 512 ms / 5 = 102 ms | 171 ms | 2 fois plus rapide
sp_updatestats / ANALYZE : 382 ms / 5 = 76 ms | 508 ms | 7 fois plus rapideMais il ne s'agit là que d'une extrapolation !
A +
Dernière modification par SQLpro (03/02/2021 21:06:57)
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
...
Dans mon entreprise, nous avons les 3 grands SGBDR du marché : Oracle SQLServer et PostgreSQL.
Ils ont chacun leurs bon côtés, mais il faut avouer que d'un point de vue business, les coûts astronomiques en licences des SGBDR propriétaires sont un frein très important.
C'est pourquoi les SI se penchent de plus en plus vers PostgreSQL qui en plus de réduire de manière conséquente les coûts, permet d'avoir des performances au moins aussi bonnes que les autres. En plus la philosophie du monde du libre et de la communauté est franchement rafraîchissante comparée aux hotlines et autres système de ticketing des mastodontes.
Alors pourquoi votre entreprise n'est elle pas sur du tout PostGreSQL ? Pourquoi n'a t-elle pas abandonné Oracle et SQL Server ?
Mais il y a beaucoup de fonctionnalité que PG ne possède pas qui font souvent la différence...
Quand aux coût astronomiques, je pense que vous parlez d'Oracle... Une licence SQL Server pour faire un site web de commerce c'est quelques dizaines d'euros par mois en SPLA...
Quand à la philosophie, dans un monde économique, elle pèse peu (hélas...). Les entreprises préfèrent payer et avoir un service, plutôt que des discussions philosophiques ! Et on peut les comprendre !
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Cependant... VACUUM FULL ne fait pas la même chose que REINDEX TABLE
Faux. VACUUM FULL reconstruit la table et les index.
ou alors c'est que la doc est catastrophique dans ses explications...
Je ne dirais pas catastrophique mais elle pourrait être améliorée sur ce point en effet.
Je persiste donc à dire qu'il faut faire les deux !
Vous avez tout à fait le droit de persister à faire quelque chose d'inutile. Ça ne vous donne pas raison pour autant. Un VACUUM FULL sur une table reconstruit la table et ses index.
Guillaume.
Hors ligne
@SqlPro: Avez-vous noté que j'ai aussi augmenté le paramètre maintenance_work_mem à 1Go ? Vous ne l'avez pas augmenté dans votre test, et ça va vous aider à éviter de passer par un tri sur disque lors de la création des index par exemple.
Thomas Reiss
Hors ligne
Alors pourquoi votre entreprise n'est elle pas sur du tout PostGreSQL ? Pourquoi n'a t-elle pas abandonné Oracle et SQL Server ?
Bonjour,
alors là désolé mais voilà une vision bien naïve du monde de l'entreprise.
Vous savez bien que certains éditeurs de progiciels impose le SGBDR et puis il y a aussi l'historique (quand vous adoptez par exemple Oracle il y a 10 ans sur un projet de coeur de métier, il est très difficile voir impossible de faire machine arrière. Parfois les DSI préfèrent payer des licences exorbitantes plutôt que de tout changer au risque de casser leur outil de production).
Cordialement,
Sébastien.
Hors ligne
Cela rejaillis immanquablement sur certains résultats, comme justement l'insertion depuis un fichier (BULK INSERT) ou l'insertion depuis une table (INSERT SELECT). Merci de refaire vos tests en appliquant de réglage ! Sans cela, l'autocroissance des fichiers (qui est à proscrire) va prendre du temps au détriment de l'opération...
J'ai donc modifié le script avec :
CREATE DATABASE test ON (NAME = test_ds, FILENAME = '/home/aegir/sqlserver/test_ds.dbf',SIZE = 12 GB) LOG ON (NAME = test_log, FILENAME = '/home/aegir/sqlserver/test_log.lbf',SIZE = 8 GB);rejoué avec :
sqlcmd -e -S localhost -U SA -P '******' < test_ms_2.sql | tee test_ms_2.logpuis fait un "diff test_ms.log test_ms_2.log" pour voir les différences (spoiler: c'est plus lent, je m'attendais à ce que ça ne change rien).
3c3
< CREATE DATABASE test ON (NAME = test_ds, FILENAME = '/home/aegir/sqlserver/test_ds.dbf');
---
> CREATE DATABASE test ON (NAME = test_ds, FILENAME = '/home/aegir/sqlserver/test_ds.dbf',SIZE = 12 GB) LOG ON (NAME = test_log, FILENAME = '/home/aegir/sqlserver/test_log.lbf',SIZE = 8 GB);
20c20
< , Temps UC = 2 ms, temps écoulé = 4 ms.
---
> , Temps UC = 1 ms, temps écoulé = 3 ms.
30c30
< , Temps UC = 1 ms, temps écoulé = 2 ms.
---
> , Temps UC = 0 ms, temps écoulé = 2 ms.
40c40
< , Temps UC = 5 ms, temps écoulé = 7 ms.
---
> , Temps UC = 1 ms, temps écoulé = 7 ms.
43c43
< , Temps UC = 18062 ms, temps écoulé = 28791 ms.
---
> , Temps UC = 18404 ms, temps écoulé = 30074 ms.
48c48
< , Temps UC = 2 ms, temps écoulé = 3 ms.
---
> , Temps UC = 5 ms, temps écoulé = 5 ms.
57c57
< , Temps UC = 11252 ms, temps écoulé = 13447 ms.
---
> , Temps UC = 10071 ms, temps écoulé = 5187 ms.
65c65
< , Temps UC = 48874 ms, temps écoulé = 7122 ms.
---
> , Temps UC = 48027 ms, temps écoulé = 7458 ms.
73c73
< , Temps UC = 50183 ms, temps écoulé = 7105 ms.
---
> , Temps UC = 50369 ms, temps écoulé = 14391 ms.
81c81
< , Temps UC = 65178 ms, temps écoulé = 14236 ms.
---
> , Temps UC = 64264 ms, temps écoulé = 8594 ms.
89c89
< , Temps UC = 27089 ms, temps écoulé = 7088 ms.
---
> , Temps UC = 27612 ms, temps écoulé = 8930 ms.
97c97
< , Temps UC = 21356 ms, temps écoulé = 2804 ms.
---
> , Temps UC = 21911 ms, temps écoulé = 7682 ms.
105c105
< , Temps UC = 21819 ms, temps écoulé = 2259 ms.
---
> , Temps UC = 21776 ms, temps écoulé = 6097 ms.
113c113
< , Temps UC = 42260 ms, temps écoulé = 8464 ms.
---
> , Temps UC = 42991 ms, temps écoulé = 8182 ms.
121c121
< , Temps UC = 29314 ms, temps écoulé = 13925 ms.
---
> , Temps UC = 28773 ms, temps écoulé = 3566 ms.
124c124
< , Temps UC = 120515 ms, temps écoulé = 21529 ms.
---
> , Temps UC = 121024 ms, temps écoulé = 32563 ms.
129c129
< , Temps UC = 0 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 1 ms.
132c132
< , Temps UC = 14694 ms, temps écoulé = 34958 ms.
---
> , Temps UC = 15314 ms, temps écoulé = 39729 ms.
141c141
< , Temps UC = 1954 ms, temps écoulé = 411 ms.
---
> , Temps UC = 1922 ms, temps écoulé = 370 ms.
148c148
< , Temps UC = 2351 ms, temps écoulé = 512 ms.
---
> , Temps UC = 2362 ms, temps écoulé = 456 ms.
154c154
< , Temps UC = 54 ms, temps écoulé = 213 ms.
---
> , Temps UC = 61 ms, temps écoulé = 138 ms.
192c192
< , Temps UC = 1 ms, temps écoulé = 2 ms.
---
> , Temps UC = 3 ms, temps écoulé = 2 ms.
194c194
< , Temps UC = 1 ms, temps écoulé = 1 ms.
---
> , Temps UC = 0 ms, temps écoulé = 1 ms.
710c710
< , Temps UC = 0 ms, temps écoulé = 0 ms.
---
> , Temps UC = 4 ms, temps écoulé = 0 ms.
918c918
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
1150c1150
< , Temps UC = 0 ms, temps écoulé = 0 ms.
---
> , Temps UC = 4 ms, temps écoulé = 0 ms.
1345c1345
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
1719c1719
< , Temps UC = 0 ms, temps écoulé = 0 ms.
---
> , Temps UC = 4 ms, temps écoulé = 0 ms.
1804c1804
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
1826c1826
< [PK__T_PERSON__218D9B38FD2A34CB], la mise à jour n'est pas nécessaire...
---
> [PK__T_PERSON__218D9B386A3EEC66], la mise à jour n'est pas nécessaire...
2007c2007
< , Temps UC = 6 ms, temps écoulé = 6 ms.
---
> , Temps UC = 4 ms, temps écoulé = 4 ms.
2020c2020
< , Temps UC = 357 ms, temps écoulé = 119 ms.
---
> , Temps UC = 353 ms, temps écoulé = 125 ms.
2023,2024c2023,2024
< , Temps UC = 357 ms, temps écoulé = 120 ms.
< [PK__T_PERSON__218D9B38B4149688] a été mis à jour...
---
> , Temps UC = 353 ms, temps écoulé = 125 ms.
> [PK__T_PERSON__218D9B38EC34EBF0] a été mis à jour...
2089c2089
< , Temps UC = 0 ms, temps écoulé = 1 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
2199c2199
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
2309c2309
< , Temps UC = 0 ms, temps écoulé = 0 ms.
---
> , Temps UC = 4 ms, temps écoulé = 0 ms.
2529c2529
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
2730c2730
< , Temps UC = 454 ms, temps écoulé = 382 ms.
---
> , Temps UC = 2362 ms, temps écoulé = 456 ms.
154c154
< , Temps UC = 54 ms, temps écoulé = 213 ms.
---
> , Temps UC = 61 ms, temps écoulé = 138 ms.
192c192
< , Temps UC = 1 ms, temps écoulé = 2 ms.
---
> , Temps UC = 3 ms, temps écoulé = 2 ms.
194c194
< , Temps UC = 1 ms, temps écoulé = 1 ms.
---
> , Temps UC = 0 ms, temps écoulé = 1 ms.
710c710
< , Temps UC = 0 ms, temps écoulé = 0 ms.
---
> , Temps UC = 4 ms, temps écoulé = 0 ms.
918c918
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
1150c1150
< , Temps UC = 0 ms, temps écoulé = 0 ms.
---
> , Temps UC = 4 ms, temps écoulé = 0 ms.
1345c1345
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
1719c1719
< , Temps UC = 0 ms, temps écoulé = 0 ms.
---
> , Temps UC = 4 ms, temps écoulé = 0 ms.
1804c1804
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
1826c1826
< [PK__T_PERSON__218D9B38FD2A34CB], la mise à jour n'est pas nécessaire...
---
> [PK__T_PERSON__218D9B386A3EEC66], la mise à jour n'est pas nécessaire...
2007c2007
< , Temps UC = 6 ms, temps écoulé = 6 ms.
---
> , Temps UC = 4 ms, temps écoulé = 4 ms.
2020c2020
< , Temps UC = 357 ms, temps écoulé = 119 ms.
---
> , Temps UC = 353 ms, temps écoulé = 125 ms.
2023,2024c2023,2024
< , Temps UC = 357 ms, temps écoulé = 120 ms.
< [PK__T_PERSON__218D9B38B4149688] a été mis à jour...
---
> , Temps UC = 353 ms, temps écoulé = 125 ms.
> [PK__T_PERSON__218D9B38EC34EBF0] a été mis à jour...
2089c2089
< , Temps UC = 0 ms, temps écoulé = 1 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
2199c2199
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
2309c2309
< , Temps UC = 0 ms, temps écoulé = 0 ms.
---
> , Temps UC = 4 ms, temps écoulé = 0 ms.
2529c2529
< , Temps UC = 4 ms, temps écoulé = 0 ms.
---
> , Temps UC = 0 ms, temps écoulé = 0 ms.
2730c2730
< , Temps UC = 454 ms, temps écoulé = 382 ms.
---
> , Temps UC = 449 ms, temps écoulé = 309 ms.Mais bon, comme je l'ai dit plus haut, je pense qu'on est dans la comparaison entre choux et carottes, de plus tester sur une machine à 120 Go (ou 16 Go) de RAM un RDBMS avec une table qui a moins de 2Go de datas, AMHA ça benche plus la gestion de cache de l'OS que le RDBMS lui-même.
Dernière modification par herve.lefebvre (04/02/2021 10:34:51)
Hors ligne
J'aimerais bien avoir votre paramétrage d'instance SQL Server car quel chose me chiffonne:
SELECT name, value_in_use FROM sys.configurationsA +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
J'aimerais bien avoir votre paramétrage d'instance SQL Server car quel chose me chiffonne:
SELECT name, value_in_use FROM sys.configurationsA +
1> SELECT name ,cast(value_in_use as nvarchar(16)) as value FROM sys.configurations
2> go
SELECT name ,cast(value_in_use as nvarchar(16)) as value FROM sys.configurations
name value
----------------------------------- ----------------
recovery interval (min) 0
allow updates 0
user connections 0
locks 0
open objects 0
fill factor (%) 0
disallow results from triggers 0
nested triggers 1
server trigger recursion 1
remote access 1
default language 2
cross db ownership chaining 0
max worker threads 0
network packet size (B) 4096
show advanced options 0
remote proc trans 0
c2 audit mode 0
default full-text language 1033
two digit year cutoff 2049
index create memory (KB) 0
priority boost 0
remote login timeout (s) 10
remote query timeout (s) 600
cursor threshold -1
set working set size 0
user options 0
affinity mask 0
max text repl size (B) 65536
media retention 0
cost threshold for parallelism 5
max degree of parallelism 0
min memory per query (KB) 1024
query wait (s) -1
min server memory (MB) 16
max server memory (MB) 2147483647
query governor cost limit 0
lightweight pooling 0
scan for startup procs 0
affinity64 mask 0
affinity I/O mask 0
affinity64 I/O mask 0
transform noise words 0
precompute rank 0
PH timeout (s) 60
clr enabled 0
max full-text crawl range 4
ft notify bandwidth (min) 0
ft notify bandwidth (max) 100
ft crawl bandwidth (min) 0
ft crawl bandwidth (max) 100
default trace enabled 1
blocked process threshold (s) 0
in-doubt xact resolution 0
remote admin connections 0
common criteria compliance enabled 0
EKM provider enabled 0
backup compression default 0
filestream access level 0
optimize for ad hoc workloads 0
access check cache bucket count 0
access check cache quota 0
backup checksum default 0
automatic soft-NUMA disabled 0
external scripts enabled 0
clr strict security 1
column encryption enclave type 0
tempdb metadata memory-optimized 0
ADR cleaner retry timeout (min) 0
ADR Preallocation Factor 0
version high part of SQL Server 0
version low part of SQL Server 0
Agent XPs 0
Database Mail XPs 0
SMO and DMO XPs 1
Ole Automation Procedures 0
xp_cmdshell 0
Ad Hoc Distributed Queries 0
Replication XPs 0
contained database authentication 0
hadoop connectivity 0
polybase network encryption 1
remote data archive 0
allow polybase export 0
allow filesystem enumeration 1
polybase enabled 0
(85 rows affected)
1> Ce sont les paramètres fournis par Microsoft lors de l'installation du package.
S'ils ne sont pas bons, contactez l'équipe de packaging de MS-sql-server. Mais bon, ici, c'est un forum PostgreSQL. Moi ce qui m'intéresse depuis le début en fait c'est d'avoir une idée de la différence de comportement de PG entre une machine Windows de folie et mon simple PC sous Linux, ainsi que la différence de comportement de SQL-Server entre Windows et Linux (pour info, en 1995/96 j'avais pas mal testé/débuggé les bêtas de Sybase V11, donc ça m'intéresse "un peu" d'un point de vue culture personnelle).
Sur la notion de bench "SQL-Server vs PG-SQL" ça ne m'intéresse pas. Comme je l'ai déjà dit je pense que c'est comparer choux et carottes. Techniquement on choisit l'un ou l'autre sur d'autres choses que le timing d'un BCP. Par exemple, les Pays-Bas qui ont des grosses problématiques de cartographie, ont dû concevoir leur propre outil d'importation, tout simplement parce que les LIDAR embarqués sur les avions scann(ai)ent 2 millions de points par seconde, et la campagne de scanning effectuée en 24h mettait plus de 24h à être importée avec les outils standards pgSQL. S'ils ont pu régler le problème avec pgSQL en codant leurs propres outils, c'est simplement parce que pgSQL est open-source.
Autrement la concurrence commerciale "pgSQL vs SQL-Server" pour moi elle n'existe pas. Je vois à la pelle des projets de migration Oracle->PgSQL (d'ailleurs je commence la semaine prochaine un tel projet) ; mais je n'ai encore jamais vu de projets SQL-Server<->PG-SQL ou Oracle->SQL-Server.
Hors ligne