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 по дате отправления: