Re: Configuration Advice

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

Current on-disk size is about 51 gig. I'm not sure if there's a different
size I should be looking at instead, but that's what du tells me the
directory for the database in the "base" directory is sized at. So, no,
it doesn't fit into memory all the way.

> I'm missing several other important values too - namely
>
> shared_buffers
> max_fsm_pages
> work_mem
> maintenance_work_mem
>

I didn't share these because they've been in flux :) I've been
experimenting with different values, but currently we're using:

8GB shared_buffers
100000 max_fsm_pages
256MB work_mem
6GB 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).

No. all vacuums are done explicitly since the database doesn't change
during the day. The 'order of operations' is:

- Load COBOL data into database (inserts/updates)
- VACUUM COBOL data
- Summarize COBOL data (inserts/updates with the big table using COPY)
- VACUUM summary tables

So everything gets vacuumed as soon as it's updated.

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

I believe it's 8kB. I definitely haven't changed it :)

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

Okay, I'll try the value you recommend. :)

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

It's currently at 6GB in postgres.conf, though you have a good
point in that maybe that should be before the indexes are made to save
room. Things are certainly kinda tight in the config as is.

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

Thanks for the tips :)

Steve