Time to commit a change - Mailing list pgsql-performance

Hi:

   How long does it take to commit a change to change
to the database?

   I'm currently developing a application where
response time should be fast. Today I notice the
following in my application log:

  [12/10/2002     16:49:52] SQL statement created
  [12/10/2002     16:49:58] Updating OK.

The SQL statement is a just a stored procedure that
insert a single row to a table. 6 seconds is quite a
long time to execute an insert statement even if the
table has referential integrity constrants and some
triggers (the database is small, no tables having more
than 100 rows). I tried to recreate the scenario by
doing the following at a psql prompt:

begin;

explain analyze
select
f_credit_insert('0810030358689',3,121002,402,1096,1654,62550/100
,'ADXLXDDN',0); -- call the stored procedure

rollback;

The following is the result of the explain analyze:
pilot=# explain analyze
pilot-# select
f_credit_insert('0810030358689',3,121002,402,1096,1654,62550/
,'ADXLXDDN',0);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual
time=195.95..195.95 rows=1
s=1)
Total runtime: 195.97 msec

NOTICE:  UPDATING fsphdr from f_ti_fspdetl
NOTICE:  Current points = 625
NOTICE:  INSERTING into sc_add_points from
f_ti_fspdetl
NOTICE:  date = 20021210 at f_ti_sc_add_points
NOTICE:  time = 1654 at f_ti_sc_add_points
NOTICE:  transtime = 1654 at f_auto_redeem
NOTICE:  transdate = 20021210 at f_auto_redeem
NOTICE:  balance = 1250
NOTICE:  points needed to redeem = 5000
NOTICE:  Lack the points to merit an auto-redemption
in f_auto_redeem


   Since the database is not yet in "full production"
mode. I put NOTOICEs to help me debug.

   I can only think of the following reasons why it
took 5 seconds to execute the sql statements in a C++
application using libpq while it took 195.67 ms. :
  a) NOTICEs are also written to /var/log/messages so
it can take some time. Does size of the
/var/log/messages affect the time to execute stored
procedures having NOTICE statements?
  b) Connection time overhead.
  c) RAID 5.

  There not much concurrent connection at that time (5
users at most concurrently connected during that time)

  One of the factor that I can't tell is the time it
takes to commit that particular transaction. Are there
ways to approximate the time to commit the changes
given the time it take execute that particular sql
statement (I'm assuming that there is only 1 SQL
statement in that particular transaction).

   Anybody has a idea why it took that long to commit?
My setup is a Pentium 4 with RAID 5. My version of
postgresql is 7.2.2


Thank you very much,

ludwig.

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com