Обсуждение: Postgresql 9.2 OOM

I recently upgraded from Postgresql 9.0.10 to 9.2.1. I am now running into problems with Postgresql running out of
memoryduring large data operations, more specifically loading the OpenStreetMap data into the database. The load under
9.0went fine and there were no memory issues. This is on the exact same machine, same postgresql.conf, same everything
exceptfor the upgrade to 9.2. Initially the OOM killer was kicking in and killing Postgresql. Once I set
vm.overcommit_memory=2,Postgresql just reports it is OOM rather than being killed. It seems the Postgresql process
keepsusing up more and more memory until it eventually fails, almost as if there is a leak. 

Are there any new 9.2 memory usage parameters I may have overlooked? Here are some parameters I have set that worked
fineunder 9.0: 

max_connections = 100
max_locks_per_transaction = 100
effective_cache_size=8GB
shared_buffers=4GB
work_mem=8MB
maintenance_work_mem=4GB
synchronous_commit=off
checkpoint_segments=100
checkpoint_timeout=10min
checkpoint_completion_target=0.9

The machine has 12 cores (24 w/ HT), 24 GB RAM, and is running CentOS 6.3 64-bit with all of the latest updates
applied.As I mentioned, over time, the Postgresql processes keep increasing memory usage until all physical memory is
usedup, and the process then fails. 

In the logs, after I got the OOM, I noticed I had several thousand lines
similar to the following:

    CachedPlan: 7168 total in 3 blocks; 2472 free (0 chunks); 4696 used
    CachedPlan: 7168 total in 3 blocks; 3200 free (0 chunks); 3968 used
    CachedPlan: 1024 total in 1 blocks; 64 free (0 chunks); 960 used
    CachedPlanSource: 3072 total in 2 blocks; 1288 free (1 chunks); 1784
used
      CachedPlanQuery: 1024 total in 1 blocks; 112 free (0 chunks); 912 used
    SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used

I am not exactly sure what this means, but could it be that the CachedPlans
are somehow not being freed and are accumulating until the OOM?



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-9-2-OOM-tp5726013p5726014.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

One further question, how exactly can a CachedPlan leak? The OpenStreetMap
data load does use plsql and triggers are involved as well. Is there
something new to 9.2 that changes the way CachedPlans are cleaned up?



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-9-2-OOM-tp5726013p5726016.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

bryanck <bryanck@gmail.com> writes:
> One further question, how exactly can a CachedPlan leak? The OpenStreetMap
> data load does use plsql and triggers are involved as well. Is there
> something new to 9.2 that changes the way CachedPlans are cleaned up?

9.2 is more aggressive about caching plans, but you've provided no
details that would allow somebody else to investigate this report.

            regards, tom lane

Is there a setting to make 9.2 less aggressive about cached plans? It seems a
bit odd to have the database keep caching things until it craps out, even if
it does improve performance in some cases.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-9-2-OOM-tp5726013p5726034.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.