MySQL vs PostgreSQL - Résultat très bizarre sur insertion

Marsh Posté le 04-09-2002 à 17:25:01

Bonjour à tous,

J'effectue quelques tests pour passer une base de MySQL à PostgreSQL. Pour me fair ela main avec l'API C, j'ai réalisé un petit programme qui fait la même chose pour MySQL et PostgreSQL : 10000 insertion dans une base tout simple.
Le schéma de la base :

|-----------------|----------------------------------------------|
| TEST |
| id | auto_increment (ou serial pour postgreSQL) |
| type_int | INT (ou integer) |
| type_varchar | varchar(255) |
| type_tinyint | tinyint |
| type_text | text |
|-----------------|----------------------------------------------|

Le code MySQL :

Code :

#define INSERTION "INSERT INTO test (type_int, type_varchar,type_tinyint,type_text) VALUES (%d,\'essai de chaine de caractère\',100,\'MAJUSCULES et minuscules\'"
int main(int argc, char **argv) {
MYSQL mysql;
unsigned int i;
char mquery(1000);
MYSQL_RES *mysql_row;
mysql_init(&mysql);
if (mysql_real_connect(&mysql, NULL, "user", NULL, "bd", 0, NULL, 0)) {
for (i=0;i<=10000;i++) {
sprintf(mquery,INSERTION,i);
if ((mysql_query(&mysql,mquery)!=0) {
printf("sql query error (%s) : %s\n",mquery,mysql_error(&mysql));
mysql_close(&mysql);
return 0;
}
}
mysql_close(&mysql);
}
else {
printf("sql connection error : %s\n",mysql_error(&mysql));
return 0;
}
return 0;
}

L'équivalent PostgreSQL :

Code :

#define INSERTION "INSERT INTO test (type_int, type_varchar,type_tinyint,type_text) VALUES (%d,\'essai de chaine de caractère\',100,\'MAJUSCULES et minuscules\'"
int main(int argc, char **argv) {
PGconn *conn;
unsigned int i;
char mquery(1000);
PGresult *res;
conn=PQconnectdb("dbname=db user="user" );
if (PQstatus(conn) == CONNECTION_OK) {
for (i=0;i<=10000;i++) {
sprintf(mquery,INSERTION,i);
res=PQexec(conn,mquery);
if (PQresultstatus(res)!= PGRES_COMMAND_OK) {
printf("sql query error (%s) : %s\n",mquery,PQresultErrorMessage(res));
PQclear(res);
PQfinish(conn);
return 0;
}
}
PQclear(res);
PQfinish(conn);
}
else {
printf("sql connection error : %s\n",PQerrorMessage(conn));
return 0;
}
return 0;
}

Rien de bien méchant.
Je lance donc ces programmes sur ma machine sous Debian avec MySQL 3.23.51 et PostgreSQL 7.2.1.
Temps pour réaliser les insertions sous MySQL : entre 1 et 2 secondes.
Temps pour réaliser les insertions sous PostgreSQL : entre 28 et 30 secondes !!!....
D'où mon problème et mon interrogation ! Y'a t-il quelque chose dans l'API C de PostgreSQL que je n'ai pas compris ? des subtilités lors de la configuration ? Je m'attendais à ce que PostgreSQL soit moins rapide mais 15x plus lent sur une requête simple c'est vraiment étranger ?!...
Merci pour tout commentaire, remarque et correction !

Message édité par Poulou le 04-09-2002 à 17:26:02

Reply

Marsh Posté le 04-09-2002 à 17:25:01

Reply

Marsh Posté le 05-09-2002 à 01:42:52

tu peux encore augmenter la vitesse d'insertion de mysql en faisant du bulk insert :

INSERT INTO test (type_int, type_varchar,type_tinyint,type_text) VALUES (%d,\'essai de chaine de caractère\',100,\'MAJUSCULES et minuscules\'" ), (%d,\'essai de chaine de caractère\',100,\'MAJUSCULES et minuscules\'" ), (%d,\'essai de chaine de caractère\',100,\'MAJUSCULES et minuscules\'" ), ...

---------------
Protèges carnets personnalisés & accessoires pour bébé

Reply

Marsh Posté le 05-09-2002 à 08:00:38

merci Joce je connaissais pas le nom Bulk Insert (moi j'apelle ça insertion multiple)

d'ailleurs très peu de personnes utilisent cette manière d'insérer plusieurs lignes en une fois et font une requête par ligne..

---------------
La musique c'est comme la bouffe, tu te souviens du restaurant dans lequel t'as bien mangé 20 ans plus tôt, mais pas du sandwich d'il y a 5 minutes :o - Plugin pour winamp ©Harkonnen : http://harko.free.fr/soft

Reply

Marsh Posté le 05-09-2002 à 09:23:15

Merci Joce, ça peut être utile à l'occasion mais le problème c'est que si je réduis encore le temps de MySQL, l'écart va encore se creuser avec PostgreSQL !... Alors bon n'enterrons pas directement PostgreSQL ! Je crois que je vais aller poster un vieux troll sur le forum de PostgreSQL, je devrais avoir des réponses !
Bien entendu je prends toujours tout commentaire, aide et indication !

Reply

Marsh Posté le 05-09-2002 à 16:54:32

c'est bizzare ton truc, on vient de faire l'essai mais on a que ~2 secondes d'ecart en faveur de MySql...

t'as pas fait bien expres de faire le test sur windows pour provoquer un troll toi ???

Message édité par kuroineko le 05-09-2002 à 16:59:19

---------------
[:kuroineko] Francois.P tel: (+33)617230820 http://www.ifrance.com/fpussault fpussault@caramail.com

Reply

Marsh Posté le 06-09-2002 à 00:29:51

Non non promis !
En fait j'ai eu des réponses sur la mailing-list de PostgreSQL : sur ma version de code pour PostgreSQL chaque insert crée une transaction donc BEGIN INSERT COMMIT. Il est donc nécessaire de créer une seule transaction pour tous les INSERT ce qui donne un code dans le genre :

Code :

conn=PQconnectdb("dbname=db user="user" );
if (PQstatus(conn) == CONNECTION_OK) {
res=PQexec(conn,"BEGIN" );
for (i=0;i<=10000;i++) {
[...]
}
res=PQexec(conn,"COMMIT" );
}

Je suis pas au boulot (vu l'heure c pas étonnant) donc j'ai pas encore eu l'occasion de tester mais je vous dirai ça demain (enfin aujourd'hui plus tard...).
Par contre kuroineko, c'est étonnant, tu n'aurais pas justement les transations désactivées sur PostgreSQL ça pourrait être une explication plausible (et puis j'aime bien ton troll )
Voilà, je vous dis ça demain !

Message édité par Poulou le 06-09-2002 à 00:30:19

Reply

Marsh Posté le 06-09-2002 à 00:33:57

Ce serait pas que tu es en autocomit dans PostGre ?

Essaie de faire ça dans une transaction unique pour voir...

(sinon tu génères une chiée d'écritures supplémentaire à chaque ligne)

Et si ça rame toujours, désactive carrément les transactions si c'est possible.

Message édité par MagicBuzz le 06-09-2002 à 00:34:22

Reply

Marsh Posté le 06-09-2002 à 00:34:58

Arf ! J'avais pas vu ta réponse :lol:

Reply

Marsh Posté le 06-09-2002 à 09:26:02

Me revoilà.
J'ai donc refait le test en déclarant explicitement le début et la fin de la transaction et j'obtiens un temps beaucoup plus cohérent de 5s. Sur ce test, MySQL est tout de même 3 fois plus rapide que PostgreSQL. Je vais faire d'autre tests pour comparer tout ça (en particulier avec des procédures stockées). Si ça vous intéresse...

Reply

Marsh Posté le 06-09-2002 à 09:29:40

Poulou a écrit a écrit :

Non non promis !

En fait j'ai eu des réponses sur la mailing-list de PostgreSQL : sur ma version de code pour PostgreSQL chaque insert crée une transaction donc BEGIN INSERT COMMIT. Il est donc nécessaire de créer une seule transaction pour tous les INSERT ce qui donne un code dans le genre :

Code :

conn=PQconnectdb("dbname=db user="user" );
if (PQstatus(conn) == CONNECTION_OK) {
res=PQexec(conn,"BEGIN" );
for (i=0;i<=10000;i++) {
[...]
}
res=PQexec(conn,"COMMIT" );
}

Je suis pas au boulot (vu l'heure c pas étonnant) donc j'ai pas encore eu l'occasion de tester mais je vous dirai ça demain (enfin aujourd'hui plus tard...).
Par contre kuroineko, c'est étonnant, tu n'aurais pas justement les transations désactivées sur PostgreSQL ça pourrait être une explication plausible (et puis j'aime bien ton troll )
Voilà, je vous dis ça demain !

heu peut etre mois les database je suis pas top top,,, mon collegue est bien meilleur que moi de ce cote la...

---------------
[:kuroineko] Francois.P tel: (+33)617230820 http://www.ifrance.com/fpussault fpussault@caramail.com

Reply

Marsh Posté le 06-09-2002 à 09:29:40

Reply

Marsh Posté le 06-09-2002 à 12:23:07

En fait, quelques petits trucs :

- Pour une transaction, 10000 lignes ça fait beaucoup. Essaie de paramètrer ton soft de façon à ce qu'il fasse ça un peu mieu (genre 1000 lignes par transaction, ça sera pas mal), sinon tu va bouffer ta RAM et générer des fichiers de LOG de transaction monstrueyx.

- Sinon, PostGRE étant comme la plupart des SGBD évolués, totalement transactionnel et journalisé, il sera forcément plus lent à l'écriture d'informations. Mais au final, (montée en charge et cnie) il sera pas moins performant (en tout cas les différences seront bien moins importantes)

Et un pour programme qui nécessite une cohérence des données complète, le fait d'être transactionnal et relationnel à 100% est un avantage certain :
-> Impossible de se retrouer avec des données incohérentes (qui ne respectent pas les contraintes logiques, qui peuvent être plus évoluées que des FK )
-> En cas d'erreur dans une requête d'un lot, le rollback est automatique et tu est en permanance avec un jeu de données cohérents.

Par exemple, sur un site de e-commerce, tu veux créer une commande.

Elle a 5 lignes.

Tu crées la de commande.
Tu crée chaque ligne de commande.

En cas de plantage avec un système non transactionnel/relationnel, tu peux te retrouver avec des lignes de commandes insérées sans commande, ou des commandes qui n'ont pas le bon nombre de ligne. Celà t'oblige donc à faire un tas de vérifications sur les données à chaque traîtement, ce qui au final fait perdre tout l'intérêt de la rapidité d'éxécution primaire : tu perds tu temps à faire des virifications, et tu te complique la vie dans le code.

A noter aussi que les transaction sont blindées même contre un plantage de bas niveau (coupure de courant, erreur d'écriture sur le disque, etc.)

Avec MySQL par exemple, en cas de coupure de courant, tu te retrouves avec l'obligation de rescanner toute ta base et vérifier manuellement toutes les contraintes de données... Ce qui peut être très long et compliqué à faire. Avec un SGBD transactionnel/relationnel, il va rollbacker toutes les transactions en cours lors du plantage, et vérifier l'intégrité des relations.

Ca a évidement un coût mais au final ça peut s'avérer bien plus rapide, et surtout beaucoup plus fiable.

Dans le même style, il suffit de comparer un FS avec une base de données (d'ailleurs un FS n'est ni plus ni moins qu'une base de données)

Prends la FAT32 ou la format natif de Linux, en cas de crash, tu te tapes un scan complet du disque, avec très souvent (surtout sous Linux) des fragments de fichiers perdus. Pour la FAT32 en tout cas il dit rien, mais il doit y en avoir autant.

Prends maintenant la NTFS ou un FS transactionnel/journalisé sous Linux, reboot à la sauvage, et ça redémarre comme si de rien n'était, bien plus rapidement, et le disque ne contient aucun fichier corrompu.

Mais revers de la médaille, NTFS est plus lent que FAT32 (idem pour les systèmes de FS Linux)
Par contre, ces FS sont généralement plus performants en cas d'accès multiple et intensifs.

Pour les SGBD, c'est exactement les mêmes règles.

Message édité par MagicBuzz le 06-09-2002 à 12:25:04

Reply

Marsh Posté le 06-09-2002 à 13:08:16

Sh@rdar a écrit a écrit :

merci Joce je connaissais pas le nom Bulk Insert

(moi j'apelle ça insertion multiple)

d'ailleurs très peu de personnes utilisent cette manière d'insérer plusieurs lignes en une fois et font une requête par ligne..

si moi

---------------
Protèges carnets personnalisés & accessoires pour bébé

Reply

Marsh Posté le 06-09-2002 à 13:10:27

MagicBuzz a écrit a écrit :

En fait, quelques petits trucs :

- Pour une transaction, 10000 lignes ça fait beaucoup. Essaie de paramètrer ton soft de façon à ce qu'il fasse ça un peu mieu (genre 1000 lignes par transaction, ça sera pas mal), sinon tu va bouffer ta RAM et générer des fichiers de LOG de transaction monstrueyx.

- Sinon, PostGRE étant comme la plupart des SGBD évolués, totalement transactionnel et journalisé, il sera forcément plus lent à l'écriture d'informations. Mais au final, (montée en charge et cnie) il sera pas moins performant (en tout cas les différences seront bien moins importantes)

Et un pour programme qui nécessite une cohérence des données complète, le fait d'être transactionnal et relationnel à 100% est un avantage certain :
-> Impossible de se retrouer avec des données incohérentes (qui ne respectent pas les contraintes logiques, qui peuvent être plus évoluées que des FK

)
-> En cas d'erreur dans une requête d'un lot, le rollback est automatique et tu est en permanance avec un jeu de données cohérents.

Par exemple, sur un site de e-commerce, tu veux créer une commande.

Elle a 5 lignes.

Tu crées la de commande.
Tu crée chaque ligne de commande.

En cas de plantage avec un système non transactionnel/relationnel, tu peux te retrouver avec des lignes de commandes insérées sans commande, ou des commandes qui n'ont pas le bon nombre de ligne. Celà t'oblige donc à faire un tas de vérifications sur les données à chaque traîtement, ce qui au final fait perdre tout l'intérêt de la rapidité d'éxécution primaire : tu perds tu temps à faire des virifications, et tu te complique la vie dans le code.

A noter aussi que les transaction sont blindées même contre un plantage de bas niveau (coupure de courant, erreur d'écriture sur le disque, etc.)

Avec MySQL par exemple, en cas de coupure de courant, tu te retrouves avec l'obligation de rescanner toute ta base et vérifier manuellement toutes les contraintes de données... Ce qui peut être très long et compliqué à faire. Avec un SGBD transactionnel/relationnel, il va rollbacker toutes les transactions en cours lors du plantage, et vérifier l'intégrité des relations.

Ca a évidement un coût mais au final ça peut s'avérer bien plus rapide, et surtout beaucoup plus fiable.

Dans le même style, il suffit de comparer un FS avec une base de données (d'ailleurs un FS n'est ni plus ni moins qu'une base de données)

Prends la FAT32 ou la format natif de Linux, en cas de crash, tu te tapes un scan complet du disque, avec très souvent (surtout sous Linux) des fragments de fichiers perdus. Pour la FAT32 en tout cas il dit rien, mais il doit y en avoir autant.

Prends maintenant la NTFS ou un FS transactionnel/journalisé sous Linux, reboot à la sauvage, et ça redémarre comme si de rien n'était, bien plus rapidement, et le disque ne contient aucun fichier corrompu.

Mais revers de la médaille, NTFS est plus lent que FAT32 (idem pour les systèmes de FS Linux)
Par contre, ces FS sont généralement plus performants en cas d'accès multiple et intensifs.

Pour les SGBD, c'est exactement les mêmes règles.

si tu veux comparer dans les mêmes condition mysql et postgre, utilise le handler innodb pour mysql qui est transactionnel et journalisé (check des tables, auto rollback, etc) plutôt que du MyISAM. Il gère les foreigns key, etc également. Contrairement à Oracle ou d'autre database, il empêche la création des phantoms rows, etc

Message édité par joce le 06-09-2002 à 13:13:23

---------------
Protèges carnets personnalisés & accessoires pour bébé

Reply

Marsh Posté le 06-09-2002 à 13:36:10

joce a écrit a écrit :

si moi

ah bin oui, moi aussi alors

---------------
La musique c'est comme la bouffe, tu te souviens du restaurant dans lequel t'as bien mangé 20 ans plus tôt, mais pas du sandwich d'il y a 5 minutes :o - Plugin pour winamp ©Harkonnen : http://harko.free.fr/soft

Reply

Marsh Posté le 06-09-2002 à 15:18:45

magicbuzz: merci de t'être donner la peine d'écrire tout cela !
Je connaissais déjà le fonctionnement des transactions mais n'avais encore jamais fait réellement de tests. C'est clair que pour tout ce qui est bancaire/e-commerce, etc. les transactions sont obligatoires. Mais en fait dans mon application (le code que j'ai passé était juste un test, je ne fais pas 10000 insertions dans une boucle for ), chaque requête est atomique (quasiment toutes les tables sont indépendantes), donc l'intérêt reste très limité (et au contraire ralenti les insertions comme j'ai pu le constater). La migration vers PostgreSQL serait a priori intéressante pour l'utilisation des procédures stockées mais là encore je suis assez déçu des résultats, pas de grande différence par rapport à des requêtes client-serveur (surtout que c en local, donc pas d'intérêt pour diminuer la charge réseau). Bref c'est une étude qu'on m'a demandé mais je crois que ma conclusion va être : restez sous MySQL (mais passez à la dernière version stable, ça vous corrigera certains bugs (ils ont des pbs de fiabilité d'où l'étude - d'ailleurs au niveau stabilité où en est PostgreSQL ? J'en étais resté qu'il l'était moins que PostgreSQL ?)). Quand le MySQL v4 sortira avec gestion des foreign key et des procédures stockées on pourra en reparler
joce:j'avais regardé le système de table InnoDB que j'avais trouvé relativement intéressant pour l'apport des fonctionnalités manquantes à MyIsam (transactions, clés étrangères, triggers,...). Malheureusement, ils l'ont estimé pas assez mature pour pouvoir le mettre en production...
Voilà, merci pour vos messages

Message édité par Poulou le 06-09-2002 à 15:20:28

Reply

Marsh Posté le 06-09-2002 à 16:13:46

InnoDB est completement stable est tres mature maintenant (est bien sur il passe le test ACID sans probleme).
Sinon InnoDB est integre dans les dernieres version stable de MySQL 3.23.x, donc tu as les foreign keys.
Pour les subqueries, c'est deja implemente dans l'arbre de dev de mysql 4.1, et ils travaillent actuellement sur les procedures stockees je pense (mais a mon avis ca sera dispo dans MySQL 5.x)

Message édité par joce le 06-09-2002 à 16:16:37

Reply

Marsh Posté le 06-09-2002 à 19:01:13

Poulou : Pour mesurer l'intérêt des procédures stockées, il faut savoir une chose...

Elles n'ont d'intérêt que lorsque tu as beaucoup de requêtes différentes et complexes.

En effet, si tu exécute 10000 fois une même requête, le cache de l'optimiseur de requête va réutiliser le plan calculé pour l'effectuer.

L'avantage de la proc stock, c'est que ce dernier est déjà compilé.

Donc lance 1000 requêtes différentes (tu vas t'amuser à les écrire :lol: ) et 1000 proc stock différentes, qui font des jointures complexes et tout le tralala. A ce moment la différence de perfs est réellement visible.

Sinon, tu ne gagnes qu'à la première éxécution, après les select classiques sont aussi rapides qu'une proc stock.

Reply

Marsh Posté le 06-09-2002 à 20:10:39

Groumf...

SQL Server via ADO et transactions sur clés étrangères en VBS, c'est pas tip top

Code :

startime = timer
set cnx = CreateObject("ADODB.Connection" )
cnx.Open connectionString
for i = 0 to 99
cnx.BeginTrans()
cnx.Execute("INSERT INTO T1 (ID, TEST) VALUES (" & i & ", 'toto" & i & "')" )
for j = 0 to 99
cnx.Execute("INSERT INTO T2 (ID, P_ID, TEST) VALUES (" & j & ", " & i & ", '" & i & j & "')" )
next
cnx.CommitTrans()
next
cnx.close
endtime = timer
Msgbox("10000 lignes créées en " & int((endtime - startime) * 1000) / 1000 & " secondes" )

=> 6.632 secondes :sweat:

Message édité par MagicBuzz le 06-09-2002 à 20:12:05

Reply

Marsh Posté le 09-09-2002 à 11:39:47

Merci magicbuzz pour ta réponse, j'aurai appris plein de choses dans ce topic
Etant donné qu'on est bien dans ce topic, qu'il fait chaud et que j'offre à boire [:t@merenslip] , une petite interrogation subsidiaire !
Avant de faire quelques tests sous PostgreSQL j'étais donc sous MySQL (v 3.23.49). J'ai repris une base précedemment crée : étant donné que MySQL ne prend pas en compte les index descendant, il a été créé un champ (rev_id) qui pour chaque insertion est mis à jour à (-id) (donc un UPDATE en plus). Ce système permet sur une grosse table d'avoir un index pour des "order by desc" et d'améliorer les temps de réponse. Par contre, ça oblige à effectuer un UPDATE supplémentaire lors de l'insertion et ce n'est pas négligeable. D'où ma question, comment se passer de ce champ ? Créer un index ranger par ordre descendant (sous PostgreSQL il ne semble pas que cela soit possible non plus) ? Une autre méthode ? Mon raisonnement est-il faux (un index rangé dans l'ordre décroissant n'est pas intéressant) ?...
Merci pour vos réponses ! (j'ai déjà essayé en vain d'avoir une réponse à ce poblème !)

Reply

Marsh Posté le 09-09-2002 à 12:42:08

tu passes à MySQL 4.0 qui gère les index descendant

---------------
Protèges carnets personnalisés & accessoires pour bébé

Reply

Marsh Posté le 10-09-2002 à 10:48:33

ouaip, c clair que ça peut être un bon argument pour les faire passer au 4. Par contre la 1ère version stable (la 4.1 je crois non) devrait sortir quand ? J'ai entendu parler de novembre ?
Sinon, y'aurait pas une autre feinte de coq ? avec PostgreSQL ? (j'ai même pas l'impression qu'on puisse choisir de trier les index avec PostgreSQL, ça doit être ASC par défaut je pense ?#?...)

Reply

Marsh Posté le 10-09-2002 à 13:55:41

la 4.0.4 est en beta (tout comme la 3), et grosso modo beta chez mysql c'est stable chez microsoft
sinon je pense que le 4.0.5 sera peut être déclarée comme stable (à moins que ce soit la 4.0.4).
Pour la 4.1, à mon avis c'est pour plus tard (la 4.1 alpha sort quand la 4.0.x sera déclarée stable)

Message édité par joce le 10-09-2002 à 13:57:27

---------------
Protèges carnets personnalisés & accessoires pour bébé

Reply

Marsh Posté le

Reply

MySQL vs PostgreSQL - Résultat très bizarre sur insertion

Sujets relatifs:

Leave a Replay