Re: Would like to know how analyze works technically

["FarjadFarid\(ChkNet\)" <farjad.farid@xxxxxxxxxxxxxxxxx>]

Tony,

 

Before assuming that this is a bug/problem with postgresql and therefore out of your control.

 

It is best to ensure there is no problem with the set up you have. It will save you bundle of time.

 

I have several development system but regularly use postgresql DBs with SSD on a portable (slow CPU)

with several dbs *each* with over 6 million records just in one table and several hundred tables and still don’t get any problem.

 

It would be good to read your post on the final solution.

 

 

 

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of FarjadFarid(ChkNet)
Sent: 01 April 2015 17:14
To: 'TonyS'; pgsql-general@xxxxxxxxxxxxxx
Subject: 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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of TonyS
Sent: 01 April 2015 14:46
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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: 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
Sent from the PostgreSQL - general mailing list archive at Nabble.com.