Bug 1347281 – [scale] Remove foreign keys from history database for better etl performance on scale

Bugzilla will be upgraded to version 5.0. The upgrade date is tentatively scheduled for 2 December 2018, pending final testing and feedback.

Bug 1347281 - [scale] Remove foreign keys from history database for better etl performance on scale

Summary:	[scale] Remove foreign keys from history database for better etl performance ...

Status:	CLOSED CURRENTRELEASE

Aliases:	None

Product:	ovirt-engine-dwh
Classification:	oVirt
Component:	Database (Show other bugs)
Sub Component:
Version:	4.0.0
Hardware:	Unspecified Unspecified

Priority	unspecified Severity high (vote)
Target Milestone:	ovirt-4.0.1
Target Release:	4.0.1
Assigned To:	Shirly Radco
QA Contact:	mlehrer
Docs Contact:

URL:
Whiteboard:
Keywords:	ZStream

Depends On:
Blocks:	1353189
	Show dependency tree / graph

Reported:	2016-06-16 08:41 EDT by Shirly Radco
Modified:	2016-08-22 23:12 EDT (History)
CC List:	7 users (show)

See Also:
Fixed In Version:
Doc Type:	Enhancement
Doc Text:	Removed foreign keys from the history database to provide better sampling scale performance.
Story Points:	---
Clone Of:
Environment:
Last Closed:	2016-08-12 10:11:46 EDT
Type:	Bug
Regression:	---
Mount Type:	---
Documentation:	---
CRM:
Verified Versions:
Category:	---
oVirt Team:	Metrics
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---

Flags:	rule-engine: ovirt‑4.0.z+ ylavi: planning_ack+ sradco: devel_ack+ pstehlik: testing_ack+

Attachments	(Terms of Use)
sample query duration values (10.89 KB, text/plain) 2016-08-09 04:29 EDT, mlehrer	no flags	Details
Add an attachment (proposed patch, testcase, etc.)

External Trackers
Tracker	ID	Priority	Status	Summary	Last Updated
oVirt gerrit	59453	master	MERGED	history: dropped all history database foreign keys	2016-06-21 08:19 EDT

Groups: None (edit)

Description Shirly Radco 2016-06-16 08:41:01 EDT

Description of problem:
In scale environments the sampling process took around 25s.
We want to lower it below 15s in order to run the sampling every 15s.

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

How reproducible:


Steps to Reproduce:
1.Install engine +dwh and set up a scale environment
2.Run dwh in debug mode and with sampling intercal set to 15.
3.Check the sampling time in the dwh log file.

Actual results:
The sampling takes around 25 seconds 

Expected results:
Should be under15s.

Additional info:

Comment 1 mlehrer 2016-08-09 04:29 EDT

Created attachment 1189126 [details]
sample query duration values

Comment 2 mlehrer 2016-08-09 04:30:56 EDT

Tested on 4.0.2-1
DWH (App & DB) on same tier as Engine App

Dataset:
hosts: 541
  vms: 6322

Standard disk used for Database.


Without postgres tuning sample query finishes in 30-39s.
With tuned postgres sample query reduced to 5-6s.
Some sample query degradation occurs during delete jobs but only by a few additional seconds still remaining under 15s for most queries.

While we tune a few settings, in order to see sample query return under 15s please set:
checkpoint_segments = 128  
checkpoint_completion_target = 0.9

Further information available: https://mojo.redhat.com/docs/DOC-1089988
Last Sample values parsed, and attached.

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