[OPEN-ILS-DEV] Re: postgres choking on my giant pile o' bibs

[Mike Rylander]

On Fri, Feb 22, 2008 at 10:18 AM, John Fink <john.fink at gmail.com> wrote:
> Dan Scott made a good point here -- I should point out that the machine I'm
> running this on has 1GB of RAM.
>

Dan has probably suggested this, but if you're willing to risk an
incomplete (and unusable) data load if there are any problems in the
data further along than you got, you can remove the BEGIN from the top
of the main SQL file.  However, with more than 60M rows going into
metabib.full_rec via copy, each with an FKEY check and a trigger to
make the data indexable by tsearch2, that may not be enough.

Another thing that would help, though, would be to remove the GIST
indexes used for searching.  Before loading the data issue the
following SQL at a psql prompt:

DROP INDEX metabib.metabib_title_field_entry_index_vector_idx;
DROP INDEX metabib.metabib_subject_field_entry_index_vector_idx;
DROP INDEX metabib.metabib_keyword_field_entry_index_vector_idx;
DROP INDEX metabib.metabib_author_field_entry_index_vector_idx;
DROP INDEX metabib.metabib_series_field_entry_index_vector_idx;
DROP INDEX metabib.metabib_full_rec_index_vector_idx;

And after, use this SQL:

CREATE INDEX metabib_title_field_entry_index_vector_idx ON
metabib.title_field_entry USING GIST (index_vector);
CREATE INDEX metabib_keyword_field_entry_index_vector_idx ON
metabib.keyword_field_entry USING GIST (index_vector);
CREATE INDEX metabib_subject_field_entry_index_vector_idx ON
metabib.subject_field_entry USING GIST (index_vector);
CREATE INDEX metabib_author_field_entry_index_vector_idx ON
metabib.author_field_entry USING GIST (index_vector);
CREATE INDEX metabib_series_field_entry_index_vector_idx ON
metabib.series_field_entry USING GIST (index_vector);
CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.full_rec
USING GIST (index_vector);

Let us know if that helps, but I think the main thing is the low
memory-to-records ratio.

--miker

> jf
>
>
>
> On Fri, Feb 22, 2008 at 9:14 AM, John Fink <john.fink at gmail.com> wrote:
>
> > OK, this isn't really Evergreen specific, admittedly, but:
> >
> > My bib load converted okay, I stopped Evergreen, logged into postgres
> console, and started the load.  Then I went home.  I come in now, and this
> is what I see:
> > evergreen=# \i pg_loader-output.sql
> > SET
> > BEGIN
> > COPY
> > COPY
> > COPY
> > COPY
> > COPY
> > COPY
> > COPY
> > psql:pg_loader-output.sql:12: ERROR:  out of memory
> > DETAIL:  Failed on request of size 32.
> > CONTEXT:  COPY full_rec, line 59785765: "               987342  m
> 999    1"
> >
> > Out of memory!  Gaaah.  Any way to do a parallel-type load into Postgres?
> >
> > jf
> >
> > --
> > http://libgrunt.blogspot.com -- library culture and technology.
>
>
>
> --
> http://libgrunt.blogspot.com -- library culture and technology.



-- 
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / The Evergreen Experts
 | phone: 1-877-OPEN-ILS (673-6457)
 | email: miker at esilibrary.com
 | web: http://www.esilibrary.com