Note: This is a beta release of Red Hat Bugzilla 5.0. The data contained within is a snapshot of the live data so any changes you make will not be reflected in the production Bugzilla. Also email is disabled so feel free to test any aspect of the site that you want. File any problems you find or give feedback here.
Bug 1359918 - [scale] Add partitions to ovirt-engine-history database
Summary: [scale] Add partitions to ovirt-engine-history database
Keywords:
Status: CLOSED WONTFIX
Alias: None
Product: ovirt-engine-dwh
Classification: oVirt
Component: Database
Version: 4.0.0
Hardware: Unspecified
OS: Unspecified
unspecified
high vote
Target Milestone: ---
: ---
Assignee: Shirly Radco
QA Contact: Lukas Svaty
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2016-07-25 19:06 UTC by mlehrer
Modified: 2018-09-05 13:02 UTC (History)
6 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed: 2018-09-05 13:02:50 UTC
oVirt Team: Metrics
ykaul: ovirt-future?
rule-engine: planning_ack?
rule-engine: devel_ack?
rule-engine: testing_ack?


Attachments (Terms of Use)

Description mlehrer 2016-07-25 19:06:35 UTC
Description of problem:
This BZ is a placeholder for the ongoing issue of DWH Database continuing to grow on disk.  This occurs because postgres, correctly, retains multiple versions of database tuples.   When a value is deleted a tuple is marked as such, and the row can be marked for re-use (this is the process of vacuuming or if done automatically autovacuuming).

Rows which are vacuumed, marked for reuse, are not reclaimed and returned back to physical disk space of the OS.  

In DWH the rate of table growth is caused by the rate of sampling.  Tables which store these samples continue to add rows to the bottom of the sample tables.

DWH Delete process marks the rows which are no longer needed via the dwh delete process.

The risk is that without using vacuum full, cluster,or pg_repack or some other method to rewrite tables compactly, the size of the database will continue to grow on disk as vacuum and autovacuum are not suitable.


Vacuum Full was investigated but this option will force DWH to be stopped as Vacuum Full waits to be granted an exclusive lock, while DWH waits on Vacuum Full in order to continue sampling [1]

Currently other options are being investigated by development which will eliminate the need for long running deletes or vacuum full dependency.  Until then daily vacuum full execution is likely necessary to reclaim disk space and keep Vacuum full process to a minimum run time.

[1] Full details: https://mojo.redhat.com/docs/DOC-1088191


Version-Release number of selected component (if applicable):


How reproducible:


Steps to Reproduce:
1.
2.
3.

Actual results:

DWH growth will occur based on number of assets in data set, and interval sample rate.

Expected results:

After 48 hours database size should reach a steady state or have only minimal growth.


Additional info:

Comment 1 Shirly Radco 2016-07-25 19:44:09 UTC
Please add details on the scale of the environment this behavior appeared on.
Did you see this happening on smaller scale?

Comment 2 mlehrer 2016-07-25 20:57:46 UTC
(In reply to Shirly Radco from comment #1)
> Please add details on the scale of the environment this behavior appeared on.

I will add this as a private comment.

> Did you see this happening on smaller scale?

Smaller data set wasn't measured because the scale of the data set and sampling interval map to the amount of rows inserted which maps to physical space needed; so no I didn't check a smaller enviroment, but i would assume that smaller amounts of rows added would lead to growth but at a smaller rate than a larger scale.  If you are curious if aggressive auto-vacuuming alone is enough on a smaller scale; I would still say no; a full vacuum is necessary to reclaim disk space.

Comment 3 mlehrer 2016-07-25 21:01:32 UTC
(In reply to Shirly Radco from comment #1)
> Please add details on the scale of the environment this behavior appeared on.

DataSet
vms_disks	 6000
vms	         3000
hosts	         300
vms_interfaces   6000
hosts_interfaces 3000

> Did you see this happening on smaller scale?

Comment 4 Shirly Radco 2016-07-26 07:25:11 UTC
Did you try tuning the auto vacuum ?
https://www.postgresql.org/docs/9.1/static/runtime-config-autovacuum.html

autovacuum_vacuum_cost_delay (integer) - default is  20 milliseconds.
autovacuum_vacuum_cost_limit (integer) - default is  only 200 rows altogether in all tables. which is clearly not enough.
autovacuum_max_workers - default is three.
autovacuum_naptime -  default is one minute (1min).

Example: http://dba.stackexchange.com/questions/21068/aggressive-autovacuum-on-postgresql

Tuning this may resolve our vacuuming issue.

Comment 5 Yaniv Kaul 2016-07-27 20:05:13 UTC
Shirly - any intentions of doing anything about it for 4.0.2? I don't think so, and I don't think it's a blocker either. Please defer unless you have a safe solution to this.

Comment 6 Shirly Radco 2016-07-28 05:00:47 UTC
I move this to 4.1 since this is only on large scale environment and there is a workaround of changing the sampling back to 60 seconds in the conf file.

Comment 7 Gil Klein 2016-07-28 06:42:46 UTC
Not fixing this for 4.0 and deciding on keeping a lower interval than we had in 3.6, will most probably bring us back to hit issues like BZ #1328709 (much faster this time, due to the new interval).

I suggest we do one of the following:
1. Find a way to fix it in 4.0 
2. Reduce the interval back to 60 sec, so at least we reduce the risk to the level we had in 3.6

Comment 8 Shirly Radco 2016-07-28 07:18:23 UTC
(In reply to Gil Klein from comment #7)
> Not fixing this for 4.0 and deciding on keeping a lower interval than we had
> in 3.6, will most probably bring us back to hit issues like BZ #1328709
> (much faster this time, due to the new interval).
> 
> I suggest we do one of the following:
> 1. Find a way to fix it in 4.0 
> 2. Reduce the interval back to 60 sec, so at least we reduce the risk to the
> level we had in 3.6

This issue can be resolved by updating the vacuum options for our large scale users. The defaults are definitely not enough.
 
In a large scale like the one we tested, or the bug you mentioned, a db maintenance is a must, even with the 60 seconds interval.

We will provide a best practice on our recommendations.
Also, There is an option to add a conf file to change it back to 60 seconds.

Comment 17 Roy Golan 2018-09-05 06:15:43 UTC
Partitioning makes sense when the tables are very large, not necessarily when we have a lot of junk. The observation is accurate though, the rate of record removal is high and therefor tons of garbage is piling up, in the sampling tables.

For example in on production system that I track I see a relatively stable, high-rate of dead tuples for the sampling tables. I sample it daily and I see around 1.5 milion dead records. I must say that the disk of that db is not the strongest, so perhaps a stronger setup would be quicker to cleanup.

Mordechai do you have a report of one of your running setups to track dead rows over time? 

Shirly have you seen bugs open on too much diskspace taken by the db, or other bugs related to vacuuming?

Comment 21 Shirly Radco 2018-09-05 13:02:50 UTC
Closing this for now. Will reopen if needed.


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