Search Postgresql Archives

Re: Out of memory/corrupted shared memory problem on server

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Johann Spies <johann.spies@xxxxxxxxx> writes:
> While restoring a dump from our development server (768G ram) to the
> production server, (PG 9.6.3 on Debian Stretch with 128G Ram) the
> refreshing of a Materialized View fails like this:

> local] js@wos=# REFRESH MATERIALIZED VIEW wos_2017_1.citation_window_mv ;
> server closed the connection unexpectedly

> In the log:
> 2017-08-24 19:23:26 SAST [7532-18] LOG:  server process (PID 4890) was
> terminated by signal 9: Killed

As Christoph said, this looks a lot like the kernel OOM killer decided
you'd eaten too much memory.

> The Materialized View  uses a complex query and  should contain 69 772
> 381 records.

How complex is "complex"?  I can think of two likely scenarios:
1. You've stumbled across some kind of memory-leak bug in Postgres.
2. The query's just using too much memory.  In this connection, it's
not good that you've got
> work_mem = 2GB
Remember that "work_mem" is "work memory per plan node", so a complex
query could easily chew up a multiple of that number --- and that's
with everything going according to plan.  If, say, the planner
underestimates the number of table entries involved in a hash
aggregation, the actual consumption might be much larger.

My first move would be to reduce work_mem by an order of magnitude
or two.  If that doesn't help, check the plan for the view's query
and see if it contains any hash aggregation steps --- if so, does
"set enable_hashagg = off" help?  (Also, make sure the view's input
tables have been ANALYZEd recently.)

If none of that helps, we should investigate the memory-leak-bug
theory.  One thing you could do in that direction is to run
the postmaster with a "ulimit -v" size less than what will trigger
the ire of the OOM killer, so that the query encounters a normal
ENOMEM error rather than SIGKILL when it's eaten too much memory.
That should result in it dumping a memory consumption map to stderr,
which would give some clue where the problem is.  We'd need to see
that map as well as details about your query to make progress.

			regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux