PostgreSQL vs MySQL : strange results on insertion

On Thu, 5 Sep 2002 fpaul@netcourrier.com wrote:

I launch these programs on my computer : Linux Debian with MySQL 3.23.51 and PostgreSQL 7.2.1 (installation by default with 'apt-get install').
Time to realize 10000 insertions with MySQL:
$ time ./test__mysql

real 0m1.500s
user 0m0.150s
sys 0m0.090s
(between 1 and 2 seconds)

Time to realize 10000 insertions with PostgreSQL:
$time ./test_postgresql

real 0m28.568s
user 0m0.390s
sys 0m0.270s
(between 28 and 30 seconds !!!.... )

Very strange, isn't it ? Is there something in PostgreSQL's C API that I didn't understand ? Subtleties during the configuration ? I do not want to believe that PostgreSQL is 15 times slower than MySQL !
Thank you for any comment, remark and correction!

Not really all that strange. Again, this is a side effect of using a
transactionally oriented database. Try adding a begin; and end; pair
around your inserts in postgresql and see if it runs faster.

fpaul@netcourrier.com a écrit:

Hello,

I'm making some tests to migrate a MySQL DB to PostgreSQL DB. I realized a small program in C which does the same thing for MySQL (C API) and PostgreSQL (libpq) : 10000 insertion in a quite simple base.
My DB :
|-----------------|----------------------------------------------|
| test |
| id | auto_increment (or serial for postgreSQL) |
| type_int | INT (or integer) |
| type_varchar | varchar(255) |
| type_int2 | INT (or integer) |
| type_text | text |
|-----------------|----------------------------------------------|

/* -------------------- 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;
}

/* -------------------- 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;
}

I launch these programs on my computer : Linux Debian with MySQL 3.23.51 and PostgreSQL 7.2.1 (installation by default with 'apt-get install').
Time to realize 10000 insertions with MySQL:
$ time ./test__mysql

real 0m1.500s
user 0m0.150s
sys 0m0.090s
(between 1 and 2 seconds)

Time to realize 10000 insertions with PostgreSQL:
$time ./test_postgresql

real 0m28.568s
user 0m0.390s
sys 0m0.270s
(between 28 and 30 seconds !!!.... )

Very strange, isn't it ? Is there something in PostgreSQL's C API that I didn't understand ? Subtleties during the configuration ? I do not want to believe that PostgreSQL is 15 times slower than MySQL !
Thank you for any comment, remark and correction!

Florent Paul

pgsql launches 10000 transactions (I don't know if mysql does this)
You should launch a "BEGIN;" before your 10000 insert and an "END;" after.
or better: test your server and discover the proper number of insert
to be done in one transaction to have the max speed.
For mine, 3000 insert for each transaction is good.

Time to realize 10000 insertions with PostgreSQL:
$time ./test_postgresql

real 0m28.568s
user 0m0.390s
sys 0m0.270s
(between 28 and 30 seconds !!!.... )

Very strange, isn't it ? Is there something in PostgreSQL's C API that I
didn't understand ? Subtleties during the configuration ? I do not want to
believe that PostgreSQL is 15 times slower than MySQL ! Thank you for any
comment, remark and correction!

On my system (compiled for profiling, debugging and slow disk) it takes 2 minutes without transactions, and 4.5 seconds with transactions:

#include <stdio.h>
#include <libpq-fe.h>

#define INSERTION "INSERT INTO test (type_int, type, type_int2, type_text) VALUES (%d,\'essai de chaine decaractère\',100,\'MAJUSCULES et minuscules\')"

int main(int argc, char **argv) {
PGconn *conn;
unsigned int i;
char mquery[1000];
PGresult *res;

conn=PQconnectdb("dbname=mario");
if (PQstatus(conn) == CONNECTION_OK) {
res=PQexec(conn,"begin");
PQclear(res);
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);
res=PQexec(conn,"commit ");
PQclear(res);
PQfinish(conn);
return 0;
}
}
PQclear(res);
PQfinish(conn);
}
else {
printf("sql connection error :%s\n",PQerrorMessage(conn));
return 0;
}
return 0;
}

best regards,
mario weilguni