Bug 563902

Summary:	012-2-rhnPackageFile-data.sql causes UNDO_TBS overflows for very large Satellite databases
Product:	Red Hat Satellite 5	Reporter:	Milan Zázrivec <mzazrivec>
Component:	Upgrades	Assignee:	Michael Mráka <mmraka>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Martin Minar <mminar>
Severity:	medium	Docs Contact:
Priority:	low
Version:	unspecified	CC:	cperry, gkhachik, jhutar, mkoci, mminar, mmraka
Target Milestone:	---
Target Release:	---
Hardware:	All
OS:	Linux
Whiteboard:	Fixed in the 5.4.0 Release - GA'd 2010-10-27
Fixed In Version:	oracle-server-scripts-10.2.0-32	Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2010-10-28 15:04:01 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	487678, 608757

Description Milan Zázrivec 2010-02-11 14:49:58 UTC

Description of problem:
This bug report is to track problems with schema upgrade scriptlet
012-2-rhnPackageFile-data.sql, introduced in Spacewalk 0.8.

It's aim is to update rhnPackageFile and rhnChecksum tables with 
correct sha256 checksums and checksum ids. For very large
customer databases (> 20GB DATA_TBS), this schema upgrade scriptlet
is very likely to run out of standard UNDO_TBS tablespace size (500 MB).

For very large rhnPackageFile tables (~ 50M rows), this update
needs further optimizations.

Version-Release number of selected component (if applicable):
spacewalk-schema-0.8.12-1

How reproducible:
Always

Steps to Reproduce:
1. Satellite 5.3.0 and earlier with very large rhnPackageFile table
(~ 50M rows)
2. Upgrade to newer Satellite version
  
Actual results:
You run out of standard - sized UNDO_TBS during schema upgrade.

Expected results:
You won't run out of standard - sized UNDO_TBS during schema upgrade.

Additional info:
At the time of writing this bug, I'm referring to non-existent, future
version of Satellite product.

Comment 2 Michael Mráka 2010-03-15 11:22:31 UTC

We made several attempts to optimize sql upgrade script for current minimal UNDO (500MB) and TEMP (250MB)  size unfortunately all of them took more then 24 hours to complete.
As this is an unacceptable long upgrade window for customers we agreed to change minimal sizes to 1GB UNDO and 1GB TEMP space.
After this modified upgrade script completed in about 4hrs (on ~ 50M row rhnPackageFile table).

Comment 3 Michael Mráka 2010-03-15 11:24:02 UTC

Spacewalk git:
commit 32f607647e4c0214233d6de15c05a5f5f41ec542
    563902 - tuned sql upgrade script
    we have to fit into 1GB TEMP and 1GB UNDO here
commit dbf50d25c3839a113ec9065254cc8c776e0d8bad
    563902 - let's make default TEMP and UNDO 1GB
    and save us from many troubles during upgrades

Comment 4 Michael Mráka 2010-03-15 12:35:04 UTC

Make sure upgrade instructions for satellite >5.3.X contain:

Prior running schema upgrade (spacewalk-schema-upgrade) extend both UNDO and TEMP_TBS tablespaces to at least 1GB.

Comment 8 Garik Khachikyan 2010-10-11 13:10:23 UTC

# VERIFIED

the instruction about extending UND_TBS and TEMP_TBS both to 1GB is written in both rhel4, rhel5 rhn-upgrade documents.

Comment 9 Clifford Perry 2010-10-28 15:04:01 UTC

The 5.4.0 RHN Satellite and RHN Proxy release has occurred. This issue has been resolved with this release. 


RHEA-2010:0801 - RHN Satellite Server 5.4.0 Upgrade
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10332

RHEA-2010:0803 - RHN Tools enhancement update
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10333

RHEA-2010:0802 - RHN Proxy Server 5.4.0 bug fix update
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10334

RHEA-2010:0800 - RHN Satellite Server 5.4.0
https://rhn.redhat.com/rhn/errata/details/Details.do?eid=10335

Docs are available:

http://docs.redhat.com/docs/en-US/Red_Hat_Network_Satellite/index.html 

Regards,
Clifford