[Actualité] Benchmark PostgreSQL vs MySQL
par
, 22/06/2015 à 18h54 (7120 Affichages)
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 ?