Re: [PERFORM] two memory-consuming postgres processes

2008-05-03 Thread Merlin Moncure
On Fri, May 2, 2008 at 4:51 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: > > On May 2, 2008, at 1:40 PM, Scott Marlowe wrote: > > > Again, a database protects your data from getting scrambled should the > > program updating it quit halfway through etc... > > > > Right -- but this is a data minin

Re: [PERFORM] two memory-consuming postgres processes

2008-05-03 Thread Matthew Wakeling
On Fri, 2 May 2008, PFC wrote: CREATE TABLE derived AS SELECT ... FROM ... (perform all your derived calculations here) Given what you have said (that you really want all the data in one table) it may be best to proceed like this: First, take your original table, create an index on the prima

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Alexy Khrabrov
On May 2, 2008, at 2:43 PM, Kevin Grittner wrote: Alexy Khrabrov wrote: SInce I don't index on that new column, I'd assume my old indices would do -- do they change because of rows deletions/insertions, with the effective new rows addresses? Every update is a delete and insert. The new ver

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Kevin Grittner
>>> Alexy Khrabrov wrote: > OK. I've cancelled all previous attempts at UPDATE and will now > create some derived tables. See no changes in the previous huge table > -- the added column was completely empty. Dropped it. Should I > vacuum just in case, or am I guaranteed not to have any

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Kevin Grittner
>>> Alexy Khrabrov wrote: > SInce I don't index on that > new column, I'd assume my old indices would do -- do they change > because of rows deletions/insertions, with the effective new rows > addresses? Every update is a delete and insert. The new version of the row must be added to th

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Alexy Khrabrov
On May 2, 2008, at 2:23 PM, Greg Smith wrote: On Fri, 2 May 2008, Alexy Khrabrov wrote: I created several indices for the primary table, yes. That may be part of your problem. All of the indexes all are being updated along with the main data in the row each time you touch a record. Ther

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread PFC
I created several indices for the primary table, yes. Sure I can do a table for a volatile column, but then I'll have to create a new such table for each derived column -- that's why I tried to add a column to the existing table. Yet seeing this is really slow, and I need to to many der

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Greg Smith
On Fri, 2 May 2008, Alexy Khrabrov wrote: I created several indices for the primary table, yes. That may be part of your problem. All of the indexes all are being updated along with the main data in the row each time you touch a record. There's some optimization there in 8.3 but it doesn't

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Alexy Khrabrov
On May 2, 2008, at 2:02 PM, Craig James wrote: On Fri, May 2, 2008 at 2:26 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: I naively thought that if I have a 100,000,000 row table, of the form (integer,integer,smallint,date), and add a real coumn to it, it will scroll through the memory reas

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Craig James
On Fri, May 2, 2008 at 2:26 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: I naively thought that if I have a 100,000,000 row table, of the form (integer,integer,smallint,date), and add a real coumn to it, it will scroll through the memory reasonably fast. In Postgres, an update is the same as

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Alexy Khrabrov
On May 2, 2008, at 1:40 PM, Scott Marlowe wrote: Again, a database protects your data from getting scrambled should the program updating it quit halfway through etc... Right -- but this is a data mining work, I add a derived column to a row, and it's computed from that very row and a small s

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Scott Marlowe
On Fri, May 2, 2008 at 2:26 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: > > So how should I divide say a 512 MB between shared_buffers and, um, what > else? (new to pg tuning :) Don't worry so much about the rest of the settings. Maybe increase sort_mem (aka work_mem) to something like 16M or

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Alexy Khrabrov
Interestingly, after shutting down the server with shared_buffer=1500MB in the middle of that UPDATE, I see this: bash-3.2$ /opt/bin/pg_ctl -D /data/pgsql/ stop waiting for server to shut downLOG: received smart shutdown request LOG: autovacuum launcher shutting down .

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Alexy Khrabrov
On May 2, 2008, at 1:22 PM, Greg Smith wrote: On Fri, 2 May 2008, Alexy Khrabrov wrote: I have an UPDATE query updating a 100 million row table, and allocate enough memory via shared_buffers=1500MB. In addition to reducing that as you've been advised, you'll probably need to increase chec

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Alexy Khrabrov
On May 2, 2008, at 1:13 PM, Tom Lane wrote: I don't think you should figure on more than 1GB being usefully available to Postgres, and you can't give all or even most of that space to shared_buffers. So how should I divide say a 512 MB between shared_buffers and, um, what else? (new to pg

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Greg Smith
On Fri, 2 May 2008, Alexy Khrabrov wrote: I have an UPDATE query updating a 100 million row table, and allocate enough memory via shared_buffers=1500MB. In addition to reducing that as you've been advised, you'll probably need to increase checkpoint_segments significantly from the default (3)

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Tom Lane
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > On Fri, May 2, 2008 at 1:38 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: >> I randomly increased values in postgresql.conf to >> >> shared_buffers = 1500MB >> max_fsm_pages = 200 >> max_fsm_relations = 1 > On a laptop with 2G ram, 1.5Gig shar

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Scott Marlowe
On Fri, May 2, 2008 at 1:38 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: > > > On May 2, 2008, at 12:30 PM, Scott Marlowe wrote: > > > > On Fri, May 2, 2008 at 1:24 PM, Alexy Khrabrov <[EMAIL PROTECTED]> > wrote: > > > > > Greetings -- I have an UPDATE query updating a 100 million row table, > an

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Alexy Khrabrov
On May 2, 2008, at 12:30 PM, Scott Marlowe wrote: On Fri, May 2, 2008 at 1:24 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: Greetings -- I have an UPDATE query updating a 100 million row table, and allocate enough memory via shared_buffers=1500MB. However, I see two processes in top, the U

Re: [PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Scott Marlowe
On Fri, May 2, 2008 at 1:24 PM, Alexy Khrabrov <[EMAIL PROTECTED]> wrote: > Greetings -- I have an UPDATE query updating a 100 million row table, and > allocate enough memory via shared_buffers=1500MB. However, I see two > processes in top, the UPDATE process eating about 850 MB and the writer > p

[PERFORM] two memory-consuming postgres processes

2008-05-02 Thread Alexy Khrabrov
Greetings -- I have an UPDATE query updating a 100 million row table, and allocate enough memory via shared_buffers=1500MB. However, I see two processes in top, the UPDATE process eating about 850 MB and the writer process eating about 750 MB. The box starts paging. Why is there the wri