Re: Updates are slow..

send the output of explain and explain analyse for the query this may help
determine what is happening during the query.



Darren Ferguson

On Tue, 11 Jun 2002, Tom Burke wrote:

> Hello,
>
> We've been using PostgreSQL for a few months and it has been running
> much slower than anticipated. We've tried playing with configuration
> options with limited results, and I'm hoping someone may have some tips.
>
> For the purposes of our performance test, we created a PostgreSQL
> database with only a single table of about 1.2 million records. Updating
> an indexed column for 600K records on that table is unusually slow. It
> takes anywhere from forty minutes to over an hour.
>
> To describe the situation, I'm going to call our table EMP (employee).
> Our EMP has 1.2MM records, and 600K of those records have a Dept_ID of 4.
> Our EMP has 20 columns, and there are two indexes on EMP:
> 1) (EMP_ID)
> 2) (DEPT_ID, EMP_STATUS)
>
> The update statement that takes so long is:
> UPDATE Emp
>    SET Dept_ID = 5
>  WHERE Dept_ID = 4;
>
> Thus it is reading from the index and also having to update it at the
> same time. As I mentioned, 600K records are impacted by this update. An
> out of the box configuration of PostgreSQL takes over an hour to do this.
>
> I've tried changing the default settings as follows:
> shared_buffers = 8192
> sort_mem = 15360
> fsync = false
> checkpoint_segments = 15
> wal_files = 10
> wal_sync_method = fdatasync
>
> With these settings, it still takes 39 minutes. Here are the executor
> statistics from the last test we did yesterday:
> 2002-06-10 13:15:18 DEBUG:  EXECUTOR STATISTICS
> ! system usage stats:
> !       2315.929107 elapsed 142.990000 user 87.310000 system sec
> !       [163.390000 user 93.610000 sys total]
> !       0/0 [0/0] filesystem blocks in/out
> !       8/14 [16609/5363] page faults/reclaims, 0 [0] swaps
> !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
> !       0/0 [0/0] voluntary/involuntary context switches
> ! postgres usage stats:
> !       Shared blocks:     552060 read,     476736 written, buffer hit rate =
> 95.33%
> !       Local  blocks:          0 read,          0 written, buffer hit rate =
> 0.00%
> !       Direct blocks:          0 read,          0 written
>
>
> For the basis of comparison, we downloaded Oracle, created the same table,
> and loaded the same data. With Oracle, the same update took at most 5 minutes!
>
> Our machine is a PIII 850 with 768MB RAM and an ATA100 40GB drive and RH Linux
> 7.2.  Postgres is v7.2. Every time we run the test on Postgres, we a vacuum
> analyze immediately before.  Foreign key constraints are not enabled.
>
> I would not expect the performance difference between Oracle and Postgres to
> be so large. While the example we chose for our test may not happen that often
> (updating an indexed column), various other queries we run on our full system
> have not performed to our expectations.
>
> I'm hoping we're overlooking something basic. If anyone has any suggestions,
> I would greatly appreciate it.
>
> Thanks,
>
> Tom Burke
> Eppend, Inc.
> http://www.eppend.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>