Clearing rows periodically

От: Martin Foster
Тема: Clearing rows periodically
Дата: ,
Msg-id: 3F1799D0.1010009@ethereal-realms.org
(см: обсуждение, исходный текст)
Ответ на: Re: Sanity check requested  ("Shridhar Daithankar")
Ответы: Re: Clearing rows periodically  (Paul Thomas)
Re: Clearing rows periodically  (Andrew Sullivan)
Re: Clearing rows periodically  (Tom Lane)
Список: pgsql-performance

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

Sanity check requested  ("Nick Fankhauser", )
 Re: Sanity check requested  ("Shridhar Daithankar", )
  Re: Sanity check requested  ("Nick Fankhauser", )
   Re: Sanity check requested  (Josh Berkus, )
    Re: Sanity check requested  ("Nick Fankhauser", )
     Re: Sanity check requested  (Joe Conway, )
   Re: Sanity check requested  ("Shridhar Daithankar", )
    Clearing rows periodically  (Martin Foster, )
     Re: Clearing rows periodically  (Paul Thomas, )
     Re: Clearing rows periodically  (Andrew Sullivan, )
     Re: Clearing rows periodically  (Tom Lane, )
    Re: Sanity check requested  (Ang Chin Han, )
     File systems (RE: Sanity check requested)  ("Nick Fankhauser", )
      Re: File systems (RE: Sanity check requested)  (Tom Lane, )
     Re: Sanity check requested  ("scott.marlowe", )
   Re: Sanity check requested  (Vincent van Leeuwen, )
    Re: Sanity check requested  ("Nick Fankhauser", )
     Re: Sanity check requested  (Josh Berkus, )
     Re: Sanity check requested  (Rod Taylor, )
 Re: Sanity check requested  ("Oliver Scheit", )
  Re: Sanity check requested  (Vincent van Leeuwen, )
 Re: Sanity check requested  ("Oliver Scheit", )

I have two tables in the database which are used almost every time
someone makes use of the webpages themselves.   The first, is some sort
of database side parameter list which stores parameters from session to
session.  While the other, is a table that handles the posting activity
of all the rooms and chatters.

The first is required in all authentication with the system and when
entries are missing you are challenged by the system to prove your
identity.   This table is based on a randomized order, as in the unique
number changes pseudo randomly and this table sees a reduction in
entries every hour on the hour as to keep it's information fresh and
manageable.

The other table follows a sequential order and carries more columns of
information.   However, this table clears it's entry nightly and with
current settings will delete roughly a days traffic sitting at 50K rows
of information.

The difference is as follows:   Without making the use of vacuum every
hour the parameter table performs very well, showing no loss in service
or degradation.    Since people authenticate more then post, it is safe
to assume that it removes more rows daily then the posting table.

The posting table often drags the system down in performance when a day
has been skipped, which includes the use of VACUUM ANALYZE EXPLAIN.
This seems to be an indication that the process of a daily delete is
actually a very wise step to take, even if the information itself is not
needed for very long.

A VACUUM FULL will correct the issue, but put the site out of commission
for roughly 20 minutes as the drive crunches the information.

My question is, should the purging of rows be done more often then once
a day for both tables.   Is this why performance seems to take a hit
specifically?  As there were too many rows purged for vacuum to
accurately keep track of?

    Martin Foster
    Creator/Designer Ethereal Realms
    




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

От: Rajesh Kumar Mallah
Дата:
Сообщение: Yet another slow join query..
От: Tom Lane
Дата:
Сообщение: Re: index / sequential scan problem