Rép. : Very slow performance - Mailing list pgsql-novice

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 <dmitri@listsoft.ru> 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)