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.
