Friday, January 23, 2015

Postgres: vacuum specific tables

VACUUM FULL can be intense running on production environment. However, majority of the problem I have heard of are database keeps growing. So, I would one address the space issue without jeopardizing the production environment too much ? Int his case, I know there are a few times that constantly get flush and fill. I know that the following 2 tables always problematic even in Oracle or SQL Server.

VCDB-# \d vpx_event; \d vpx_event_arg
                     Table "vpx.vpx_event"
        Column        |            Type             | Modifiers
----------------------+-----------------------------+-----------
 event_id             | integer                     | not null
 chain_id             | integer                     |
 event_type           | character varying(255)      |
 extended_class       | numeric(1,0)                |
 create_time          | timestamp without time zone |
 username             | character varying(255)      |
 category             | character varying(255)      |
 vm_id                | integer                     |
 vm_name              | character varying(255)      |
 host_id              | integer                     |
 host_name            | character varying(255)      |
 computeresource_id   | integer                     |
 computeresource_type | integer                     |
 computeresource_name | character varying(255)      |
 datacenter_id        | integer                     |
 datacenter_name      | character varying(255)      |
 datastore_id         | integer                     |
 datastore_name       | character varying(255)      |
 network_id           | integer                     |
 network_name         | character varying(255)      |
 network_type         | character(1)                |
 dvs_id               | integer                     |
 dvs_name             | character varying(255)      |
 storagepod_id        | integer                     |
 storagepod_name      | character varying(255)      |
 change_tag_id        | integer                     |
Indexes:
    "pk_vpx_event" PRIMARY KEY, btree (event_id)
    "vpx_event_f1" btree (computeresource_type)
    "vpxi_change_tag" btree (change_tag_id)
    "vpxi_event_category" btree (category)
    "vpxi_event_chain_id" btree (chain_id)
    "vpxi_event_computeresource_id" btree (computeresource_id)
    "vpxi_event_create_time" btree (create_time)
    "vpxi_event_datacenter_id" btree (datacenter_id)
    "vpxi_event_datastore_id" btree (datastore_id)
    "vpxi_event_dvs_id" btree (dvs_id)
    "vpxi_event_event_type" btree (event_type)
    "vpxi_event_extended_class" btree (extended_class)
    "vpxi_event_host_id" btree (host_id)
    "vpxi_event_network_id" btree (network_id)
    "vpxi_event_username" btree (username)
    "vpxi_event_vm_id" btree (vm_id)
Foreign-key constraints:
    "fk_vpx_change_tag" FOREIGN KEY (change_tag_id) REFERENCES vpx_change_tag(change_tag_id) ON DELETE SET NULL
    "fk_vpx_event_ref_computeres" FOREIGN KEY (computeresource_type) REFERENCES vpx_object_type(id)
Referenced by:
    TABLE "vpx_event_arg" CONSTRAINT "fk_vpx_event_arg_ref_event" FOREIGN KEY (event_id) REFERENCES vpx_event(event_id) ON DELETE CASCADE
    TABLE "vpx_entity_last_event" CONSTRAINT "fk_vpx_last_event_event" FOREIGN KEY (last_event_id) REFERENCES vpx_event(event_id) ON DELETE CASC
ADE


               Table "vpx.vpx_event_arg"
       Column       |          Type          | Modifiers
--------------------+------------------------+-----------
 event_id           | integer                | not null
 arg_id             | integer                | not null
 arg_type           | character varying(255) |
 arg_data           | text                   |
 obj_type           | integer                |
 obj_name           | character varying(255) |
 vm_id              | integer                |
 host_id            | integer                |
 computeresource_id | integer                |
 datastore_id       | integer                |
 network_id         | integer                |
 network_type       | character(1)           |
 datacenter_id      | integer                |
 resourcepool_id    | integer                |
 dvs_id             | integer                |
 folder_id          | integer                |
 alarm_id           | integer                |
 scheduledtask_id   | integer                |
Indexes:
    "pk_vpx_event_arg" PRIMARY KEY, btree (event_id, arg_id)
    "vpx_event_arg_f1" btree (obj_type)
    "vpx_event_arg_f2" btree (alarm_id)
    "vpx_event_arg_f3" btree (scheduledtask_id)
    "vpxi_event_arg_event_id" btree (event_id)
Foreign-key constraints:
    "fk_vpx_event_arg_ref_entity" FOREIGN KEY (obj_type) REFERENCES vpx_object_type(id)
    "fk_vpx_event_arg_ref_event" FOREIGN KEY (event_id) REFERENCES vpx_event(event_id) ON DELETE CASCADE


VCDB=# vacuum FULL ANALYZE VERBOSE vpx_event;
INFO:  vacuuming "vpx.vpx_event"
INFO:  "vpx_event": found 0 removable, 284990 nonremovable row versions in 6003 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.21s/0.70u sec elapsed 1.83 sec.
INFO:  analyzing "vpx.vpx_event"
INFO:  "vpx_event": scanned 6003 of 6003 pages, containing 284990 live rows and 0 dead rows; 30000 rows in sample, 284990 estimated total rows
VACUUM
VCDB=# vacuum FULL ANALYZE VERBOSE vpx_event_arg;
INFO:  vacuuming "vpx.vpx_event_arg"
INFO:  "vpx_event_arg": found 0 removable, 1289100 nonremovable row versions in 18360 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.44s/2.14u sec elapsed 5.92 sec.
INFO:  analyzing "vpx.vpx_event_arg"
INFO:  "vpx_event_arg": scanned 18360 of 18360 pages, containing 1289100 live rows and 0 dead rows; 30000 rows in sample, 1289100 estimated total rows
VACUUM
VCDB=#

By vacuum-ing only 2 of these tables, I would have to touch every single tables in the database.

No comments:

Post a Comment