PostgreSQL 8 on windows very slow

lol wrote:

Hi,

I'm currently testing several databases for an application written in
Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000
SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT
request. Have-you seen this problem ? May be some parameters should be
adjusted. What should I check ?
Thanks

I think first you should use a newer RC. In the first RC there were some
probs...

With RC3 I don't have such probs. Sure If I compared Win and Linux PG
and ma results where that LINUX is about 3 times faster under high access.

Daniel

Daniel Schuchardt wrote:

lol wrote:

Hi,

I'm currently testing several databases for an application written in
Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000
SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT
request. Have-you seen this problem ? May be some parameters should be
adjusted. What should I check ? Thanks

I think first you should use a newer RC. In the first RC there were
some probs...

With RC3 I don't have such probs. Sure If I compared Win and Linux PG
and ma results where that LINUX is about 3 times faster under high
access.

Daniel

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

I don't have any probs with RC3 either. I was wondering if the
PostgreSQL community has any support for gay users? Does the Linux
version have gay mode and is it faster?

I have tried RC4, but there's no differences

My results using my configuration are :
MySQL 4 is 6 times faster than pgSQL
Firebird 1.5 is 3 times faster than pgSQL

Are these results coherent ? May be the problem comes more from ZeosLib
than pgSQL8

lol wrote:

I have tried RC4, but there's no differences

My results using my configuration are :
MySQL 4 is 6 times faster than pgSQL
Firebird 1.5 is 3 times faster than pgSQL

Are these results coherent ? May be the problem comes more from ZeosLib
than pgSQL8

what zeoslib do you use?

6.X?

thay are known slow. I still use 5.X. I haven't tried this yet. Do you
use Delphi? Have you tried dbexpress? (www.vitavoom.com)

Daniel.

I use zeoslib 6.1.5 for Delphi 7. I have also tried 6.5.1-alpha. It's
faster (especially with firebird).
I will take a look at dbExpress.

Thank you.

What queries are you running?
What sort of a machine are the database systems running on?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of lol
Sent: Tuesday, January 11, 2005 6:47 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL 8 on windows very slow

I have tried RC4, but there's no differences

My results using my configuration are :
MySQL 4 is 6 times faster than pgSQL
Firebird 1.5 is 3 times faster than pgSQL

Are these results coherent ? May be the problem comes more from ZeosLib
than pgSQL8

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On 11 Jan 2005 at 8:16, lol wrote:

I use zeoslib 6.1.5 for Delphi 7. I have also tried 6.5.1-alpha. It's
faster (especially with firebird).
I will take a look at dbExpress.

I've recently started using dbExpress with Delphi 6 for a desktop
client application, and have found it fast and easy to use.

--Ray.

-------------------------------------------------------------
Raymond O'Donnell http://www.galwaycathedral.org/recitals
rod@iol.ie Galway Cathedral Recitals
-------------------------------------------------------------

Well, for SELECT request pgsql runs well, although it's a little
slower than firebird & MySQL.
The problem is especially for INSERT request. It's strange there's a
lot of disk access, as if it doesn't use the cache or something like
that (I'm not a db expert).
The application runs on windows 2000 SP4 english, developped in Delphi
7 and access the database using ZeosLib 6.1.5 (I have also tried with
6.5.1-alpha 2).
We don't make a lot of request in our application. We use MS-Access 97
and we want to change for an SQL compliant open source database.
Out application with pgsql (but I think the problem is zeoslib) is a
little slower than ms-access ! We have good results with MySQL and
Firebird (using zeoslib).

Thank you

In our testing Postgres came out on top, well MySql was faster if you
use the non-transactional table type "MyISAM". If you use the InnoDb
table type it comes out slower. All of this is very subjective depending
on your querys and data set. Have you run the ANALYZE command on
Postgres since you have added the data into the tables? It makes a huge
difference in performance for us.

Hope that helps, Jasper

lol wrote:

Out application with pgsql (but I think the problem is zeoslib) is a
little slower than ms-access ! We have good results with MySQL and
Firebird (using zeoslib).

access ? really ?
what is the size of your dataset ?
i've seen an access application literally die, belly-up with like 15
minutes queries, and the data set size was tiny by postgresql standards !
If you do 10.000 inserts, each in its transaction, sure postgres is going
to be slow as hell... IMHO you should check if your library isn't turning
on autocommit and/or adding begin's and commit's behind your back.

You can make the following test :

CREATE TABLE dummy( id serial, data integer );
INSERT INTO dummy (data) VALUES (0);
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;

EXPLAIN ANALYZE INSERT INTO dummy (data) SELECT data FROM dummy;
Seq Scan on dummy (cost=0.00..1388.05 rows=68870 width=4) (actual
time=0.084..1024.668 rows=65536 loops=1)
Total runtime: 2428.795 ms

With a vanilla IDE 7200rpm disk on Linux. If you get 10 seconds, there's
a problem in your conriguration.

Now, a little demonstration of MySQL, with an half-completed query :

mysql> create table test (id integer);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test (id) values (1),(2),(3),(3),(4),(5);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> create table testu (id integer, unique(id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testu select * from test;
ERROR 1062: Duplicate entry '3' for key 1
mysql> select * from testu;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

--- Now, even better

mysql> delete from testu;
Query OK, 3 rows affected (0.00 sec)

mysql> insert into testu (id) values (1),(2),(3),(3),(4),(5);
ERROR 1062: Duplicate entry '3' for key 1
mysql> select * from testu;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

I've runned your test: 3304 ms (7200 rpm hard disk, pentium3 1Ghz).