Обсуждение: PostgreSQL vs MySQL : strange results on insertion

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
forMySQL (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,\'MAJUSCULESet 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,\'MAJUSCULESet 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

-------------------------------------------------------------
NetCourrier, votre bureau virtuel sur Internet : Mail, Agenda, Clubs, Toolbar...
Web/Wap : www.netcourrier.com
Téléphone/Fax : 08 92 69 00 21 (0,34 € TTC/min)
Minitel: 3615 NETCOURRIER (0,15 € TTC/min)

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
defaultwith '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
thingfor 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,\'MAJUSCULESet 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,\'MAJUSCULESet 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
defaultwith '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
withtransactions: 

#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,\'MAJUSCULESet 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