Re: Would like to know how analyze works technically

On Wed, April 1, 2015 12:18 am, Tom Lane-2 [via PostgreSQL] wrote:
>

>
> TonyS <tony(at)exquisiteimages(dot)com> writes:
>
>> Running "analyze verbose;" and watching top, the system starts out
>> using no swap data and about 4GB of cached memory and about 1GB of used
>> memory. As it runs, the amount of used RAM climbs, and eventually the
>> used swap memory increases to 100% and after being at that level for a
>> couple of minutes, the analyze function crashes and indicates "server
>> closed the connection unexpectedly."
>
> ANALYZE is supposed to work in a constrained amount of memory, though
> that amount depends quite a bit on what the data is and what you've got the
> statistics target set to.
>
> We've heard reports that there are memory leaks in (some versions of?)
> PostGIS's analyze support. Whether that would apply to you would depend
> on whether you're using PostGIS.
>
> Hard to say more without a lot more concrete info about what your
> data is, what PG version you're using, etc.
>
> regards, tom lane
>

Thanks for the response Tom.

I am not using PostGIS. The data in my system is mostly along the lines of
what you would see in an accounts payable, accounts receivable, and
billing type situation. Names and addresses of individuals, information
about billing, payments received, payments sent etc.

All of my indexes are b-tree indexes.

Currently, the largest individual table is 1.8GB.

select version() returns:
PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit

OS: Ubuntu 14.04.1 LTS

Physical memory: 8GB

The postgresql log has these entries at the crash point:
2015-04-01 06:24:37 EDT LOG: server process (PID 1384) was terminated by
signal 9: Killed
2015-04-01 06:24:38 EDT DETAIL: Failed process was running: analyze verbose;
2015-04-01 06:24:38 EDT LOG: terminating any other active server processes

I started this process at 11PM, so it ran for about 7.5 hours before
crashing.

Is there anything else that would be helpful?

--
View this message in context: http://postgresql.nabble.com/Would-like-to-know-how-analyze-works-technically-tp5844197p5844240.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.