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

[John Fink]

Mike,

Thanks a lot for the pointers, I appreciate it.  I'll try removing the GIST
indexes first, and if that fails, I'll try removing BEGIN.  And if
*that*fails, I'll start pestering my boss for gobs of RAM and/or a new
machine.

jf

On Fri, Feb 22, 2008 at 11:35 AM, Mike Rylander <mrylander at gmail.com> wrote:

> 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
>



-- 
http://libgrunt.blogspot.com -- library culture and technology.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://list.georgialibraries.org/pipermail/open-ils-dev/attachments/20080222/834d8302/attachment-0001.html