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% |
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 relationnellesCréation des tables, index, contraintes référentielles (requêtes standard SQL, compatible PostgreSQL et MySQL) :
| Table médecin |
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 |
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 |
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 |
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; |
|
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 |
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; |
|
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 |
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; |
|
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 |
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; |
|
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 |
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; |
|
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 |
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; |
|
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 |
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; |
|
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 ? 