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
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++
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..
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 Szabo||Date: 2002-12-12 17:15:43|
|Subject: Re: Time to commit a change|
|Previous:||From: Ludwig Lim||Date: 2002-12-12 07:35:29|
|Subject: Time to commit a change|