Announcement

Collapse
No announcement yet.

5.5-6.1 Migration: update_content.sh

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 5.5-6.1 Migration: update_content.sh

    I am at the point in my 5.5 to 6.1 migration where I have a clean run of dbupdate_60.sql and I am watching update_content.sh crawl through all the csv files that it processes(200+ for glogowner). It seems to take a couple minutes to process each csv file, but there is minimal database activity, just a small blip every 90-120 seconds. Is there anything that I might be able to look into on my side to get the following function to perform a little faster?:


    function load_data
    {
    echo $1
    echo >> $LOG_FILE

    TABLENAME=$1
    LOWERTAB=$(echo $1 | tr '[A-Z]' '[a-z]')
    clobdir=./content_glogowner/xml/${LOWERTAB#${LOWERVERSION}_}/
    java -Duser.home=$GLOGPROP glog.database.admin.CSVUtil -command $2 -DtranslateErrors=false -connectionId $CONN \
    -tableName $TABLENAME -dataDir ./content_glogowner/ -dataFileName $TABLENAME.csv -clobDir $clobdir >> $LOG_FILE
    }
    Joe Patton

    Technical Specialist, DB2-Oracle DBA
    Database Management and Support
    Parker Hannifin Corporation

  • #2
    Re: 5.5-6.1 Migration: update_content.sh

    I forgot to mention that we are on Oracle 11.2 64-bit on Redhat 5.4. From what I've read, there is no need to install JAccelerator in 11g because the OracleJVM JIT compiler replaces it. Is that correct? I traced down from the pid of update_content.sh to watch the java child processes as they come & go for each CSV file, they just sit there for a minute or so, then jump in cpu for a second and disappear.
    Joe Patton

    Technical Specialist, DB2-Oracle DBA
    Database Management and Support
    Parker Hannifin Corporation

    Comment


    • #3
      Re: 5.5-6.1 Migration: update_content.sh

      Hi Again,

      I figured I would pass along my experience while working through the migration in case anyone else runs into similar performance problems. The update_content.sh script took 16 hours to complete on my DEV web-app server. I've tried to let dbpatch_60.sql(which also calls update_content.sh) run overnight, but my computer was kicked off the network during the "Updating General Content" phase. I restarted it this morning. The following is a paste of a "top -u otm" command on my DEV web-app server(the otm user is the software owner account):

      top - 08:54:42 up 9 days, 21:57, 3 users, load average: 0.06, 0.04, 0.00
      Tasks: 136 total, 1 running, 135 sleeping, 0 stopped, 0 zombie
      Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.9%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st
      Mem: 9200632k total, 8486196k used, 714436k free, 323904k buffers
      Swap: 6144852k total, 204k used, 6144648k free, 7260668k cached
      PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
      13976 otm 15 0 90116 1860 1108 S 0.0 0.0 0:00.02 sshd
      13977 otm 15 0 64928 1652 1192 S 0.0 0.0 0:00.00 ksh
      14079 otm 15 0 76528 19m 9808 S 0.0 0.2 0:00.88 sqlplus
      14082 otm 15 0 90116 1844 1104 S 0.0 0.0 0:00.13 sshd
      14083 otm 15 0 64928 1712 1252 S 0.0 0.0 0:00.00 ksh
      14442 otm 16 0 64800 1340 1084 S 0.0 0.0 0:00.00 update_content.
      14472 otm 17 0 64816 1416 1120 S 0.0 0.0 0:00.02 csv_update_glog
      14951 otm 15 0 12740 1136 832 R 0.0 0.0 0:01.32 top
      15474 otm 17 0 3363m 98m 3724 S 0.0 1.1 0:02.39 java

      The server is not swapping at all, and the java processes that come & go consistently follow the same pattern:

      1. use a couple hundredths of a second worth of CPU time
      2. sleep for a couple minutes
      3. go into run state for a couple seconds
      4. terminate

      The other strange thing is that the top util is telling me that the java child process is using "3363m" of virtual memory. That doesn't make sense if the program is only using 98MB of memory and vmstat is telling me that there is no swapping going on. It is pretty clear to me that glog.database.admin.CSVUtil is the source of the marathon runtime of the content updates. I created a SR with Oracle Support and will update here if I get a solution.
      Last edited by Joe Patton; March 12, 2010, 16:18.
      Joe Patton

      Technical Specialist, DB2-Oracle DBA
      Database Management and Support
      Parker Hannifin Corporation

      Comment


      • #4
        Re: 5.5-6.1 Migration: update_content.sh

        Joe,

        Within the environments that we've migrated so far, we haven't experienced this same issue. There is a notable difference, however, in that our migrations have been to v6.0 and we've doing new installs on v6.1, until the v6.1.1 patch comes out in a couple of weeks. Once it does, we'll be migrating several systems over to that version.

        I agree that the suspect code would be CSVUtil (glog.database.admin.CSVUtil) - but can't imagine why it took that long to run. How large is your OTM DB?

        --Chris

        Comment


        • #5
          Re: 5.5-6.1 Migration: update_content.sh

          Hi Chris,

          Our OTM Database is about 9GB in size. Our upgrade also involved 32 to 64-bit OS upgrades, going from Oracle 10.2 32-bit to 11.2 64-bit, and from 32-bit Oracle Application Server to 64-bit Web Logic. Our DEV environment consists of a database server and a web/app server. We broke the upgrade into 2 phases. First, we upgraded the OS from Redhat Enterprise 4 32-bit to Redhat Enterprise 5 64-bit along with upgrading the RDBMS to 64-bit 11.2. OAS, Apache, and Tomcat remained at 32-bit. We tested the environment for a while at this level with no issues.

          In the next phase, we applied OTM 5.5 CU6 and RU1, tested the environment, and then proceeded to migrate the database from 5.5 to 6.1. DBPATCH_55.sql took nearly 5 hours to complete during the CU6 install, and the update_onecsv.sh runs that were part of the RU1 install were also very slow running. The 5.5-6.1 migration process calls update_content.sh in steps 9, 11, and 14. One thing I did notice during step 9 is that update_content.sh was looking for some "/content_glogowner/xml/finder_set/" xml files that didn't exist(curve.xml for example). Other than the handful of <Error> lines in the log related to missing FINDER_SET xml files, the process took an extremely long time to run. The calls to update_content.sh from dbmigrate_60.sql and dbpatch_60.sql were also long running, but not nearly as bad as in step 9.

          Oracle support gave us a one-off patch(part of OTM 6.1.1) that might help us with performance on the CSV loads. When we upgrade our TEST environment, I will apply this patch immediately before we start our 5.5-6.1 database migration. I will post here to let you know how it runs. We will be testing in our 6.1 DEV environment for a while now that we have it up & running.
          Joe Patton

          Technical Specialist, DB2-Oracle DBA
          Database Management and Support
          Parker Hannifin Corporation

          Comment


          • #6
            Re: 5.5-6.1 Migration: update_content.sh

            Joe,

            I appreciate the rundown and definitely let me know how the patch works out.

            I guess what's odd to me is the 5 hour runtime for DBPATH_55.sql. You definitely don't have

            As long as you have enough memory on the App server for the java commands to run and the DB is running optimally, this should complete MUCH faster.

            I'm just thinking out-loud here, but maybe the CSVUtil calls would benefit from a larger heap size, set manually. By default, I think the jre sets a heap max of 64 or 128MB, which may not be enough for some of the larger CSV data loads.

            Strange, though, that we haven't seen the same. One difference on our side is that the servers were already RH Linux 5 64-bit and the DBs are originally 64-bit, so we're just upgrading from 10.2 to 11.2.

            Again - thinking out loud, since you're using OAS in v5.5 - is your JVM Sun Java or JRockit? This would also account for the extreme difference. Since we standardize on WebLogic, our installs always use JRockit - which is considerably faster.

            --Chris

            Comment


            • #7
              Re: 5.5-6.1 Migration: update_content.sh

              Hi Chris,

              I ended up using the following to apply OTM 5.5 CU6:
              ----------------------------------------------------------------
              java version "1.5.0_17"
              Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_17-b03)
              Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_17-b03, mixed mode)
              ----------------------------------------------------------------
              We are currently at OTM 6.1 in our DEV environment, so the JRockit 64-bit 1.6.0_14-b08 JRE is in place. Most of the team that was working with me on our OTM 6.1 upgrade has been pulled over to our Kewill Flagship shipping system upgrade for the past couple weeks. I'm hoping to get otm61_quickpatch_9439275 applied to our DEV OTM environment by the end of this week and re-run dbpatch_60.sql. I will reply as soon as I have some results.

              Thanks,
              Joe Patton

              Technical Specialist, DB2-Oracle DBA
              Database Management and Support
              Parker Hannifin Corporation

              Comment


              • #8
                Re: 5.5-6.1 Migration: update_content.sh

                Good deal - thanks for the update.

                I'd definitely recommend using JRockit as the JDK for when running the dbpatch scripts -- it'll speed up the data loads considerably.

                --Chris

                Comment


                • #9
                  Re: 5.5-6.1 Migration: update_content.sh

                  Hi Chris,

                  My last response to Oracle on my service request is pasted below. The patch that they suggested cut the runtime of dbpatch_60.sql from 7.5 hours to 27 minutes. We will upgrade our TEST environment sometime within the next month. We will apply the one-off patch after installing the 6.1 application software(before the 5.5-6.1 db migration). I will follow up on this thread when our TEST environment upgrade is complete.

                  Thanks,

                  Hello,

                  I was able to apply patch 9439275 after making the suggested change to our glog.properties file. Next, I ran $GLOG_HOME/glog/oracle/script8/dbpatch_60.sql. The CSV loads ran in seconds instead of minutes and the total runtime for dbpatch_60.sql was only about 27 minutes(the end of the patch log is pasted below). The previous run back on 3/12/2010 ran for 7 hours and 25 minutes. This patch appears to have solved our database migration performance problem. Thanks for all your help.
                  Joe Patton
                  ----------------------------------------------------------------------------------------------------------
                  All patches applied successfully, but please review dbpatch_60_otmdev.log
                  to ensure all other parts of the update (creation of new tables, keys, triggers)
                  also were successful, and that everything compiled correctly.
                  Also, review the update_content_V60P_<timestamp>.log to ensure
                  that no errors occurred for data content load.
                  Removing old version of novpd_load.sql, Run novpd_domain_copy_script_builder.sql to create new version
                  rm: cannot remove `novpd_load.sql': No such file or directory

                  Restart database ....

                  End time: 31-MAR-2010 14:40:07
                  Time Elapsed: 26 Mi 58 Sec
                  Patch process complete.
                  Joe Patton

                  Technical Specialist, DB2-Oracle DBA
                  Database Management and Support
                  Parker Hannifin Corporation

                  Comment

                  Working...
                  X