Re: PostgreSQL vs MySQL : strange results on insertion
От | frbn |
---|---|
Тема | Re: PostgreSQL vs MySQL : strange results on insertion |
Дата | |
Msg-id | 3D7792CF.3080306@efbs-seafrigo.fr обсуждение исходный текст |
Ответ на | PostgreSQL vs MySQL : strange results on insertion (fpaul@netcourrier.com) |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления:
Предыдущее
От: "scott.marlowe"Дата:
Сообщение: Re: PostgreSQL vs MySQL : strange results on insertion
Следующее
От: Oleg BartunovДата:
Сообщение: Re: Problem with restoring dump (may be tsearch-related)