[OPEN-ILS-DEV] Postgresql Load Speed

[Mike Rylander]

On Tue, May 5, 2009 at 11:05 AM, Dan Wells <dbw2 at calvin.edu> wrote:
> Hello all,
>
> Well, summer is almost here, and in the academic world, that means more time for side projects like Evergreen :)  After failing in years past for various reasons, we are determined to load our entire catalog into Evergreen in order to get a realistic evaluation environment.
>
> Having been mostly out of the loop for the last nine months, I am happy to note that the overall setup process has been much improved and went quite smoothly.  The data load however, has basically stalled.  While there has not been any errors, the data (around 1 million records) has been loading for close to four days now.
>
> So, here are my questions:
>
> 1) Is there any way to see the progress of the load?  It is currently executing the SQL statements, and has been since Saturday.

You can tell which of the statements is executing with:

SELECT NOW()-query_start as duration, current_query FROM pg_stat_activity;

>
> 2) Is this process memory bound or disk bound?  (It doesn't seem to be stressing the CPU)

That depends on the configuration of Postgres.  If you left it at the
defaults, it's (basically) memory-bound.  Well-tuned, it's IO bound.

>
> 3) Are there any triggers or indexes which can be disabled to speed things up?  (other than materialized_simple_record)
>

No, that's the baddie, and should be disabled automatically.

> 4) Past threads suggest increasing shared_buffers and/or work_mem.  Anyone care to suggest some values for these?  Are there any other config setting to carefully look at?
>

The values depend on your server.  A good set of rules of thumb (IME, anyway):

 * shared_buffers -- 2G or 20% of RAM, whichever is smaller
 * work_mem -- at least 128M on a server with 4G or more RAM, 64M on a
server with less
 * effective_cache_size -- (free memory + cache + shared_buffers) -
10% of total RAM, on a cold system

All of these will need to be adjusted based on DB size, amount of RAM,
and usage patterns.  They're just very basic starting points.

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