Rép. : Very slow performance

1)
Autocommit is the default behaviour for PostgreSQL. Like MSSQL, unlike
Oracle, ..
if you run batches with several insert or updates, you should
explicitly enclose them in a begin/commit.
This will me much faster.

Also: Did you run VACUUM. This command _must_ be run on a regular basis
or your performances will slowly go down. 
Once a day seems to be a good start, plusafter every large amount of
changes (batches).
See the doc for all options to the VACUUM command.

And: Indexes can speed up queries if correctly used. 've seen queries
speedup to 100x.

2) these statements are just info, not error messages. 
However, I don't know why it says 'rollback'.


Erwan

>>> Dmitri Touretsky  12/04 2:42  >>>
Good time of the day!

Sorry in advance if the question is too stupid... After some updates
in apllications my DB starts to respond ve-e-e-ry slowly. Looking in
the debug log I found:

1. Every query (including SELECTs) are "wrapped" into transactions.
E.g.
2002-12-04 01:54:12 [353]    DEBUG:  StartTransactionCommand
2002-12-04 01:54:12 [353]    DEBUG:  query: SELECT * FROM ....
2002-12-04 01:54:12 [353]    DEBUG:  CommitTransactionCommand

Is in normal or not? In code I use transactions only in some places
where few INSERTS or UPDATES goes one-by-one...


2. In the log pretty often I see following sequences:
2002-12-04 01:53:52 [353]    DEBUG:  StartTransactionCommand
2002-12-04 01:53:52 [353]    DEBUG:  query: SELECT * FROM ....
2002-12-04 01:53:52 [353]    DEBUG:  CommitTransactionCommand
2002-12-04 01:53:52 [353]    DEBUG:  StartTransactionCommand
2002-12-04 01:53:52 [353]    DEBUG:  query: BEGIN;ROLLBACK;
2002-12-04 01:53:52 [353]    DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2002-12-04 01:53:52 [353]    DEBUG:  CommitTransactionCommand
2002-12-04 01:53:52 [353]    DEBUG:  StartTransactionCommand
2002-12-04 01:53:52 [353]    DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
2002-12-04 01:53:52 [353]    DEBUG:  CommitTransactionCommand
2002-12-04 01:54:12 [353]    DEBUG:  StartTransactionCommand
2002-12-04 01:54:12 [353]    DEBUG:  query: SELECT * FROM ...
2002-12-04 01:54:12 [353]    DEBUG:  CommitTransactionCommand

Strings like "ProcessUtility: BEGIN;ROLLBACK;" makes me warry that
something is wrong in here... But I don't see what is causing
RALLBACK... I suspect that slow performance came from here, because
often ROLLBACK is followed by a noticable delay (20-30 seconds).

I was trying to search the Net for this, but found nothing. I'll be
glad to any idea on where and what to look/check/read.
Just in case: PostgreSQL 7.2, FreeBSD.



Best regards,
 Dmitri ( mailto:dmitri@listsoft.ru )

New SOFT daily (RUS):  http://www.listsoft.ru/ 
               (ENG):  http://www.listsoft.com/ 
Articles, tips      :  http://www.diskovod.ru/ 
---
Computer - A device designed to speed and automate errors.


---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)