Thread: Tuning

Tuning

From
Carol Walter
Date:
I have some questions about tuning.  The PostgreSQL documentation
says that you don't need to worry about index maintenance and tuning
with PostgreSQL.  I'm used to systems that work a lot better if they
are periodically re-orged or re-indexed.  Is it true that one need
not be concerned with this?  I'm certain that the databases must
require some human intervention.  What kind of tuning or other
intervention are you doing.  What kind of tools are available and are
being used.

Carol Walter

Re: Tuning

From
"Scott Marlowe"
Date:
On Mon, Apr 7, 2008 at 9:33 AM, Carol Walter <walterc@indiana.edu> wrote:
> I have some questions about tuning.  The PostgreSQL documentation says that
> you don't need to worry about index maintenance and tuning with PostgreSQL.
> I'm used to systems that work a lot better if they are periodically re-orged
> or re-indexed.  Is it true that one need not be concerned with this?  I'm
> certain that the databases must require some human intervention.  What kind
> of tuning or other intervention are you doing.  What kind of tools are
> available and are being used.

Not sure where in the docs it exactly says to never worry about your indexes.

While most access patterns can indeed be handled fine by autovacuum,
you can use the reindex command in pgsql to optimize your indexes if
they're chock full of empty space that can't be reclaimed by vacuum.

You can monitor things like index and table bloat with the pg_stat_*
series of views.

Re: Tuning

From
Chris Browne
Date:
walterc@indiana.edu (Carol Walter) writes:
> I have some questions about tuning.  The PostgreSQL documentation
> says that you don't need to worry about index maintenance and tuning
> with PostgreSQL.  I'm used to systems that work a lot better if they
> are periodically re-orged or re-indexed.  Is it true that one need
> not be concerned with this?  I'm certain that the databases must
> require some human intervention.  What kind of tuning or other
> intervention are you doing.  What kind of tools are available and are
> being used.

Well, there is one side to things where yes, indeed, "maintenance is
quite necessary," and that being in the area of "vacuuming."

<http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html>

The need to 'reindex' or 'reorg' tables is not non-existent, however
it is needed *way* less frequently than was the case in much older
versions of PostgreSQL.  e.g. - with v7.2, there were patterns of
updates that would leave portions of indexes not usable, but the issue
was rectified in ~7.4, and people have not been observing problems
relating to this former scenario.

Back when we had systems on v7.2, we had to shut down every few months
and reindex some tables in order to keep performance OK.  That's no
longer the case with systems running on v8.1, and as we bring 8.3 into
production, I expect even less need for manual interventions.

If you are running VACUUM and ANALYZE often enough, and autovacuum
pretty much does so, now, then there shouldn't be much need to do
"re-orging" of the system.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/nonrdbms.html
In the name of the Lord-High mutant, we sacrifice this suburban girl
-- `Future Schlock'

Re: Tuning

From
Andrew Sullivan
Date:
On Mon, Apr 07, 2008 at 12:45:38PM -0400, Chris Browne wrote:
> versions of PostgreSQL.  e.g. - with v7.2, there were patterns of
> updates that would leave portions of indexes not usable, but the issue
> was rectified in ~7.4, and people have not been observing problems
> relating to this former scenario.

There remain use patterns that will leave the indexes in pretty bad shape.
This is an inherent limiation with btrees, though -- if you just unbalanced
the tree with a large number of deletes, there's nothing you can do except
REINDEX.

A


Re: Tuning

From
Michael Monnerie
Date:
On Montag, 7. April 2008 Scott Marlowe wrote:
> You can monitor things like index and table bloat with the pg_stat_*
> series of views.

Are there scripts to automate this look at stats?
Or could one draw graphs from that values, to visualize how well the db
is? E.g., if you could calculate a % value, you could make RRD stats to
see it's change over time. Is there any project on this?

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0676/846 914 666                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net                   Key-ID: 1C1209B4

Attachment

Re: Tuning

From
paul rivers
Date:
Michael Monnerie wrote:
> On Montag, 7. April 2008 Scott Marlowe wrote:
>
>> You can monitor things like index and table bloat with the pg_stat_*
>> series of views.
>>
>
> Are there scripts to automate this look at stats?
> Or could one draw graphs from that values, to visualize how well the db
> is? E.g., if you could calculate a % value, you could make RRD stats to
> see it's change over time. Is there any project on this?
>
> mfg zmi
>


I don't see a direct way to monitor bloat from pg_stat*.   If I'm wrong,
please set me straight.

For example, monitoring index bloat would involve deciding how many
pages an index would ideally consume, based on either sampling the table
yourself or raiding stats, and making assumptions.  Then you'd compare
to actual (or approximated actual) and decide if you were within
whatever threshold you think is OK.

Paul






Re: Tuning

From
"Jeff Frost"
Date:

> Are there scripts to automate this look at stats?
> Or could one draw graphs from that values, to visualize how well the db
> is? E.g., if you could calculate a % value, you could make RRD stats to
> see it's change over time. Is there any project on this?

I don't know about rrd graphing it, but there are some great nagios plugins for this at http://bucardo.org/nagios

You could have a look at the queries used in those and roll your own mrtg plugins.  If you do, please share.



Re: Tuning

From
Michael Monnerie
Date:
On Dienstag, 8. April 2008 paul rivers wrote:
> I don't see a direct way to monitor bloat from pg_stat*.   If I'm
> wrong, please set me straight.
>
> For example, monitoring index bloat would involve deciding how many
> pages an index would ideally consume, based on either sampling the
> table yourself or raiding stats, and making assumptions.  Then you'd
> compare to actual (or approximated actual) and decide if you were
> within whatever threshold you think is OK.

If there's no rule of thumb, how could you manually decide on actions,
other than by reading from a crystal ball?

Also, if there are no tools, almost no admin can/will do anything.
Except for the specialist having too much free time to dig into the db
just for fun and reading tons of pg_stat* tables and values *g*
As most won't have that funny time, tools which show possible problems
or performance losses would greatly help.

On Dienstag, 8. April 2008 Jeff Frost wrote:
> I don't know about rrd graphing it, but there are some great nagios
> plugins for this at http://bucardo.org/nagios
>
> You could have a look at the queries used in those and roll your own
> mrtg plugins.  If you do, please share.

Thank you. I'll have a look at that nagios plugins.

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0676/846 914 666                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net                   Key-ID: 1C1209B4

Attachment