IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

PostGreSQL vs Microsoft SQL Server

Partie 1 : performances des commandes pour le DBA

Pour réagir au contenu de cet article, un espace de dialogue vous est proposé sur le forum. 85 commentaires Donner une note  l'article (3.5)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. L’auteur

II. Introduction

Image non disponible

Ce premier papier parle de quelques comparaisons entre PostGreSQL et SQL Server et pointe les différences en termes de performances pour quelques-unes des requêtes et commandes administratives qu’un DBA ordinaire doit exécuter.

III. Matériel de test utilisé

  • CPU : 2 sockets (Intel Xeon E5-2680 v3).
  • 128 Go de RAM.
  • OS Windows 10 Enterprise.

IV. Versions des SGBDR

  • SQL Server 2019 édition Developper.
  • PostGreSQL v 13.1 édition communautaire.

V. Applications clientes

  • SQL Server management Studio v 18.5 (pour SQL Server).
  • PGadmin 4 v 4.29 (pour PostGreSQL).

VI. Configuration des SGBDR

  • SQL Server n’a aucune configuration particulière. La collation d’installation du serveur est French_100_BIN2.
  • PostGreSQL a une configuration particulière qui est la suivante :
 
Sélectionnez
ALTER SYSTEM SET effective_cache_size = 12000000; -- 12 000 000 x 8 192 o :  96 Go
ALTER SYSTEM SET shared_buffers = 2560000;        --  2 560 000 x 8 192 o :  20 Go
ALTER SYSTEM SET work_mem = 500000;               --    500 000 x 1 024 o : 500 Mo
ALTER SYSTEM SET temp_buffers = 512000;           --    512 000 x 8 192 o :   4 Go
ALTER SYSTEM SET max_worker_processes = 48;
ALTER SYSTEM SET max_parallel_workers = 48;
ALTER SYSTEM SET max_parallel_workers_per_gather = 8;

VII. Configuration de la base de données

SQL Server est configuré comme suit au niveau du stockage :

  • fichier de données: 12 Gb ;
  • fichier du journal de transaction : 8 Gb.

PostGreSQL a une collation spécifique du fait de l’OS utilisé :

  • encoding = UTF8 ;
  • collation = French_France.1252 ;
  • character type = French_France.1252.

VIII. Protocole de test

Le protocole utilisé est le suivant :

  • exécution de chaque requête 10 fois ;
  • élimination du meilleur et du moins bon temps de réponse ;
  • calcul de la moyenne sur les huit temps de réponse restants ;
  • arrêt du service d’un SGBDR pendant le test de l’autre.

IX. Téléchargement des requêtes de test

Les requêtes permettant de reproduire nos tests peuvent être téléchargées ici :

https://1drv.ms/u/s!AqvZfiQYoNpBihI4efgbhodhRZec?e=07igoa (lien)

  • Le fichier « Benchmark test PostGreSQL.sql » contient le script SQL pour créer les tables et les peupler, ainsi que les requêtes que nous avons testées pour PostGreSQL ;
  • Le fichier « Benchmark test SQL Server.sql » contient le script SQL pour créer les tables et les peupler, ainsi que les requêtes que nous avons testées pour SQL Server ;
  • Le fichier « personnes.zip » contient les données pour charger la table de notre test (la table est en deux versions). C’est un fichier ZIP contenant un fichier texte de 239 820 Ko dans lequel vous trouverez 10 millions de lignes de personnes.

X. Script de définition des tables

SQL Server

PostGreSQL

 
Sélectionnez
CREATE TABLE T_PERSONNE_PRS
(PRS_ID INT IDENTITY PRIMARY KEY,
PRS_NOM VARCHAR(32),
PRS_PRENOM VARCHAR(25))
 
Sélectionnez
CREATE TABLE T_PERSONNE_PRS 
(PRS_ID SERIAL PRIMARY KEY,
PRS_NOM VARCHAR(32),
PRS_PRENOM VARCHAR(25));
 
Sélectionnez
CREATE TABLE T_PERSONNE_CI_AI_PRS 
(PRS_ID INT PRIMARY KEY, 
PRS_NOM VARCHAR(32) COLLATE french_ci_ai, 
PRS_PRENOM VARCHAR(25) COLLATE french_ci_ai);
 
Sélectionnez
CREATE COLLATION fr_ci_ai 
(provider = icu, 
locale = 'fr-u-ks-level1-kc-false', 
deterministic=false);
 
Sélectionnez
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);

Nous avons dû créer une collation pour PostGreSQL pour la deuxième table, car PostGreSQL possède très peu de collations internes contrairement à SQL Server qui en a plus de 5500.

XI. Requêtes DBA

Les requêtes à destination des DBA sont les suivantes :

 
 

SQL Server

temps ms

PostGreSQL

temps ms

ratio (MS/PG)

DBA 1

 
Sélectionnez
BULK INSERT T_PERSONNE_PRS 
FROM 'C:\tmp\Personnes.txt' 
WITH (FIELDTERMINATOR = ',', 
ROWTERMINATOR = '\n',
KEEPIDENTITY,
BATCHSIZE = 10000000, 
CODEPAGE = 'ACP');

19 729

 
Sélectionnez
COPY T_PERSONNE_PRS 
(PRS_ID, PRS_NOM , PRS_PRENOM) 
FROM 'C:\tmp\Personnes.txt' 
WITH (DELIMITER ',',
ENCODING 'WIN1252');

50 850

2.58

DBA 2

 
Sélectionnez
ALTER TABLE T_PERSONNE_PRS REBUILD;
GO

2 269

 
Sélectionnez
VACUUM FULL t_personne_prs;
REINDEX TABLE t_personne_prs;

80 000

35.26

DBA 3

 
Sélectionnez
CREATE INDEX X_1 
ON T_PERSONNE_PRS (PRS_NOM);

2 348

 
Sélectionnez
CREATE INDEX X_1 
ON T_PERSONNE_PRS (PRS_NOM);

37 713

16.06

DBA 4

 
Sélectionnez
CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);

2 312

 
Sélectionnez
CREATE INDEX X_2
ON T_PERSONNE_PRS (PRS_PRENOM);

35 341

15.29

DBA 5

 
Sélectionnez
CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);

4 779

 
Sélectionnez
CREATE INDEX X_3
ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);

57 825

12.04

DBA 6

 
Sélectionnez
ALTER TABLE T_PERSONNE_PRS REBUILD
WITH(DATA_COMPRESSION = PAGE);

2 338

PostGreSQL n'a pas la possibilité de faire de la compression sur les index relationnels.

   

DBA 7

 
Sélectionnez
ALTER INDEX X_1 ON T_PERSONNE_PRS 
REBUILD WITH(DATA_COMPRESSION = PAGE);

1 465

PostGreSQL n'a pas la possibilité de faire de la compression sur les index relationnels.

   

DBA 8

 
Sélectionnez
ALTER INDEX X_2 ON T_PERSONNE_PRS 
REBUILD WITH(DATA_COMPRESSION = PAGE);

1 632

PostGreSQL n'a pas la possibilité de faire de la compression sur les index relationnels.

   

DBA 9

 
Sélectionnez
ALTER INDEX X_3 ON T_PERSONNE_PRS 
REBUILD WITH(DATA_COMPRESSION = PAGE);

1 760

PostGreSQL n'a pas la possibilité de faire de la compression sur les index relationnels.

   

DBA 10

 
Sélectionnez
ALTER TABLE T_PERSONNE_PRS REBUILD; 
ALTER INDEX ALL ON T_PERSONNE_PRS REBUILD;

8 556

 
Sélectionnez
VACUUM FULL t_personne_prs;
REINDEX TABLE t_personne_prs;

266 000

31.09

DBA 11

 
Sélectionnez
INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;

13 197

 
Sélectionnez
INSERT INTO T_PERSONNE_CI_AI_PRS
SELECT * FROM T_PERSONNE_PRS;

102 000

7.73

DBA 12

 
Sélectionnez
UPDATE STATISTICS T_PERSONNE_PRS(X_1)

173

 
Sélectionnez
ANALYZE T_PERSONNE_PRS (prs_nom);

1 376

7.95

DBA 13

 
Sélectionnez
UPDATE STATISTICS T_PERSONNE_PRS(X_3)

338

 
Sélectionnez
ANALYZE T_PERSONNE_PRS (prs_nom, prs_prenom);

1 492

4.41

DBA 14

EXEC sp_updatestats

316

ANALYZE;

5 729

18.13

Quelques explications au sujet des statistiques : les paramètres de calcul sont proches les uns des autres. Les entrées de statistiques sont limitées à 100 dans PostGreSQL et 200 dans SQL Server. SQL Server utilise un échantillon de 0,5 %, tandis que l’échantillon de PostGreSQL est de 0,4 %. Ainsi, SQL Server fait un peu plus de travail que PostGreSQL pour le calcul des statistiques. Pour le recalcul des statistiques de toute la base de données (ce qui inclut les tables systèmes), SQL Server a 236 statistiques tandis que PostGreSQL en a 423.

Ce que nous pouvons dire au sujet des performances comparées entre PostGreSQL et SQL Server concernant les requêtes d’administration, sont les éléments suivants :

  • l’insertion des données est environ 5 fois plus lente dans PostGreSQL par rapport à SQL Server ;
  • le recalcul des statistiques est environ 10 fois plus lent dans PostGreSQL par rapport à SQL Server ;
  • la création d’index est environ 14 fois plus lente dans PostGreSQL par rapport à SQL Server  ;
  • la maintenance d’index est environ 32 fois plus lente dans PostGreSQL par rapport à SQL Server.

La création d’index est une opération rare et non récurrente. La différence de performances sera problématique en ce qui concerne de grandes tables. Même chose pour l’insertion des données, sauf si vous avez des processus récurrents comme un import planifié de données.

D’un autre côté, la maintenance, comme le recalcul des statistiques ou la défragmentation des index est un processus très récurrent si vous désirez maintenir un haut niveau de performances (des tables ou index fragmentés occupent beaucoup plus de place en mémoire cache au détriment d’autres données). Malheureusement, PostGreSQL sera près de 30 fois plus lent globalement pour ces opérations que SQL Server. C’est une différence très significative lorsqu’on doit faire face à de grosses bases de données…

Image non disponible

Article suivant (à paraître) : performances comparées de la fonction d’agrégation COUNT entre PostGreSQL et SQL Server.

Vos commentaires et remarques, comme vos protestations sont les bienvenus sur le forum de developpez.com et peuvent être postés sur cette enfilade : PostGreSQL vs Microsoft SQL Server - Partie 1 : performances des commandes pour le DBA.

XII. Remerciements Developpez.com

Nous tenons à remercier escartefigue pour la relecture orthographique de cet article.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Copyright © 2020 Frédéric BROUARD. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.