Skip site navigation (1) Skip section navigation (2)

Re: Configuration Advice

From: Steve <cheetah(at)tanabi(dot)org>
To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Configuration Advice
Date: 2007-01-17 23:55:20
Message-ID: Pine.GSO.4.64.0701171834010.4471@kingcheetah.tanabi.org (view raw or flat)
Thread:
Lists: pgsql-performance
> Would it be possible to just update the summary table, instead of recreating 
> it from scratch every night?

 	Hrm, I believe it's probably less work for the computer to do if 
it's rebuilt.  Any number of rows may be changed during an update, not 
including additions, so I'd have to pull out what's changed and sync it 
with what's in the summary table already.  It'll be a lot more selects and 
program-side computation to save the big copy; it might work out, but I'd 
say this would be my last ditch thing. :)

>> Building these indexes takes forever!  It's a long grind through inserts 
>> and then building the indexes takes a hefty amount of time too.  (about 9 
>> hours).  Now, the application is likely part at fault, and we're working to 
>> make it more efficient, but it has nothing to do with the index building 
>> time.  I'm wondering what we can do to make this better if anything; would 
>> it be better to leave the indexes on?  It doesn't seem to be.  Would it be 
>> better to use INSERTs instead of copies?  Doesn't seem to be.
>
> Would it help if you created multiple indexes simultaneously? You have enough 
> CPU to do it. Is the index creation CPU or I/O bound? 9 million rows should 
> fit in 16 GB of memory, right?

 	This is a very very interesting idea.  It looks like we're 
probably not fully utilizing the machine for the index build, and this 
could be the ticket for us.  I'm going to go ahead and set up a test for 
this and we'll see how it goes.

> Can you describe the load process in more detail? What's it doing with the 6 
> gigs?

 	There's two halves to the load process; the loader and the 
summarizer.  The loader is the part that takes 6 gigs; the summarizer only 
takes a few hundred MEG.

 	Basically we have these COBOL files that vary in size but 
are usually in the hundred's of MEG realm.  These files contain new data 
OR updates to existing data.  We load this data from the COBOL files in 
chunks, so that's not a place where we're burning a lot of memory.

 	The first thing we do is cache the list of COBOL ID codes that are 
already in the DB; the COBOL ID codes are really long numeric strings, so 
we use a sequenced integer primary key.  The cache translates COBOL IDs to 
primary keys, and this takes most of our memory nowadays.  Our cache is 
fast, but it's kind of a memory hog.  We're working on trimming that down, 
but it's definitely faster than making a query for each COBOL ID.

 	The load is relatively fast and is considered "acceptable", and 
has been relatively constant in speed.  It's the summarizer that's brutal.

 	The summarizer produces 3 main summary tables and a few 
secondaries that don't take much time to make.  Two of them are smallish 
and not that big a deal, and the last one is the biggie that's 9 mil rows 
and growing.  To produce the 9 mil row table, we query out the data in 
groups, do our processing, and save that data to a series of text files 
that are in blocks of 10,000 rows as I recall.  We then copy each file 
into the DB (there were some issues with copying in an entire 9 mil row 
file in the past, which is why we don't use just one file -- those issues 
have been fixed, but we didn't undo the change).

> What's your maintenance_work_mem setting? It can make a big difference in 
> sorting the data for indexes.

 	6 gigs currently. :)

> If you could post the schema including the indexes, people might have more 
> ideas...

 	I'll have to ask first, but I'll see if I can :)

Talk to you later, and thanks for the info!


Steve

In response to

pgsql-performance by date

Next:From: SteveDate: 2007-01-17 23:58:52
Subject: Re: Configuration Advice
Previous:From: Dave CramerDate: 2007-01-17 23:43:44
Subject: Re: Configuration Advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group