pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Mario Weilguni <mweilguni@sime.com>
To: Andreas Hartmann <andreas@apache.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Less rows -> better performance?
Date: Mon, 21 Jul 2008 17:29:14 +0200
Message-ID: <4884AB4A.2020709@sime.com> (raw)
In-Reply-To: <4884A7D3.7050501@apache.org>
References: <48846A02.7090702@apache.org>
	<48846DA3.2090308@archonet.com>
	<4884722A.1000405@apache.org>
	<1d4e0c10807210432v45c6272awb1ea13974747de54@mail.gmail.com>
	<4884A0F6.2000501@apache.org>
	<4884A4A7.7050503@sime.com>
	<4884A7D3.7050501@apache.org>

Andreas Hartmann schrieb:
> Mario Weilguni schrieb:
>> Andreas Hartmann schrieb:
>
> […]
>
>>> I just verified that the autovacuum property is enabled.
>
> […]
>
>> Did you have:
>> stats_start_collector = on
>> stats_block_level = on
>> stats_row_level = on
>>
>> Otherwise autovacuum won't run IMO.
>
> Thanks for the hint! The section looks like this:
>
> stats_start_collector = on
> #stats_command_string = off
> #stats_block_level = off
> stats_row_level = on
> #stats_reset_on_server_start = off
>
>
> I'll check the logs if the vacuum really runs - as soon as I find them :)
>
> -- Andreas
You might want to use these entries in your config:
redirect_stderr = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d

Fit those to your needs, then you will find log entries in $PGDATA/pg_log/

And BTW, I was wrong, you just need to have stats_row_level=On, 
stats_block_level doesn't matter. But in fact it's simple, if you don't 
have 24x7 requirements type VACUUM FULL ANALYZE; and check if your DB 
becomes smaller, I really doubt you can have that much indizes that 27MB 
dumps might use 2.3 GB on-disk.

You can check this too:
select relname, relpages, reltuples, relkind
  from pg_class
where relkind in ('r', 'i')
order by relpages desc limit 20;

Will give you the top-20 tables and their sizes, 1 page is typically 
8KB, so you can cross-check if relpages/reltuples is completly off, this 
is a good indicator for table/index bloat.

Regards,
Mario

view thread (12+ messages)  latest in thread

Message-ID: <4884AB4A.2020709@sime.com>
Permalink:  ../4884AB4A.2020709@sime.com/
Also on:    postgresql.org/message-id/4884AB4A.2020709@sime.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mweilguni@sime.com, andreas@apache.org
  Subject: Re: Less rows -> better performance?
  In-Reply-To: <4884AB4A.2020709@sime.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox