Bug 970566 - [rhevm] - Postgresql - postgres base folders occupies large disk space (100% on large setup+reports)
[rhevm] - Postgresql - postgres base folders occupies large disk space (100% ...
Status: CLOSED NOTABUG
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: ovirt-engine (Show other bugs)
3.2.0
x86_64 Linux
unspecified Severity high
: ---
: ---
Assigned To: Yaniv Lavi (Dary)
infra
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-06-04 07:16 EDT by David Botzer
Modified: 2016-02-10 14:24 EST (History)
11 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2013-06-09 03:40:31 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: Infra
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
rhevm-db-logs (375.51 KB, application/x-gzip)
2013-06-04 07:16 EDT, David Botzer
no flags Details
pg-logs (22.75 KB, application/x-gzip)
2013-06-04 07:18 EDT, David Botzer
no flags Details
delete-date (73.46 KB, image/png)
2013-06-09 08:44 EDT, David Botzer
no flags Details

  None (edit)
Description David Botzer 2013-06-04 07:16:37 EDT
Created attachment 756718 [details]
rhevm-db-logs

Description of problem:
Postgresql - postgres base folders occupies large disk space (100% on large setup + reports and DWH)

Version-Release number of selected component (if applicable):
3.2/sf17.3

How reproducible:
Always

Steps to Reproduce:
1.install rhevm+dwh+reports
2.create vm without NIC nor Disk
3.create template
4.create several pools of 1000 vms
5.Leave setup for 24H
6.Delete all pools & VMs
7.via pgsql -Vacuum both DBs - engine & history
8.reboot Server/rhevm

Actual results:
- History DB gets up to 12GB.
- Postgres ../base/ folder takes allot of disk space and its data cannot be 
  deleted /var/lib/pgsql/data/base/* --> *5.2G*
-and several History Tables
 (vm_configuration,133379)
 (vm_disks_usage_hourly_history,835190)
 (vm_disks_usage_samples_history,25519585)
 (vm_hourly_history,835192)
 (vm_samples_history,25518585) 

Expected results:
- DBs size should be reduce after vacuum
- psql /base/ folders should be reduce or allow to be deleted to free up space

Additional info:
This also happens when I stop DWH

More stats on tables:
               relation                |  size
----------------------------------------+---------
 public.audit_log                       | 8896 kB
 public.vm_device                       | 4816 kB
 public.pk_vm_device                    | 2072 kB
 public.vm_static                       | 2032 kB
 public.vm_dynamic                      | 1736 kB
 public.idx_audit_log_vm_template_name  | 1192 kB
 public.idx_audit_correlation_id        | 1104 kB
 public.idx_audit_log_user_name         | 1104 kB
 public.vm_statistics                   | 1072 kB
 public.idx_audit_log_storage_pool_name | 1040 kB
(10 rows)

./Run_Hist_Tables_Size.sh
                     relation                     |  size
--------------------------------------------------+---------
 public.vm_samples_history                        | 2638 MB
 public.vm_disks_usage_samples_history            | 1935 MB
 public.vm_samples_history_vm_id_idx              | 1282 MB
 public.idx_disks_usage_vm_id_samples             | 1276 MB
 public.idx_vm_configuration_version_samples      | 961 MB
 public.idx_vm_history_datetime_samples           | 817 MB
 public.idx_disks_usage_history_datetime_samples  | 815 MB
 public.idx_vm_current_host_configuration_samples | 808 MB
 public.vm_samples_history_pkey                   | 694 MB
 public.vm_disks_usage_samples_history_pkey       | 682 MB
Comment 1 David Botzer 2013-06-04 07:18:09 EDT
Created attachment 756719 [details]
pg-logs
Comment 2 David Botzer 2013-06-06 00:15:48 EDT
In a setup with only rhevm this issue doesnt reproduce,
Hence requires rhevm+dwh+reports
Comment 3 Barak 2013-06-07 12:24:25 EDT
It is obvious that since you have a few thousands of VMs the historical information of these VMs will remain in the history DB.
The vms will be marked as deleted but still.

So what is the problem you see (dir size?), and what does it have to do with vacuum-ing the DB.
Comment 4 David Botzer 2013-06-09 02:57:25 EDT
1. after history data in samples tables is moved from samples to hours and to daily it should minimize the number of rows, hence the db/disk space

2. if not .1. than maybe need to run "vacuum" cleaner in order to clear/clean disk space, like I was suggested, also its good to explore such option to see if it helps in any way,

3. we need to think of a way to optimize the history db in such a way it will not grow in as so high potential as 0% disk space
especially if "vacuum" doesnt help.

Problems I see :
1. History DB gets up to 12GB.
2. Postgres ../base/ folder takes allot of disk space and its data cannot be 
   deleted, sice it could temper with the database
   /var/lib/pgsql/data/base/* --> *5.2G*

3. several History Tables grow largly in size

public.vm_samples_history                        | 2638 MB
 public.vm_disks_usage_samples_history            | 1935 MB
 public.vm_samples_history_vm_id_idx              | 1282 MB
 public.idx_disks_usage_vm_id_samples             | 1276 MB

 (vm_configuration,133379)
 (vm_disks_usage_hourly_history,835190)
 (vm_disks_usage_samples_history,25519585)
 (vm_hourly_history,835192)
 (vm_samples_history,25518585)
Comment 5 Yaniv Lavi (Dary) 2013-06-09 03:40:31 EDT
This is not a bug data warehouse requires a lot of disk space. It makes a lot of sense that disk space will grow quickly with so many VMs. Vacuum is a maintenance task that the DBA need to perform. In any rate, if the vacuum doesn't work, open a bug on postgres, not rhevm.




Yaniv
Comment 6 David Botzer 2013-06-09 04:04:27 EDT
Yaniv,
The db should get free since samples is converted into hours and daily
Espically after I deleted all pools & VMs

are the records in samples deleted after they "move" to hours & daily ?
vm_disks_usage_samples_history,24584608
Comment 7 Barak 2013-06-09 08:15:47 EDT
(In reply to David Botzer from comment #6)
> Yaniv,
> The db should get free since samples is converted into hours and daily
> Espically after I deleted all pools & VMs
> 
> are the records in samples deleted after they "move" to hours & daily ?
> vm_disks_usage_samples_history,24584608

You miss the point of DWH and reports,
It is called history DB to save the history, when you delete VMs from the engine, their mark will be left in the history DB for a very long time.
The raw samples tables are cleared after up to 48 hours (default & configurable).


In addition to the best of my knowledge vacuuming a postgres DB does not clear the diskspace. if such a utility is required it should be handled by postgres.
Comment 8 David Botzer 2013-06-09 08:44:18 EDT
"The raw samples tables are cleared after up to 48 hours (default & 
configurable)." - this is the main issue I am referring

This data is kept !!
look at my phsyical server (x.x.x.150)

The VMs were deleted on 3-4/6 -- see pic when-vm-were-deleted-more-than-24hy.png

View still has data (Samples)
2013-06-09
vm_disks_usage_samples_history,24584608
Comment 9 David Botzer 2013-06-09 08:44:51 EDT
Created attachment 758788 [details]
delete-date

Note You need to log in before you can comment on or make changes to this bug.