Re: PostgreSQL vs MySQL : strange results on insertion

fpaul(at)netcourrier(dot)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.