Re: [PERFORM] PG writes a lot to the disk

[Laurent Raufaste]

2008/3/19, Laurent Raufaste <[EMAIL PROTECTED]>:
>  What does it writes so much in the base directory ? If it's some
>  temporary table or anything, how can I locate it so I can fix the
>  problem ?

Thanks for your help everybody ! I fixed the problem by doing an
ANALYZE to every table (yes I'm so noob ;) ).

The problem was that the optimiser didn't know how to run the queries
well and used millions of tuples for simple queries. For each tuple
used it was updating some bit in the table file, resulting in a huge
writing activity to that file.

After the ANALYZE, the optimiser worked smarter, used thousand time
less tuple for each query, and PG was not required to update so much
bits in the table files.

The server is now OK, thanks !

-- 
Laurent Raufaste


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

Re: [PERFORM] PG writes a lot to the disk

[Albe Laurenz]

Laurent Raufaste wrote:
> The problem was that the optimiser didn't know how to run the queries
> well and used millions of tuples for simple queries. For each tuple
> used it was updating some bit in the table file, resulting in a huge
> writing activity to that file.

Good that you solved your problem.

PostgreSQL doesn't write into the table files when it SELECTs data.

Without an EXPLAIN plan it is impossible to say what PostgreSQL
was doing, but most likely it was building a large hash structure
or something similar and had to dump data into temporary files.

Yours,
Laurenz Albe

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

Re: [PERFORM] PG writes a lot to the disk

[Bill Moran]

In response to "Albe Laurenz" <[EMAIL PROTECTED]>:

> Laurent Raufaste wrote:
> > The problem was that the optimiser didn't know how to run the queries
> > well and used millions of tuples for simple queries. For each tuple
> > used it was updating some bit in the table file, resulting in a huge
> > writing activity to that file.
> 
> Good that you solved your problem.
> 
> PostgreSQL doesn't write into the table files when it SELECTs data.
> 
> Without an EXPLAIN plan it is impossible to say what PostgreSQL
> was doing, but most likely it was building a large hash structure
> or something similar and had to dump data into temporary files.

As a parting comment on this topic ...

Based on his previous messages, he was able to definitively tie
filesystem write activity to specific tables, but also claimed that
his PG logs showed only SELECT statements being executed.

However, the part I wanted to comment on (and got busy yesterday so
am only getting to it now) is that there's no guarantee that SELECT
isn't modifying rows.

SELECT nextval('some_seq');

is the simplest example I can imagine of a select that modifies database
data, but it's hardly the only one.  I suspect that the OP has procedures
in his SELECTs that are modifying table data, or triggers that do it ON
SELECT or something similar.

Of course, without any details, this is purely speculation.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

[EMAIL PROTECTED]
Phone: 412-422-3463x4023

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

Re: [PERFORM] PG writes a lot to the disk

[Tom Lane]

Bill Moran <[EMAIL PROTECTED]> writes:
> However, the part I wanted to comment on (and got busy yesterday so
> am only getting to it now) is that there's no guarantee that SELECT
> isn't modifying rows.

Another way that SELECT can cause disk writes is if it sets hint bits on
recently-committed rows.  However, if the tables aren't actively being
modified any more, you'd expect that sort of activity to settle out pretty
quickly.

I concur with the temporary-file theory --- it's real hard to see how
analyzing the tables would've fixed it otherwise.

regards, tom lane

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

[PERFORM] PostgreSQL NetApp and NFS

[Chris Hoover]

I just found out that my company is planning on migrating my databases from
our current ISCSI storage solution to NetApps connected via NFS.  I knew
about the NetApp migration, but always assumed (and shame on me) that I
would have direct attachments to the servers.

Well, I am very uncomfortable with the NFS attachement.  I have been
searching the archives and see a lot of anocdotal stories of NFS horrors,
but so far, nothing but general stories and statements.

I need to know if anyone out there is/has run their PostgreSQL on NetApp
arrays via NFS.  My particular situation is RH Linux 4 servers running
Postgresql 8.1.  I need to provide our Operations manager with specific
reasons why we should not run PostgreSQL over NetApp NFS.  Otherwise, they
will go forward with this.

If you have any real life good or bad stories, I'd love to hear it.  Given
the NetApp arrays supposedly being very good NFS platforms, overall, is this
a recommended way to run PostgreSQL, or is it recommended to not run this
way.


Feel free to reply directly if you are not comfortable talking to this on
the list, but list replies would be preferred so others in my shoes can find
this information.

Thanks,

Chris
-- 
Come see how to SAVE money on fuel, decrease harmful emissions, and even
make MONEY. Visit http://colafuelguy.mybpi.com and join the revolution!

Re: [PERFORM] PG writes a lot to the disk

[Kris Jurka]

On Thu, 20 Mar 2008, Albe Laurenz wrote:


PostgreSQL doesn't write into the table files when it SELECTs data.



It could easily be hint bit updates that are set by selects getting 
written.


Kris Jurka


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

Re: [PERFORM] PostgreSQL NetApp and NFS

[Bruce Momjian]

Chris Hoover wrote:
> If you have any real life good or bad stories, I'd love to hear it.  Given
> the NetApp arrays supposedly being very good NFS platforms, overall, is this
> a recommended way to run PostgreSQL, or is it recommended to not run this
> way.

We do have an NFS section in our documentation at the bottom of this
page:

http://www.postgresql.org/docs/8.3/static/creating-cluster.html

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [PERFORM] PostgreSQL NetApp and NFS

[Woody Woodring]

 > I need to know if anyone out there is/has run their PostgreSQL on NetApp
arrays via NFS.  My particular situation is RH Linux 4 servers running
Postgresql 8.1.  I need  
> to provide our Operations manager with specific reasons why we should not
run PostgreSQL over NetApp NFS.  Otherwise, they will go forward with this.

 > If you have any real life good or bad stories, I'd love to hear it.
Given the NetApp arrays supposedly being very good NFS platforms, overall,
is this a recommended way  
> to run PostgreSQL, or is it recommended to not run this way. 
 
We have been running Postgres over NFS to a NetApp since 7.1 and we have
nothing but good things to say.  We have 75 databases in 3 clusters all
connected to one netapp.  We don't store a huge amount of data, currently
~43Gig, but it is constantly updated.  
 
We keep the pgsql/data directory on the netapp.  If one of our db servers
ever have a problem, we can just swap out the box, mount the drive and
restart postgres. 
 
We like our support we get from them, the only issue we ever have is having
a drives fail which they get replacements to us promptly.  Our NetApp has an
uptime currently over 2 years.
 
By the way, I though NetApp boxes came with an iSCSI license.  NetApp
downplayed the iSCSI with us because they said you cannot share drives
between servers, but for postgres you don't want that anyway.   It could
have also been that the NetApp is better tuned for NFS throughput and they
want to steer the user toward that.
 
If you want more specifics, feel free to ask. 
 
Woody
iGLASS Networks 
 

Re: [PERFORM] PostgreSQL NetApp and NFS

[Frits Hoogland]

My experience postgresql work good on NFS. Of course, use NFS over TCP, and
use noac if you want to protect your database even more (my experience is
NFS client caching doesn't lead to an irrecoverable database however)

I've encountered problems with RHEL4 as a database server and a client of a
Netapp filer, due to a bug in the (redhat nfs client)
Postgresql uses BSD read/write semantics. The BSD semantics mean an IO call
(either read or write) is atomic.
Linux uses system V read/write semantics. The system V semantics mean an IO
is NOT atomic and can be interrupted.
A read call got interrupted (due to the bug in the nfs client), which meant
the IO call kept waiting until infinity.
It even caused all other IO done against the inode to be waiting, leading to
a situation where the server needed a reboot to be able to function
propertly.

frits

On Thu, Mar 20, 2008 at 9:09 PM, Bruce Momjian <[EMAIL PROTECTED]> wrote:

> Chris Hoover wrote:
> > If you have any real life good or bad stories, I'd love to hear it.
>  Given
> > the NetApp arrays supposedly being very good NFS platforms, overall, is
> this
> > a recommended way to run PostgreSQL, or is it recommended to not run
> this
> > way.
>
> We do have an NFS section in our documentation at the bottom of this
> page:
>
>http://www.postgresql.org/docs/8.3/static/creating-cluster.html
>
> --
>  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
>  EnterpriseDB http://postgres.enterprisedb.com
>
>  + If your life is a hard drive, Christ can be your backup. +
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Re: [PERFORM] PostgreSQL NetApp and NFS

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Chris Hoover wrote:
>> If you have any real life good or bad stories, I'd love to hear it.  Given
>> the NetApp arrays supposedly being very good NFS platforms, overall, is this
>> a recommended way to run PostgreSQL, or is it recommended to not run this
>> way.

> We do have an NFS section in our documentation at the bottom of this
> page:
>   http://www.postgresql.org/docs/8.3/static/creating-cluster.html

Aside from what's said there, I'd note that it's a seriously bad idea
to use a "soft mount" or any arrangement wherein it's possible for
Postgres to be running while the NFS disk is not mounted.  Joe Conway
can still show you the scars from learning that lesson, I believe.
See the archives...

regards, tom lane

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

Re: [PERFORM] PostgreSQL NetApp and NFS

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Chris Hoover wrote:
> >> If you have any real life good or bad stories, I'd love to hear it.  Given
> >> the NetApp arrays supposedly being very good NFS platforms, overall, is 
> >> this
> >> a recommended way to run PostgreSQL, or is it recommended to not run this
> >> way.
> 
> > We do have an NFS section in our documentation at the bottom of this
> > page:
> > http://www.postgresql.org/docs/8.3/static/creating-cluster.html
> 
> Aside from what's said there, I'd note that it's a seriously bad idea
> to use a "soft mount" or any arrangement wherein it's possible for
> Postgres to be running while the NFS disk is not mounted.  Joe Conway
> can still show you the scars from learning that lesson, I believe.
> See the archives...

Do the docs need updating for this?

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [PERFORM] PostgreSQL NetApp and NFS

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Aside from what's said there, I'd note that it's a seriously bad idea
>> to use a "soft mount" or any arrangement wherein it's possible for
>> Postgres to be running while the NFS disk is not mounted.

> Do the docs need updating for this?

Wouldn't be a bad idea to mention it, if we're going to have a section
pointing out NFS risks.

regards, tom lane

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