Skip site navigation (1) Skip section navigation (2)

Re: Time to commit a change

From: "Shridhar Daithankar" <shridhar_daithankar(at)persistent(dot)co(dot)in>
To: PostgreSQL Mailing list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Time to commit a change
Date: 2002-12-12 08:07:34
Message-ID: 3DF8911E.6439.8F9553F@localhost (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

pgsql-performance by date

Next:From: Stephan SzaboDate: 2002-12-12 17:15:43
Subject: Re: Time to commit a change
Previous:From: Ludwig LimDate: 2002-12-12 07:35:29
Subject: Time to commit a change

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group