Re: How to fast the REINDEX

От: Kevin Grittner
Тема: Re: How to fast the REINDEX
Дата: ,
Msg-id: 4BB4653C02000025000302F6@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: How to fast the REINDEX  (raghavendra t)
Список: pgsql-performance

Скрыть дерево обсуждения

How to fast the REINDEX  (raghavendra t, )
 Re: How to fast the REINDEX  ("Kevin Grittner", )
  Re: How to fast the REINDEX  (raghavendra t, )
   Re: How to fast the REINDEX  ("Kevin Grittner", )
    Re: How to fast the REINDEX  (raghavendra t, )
     Re: How to fast the REINDEX  ("Kevin Grittner", )
      Re: How to fast the REINDEX  (raghavendra t, )
       Re: How to fast the REINDEX  ("Kevin Grittner", )
        Re: How to fast the REINDEX  (raghavendra t, )
         Re: How to fast the REINDEX  (Hannu Krosing, )
   Re: How to fast the REINDEX  (Jaime Casanova, )
    Re: How to fast the REINDEX  (Craig Ringer, )
     Re: How to fast the REINDEX  (Steve Clark, )
      Re: How to fast the REINDEX  ("Pierre C", )
       Re: How to fast the REINDEX  (raghavendra t, )
        Re: How to fast the REINDEX  (Brad Nicholson, )
        Re: How to fast the REINDEX  ("Kevin Grittner", )
        Re: How to fast the REINDEX  (Robert Haas, )
 Re: How to fast the REINDEX  (Greg Smith, )

raghavendra t <> wrote:

> System Config
> ---------------------
> CPU - Intel* Xenon* CPU
> CPU Speed - 3.16 GHz
> Server Model - Sun Fire X4150
> RAM-Size - 16GB

The disk system matters a lot, too.  How many drives do you have in
what RAID configuration(s)?

> My question is something like Steve's, why we should not do
> reindexing as our maintenance task.

If your VACUUM policy is good, the REINDEX should not be necessary.
A good VACUUM policy, in my experience, usually involves setting it
to VACUUM any table in which 20% or more of the rows have changed
(with autovacuum_vacuum_threshold set pretty low).  Cut those about
in half for the autovacuum ANALYZE trigger point.  You may need to
use cost limits to avoid a hit on the production workload when
autovacuum kicks in.  If you do need that, I've found a 10ms naptime
is adequate for us.  Then, try running VACUUM ANALYZE VERBOSE
*nightly* (again, with cost limits if needed to avoid impact on
other processes).  Capture the output, as it can be used to find
where you have bloat problems. Monitor the last few lines to make
sure your fsm (free space manager) settings are high enough -- it'll
tell you if they're not.

If you do this, you should be able to stop running REINDEX without
any performance hit.  There will be some dead space in the indexes,
but this will likely help with performance of UPDATE and INSERT, as
page splits will happen less frequently, and PostgreSQL won't have
to constantly be asking the OS for more disk space.

> I was doing reindex only to get a best fit and not fall short of
> 90% hole, bcoz my table has lot of updates and deletes. We also
> has the weekly maintance of VACUUM, but still reindex takes lot of
> time.

VACUUM won't help a lot with REINDEX time, since REINDEX has to read
the entire table once per index and build everything up from scratch
every time.  If you VACUUM often enough, it is kept in good shape as
you go.

> Present Paramters settings
> ----------------------------------------
> maintainence_work_mem - 1GB
> Checkpoint_segment and Wal_buffers are default values.

You will probably benefit from increasing those last two.  Is
everything else at the default?  There are a few others which almost
always need to be tuned to your run time environment.  The defaults
are designed to allow the server to start and run even on a very
small desktop machine, so that someone's first "test drive" isn't
marred by problems.  When you gear up for production use, you
normally need to tune it.

> Kevin, Pierre, Greg, Steve, Hannu, Jorge -----   Thank you for
> your wonderfull support and giving me the correct picture on
> REINDEX on this thread. I appoligies if i couldnt have shared the
> proper information in resolving my issue. Is the above information
> provided by me will help out in tuning better.

I'm starting to get a better picture of the environment.  I really
think that if you modify your VACUUM policy you can drop the REINDEX
and be in much better shape overall.  If you provide information on
your disk subsystem, show us what your postgresql.conf file looks
like (with all comments stripped out), and give us a general idea of
the workload, we might be able to suggest some tuning that will help
you overall.  And you might think about when and how to upgrade --
autovacuum is something which has been getting better with major
releases, and performance in general has been improving markedly.

I hope this helps.

-Kevin


В списке pgsql-performance по дате сообщения:

От: Scott Carey
Дата:
Сообщение: Re: Database size growing over time and leads to performance impact
От: Tom Lane
Дата:
Сообщение: Re: Database size growing over time and leads to performance impact