Bug 509112 - Creating time_series_probe_id_idx index causes running out of TEMP_TBS for very large time_series tables
Creating time_series_probe_id_idx index causes running out of TEMP_TBS for ve...
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Upgrades (Show other bugs)
All Linux
high Severity high
: ---
: ---
Assigned To: Milan Zazrivec
Jeff Browning
Depends On:
Blocks: sat531-blockers 523386
  Show dependency treegraph
Reported: 2009-07-01 08:44 EDT by Milan Zazrivec
Modified: 2010-01-29 22:43 EST (History)
6 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2009-10-06 10:36:10 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

  None (edit)
Description Milan Zazrivec 2009-07-01 08:44:29 EDT
Description of problem:
Solution for performance problems described in bug #494064 was creating
a functional index (TIME_SERIES_PROBE_ID_IDX) on top of TIME_SERIES
table. When creating this index during schema upgrade on a table that
has several millions of rows (according to bug #494064, such customers
exist already), schema upgrade fails with ORA-01652: Unable to extend
temp segment ... -- in other words, when sorting this index in TEMP_TBS,
we will run out of its default size (embedded database).

There are several solutions to this problem that I see:

1) Document one additional step in upgrade preparation steps telling
the customer to extend the TEMP_TBS tablespace if needed (this would
depend on the number of rows in time_series table and we're able to
provide exact procedures, selects, numbers ...). I prefer this solution.

2) Create a new empty table, with rows defined like TIME_SERIES, create
the functional index on the empty table, insert the data from old table
to the new table (committing after every ~100000 records), drop
TIME_SERIES and rename the new table.
We're able to automate all this, but there's a danger the customer might
need to extend DATA_TBS for the new table.

3) Wrap the current schema upgrade script creating TIME_SERIES_PROBE_ID_IDX
with an except block and ignore the error. After the schema upgrade completes,
instruct the customer to look into the schema upgrade logs and contact
the support if the index creation failed. Ugly solution :)

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

How reproducible:

Steps to Reproduce:
1. Install < 5.3.0 Satellite and populate the time_series table
so that it contains ~ 40 million rows at least.
2. Install rhn-upgrade and upgrade to 5.3.0 Satellite, following
the upgrade instructions.
Actual results:
Schema upgrade for this scenario fails with ORA-01652: Unable to extend
temp segment.

Expected results:
Schema upgrade succeeds.

Additional info:
Comment 2 Milan Zazrivec 2009-07-01 14:57:55 EDT
Fixed according to option 1, satellite.git, SATELLITE-5.3:

Updated documentation / sql scripts are in rhn-upgrade-5_3_0_15-1.
Comment 3 Milan Zazrivec 2009-07-07 08:33:40 EDT
Comment 4 Jeff Browning 2009-07-07 16:08:53 EDT
The instructions above are contained in the upgrade docs now. Following these instructions results in an upgrade from 520 to 530 without error.

Here is what is currently in the 1b.txt file:

2. Conversion of database schema.

   NOTE: If you are using external database you have to upgrade it now!
         Please consult your DBA. Do not continue untill your database
         is upgraded.

   NOTE: If you are using 64-bit embedded database -- see output of
           file /opt/apps/oracle/web/product/10.2.0/db_1/lib/libclntsh.so.10.1 \
           | awk '{print $3}'
         log in as oracle user and run:
           cd /etc/sysconfig/rhn/satellite-upgrade/
           export ORACLE_SID=rhnsat
           sqlplus '/ as sysdba' @satellite-oracle-64bit-fix.sql
         Log out to become root again.

   Convert database schema (your DBA may want to do this for you):
   NOTE: these scripts generate log files. After running a script, inspect
         the corresponding log file for errors. Consult your Red Hat
         representative if any errors are encountered.
         ***These scripts and their corresponding log files need to be
            archived indefinitely.***

   NOTE: as a part of schema upgrade, a function based index to lookup in
         TIME_SERIES table will be created. For the index creation to complete
         successfully, you need to make sure there's enough free space in
         database temporary tablespace. Space requirements in temporary tablespace
         may vary and will depend on the current size of TIME_SERIES table. In
         case there is not enough free space in temporary tablespace, its size
         needs to be extended.

         To find out how much free space in temporary tablespace is needed:
           cd /etc/sysconfig/rhn/satellite-upgrade
           sqlplus -S dbusername/dppassword@dbSID @satellite-time_series-space.sql

         If you use external database, consult your DBA.

         If you use embedded database, do following to find out current free space
         in temporary tablespace:
           su - oracle
           cd /etc/sysconfig/rhn/satellite-upgrade
           ORACLE_SID=rhnsat sqlplus -S '/ as sysdba' @satellite-temp_tbs.sql
         In case there's less free space in temporary tablespace than required,
         do following:
           su - oracle
           db-control extend TEMP_TBS
         Make sure the above steps gained enough free space in temporary tablespace
         and use db-control command again if necessary.

   Verify that database tablespaces have some free space. The database
   schema upgrade scripts below may insert/update some data and cause
   the tablespaces to run out of space if it is close to full. The
   db-control report command can be useful for quick review.

   Ensure that all satellite services except database are stopped:
      /usr/sbin/rhn-satellite stop

   If you are using embedded database, start it with:
      service oracle start

   Upgrade your database schema using spacewalk-schema-upgrade command
   (see manual page for spacewalk-schema-upgrade for more information):

        - run command rhn-schema-version
        - that should match the version of installed satellite-schema package:
          rpm -q --qf '%{version}-%{release}\n' satellite-schema

   Should this database schema conversion process fail (for example if it
   runs out of disk space), restore the database from backup, fix the
   problem, and rerun this step.
Comment 5 Preethi Thomas 2009-08-26 10:04:44 EDT
Release Pending
rhn-upgrade.noarch 0:                                        

step 2 has the Time Series table information.
Comment 6 Miroslav Suchý 2009-08-26 11:57:26 EDT
I created sat520 and insterted 53 290 656 records to time_series table [1].

I followed the upgrade doc and got:
[root@xen15 satellite-upgrade]# sqlplus -S rhnsat/rhnsat@rhnsat @satellite-time_series-space.sq

Required space (MB)

I run:
  db-control extend TEMP_TBS 
several times till I got over 1016 MB. In fact I end on 1250 MB.

Then I run:
and in other terminal I run:
 watch ORACLE_SID=rhnsat sqlplus -S '/ as sysdba' @satellite-temp_tbs.sql
First it allocate fast a lot of TEMP_TBS and then speed of allocation slowed down.
Nevertheless it went past 200 MB of free space, which means that calculation is wrong.
I then extended TEMP_TBS few times again and it stops on:
TABLESPACE                        MB_TOTAL    MB_FREE
------------------------------- ---------- ----------
TEMP_TBS                              1750        521

Which means we need 1229 MB for 50M of records. 
Which means we have to multiply required space by factor 1.21. At least.
I suggest to use factor 1.5 or even 2.

[1] I had to extend both DATA_TBS and UNDO_TBS up to 9 GB for each.

Additionally before this action I had:
[root@xen15 ~]# su - oracle
-bash-3.2$ db-control report
Tablespace                  Size    Used   Avail   Use%
DATA_TBS                   10.7G    6.6G    4.1G    62%

and now had:
-bash-3.2$ db-control report
Tablespace                  Size    Used   Avail   Use%
DATA_TBS                   10.7G    7.5G    3.1G    71%

So I assume that insted of:
 Verify that database tablespaces have some free space.
in rhn-satellite-5-upgrade-scenario-1b.txt, we put something like:
 Verify that database tablespaces have some free space. And make sure that free space in DATA_TBS is at least the same as suggested for TEMP_TBS in previous paragraph.
Comment 8 Milan Zazrivec 2009-09-22 08:32:43 EDT
satellite.git, SATELLITE-5.3: fe72e72ac92c92e0a28bde2f35cec31e076a9fcb
Comment 11 Milan Zazrivec 2009-09-25 03:41:13 EDT
rhn-upgrade- & rhn-upgrade-
Comment 12 Jan Hutař 2009-09-29 19:12:19 EDT
Estimates looks good on RHEL4 (79 GB in /var/satellite and 52M rows in time_series):

[root@pipa03 ~]# su - oracle
-bash-3.00$ cd /etc/sysconfig/rhn/satellite-upgrade
-bash-3.00$ sqlplus -S rhnsat/rhnsat@rhnsat @satellite-temp_tbs-estimate.sql

Space required (MB)

-bash-3.00$ ORACLE_SID=rhnsat sqlplus -S '/ as sysdba' @satellite-temp_tbs.sql

TABLESPACE                        MB_TOTAL    MB_FREE
------------------------------- ---------- ----------
TEMP_TBS                               250        250
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ db-control extend TEMP_TBS
Extending TEMP_TBS... done.
-bash-3.00$ ORACLE_SID=rhnsat sqlplus -S '/ as sysdba' @satellite-temp_tbs.sql

TABLESPACE                        MB_TOTAL    MB_FREE
------------------------------- ---------- ----------
TEMP_TBS                              1500       1500
Comment 13 Petr Sklenar 2009-10-01 13:01:30 EDT
testing procedure, tried 
[root@dhcp-lab-163 newsat]# rpm -qf /usr/bin/satellite-5.3.0-schema-upgrade

rhel5 x8664, sat520
81 GB in /var/satellite and 62M rows in time_series
-bash-3.2$ sqlplus -S rhnsat/rhnsat@rhnsat @satellite-temp_tbs-estimate.sql

Space required (MB)
7 x `db-control extend TEMP_TBS` = extended to 2000MB

[root@dhcp-lab-163 newsat]# /usr/bin/satellite-5.3.0-schema-upgrade
Schema upgrade: [rhn-satellite-schema-5.2.0-8] -> [satellite-schema-]
Searching for upgrade path: [rhn-satellite-schema-5.2.0-8] -> [satellite-schema-]
Searching for upgrade path: [rhn-satellite-schema-5.2.0] -> [satellite-schema-]
Searching for upgrade path: [rhn-satellite-schema-5.2] -> [satellite-schema-5.3.0]
Searching for upgrade path: [rhn-satellite-schema-5.2] -> [satellite-schema-5.3]
The path: [rhn-satellite-schema-5.2] -> [satellite-schema-5.3]
Planning to run sqlplus with [/var/log/spacewalk/schema-upgrade/20091001-114123-script.sql]
Hit Enter to continue or Ctrl+C to interrupt: 
Executing sqlplus, the logs are in /var/log/spacewalk/schema-upgrade/20091001-114123-*.
The database schema was upgraded to version [satellite-schema-].

passed, estimation was OK
Comment 16 errata-xmlrpc 2009-10-06 10:36:10 EDT
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.


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