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:
This query do one big update (usually in table is 5M records) and it will consume a lot of UNDO_TBS.
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
Steps to Reproduce:
1. upgrade from sat520 to sat530
2. watch UNDO_TBS and TEMP_TBS grow
UNDO_TBS and TEMP_TBS are consumed (nearly one giga)
not much UNDO_TBS and TEMP_TBS will be consumed
Schema fix, spacewalk.git master: 9c5eb487dfe81a8900be7eda42f82dc8bf82ef09
Support for schema upgrade overrides, spacewalk.git master:
TEMP usage fixed in spacewalk master:
523389 - decrease TEMP usage
Additional fix in spacewalk.git master: d6c844b22916e09f5ed07ffe8f3a05f0888c7a1a
Testing with very large rhnPackageChangelog table revealed the fix
contained in rhn-upgrade-220.127.116.11-1 was able to solve problem
with growing UNDO_TBS, but did not solve the problem with growing
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).
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.
rhn-upgrade-18.104.22.168-1.el4sat & rhn-upgrade-22.214.171.124-1.el5sat
Estimates with rhn-upgrade-126.96.36.199-1.el5sat seems to be OK: see bug 509112 comment #12.
Estimates with rhn-upgrade-188.8.131.52-1 seems to be OK for my satellite upgrade too: bug 509112#c13
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.