Thread: Time to commit a change
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
On 11 Dec 2002 at 23:35, Ludwig Lim wrote: > How long does it take to commit a change to change > to the database? Shoudln't be long actually.. > [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: I don't believe it would take so long. Last time I benchmarked postgresql on mandrake 8.2, I was able to insert/update/delete in 210-240ms on average. I was benhmarking a server application on a lowly P-III-450 with 256MB RAM and IDE disk. I put 30 clients on that and still excecution time was 240ms. But since there were 20 clients I was getting 240/30=8ms on an average thorughput. All the inserts/updates/deletes were in single transaction as well and tables were small 100-1000 rows. > 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. I don't think any of these matters. What explain throws out is an estimate and it might be wrong as well. > 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). Yes. Try something like this in C/C++ gettimeofday begin transact gettimeofday commit gettimeofday. I am certain it will be in range of 200-250ms. Couldn't get it below that on a network despite of pooled connections.. I am not sure second gettimeofday will be of any help but first and third will definitely give you an idea. > 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 I would put that to 200ms if client and server on same machine. Let us know what it turns out.. HTH Bye Shridhar -- Jim Nasium's Law: In a large locker room with hundreds of lockers, the few people using the facility at any one time will all have lockers next to each other so that everybody is cramped.
On Wed, 11 Dec 2002, Ludwig Lim wrote: > > 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: Was this run while anything else was hitting the database or just by itself? I'd wonder if there were any lock contentions (for example on foreign keys) or anything like that which might have had some effect.
Ludwig, > 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 Disk contention is also a very possible issue. I'd suggest trying the same test when you are certain that no other disk activity is happening. I've seen appalling wait times for random writes on some RAID5 controllers. Also, how about publishing the text of the function? What controller are you using? How many dirves, of what type? -Josh Berkus