Bug 523389 - Upgrade of rhnPackageChangelog table consumes lot of UNDO_TBS and TEMP_TBS
Summary: Upgrade of rhnPackageChangelog table consumes lot of UNDO_TBS and TEMP_TBS
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Upgrades
Version: 530
Hardware: All
OS: Linux
high
medium
Target Milestone: ---
Assignee: Milan Zázrivec
QA Contact: Brandon Perkins
URL:
Whiteboard:
Depends On:
Blocks: sat531-blockers 523386
TreeView+ depends on / blocked
 
Reported: 2009-09-15 10:11 UTC by Miroslav Suchý
Modified: 2018-10-20 04:18 UTC (History)
6 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2009-10-06 14:36:13 UTC
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Red Hat Product Errata RHBA-2009:1479 0 normal SHIPPED_LIVE Red Hat Network Satellite bug fix update 2009-10-06 14:35:58 UTC

Description Miroslav Suchý 2009-09-15 10:11:29 UTC
Description of problem:
We got a lot of reports that during upgrade of sat520 to sat530, spacewalk-schema-upgrad consume a lot of UNDO_TBS and TEMP_TBS.

After investigation it seem that the problem is in upgrade of table rhnPackageChangelog. Problems are two:
/etc/sysconfig/rhn/schema-upgrade/satellite-5.1-spacewalk-0.2/055-2-rhnPackageChangeLog-id-data.sql
This query do one big update (usually in table is 5M records) and it will consume a lot of UNDO_TBS.

/etc/sysconfig/rhn/schema-upgrade/satellite-5.1-spacewalk-0.2/055-3-rhnPackageChangeLog-id-not-null.sql
This query will create index in big table, which will require a lot of TEMP_TBS.

Version-Release number of selected component (if applicable):
rpm -q spacewalk-schema
spacewalk-schema-0.5.20-23.el5sat

How reproducible:
during upgrade

Steps to Reproduce:
1. upgrade from sat520 to sat530
2. watch UNDO_TBS and TEMP_TBS grow
 
Actual results:
UNDO_TBS and TEMP_TBS are consumed (nearly one giga)

Expected results:
not much UNDO_TBS and TEMP_TBS will be consumed

Comment 1 Milan Zázrivec 2009-09-17 15:43:13 UTC
Schema fix, spacewalk.git master: 9c5eb487dfe81a8900be7eda42f82dc8bf82ef09

Comment 2 Milan Zázrivec 2009-09-17 15:58:43 UTC
Support for schema upgrade overrides, spacewalk.git master:
1a33fe5ffc6d7fbc69c03dcb0497bb664bac88ca

Comment 3 Michael Mráka 2009-09-21 12:34:32 UTC
TEMP usage fixed in spacewalk master:

commit 88cec86f324c49dc9d580429d3fb3498b65f6c5d
    523389 - decrease TEMP usage

Comment 4 Milan Zázrivec 2009-09-22 12:19:11 UTC
Additional fix in spacewalk.git master: d6c844b22916e09f5ed07ffe8f3a05f0888c7a1a

Comment 5 Milan Zázrivec 2009-09-22 12:35:04 UTC
satellite.git, SATELLITE-5.3:
213f2294728ced55037080e3b600422b4850e764
9a4d2e8ab423e78cbf9db345cb545610f7afa5a1

Comment 8 Milan Zázrivec 2009-09-24 14:26:53 UTC
Testing with very large rhnPackageChangelog table revealed the fix
contained in rhn-upgrade-5.3.0.23-1 was able to solve problem
with growing UNDO_TBS, but did not solve the problem with growing
TEMP_TBS.

What I'll have to do is to create some estimates for required size
of TEMP_TBS (depending on the size of rhnPackageChangelog table).

For most cases, there won't be any need to extend standard size
of TEMP_TBS (i.e. if your rhnPackageChangelog has < 10 mil. rows,
the schema upgrade should fit into standard 250 MB TEMP_TBS).

Comment 9 Milan Zázrivec 2009-09-24 18:50:55 UTC
rhn-upgrade now contains script named satellite-temp_tbs-estimate.sql
which, based on sizes of rhnPackageChangelog & time_series tables, will try
to compute an estimate for TEMP_TBS consumption.

Comment 10 Milan Zázrivec 2009-09-25 07:41:19 UTC
rhn-upgrade-5.3.0.24-1.el4sat & rhn-upgrade-5.3.0.23-1.el5sat

Comment 11 Jan Hutař 2009-09-30 05:44:43 UTC
Estimates with rhn-upgrade-5.3.0.24-1.el5sat seems to be OK: see bug 509112 comment #12.

Comment 12 Petr Sklenar 2009-10-02 07:21:58 UTC
Estimates with rhn-upgrade-5.3.0.24-1 seems to be OK for my satellite upgrade too: bug 509112#c13

Comment 15 errata-xmlrpc 2009-10-06 14:36:13 UTC
An advisory has been issued which should help the problem
described in this bug report. This report is therefore being
closed with a resolution of ERRATA. For more information
on therefore solution and/or where to find the updated files,
please follow the link below. You may reopen this bug report
if the solution does not work for you.

http://rhn.redhat.com/errata/RHBA-2009-1479.html


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