Postgres consuming way too much memory???

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

 



I have a box with an app and postgresql on it. Hardware includes with 2 2.8 Ghz xeons 512KB cache, 4 GB of memory, 6 scsi disk in a software 
raid 5 on a trustix 2.2 with a 2.6.15.3 kernel. The data and indexes are on the raid array while the tx log is on disk 
with the OS. All is well.

The one application executes one transaction every 60 seconds or so. The transaction can range from tiny 
to relatively large. Maybe 30-70k inserts, 60-100k updates... nothing too heavy, take about 8-12 seconds 
to finish the the entire update in the worst case. The application is using the latest jdbc.... I am using 
preparedStatements with addBatch/executebatch/clearBatch to send statements in batches of 10 thousand... 
(is that high?)

The box itself is a little over subscribed for memory which is causing us to swap a bit... As the 
application runs, I notice the postgres process which handles this particular app connection grows in memory seemingly 
uncrontrollably until kaboom. Once the kernel kills off enough processes and the system settles, I see the postgres process is at 1.9GB 
of res memory and 77MB of shared memory. This challenges a number of assumptions I have made in the last while and raises a 
few questions... BTW, I am assuming this is not a memory leak b/c the same install of our software on a box 
with 8GB of memory and no swap being used has no unexplained growth in the memory... it is perfectly healthy 
and quite performant.

Anyway, due to errors in the transaction, it is rolledback afterwhich the postgres process remains at 901MB of 
resident memory and 91MB of of shared memory.

27116 postgres  15   0 1515m 901m  91m S  0.0 22.9  18:33.96 postgres: qradar qradar ::ffff:x.x.x.x(51149) idle

There are a few things I would like to understand. 

- What in the postgres will grow at an uncontrolled rate when the system is under heavy load or the transaction 
  is larger... there must be something not governed by the shared memory or other configuration in postgresql.conf. 
  It seems like, once we start hitting swap, postgres grows in memory resulting in more swapping... until applications 
  start getting killed.
- when the transaction was rolled back why did the process hold onto the 901MB of memory? 
- when is a transaction too big? is this determined by the configuration and performance of wal_buffers and wal log or is there 
  house cleaning which MUST be done at commit/rollback to avoid siutations like this thus indicating there is an upper bound.

I have been configuring postgres from tidbits I collected reading this list in the last few months.... 
not sure if what I have is totally right for the work load, but when I have adequate memory and avoid swap, we are more than 
happy with performance. Configuration which is not below is just the default.

shared_buffers = 32767
work_mem = 20480
maintenance_work_mem = 32768
max_fsm_pages = 4024000
max_fsm_relations = 2000
fsync = false
wal_sync_method = fsync
wal_buffers = 4096
checkpoint_segments = 32
checkpoint_timeout = 1200
checkpoint_warning = 60
commit_delay = 5000
commit_siblings = 5
effective_cache_size = 175000
random_page_cost = 2
autovacuum = true
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay=100
autovacuum_vacuum_cost_limit=100
default_statistics_target = 40

Is there anything here which looks weird or mis configured? I am just starting to play with the bg writer configuration so I did not include.
typically, there is little or no iowait... and no reason to think there is something miconfigured... from what I have seen.

In one transaction i have seen as many as 5 checkpoint_segments be created/used so I was considering increasing wal_buffers to 8192 from 4096 
given as many as 4 segments in memory/cache at once... need to test this though ....

Anyone have any thoughts on what could have caused the bloat? 

thanks


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux