Re: Would like to know how analyze works technically

Because your system had crashed. I would check everything from bottom up.

The duration of operation and memory usage does suggest it may be hitting a
recurring problem.

First do a simple check on your hard disk. Making sure it is healthy there
are no hanging indexes.

Then rebuild the whole database, this time ensuring *your indexing is much
tighter than before*, also as a test to be less dependent on memory. I
personally use SSDs for my DBs. It saves a lot of time. The other advantage
of SSD is *the relative* lack of performance degradation due to
fragmentation of data.

Once you are happy with this setup then try to optimise the performance. You
might find the cost of a few good SSD will more than pay for themselves in
terms of the cost of your team's time.

Otherwise indexing shouldn't take so long.

Hope this helps.

Good luck.

Best Regards

Farjad

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of TonyS
Sent: 01 April 2015 14:46
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Would like to know how analyze works technically

On Wed, April 1, 2015 9:25 am, FarjadFarid(ChkNet) [via PostgreSQL] wrote:
>

>
> 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: [hidden email]
> [mailto:[hidden email]] On Behalf Of Bill Moran
> Sent: 01 April 2015 13:48
> To: TonyS
> Cc: [hidden email]
> Subject: Re: [GENERAL] Would like to know how analyze works technically
>
>
> On Wed, 1 Apr 2015 04:33:07 -0700 (MST)
> TonyS <[hidden email]> wrote:
>
>
>> On Wed, April 1, 2015 12:18 am, Tom Lane-2 [via PostgreSQL] wrote:
>>
>>>
>>> TonyS <[hidden email]> 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."
>>>

Thanks for the suggestion. What command/tool do you use to check a
PostgreSQL database for corruption?

_____

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