Benchmark PostgreSQL vs MySQL
Par Gugelhupf

Le , par Gugelhupf

0PARTAGES

Le document qui suit est un extrait du benchmark PostgreSQL 9.3.5 vs MySQL 5.5.40 que j’ai réalisé dans le cadre du projet de synthèse (étude). Son contenu est comme chaque benchmark contestable.

Les benchmarks ont entièrement étés réalisés sous les machines virtuelles de l’Esiag :
  • OS : Linux 3.13 | distribution Ubuntu Server 14.04 LTS 64 bits.
  • RAM : 8 Go.

CPU Intel (cf : commande lscpu)
  • Fréquence : 2.00 Ghz
  • Nombre de cœur : 4
  • Architecture : x86_64
  • Byte Order : Little Endian


Afin de ne pas influencer les résultats du benchmark, tous les tests liés aux bases relationnelles ont étés effectués à partir d’un OS Linux sans interface graphique :
  • Pour PostgreSQL je me suis servi du client psql et de la fonctionnalité « EXPLAIN ANALYZE ».
  • Pour MySQL je me suis servi du client mysql et de la fonctionnalité « SET PROFILING = 1 ».


Dimensionnement : Les HealthNet souhaitent une base pouvant gérer 1M de patients pour 4000 médecins. Nous allons donc effectuer des tests d’écriture et de lecture afin de déterminer la base relationnelle la plus adaptée par rapport à nos besoins Durée & FURPS PostgreSQL 9.3.5 MySQL 5.5.40
(InnoDB engine)
A1: Durée d’insertion des 4000 médecins dans la base (my_proc1) Durée en ms 254.646 6860.102
FURPS = 500 ms
1 - (X / 500)
49.07% -1272.02%
A2: Durée d’insertion de 1M de patients dans la base (my_proc2) Durée en ms 21936.422 25 min 4.53 sec
FURPS = 125000 ms
1 - (X / 125000)
82.45% DEAD
A3: Durée d’attribution des médecins pour chaque patient (insertion dans la table charnière) (my_proc3) Durée en ms 58830.697 23 min 51.62 sec
FURPS = 125000 ms
1 - (X / 125000)
52.94% DEAD
A4: Durée pour récupérer l’ensemble des patients (1M) ainsi que leurs médecins Durée en ms 2995.391 19320.937
FURPS = 4000 ms
1 - (X / 4000)
25.12% -383.02%
A5: Durée pour récupérer les patients d’1 médecin Durée en ms 222.573 13.505
FURPS = 500 ms
1 - (X / 500)
55.49% 97.30%
A6: Durée pour récupérer les 10 médecins ayant le plus de patients (utilisation de fonction d’agrégat) Durée en ms 404.421 1041.214
FURPS = 500 ms
1 - (X / 500)
19.12% -108.24%
Analyse du tableau : Les trois premiers tests sont axés sur de l’écriture dans la base, les trois suivantes sur de la lecture.

Concernant l’écriture, PostgreSQL est très rapide et les performances affichées par MySQL sont juste aberrantes.

Concernant la lecture :
  • A4 : On peut remarquer que pour la lecture d’un grand nombre de tuples (1M), PostgreSQL affiche des performances bien supérieures à MySQL.
  • A5 : A contrario, sur des petites quantités de données à récupérer, MySQL est plus rapide que PostgreSQL.
  • A6 : Les performances affichés par PostgreSQL sont bien meilleures que celle de MySQL lors de l’utilisation des fonctions d’agrégats.


De par la qualité de sa documentation, la robustesse et les performances qu’elle propose, PostgreSQL semble être la solution la plus adaptée aux besoins de nos utilisateurs, c’est la raison pour laquelle PostgreSQL devrait être un composant de notre solution de PAAS.

NB : Les scripts utilisés ainsi que les screens des clients psql et mysql sont présents dans l’Annexe A,

Annexe A : Bases relationnelles

Création des tables, index, contraintes référentielles (requêtes standard SQL, compatible PostgreSQL et MySQL) :
Table médecin
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
CREATE TABLE t_medecin( 
    id int, 
    nom varchar(50), 
    prenom varchar(50), 
    mail varchar(50), 
    specialite int 
); 
  
ALTER TABLE t_medecin ADD CONSTRAINT pk_medecin PRIMARY KEY (id);
Table patient
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
CREATE TABLE t_patient( 
    id int, 
    nom varchar(50), 
    prenom varchar(50), 
    mail varchar(50) 
); 
  
ALTER TABLE t_patient ADD CONSTRAINT pk_patient PRIMARY KEY (id);
Table charnière médecin/patient
Code sql : Sélectionner tout
1
2
3
4
5
6
7
CREATE TABLE t_charniere_medecin_patient(id_medecin int, id_patient int); 
  
ALTER TABLE t_charniere_medecin_patient ADD CONSTRAINT fk_id_medecin 
FOREIGN KEY (id_medecin) REFERENCES t_medecin(id); 
  
ALTER TABLE t_charniere_medecin_patient ADD CONSTRAINT fk_id_patient 
FOREIGN KEY (id_patient) REFERENCES t_patient(id);
Fonction permettant de générer un nombre aléatoire entre 2 valeurs :
PostgreSQL 9.3.5 MySQL 5.5.40
Code sql : Sélectionner tout
1
2
3
4
5
6
7
-- start_range  inclusive | end_range exclusive 
CREATE FUNCTION random_range_int(start_range int, end_range int) 
RETURNS INTEGER AS $$ 
BEGIN 
    RETURN trunc(random() * (end_range - start_range) + start_range); 
END; 
$$ LANGUAGE plpgsql;
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
-- start_range  inclusive | end_range inclusive 
DELIMITER $$ 
CREATE FUNCTION random_range_int(start_range INTEGER, end_range INTEGER) 
RETURNS int 
BEGIN 
    RETURN round(rand() * (end_range - start_range) + start_range); 
END $$ 
DELIMITER ;
Requête des tests :
Test PostgreSQL 9.3.5 MySQL 5.5.40
Note SET profiling = 1;
A1
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
CREATE FUNCTION my_proc1() 
RETURNS void 
AS $$ 
    FOR i IN 1..4000 LOOP 
        INSERT INTO t_medecin(id, nom, prenom, mail, specialite) 
        VALUES(i, 'Nom ' || i, 'Prenom ' || i, '@mail ' || i, random_range_int(1, 9)); 
    END LOOP; 
END; 
$$ LANGUAGE plpgsql;
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$ 
CREATE PROCEDURE my_proc1() 
BEGIN 
    DECLARE i INT DEFAULT 1;    
    WHILE i <= 4000 DO 
        INSERT INTO t_medecin(id, nom, prenom, mail, specialite) 
        VALUES(i, CONCAT('Nom ', i), CONCAT('Prenom ', i), CONCAT('@mail ', i), random_range_int(1, 8)); 
        SET i = i + 1; 
    END WHILE; 
END $$ 
DELIMITER ;
A2
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
CREATE FUNCTION my_proc2() 
RETURNS void 
AS $$ 
    FOR i IN 1..1000000 LOOP 
        INSERT INTO t_patient(id, nom, prenom, mail) 
        VALUES(i, 'Nom ' || i, 'Prenom ' || i, '@mail ' || i); 
    END LOOP; 
END; 
$$ LANGUAGE plpgsql;
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$ 
CREATE PROCEDURE my_proc2() 
BEGIN    
    DECLARE i INT DEFAULT 1; 
    WHILE i <= 1000000 DO 
        INSERT INTO t_patient(id, nom, prenom, mail) 
        VALUES(i, CONCAT('Nom ', i), CONCAT('Prenom ', i), CONCAT('@mail ', i)); 
        SET i = i + 1; 
    END WHILE; 
END $$ 
DELIMITER ;
A3
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
CREATE FUNCTION my_proc3() 
RETURNS void 
AS $$ 
    FOR i IN 1..1000000 LOOP 
        INSERT INTO t_charniere_medecin_patient(id_medecin, id_patient) 
        VALUES(random_range_int(1, 4001), i); 
    END LOOP; 
END; 
$$ LANGUAGE plpgsql;
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$ 
CREATE PROCEDURE my_proc3()   
BEGIN 
    DECLARE i INT DEFAULT 1; 
    WHILE i <= 1000000 DO 
        INSERT INTO t_charniere_medecin_patient(id_medecin, id_patient) 
        VALUES(random_range_int(1, 4000), i); 
        SET i = i + 1; 
    END WHILE; 
END $$ 
DELIMITER ;
A4
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
EXPLAIN ANALYZE 
SELECT 
    P.*, 
    M.nom AS nom_medecin, 
    M.prenom AS prenom_medecin 
FROM t_medecin M 
JOIN t_charniere_medecin_patient MP 
ON M.id = MP.id_medecin 
JOIN t_patient P 
ON P.id = MP.id_patient;
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
SELECT 
    P.*, 
    M.nom AS nom_medecin, 
    M.prenom AS prenom_medecin 
FROM t_medecin M 
JOIN t_charniere_medecin_patient MP 
ON M.id = MP.id_medecin 
JOIN t_patient P 
ON P.id = MP.id_patient;
A5
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
EXPLAIN ANALYZE 
SELECT 
    P.*, 
    M.nom AS nom_medecin, 
    M.prenom AS prenom_medecin 
FROM t_medecin M 
JOIN t_charniere_medecin_patient MP 
ON M.id = MP.id_medecin 
JOIN t_patient P 
ON P.id = MP.id_patient 
WHERE M.id  = 40;
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
SELECT 
    P.*, 
    M.nom AS nom_medecin, 
    M.prenom AS prenom_medecin 
FROM t_medecin M 
JOIN t_charniere_medecin_patient MP 
ON M.id = MP.id_medecin 
JOIN t_patient P 
ON P.id = MP.id_patient 
WHERE M.id  = 40;
A6
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
EXPLAIN ANALYZE 
SELECT 
    MP.id_medecin, 
    COUNT(*) AS c 
FROM t_charniere_medecin_patient MP 
GROUP BY MP.id_medecin 
ORDER BY c 
DESC LIMIT 10;
Code sql : Sélectionner tout
1
2
3
4
5
6
7
SELECT 
    MP.id_medecin, 
    COUNT(*) AS c 
FROM t_charniere_medecin_patient MP 
GROUP BY MP.id_medecin 
ORDER BY c 
DESC LIMIT 10;

Schéma : ANALYZE EXPLAIN PostgreSQL (A4)


Schéma : ANALYZE EXPLAIN PostgreSQL (A5)


Schéma : ANALYZE EXPLAIN PostgreSQL (A6)


Schéma : SHOW PROFILES MySQL (La 1ère flèche rouge représente A4, la 2ème A5, et la 3ème A6)


Que pensez-vous de ce benchmark ?
Selon vous, comment pourrais-t-on améliorer ce benchmark ?

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

Contacter le responsable de la rubrique SGBD & SQL

Partenaire : Hébergement Web