pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Performance on large data transformations
2+ messages / 2 participants
[nested] [flat]

* Performance on large data transformations
@ 2003-03-17 17:38 Josh Berkus <josh@agliodbs.com>
  2003-03-17 20:06 ` Re: Performance on large data transformations Ron Mayer <ron@intervideo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Josh Berkus @ 2003-03-17 17:38 UTC (permalink / raw)
  To: pgsql-performance

Folks,

On one database, I have an overnight data transformation procedure that goes 
like:

TableA has about 125,000 records.

Begin Transaction:
1) Update 80% of records in TableA
2) Update 10% of records in TableA
3) Update 65% of records in TableA
4) Update 55% of records in TableA
5) Update 15% or records in TableA with references to other records in TableA
6) Flag what hasn't been updated.
Commit

I've found that, no matter what my FSM settings (I've gone as high as 
1,000,000) by the time I reach step 4 execution has slowed down considerably, 
and for step 5 it takes the server more than 10 minutes to complete the 
update statement. During this period, CPU, RAM and disk I/O are almost idle 
... the system seems to spend all of its time doing lengthy seeks.  There is, 
for that matter, no kernel swap activity, but I'm not sure how to measure 
Postgres temp file activity.

(FYI:  Dual Athalon 1600mhz/1gb/Hardware Raid 1 with xlog on seperate SCSI 
drive/Red Hat Linux 8.0/PostgreSQL 7.2.4)

The only way around this I've found is to break up the above into seperate 
transactions with VACUUMs in between, and "simulate" a transaction by making 
a back-up copy of the table and restoring from it if something goes wrong.  
I've tried enough different methods to be reasonably certain that there is no 
way around this in 7.2.4.

The reason I bring this up is that PostgreSQL's dramatic plunge in performance 
in large serial updates is really problematic for us in the OLAP database 
market, where large data transformations, as well as extensive use of 
calculated temporary tables, is common.    I was particularly distressed when 
I had to tell a client considering switching from MSSQL to Postgres for an 
OLAP database that they might just be trading one set of problems for 
another.

Is there any way we can improve on this kind of operation in future versions 
of PostgreSQL?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Performance on large data transformations
  2003-03-17 17:38 Performance on large data transformations Josh Berkus <josh@agliodbs.com>
@ 2003-03-17 20:06 ` Ron Mayer <ron@intervideo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Ron Mayer @ 2003-03-17 20:06 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; pgsql-performance; +Cc: ron@intervideo.com

Josh Berkus wrote:
>
>There is, for that matter, no kernel swap activity, but I'm not 
>sure how to measure Postgres temp file activity.

Of course you could:

   mv /wherever/data/base/16556/pgsql_tmp /some_other_disk/
   ln -s /some_other_disk/pgsql_tmp /wherever/data/base/16556

and use "iostat" from the "systat" package to watch how much you're
using the disk the temp directory's on.


In fact, for OLAP stuff I've had this help performance because
quite a few data warehousing operations look like:
   First read from main database, 
   do a big hash-or-sort, (which gets written to pgsql_tmp),
   then read from this temporary table and write result to main database

PS: Yes, I know this doesn't help the FSM stuff you asked about.


    Ron





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-03-17 20:06 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-17 17:38 Performance on large data transformations Josh Berkus <josh@agliodbs.com>
2003-03-17 20:06 ` Ron Mayer <ron@intervideo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox