Manual Cleanup for Reporting DB

Follow

In the past we've experienced some unusual behaviour from "eureport-delete-data" command. Even after deleting all the report using end date till latest like bellow, 

# eureport-delete-data -e 2014-12-31
Deleted 0 reporting data entries

if we checked the DB for its size, it seems the DB is not cleaned properly. 

# /usr/bin/psql -h /var/lib/eucalyptus/db/data -p 8777 -U root -c "SELECT pg_database.datname,pg_database_size(pg_database.datname),pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size DESC;" -d database_events 

 

datname | pg_database_size | pg_size_pretty
---------------------------+------------------+----------------
eucalyptus_reporting | 37663671096 | 35 GB
eucalyptus_cloudwatch | 2646016824 | 2523 MB
eucalyptus_walrus | 64709432 | 62 MB
eucalyptus_osg | 50275128 | 48 MB
eucalyptus_cloud | 23561016 | 22 MB
eucalyptus_storage | 15819576 | 15 MB
eucalyptus_auth | 9823032 | 9593 kB
eucalyptus_autoscaling | 8004408 | 7817 kB
eucalyptus_config | 7119672 | 6953 kB
eucalyptus_loadbalancing | 7045944 | 6881 kB
eucalyptus_imaging | 6701880 | 6545 kB
eucalyptus_dns | 6636344 | 6481 kB
eucalyptus_cloudformation | 6439736 | 6289 kB
eucalyptus_general | 6382392 | 6233 kB
eucalyptus_records | 6382392 | 6233 kB
eucalyptus_faults | 6349624 | 6201 kB
database_events | 6333240 | 6185 kB
postgres | 6308664 | 6161 kB
template1 | 6308664 | 6161 kB
template0 | 6201860 | 6057 kB

 

Here we see that, even after deleting reporting data for latest DB shows the size of the eucalyptus_reporting DB is 35GB.

So, We did the manual cleanup for the DB as follows step by step,

  1. Accessing the eucalyptus_reporting DB `#psql -h /var/lib/eucalyptus/db/data -p 8777  eucalyptus_reporting`
  2. Describing DB tables details by `#\d+`
  3. Vacuum the DB `# VACUUM FULL VERBOSE;`
  4. check the Description of the table size for the DB by `#\d+`
  5. quit from DB by `#\q`

That should resolve the issue.

NOTE: if VACUUM failed, try the below SQL:

# psql -p 8777 -h /var/lib/eucalyptus/db/data/ eucalyptus_reporting -c "TRUNCATE reporting_elastic_ip_attach_events, reporting_elastic_ip_create_events, reporting_elastic_ip_delete_events, reporting_elastic_ip_detach_events, reporting_instance_create_events, reporting_instance_usage_events, reporting_s3_object_create_events, reporting_s3_object_delete_events, reporting_volume_attach_events, reporting_volume_create_events, reporting_volume_delete_events, reporting_volume_detach_events, reporting_volume_snapshot_create_events, reporting_volume_snapshot_delete_events"

 

 

Have more questions? Submit a request

Comments

  • Avatar
    John Gallucci

    These instructions appear to apply only to pre 4.0 installations. Currently the database is combined into a single "eucalyptus_shared" database. To see the size of the current tables run the follwoing:
    {noformat}
    SELECT
    relname as "Table",
    pg_size_pretty(pg_total_relation_size(relid)) As "Size",
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
    FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
    {noformat}

Powered by Zendesk