Re: Create table from view, large data - out of memory (postgresql 8.2.0)

On Mon, Mar 26, 2007 at 01:02:46PM +0300, Peter Petrov wrote:
> Hi all,
>
> PostgreSQL version: 8.2.0 on Linux xeonito 2.6.19.3 #1 SMP Mon Feb 12
> 18:57:16 EET 2007 i686 i686 i386 GNU/Linux
> Slackware 11.0.0
>
> Memory parameters from postgresql.conf:
> shared_buffers = 512MB
> work_mem = 128MB
> maintenance_work_mem = 512MB
> max_fsm_pages = 1638400
> effective_cache_size = 2200MB
>
> cat /proc/sys/kernel/shmmax - 4000000000
>
> Memory parameters are smaller, because I want to avoid "out of memory"
> if there are a few parallel queries.

You don't say how much memory you have, but in any case I find your
parameters on the high side. Which may be appropriate in your case, but
worth check, especially if it's a 32-bit machine.

Note that work_mem is counted per sort, and probably for the hash too,
so that's at least four times 128MB possible. You may want to check the
maximum amount of memory allowed one process, because you're likely
going well over 1GB of memory here.

You don't specify the exact query, but it's possible that by creating
some indexes you can avoid some sorts, which saves memory also.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.