pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
effective_cache_size 2+ messages / 2 participants [nested] [flat]
* effective_cache_size @ 2018-10-08 13:53 Joel Benelli <joel.benelli@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Joel Benelli @ 2018-10-08 13:53 UTC (permalink / raw) To: pgsql-admin@lists.postgresql.org Hi, We have a Linux server 16GB RAM and HD SSD with Postgres 9.5 and configuration: max_connections = 155 shared_buffers = 4GB work_mem = 14MB maintenance_work_mem = 744MB effective_io_concurrency = 200 effective_cache_size = 12GB With these settings the memory usage is between 10 ~ 13GB and when running a dump occur errors of "out of memory" preventing new requests. By changing the effective_cache_size setting to 6GB, the memory usage is between 8 ~ 12GB, without errors during the dump. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: effective_cache_size @ 2018-10-08 14:49 Tom Lane <tgl@sss.pgh.pa.us> parent: Joel Benelli <joel.benelli@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2018-10-08 14:49 UTC (permalink / raw) To: Joel Benelli <joel.benelli@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org Joel Benelli <joel.benelli@gmail.com> writes: > We have a Linux server 16GB RAM and HD SSD with Postgres 9.5 and > configuration: > ... > effective_cache_size = 12GB > With these settings the memory usage is between 10 ~ 13GB and when running > a dump occur errors of "out of memory" preventing new requests. By changing > the effective_cache_size setting to 6GB, the memory usage is between 8 ~ > 12GB, without errors during the dump. That seems *very* odd. The only thing effective_cache_size actually affects is the planner's opinion of the cost of indexscans [1], so that it might choose different query plans after you change the setting. But there's no obvious reason why that would lead to memory problems in one case and not the other. I'd suggest looking in the postmaster log to discover exactly which query is failing, and then manually EXPLAIN'ing that query with both settings to see what plans we are talking about. That might yield some more insight. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-10-08 14:49 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-10-08 13:53 effective_cache_size Joel Benelli <joel.benelli@gmail.com> 2018-10-08 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox