VACUUM FULL not working with persistent connections in v7.2

Поиск
Список
Период
Сортировка
От Juan Jose Comellas
Тема VACUUM FULL not working with persistent connections in v7.2
Дата
Msg-id 200210181155.04795.juanjo@comellas.org
обсуждение исходный текст
Ответы Re: VACUUM FULL not working with persistent connections
Re: VACUUM FULL not working with persistent connections in
Список pgsql-general
I tried to switch a system that was using PostgreSQL 7.1.3 under Red Hat Linux
7.2 (PIII 800MHz, 768MB RAM) to PostgreSQL 7.2.3. The problem is that VACUUM
FULL ANALYZE in v7.2.3 is not working as VACUUM ANALYZE did in v7.1.3 (I
thought that they did the same thing).

Currently we have a table that has approximately 30 rows that are being
updated very frequently by a process that keeps several persistent
connections to the database. Each row is updated every 15 seconds (max.). The
description of this table is the following:

                  Table "merchant_client"
      Attribute      |           Type           | Modifier
---------------------+--------------------------+----------
 merchant_id         | integer                  | not null
 last_access_time    | timestamp with time zone |
 broker_ip_address   | character varying(15)    |
 version             | character varying(20)    | not null
 broker_port         | integer                  |
 merchant_ip_address | character varying(15)    |
 merchant_port       | integer                  |
 connection_type     | character varying(20)    | not null
 polling_frequency   | integer                  |
Index: merchant_client_pkey
Constraints: ("version" = '1.0'::"varchar")
             ((connection_type = 'Pull'::"varchar") OR (connection_type =
'Push'::"varchar"))

The problem is that Postgres seems to be making a logical delete for each row
that is updated (i.e. it seems to be doing an INSERT followed by a DELETE),
so a lot of cruft is created on the database. We need to avoid this because
there are other processes that are permanently querying this table (normally
needing a sequential scan over the table) and the performance of the queries
gradually decreases with each update. We run a VACUUM ANALYZE every 30
minutes on this table and with v7.1.3 the system performs acceptably. With
v7.2 (we tried v7.2.1, v7.2.2 and v7.2.3) if we run a VACUUM FULL ANALYZE on
the table almost none of the deleted rows are really vacuumed until the
clients disconnect. When using the VERBOSE modifier, I noticed that sometimes
some of  the rows were effectively removed, but these we normally very few
(less than 10%). We even tried a VACUUM FULL FREEZE ANALYZE without success.

Does anybody know if there is a way to have the v7.1.3 behaviour back? Is
there anything we can do to force the removal of deleted rows from a table in
v7.2?

Thanks.

--
Juan Jose Comellas
(juanjo@comellas.com.ar)



В списке pgsql-general по дате отправления:

Предыдущее
От: "doubleword"
Дата:
Сообщение: reply to the you for your database problem
Следующее
От: marco ghidinelli
Дата:
Сообщение: cannot add "not null" to an existing table