Re: Oracle vs PostgreSQL in real life

On Wed, 2002-02-27 at 23:21, Jean-Paul ARGUDO wrote:
> > What was the postgresql.conf set to ?
>
> I put parameters in another mail, please watch for it.
>
> > > The "test" is a big batch that computes stuffs in the database.
> > Could you run this batch in smaller chunks to see if PG is slow from the
> > start or does it slow down as it goes ?
>
> The batch starts really fast and past 2 minuts, begins to slow down dramatically

This usually means that it is a good time to pause the patch and do a
"vacuum analyze" (or just "analyze" for 7.2)

In 7.2 you can probably do the vacuum analyze in parallel but it will
likely run faster when other backends are stopped.

> and never stops to get slower and slower
> > > Linux Red Hat 7.2 with PostgreSQL 7.2 : hours to go (statistically, 45 hours),
> > > 80 tps (eighty tps).
> > What kind of tps are these ?
>
> Here's what we have in output:
>
> This is the WINDOWS NT4 / Oracle 8.0 ouput when the batch is totally finished:
>
> Time : 00:47:50
>
> Transaction : 25696
> Item : 344341
> Transaction (in milliseconds) : 111
> Item (in milliseconds) : 8
>
> Errors : 0
> Warnings : 0
> PLU not found : 0
> NOM not found : 0
> Alloc NOM : 739
> Free NOM : 739
> Error 1555 : 0
>
> Read : 2093582
> Write : 1772364
> Read/Write : 3865946
>
> Free memory (RAM) : 117396 Ko / 261548 Ko

Assuming these must be interpreted as TABLE COMMAND : COUNT

> PLU SELECT : 344341
> NOM SELECT : 1377364
> T04 SELECT : 1840
> T01 INSERT : 593
> T01 UPDATE : 1376771

This means for postgres with no vacuum in between that you will have in
fact a 1.3M row table to search for 593 actual rows.

Running a (parallel) vacuum or vacuum full and possibly even reindex
will help a lot.

> T02 INSERT : 28810
> T02 UPDATE : 315531

here we have only 10/1 ratio on deleted/live records all of which have
unfortunately be checked for visibility in postgres.

> T03 INSERT : 41199
> T13 INSERT : 9460
> RJT INSERT : 0
> RJT SELECT : 0
>
> --------------------
> Beware "Transaction" does not mean transaction.. a "transaction" here contains one ore
> more "item", in the context of the application/database.

I dont know ECPG very well, but are you sure that you are not running in
autocommit mode, i.e. that each command is not run in its own
transaction.

On the other end of spectrum - are you possibly running all the queries
in one transaction ?

> What for real DML orders: 3.865.946 queries done in 47 min 50 secs. (the queries
> are reparted in many tables, look for detail couting under "Free memory..."
> line.. (a table name is 3 letters long)
>
> Thats 1 347 queries per second... -ouch!

How complex are these queries ?

If much time is spent by backend on optimizing (vs. executing), then you
could win by rewriting some of these as PL/SQL or C procedures that do a
prepare/execute using SPI and use a stored plan.

> This is the Linux Red Hat 7.2 / PostgreSQL 7.2 port of the Pro*C program
> producing the output
>
> As you'll understand, it is not the COMPLETE batch, we had to stop it..:

Can you run VACUUM ANALYZE and continue ?

> Time : 00:16:26
>
> Transaction : 750
> Item : 7391
> Transaction (ms) : 1314
> Item (ms) : 133
>
> Errors : 1
> Warnings : 0
> PLU not found : 0
> NOM not found : 0
> Alloc NOM : 739
> Free NOM : 0
> Error 1555 : 0
>
> Read : 45127.000
> Write : 37849.000
> Read/Write : 82976.000
>
> PLU SELECT : 7391
> NOM SELECT : 29564
> T04 SELECT : 31
> T01 INSERT : 378

Was the T01 table empty at the start (does it have 378 rows) ?

> T01 UPDATE : 29186

could you get a plan for an update on T01 at this point

does it look ok ?

can you make it faster by manipulating enable_xxx variables ?

> T02 INSERT : 3385
> T02 UPDATE : 4006
> T03 INSERT : 613
> T13 INSERT : 281
> RJT INSERT : 0
> RJT SELECT : 0
>
> ---------------- you see
>
> we have 82.976 queries in 16 min 26 seconds thats a
>
> 84 queries per second
>
> --
>
> definitely nothing to do with Oracle :-((

Was oracle out-of-box or did you (or someone else) tune it too ?

> Very bad for us since if this customers kicks Oracle to get PG, it can be really
> fantastic, this customer has much influence on the business....

--------------
Hannu