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

Good time of the day!

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

Yes, I know that. What I'm interested in, is how can I _remove_
"begin transaction" - "commit transaction" around single SELECTs. Or
they doesn't affect performance?

Also, there are some, say, INSERTS which are not important. I can
afford loosing this data in case of server crash or something like
that. Is there a way to turn off transactions for such queries?

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

Yes, my DB is vacuumed 4 times a day. It doesn't require it more
often, because I have much more SELECTs than INSERTs.

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

Yes, it's properly indexed and queries are making use of indexes. At
least EXPLAIN says so :))

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

Same with me :)  Currently Postgres is working but it takes all the
processor time... That's the reason of slow responce from DB. Yet I
have no idea why this happened - all my queries are running fast, but
there are too many strings
DEBUG:  ProcessUtility: BEGIN;ROLLBACK;
in the log, and those rollbacks seems to be eating all the CPU...


ED> Erwan

>>>> Dmitri Touretsky <dmitri@listsoft.ru> 12/04 2:42  >>>
ED> Good time of the day!

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

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

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


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

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

ED> I was trying to search the Net for this, but found nothing. I'll be
ED> glad to any idea on where and what to look/check/read.
ED> 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/
---
Those who can't write, write help files.