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:
,'ADXLXDDN',0); -- call the stored procedure
The following is the result of the explain analyze:
pilot=# explain analyze
NOTICE: QUERY PLAN:
Result (cost=0.00..0.01 rows=1 width=0) (actual
Total runtime: 195.97 msec
NOTICE: UPDATING fsphdr from f_ti_fspdetl
NOTICE: Current points = 625
NOTICE: INSERTING into sc_add_points from
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
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,
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
pgsql-performance by date
|Next:||From: Shridhar Daithankar||Date: 2002-12-12 08:07:34|
|Subject: Re: Time to commit a change|
|Previous:||From: Shridhar Daithankar||Date: 2002-12-12 07:13:02|
|Subject: Re: Good/Bad RAID and SCSI controllers?|