Re: Would like to know how analyze works technically - Mailing list pgsql-general

It sounds like your system had crashed several times.

My suggestion would be first ensure that your tables and indexes are not
corrupted.

Second suggestion is to ensure your index is tightly represents the data you
are accessing.  The tighter it is the faster the response time. The less
memory and CPU usage.

Of course these are basic for any good DB but these essential before moving
to more complex issues.



-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bill Moran
Sent: 01 April 2015 13:48
To: TonyS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Would like to know how analyze works technically

On Wed, 1 Apr 2015 04:33:07 -0700 (MST)
TonyS <tony@exquisiteimages.com> wrote:

> On Wed, April 1, 2015 12:18 am, Tom Lane-2 [via PostgreSQL] wrote:
> >
> > TonyS <tony@exquisiteimages.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?

Don't know if I'm on the right track with this, but what is
maintenance_work_mem set to on this system?

--
Bill Moran


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general