Re: Time to commit a change

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.