SEO

Hi:

I want migrate to PostgreSQL 8.0.1 for Windows from PostgreSQL 7.4.1, but I
have a big problem.

In 7.4.1 I have a large view with nested views. It lasts 1 minute and 20
seconds to finish. In 8.0.1 for Windows it lasts.... I don't know. One
hour, two hour, one day... I always cancel the view because it lasts
forever.

Shared buffers and work mem in 8.0.1 are greater than those of 7.4.1. So I
think this isn't a memory problem. Furthermore, the 8.0.1 server has double
RAM than 7.4 server. The "simples" views work right. But when I run the
large view the problem appears.

The database was migrated with pg_dumpall (8.0.1 version) and restored with
psql (8.0.1 version).

Please, I need help.

Thanks in advance.

Indibil

postscript: Sorry for my poor english.

On Tue, 2005-02-22 at 14:03, Indibil wrote:
> Hi:
>
> I want migrate to PostgreSQL 8.0.1 for Windows from PostgreSQL 7.4.1, but I
> have a big problem.
>
> In 7.4.1 I have a large view with nested views. It lasts 1 minute and 20
> seconds to finish. In 8.0.1 for Windows it lasts.... I don't know. One
> hour, two hour, one day... I always cancel the view because it lasts
> forever.
>
> Shared buffers and work mem in 8.0.1 are greater than those of 7.4.1. So I
> think this isn't a memory problem. Furthermore, the 8.0.1 server has double
> RAM than 7.4 server. The "simples" views work right. But when I run the
> large view the problem appears.
>
> The database was migrated with pg_dumpall (8.0.1 version) and restored with
> psql (8.0.1 version).


Have you run vacuum analyze on the new database?

Are the postgresql.conf files pretty much the same (except for the
renaming and moving around caused by it being an 8.0 .conf file...)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

We are noticing on our 7.4.5 system the following files are being written
approximately every 5 minutes

/tmp/.s.PGSQL.5432
/tmp/.s.PGSQL.5432.lock

Anybody know why they are being written? The reason I ask is because we are
running on a system that uses a laptop disk drive and requires at least 10
minutes idle time to spindown so it can recalibrate. This did not happen
when we were running 7.4.2, so does anybody have any idea what may have
changed between 7.4.2 and 7.4.5 to cause this to happen.

TIA
Chris White

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Chris White (cjwhite)" <cjwhite@cisco.com> writes:
> We are noticing on our 7.4.5 system the following files are being written
> approximately every 5 minutes

> /tmp/.s.PGSQL.5432
> /tmp/.s.PGSQL.5432.lock

> Anybody know why they are being written?

So they don't get eaten by overenthusiastic /tmp cleaner scripts.

> The reason I ask is because we are
> running on a system that uses a laptop disk drive and requires at least 10
> minutes idle time to spindown so it can recalibrate. This did not happen
> when we were running 7.4.2, so does anybody have any idea what may have
> changed between 7.4.2 and 7.4.5 to cause this to happen.

In 7.4 it's done every checkpoint. Perhaps you changed your checkpoint
settings?

In 8.0 it looks like the interval is hardwired to 10 minutes; you'd have
to hack postmaster.c if you don't like it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Thanks for information. We will have to look at the changes we made in
postgresql.conf.

Chris White

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailtogsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, February 22, 2005 3:45 PM
To: cjwhite@cisco.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 7.4.5 file write issue

"Chris White (cjwhite)" <cjwhite@cisco.com> writes:
> We are noticing on our 7.4.5 system the following files are being
> written approximately every 5 minutes

> /tmp/.s.PGSQL.5432
> /tmp/.s.PGSQL.5432.lock

> Anybody know why they are being written?

So they don't get eaten by overenthusiastic /tmp cleaner scripts.

> The reason I ask is because we are
> running on a system that uses a laptop disk drive and requires at
> least 10 minutes idle time to spindown so it can recalibrate. This did
> not happen when we were running 7.4.2, so does anybody have any idea
> what may have changed between 7.4.2 and 7.4.5 to cause this to happen.

In 7.4 it's done every checkpoint. Perhaps you changed your checkpoint
settings?

In 8.0 it looks like the interval is hardwired to 10 minutes; you'd have to
hack postmaster.c if you don't like it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Just checked and found that the checkpoint interval is the same between
7.4.2 and 7.4.5. We also brought up a 7.4.2 version and didn't see these
files being written/updated every 5 minutes. In which module are these files
created?

Chris White

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailtogsql-admin-owner@postgresql.org] On Behalf Of Chris White (cjwhite)
Sent: Tuesday, February 22, 2005 4:19 PM
To: 'Tom Lane'
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 7.4.5 file write issue

Thanks for information. We will have to look at the changes we made in
postgresql.conf.

Chris White

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailtogsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, February 22, 2005 3:45 PM
To: cjwhite@cisco.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 7.4.5 file write issue

"Chris White (cjwhite)" <cjwhite@cisco.com> writes:
> We are noticing on our 7.4.5 system the following files are being
> written approximately every 5 minutes

> /tmp/.s.PGSQL.5432
> /tmp/.s.PGSQL.5432.lock

> Anybody know why they are being written?

So they don't get eaten by overenthusiastic /tmp cleaner scripts.

> The reason I ask is because we are
> running on a system that uses a laptop disk drive and requires at
> least 10 minutes idle time to spindown so it can recalibrate. This did
> not happen when we were running 7.4.2, so does anybody have any idea
> what may have changed between 7.4.2 and 7.4.5 to cause this to happen.

In 7.4 it's done every checkpoint. Perhaps you changed your checkpoint
settings?

In 8.0 it looks like the interval is hardwired to 10 minutes; you'd have to
hack postmaster.c if you don't like it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

"Chris White \(cjwhite\)" <cjwhite@cisco.com> writes:
> Just checked and found that the checkpoint interval is the same between
> 7.4.2 and 7.4.5. We also brought up a 7.4.2 version and didn't see these
> files being written/updated every 5 minutes. In which module are these files
> created?

See the TouchSocketLockFile calls in postmaster.c. I'm pretty sure that
logic did not change between 7.4.2 and 7.4.5 ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

I run vacuum analyze and the view WORKS!!

A lot of thanks Scott, (one thousand or more

Indibil



Scott Marlowe wrote:

> On Tue, 2005-02-22 at 14:03, Indibil wrote:
>> Hi:
>>
>> I want migrate to PostgreSQL 8.0.1 for Windows from PostgreSQL 7.4.1, but
>> I have a big problem.
>>
>> In 7.4.1 I have a large view with nested views. It lasts 1 minute and 20
>> seconds to finish. In 8.0.1 for Windows it lasts.... I don't know. One
>> hour, two hour, one day... I always cancel the view because it lasts
>> forever.
>>
>> Shared buffers and work mem in 8.0.1 are greater than those of 7.4.1. So
>> I think this isn't a memory problem. Furthermore, the 8.0.1 server has
>> double RAM than 7.4 server. The "simples" views work right. But when I
>> run the large view the problem appears.
>>
>> The database was migrated with pg_dumpall (8.0.1 version) and restored
>> with psql (8.0.1 version).
>
>
> Have you run vacuum analyze on the new database?
>
> Are the postgresql.conf files pretty much the same (except for the
> renaming and moving around caused by it being an 8.0 .conf file...)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

On Tue, 22 Feb 2005, Chris White (cjwhite) wrote:

CWc> We are noticing on our 7.4.5 system the following files are being written
CWc> approximately every 5 minutes
CWc>
CWc> /tmp/.s.PGSQL.5432
CWc> /tmp/.s.PGSQL.5432.lock
CWc>
CWc> Anybody know why they are being written? The reason I ask is because we are
CWc> running on a system that uses a laptop disk drive and requires at least 10
CWc> minutes idle time to spindown so it can recalibrate. This did not happen
CWc> when we were running 7.4.2, so does anybody have any idea what may have
CWc> changed between 7.4.2 and 7.4.5 to cause this to happen.

What about switching to memory-based /tmp ?

Sincerely,
D.Marck [DM5020, MCK-RIPE, DM3-RIPN]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Dmitry Morozovsky wrote:
> On Tue, 22 Feb 2005, Chris White (cjwhite) wrote:
>
> CWc> We are noticing on our 7.4.5 system the following files are being written
> CWc> approximately every 5 minutes
> CWc>
> CWc> /tmp/.s.PGSQL.5432
> CWc> /tmp/.s.PGSQL.5432.lock
> CWc>
> CWc> Anybody know why they are being written? The reason I ask is because we are
> CWc> running on a system that uses a laptop disk drive and requires at least 10
> CWc> minutes idle time to spindown so it can recalibrate. This did not happen
> CWc> when we were running 7.4.2, so does anybody have any idea what may have
> CWc> changed between 7.4.2 and 7.4.5 to cause this to happen.
>
> What about switching to memory-based /tmp ?

If you take a look at postmaster.c you will see:

/*
* Touch the socket and lock file at least every ten minutes, to
* ensure that they are not removed by overzealous /tmp-cleaning
* tasks.
*/
now = time(NULL);
if (now - last_touch_time >= 10 * 60)
{
TouchSocketFile();
TouchSocketLockFile();
last_touch_time = now;
}

I would change the "10" to perhaps "60" and try that.

I am now thinking we need to make this "10" a GUC variable for just such
cases. This is not the first request for this.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings