Re: autovacuum worker running amok - and me too ;)

On 3/3/2015 6:17 PM, Tom Lane wrote:
> wambacher <wnordmann(at)gmx(dot)de> writes:
>> My system has 24GB of real memory but after some hours one autovacuum worker
>> is using 80-90% of memory, the OOM-Killer (out of memory killer) kills the
>> process with kill -9 and the postgresql-server is restarting because of that
>> problem.
>> i changed the base configuration to use very small buffers, restartetd the
>> server twice but the problem still exists.
>> i think, it's allways the same table and that table is huge: 111GB data and
>> 3 Indices with 4GB, 128 GB and 12 GB. It's the table planet_osm_ways from
>> openstreetmap. maybe that helps.
> Maybe you could reduce the statistics targets for that table.
>
> I think we've heard that the analyze functions for PostGIS data types are
> memory hogs, too --- maybe it's worth inquiring about that on the postgis
> mailing lists.

Most definitely ask on the Postgis list. Identify the full Postgis
version and Postgres versions as well.
We had a case on a trial upgrade (9.1 to 9.3 and Postgis upgrade
(2.0->2.1)) where analyze was running amok memory wise on 3 tables with
geometry types. (posted on this list)
Unfortunately the VM that system was on got corrupted and I wasn't able
to provide the data for a test scenario to Paul Ramsey - so never filed
the bug report.
(perhaps the VM was the issue and NOT the upgrade...) At the time, we
ended up NOT upgrading the production box due this issue potentially
being a show stopper. I've also never tried to re-create the upgrade
stack to test it out on a current copy of the data.

Roxanne
>
> regards, tom lane
>
>

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth