Re: Configuration Advice - Mailing list pgsql-performance

> Any finally, any ideas on planner constants?  Here's what I'm using:
>
> seq_page_cost = 0.5                     # measured on an arbitrary scale
> random_page_cost = 1.0                  # same scale as above
> cpu_tuple_cost = 0.001                  # same scale as above
> cpu_index_tuple_cost = 0.0001           # same scale as above
> cpu_operator_cost = 0.00025             # same scale as above
> effective_cache_size = 679006
>
> I really don't remember how I came up with that effective_cache_size
> number....

I don't have much experience with the way your application works, but:

1) What is the size of the whole database? Does that fit in your memory?
   That's the first thing I'd like to know and I can't find it in your
   post.

   I'm missing several other important values too - namely

     shared_buffers
     max_fsm_pages
     work_mem
     maintenance_work_mem

   BTW, is the autovacuum daemon running? If yes, try to stop it during
   the import (and run ANALYZE after the import of data).

2) What is the size of a disc page? Without that we can only guess what
   doest the effective_cache_size number means - in the usual case it's
   8kB thus giving about 5.2 GiB of memory.

   As suggested in http://www.powerpostgresql.com/PerfList I'd increase
   that to about 1.400.000 which about 10.5 GiB (about 2/3 of RAM).

   Anyway - don't be afraid this breaks something. This is just an
   information for PostgreSQL how much memory the OS is probably using
   as a filesystem cache. PostgreSQL uses this to evaluate the
   probability that the page is in a cache.

3) What is the value of maintenance_work_mem? This is a very important
   value for CREATE INDEX (and some other). The lower this value is,
   the slower the CREATE INDEX is. So try to increase the value as much
   as you can - this could / should improve the import performance
   considerably.

   But be careful - this does influence the amount of memmory allocated
   by PostgreSQL. Being in your position I wouldn't do this in the
   postgresql.conf - I'd do that in the connection used by the import
   using SET command, ie. something like

   SET maintenance_work_mem = 524288;
   CREATE INDEX ...
   CREATE INDEX ...
   CREATE INDEX ...
   CREATE INDEX ...

   for a 512 MiB of maintenance_work_mem. Maybe even a higher value
   could be used (1 GiB?). Just try to fiddle with this a little.

4) Try to set up some performance monitoring - for example a 'dstat' is
   a nice way to do that. This way you can find yout where's the
   bottleneck (memory, I/O etc.)

That's basically all I can think of right now.

Tomas