12 Replies Latest reply on Nov 21, 2019 1:33 PM by Marcell Alzate

    FTS  reindexing is running for long time - more than a month

    Viknesh PS
      Share This:

      Dear All,

       

      Can someone please help.

       

      FTS re indexing is going for month in our project.

      I have checked the collection folder which is getting updated with latest time-stamp ( there are new folders getting created under collection folder till now).

      So I concluded plugin is up and running but not sure why it's taking so long.

       

      Also the count of ft_pending around 9,00,000.

       

      Kindly guide me the steps to be checked.

      Also kindly clarify if the reindex is completed how the new records gets reindexed - Is this like getting started and completed everyday or schedule it - please advise

       

      Regards,

      Viknesh

        • 1. Re: FTS  reindexing is running for long time - more than a month
          Tom Tellefsen

          I cannot help you, but this type of problem is common. What I can do is telling you why this takes a lot of time.

           

          I am in charge of a CRM-system based om Remedy ARS. We use version 9.1. sp1

          15 million records in an Oracle db. Dual Win app servers. Dual win web-servers. Big-ip load balancing.

          Average a few hundre bytes records. Total size of arsystem.dbf is 1.9 GB. That includes ar system base, but not temp, undo, users and so one tablespaces.

           

          To reindex 1.9 GB (edit: TB that is) of data in a db running on flash disks, 22 GB SGA, 4 dedicated cpu's and 10 Gbit/s network, it takes about a week.

          Our super fast db has logged 60 billions disk reads 35 billions buffer gets and about a billion direct writes. Most of the traffic is generated by the indexer. Online production with with 6-10.000 new records each day.

           

          Here is a snapshot for one view accessing one table.

          FTS select  some rows, but fetches all 15 million rows each and evry select, then  sorting, then indexing, then new select again and again, then a new table. Then a new day, a new week an in your case a new month, hurting online traffic and giving the user no search hits since index is not up to date. Bad for production, bad for business.

           

           

          I reindexed my Outlook mailbox yesterday. 2 GB PST-file. Done in less than an hour on a regular win10 PC.

           

          Remedy ARS uses about 200 hours for indexing merely 1.9 GB of data on a superfast production system using 100 billions of disk reads/buffer gets.

          1 of 1 people found this helpful
          • 2. Re: FTS  reindexing is running for long time - more than a month
            LJ LongWing

            Tom,

            You say 9.1....which SP?  I ask because there were some major breakthroughs in FTS indexing speed with SP2...don't know if you are using it.

             

            Additionally....you say your production DB is 2GB?....not 2TB?

            • 3. Re: FTS  reindexing is running for long time - more than a month
              Ravindrakumar Rodge

              I believe the best way to figure out the indexing speed would be to capture the arftindx.log file and analysed it for 1/2 hour or 1 hour time and then figure out how many records indexes are created successfully. This might vary based on the load on the system but atleast will give some rough estimation of the time it'll take for indexing.

               

              Regards,

              Ravi

              2 of 2 people found this helpful
              • 4. Re: FTS  reindexing is running for long time - more than a month
                Tom Tellefsen

                Yes, its TB not GB.

                1.8T    arsystem.dbf

                1.1M    artmp

                12M     control01.ctl

                2.0G    sysaux01.dbf

                904M    system01.dbf

                9.3G    temp01.dbf

                14G     temp2_01.dbf

                7.6G    undotbs01.dbf

                323M    users01.dbf

                 

                Edited my first post and added sp1.

                 

                We are about to upgrade to SP4. So if sp2 has some major improvements in indexing, I will be very happy.

                • 5. Re: FTS  reindexing is running for long time - more than a month
                  LJ LongWing

                  Ok....a 2TB db makes more sense than a 2GB one ....so you are just 9.1.00 at the moment?  You will be pleasantly surprised by the increase with SP2 and beyond.  I'm curious if you would be willing to come back and provide a 'difference' report on the speed of a reindex after the upgrade.

                  1 of 1 people found this helpful
                  • 6. Re: FTS  reindexing is running for long time - more than a month
                    Tom Tellefsen

                    A good advice. Thanks.  The indexer logs a lot and analyzing will take some time. I will try15 minutes of logging and hope that is enough for a rough estimate.

                    I am reindexing for the second time for the moment. Had a problem with the stp-file.  Easter holiday starts tomorrow at noon and last until next tuesday. Hopefully we are finished re indexing by then.

                    • 7. Re: FTS  reindexing is running for long time - more than a month
                      Tom Tellefsen

                      Well, I can do that, but only if the improvements are really major

                       

                      I guess I can do a reindexing starting on a friday afternoon. Production starts monday morning at 7 am.

                      But first: Upgrading sometime this spring.  We are really past due.

                      • 8. Re: FTS  reindexing is running for long time - more than a month
                        Tom Tellefsen

                        A brief update:

                        The indexer has been running for 10 days now. We are about half way trough, using the size of the collection directory as a guideline. That is 240 hours. Most days where Easter holiday and no user traffic. Seems that when using real Norwegian words in the stp-list, the indexer collection directory got smaller but the indexing process itself got even slower than ever.

                         

                        Tens of billions disk read and buffer gets. A disaster really.

                         

                        The solution seems obvious: Stop using FTS.

                         

                        To accomplish case insensitivity for text searches:

                        set nls_sort = 'BINARY_CI';    (CI = Case Insensitive)

                        set nls_comp = 'LINGUISTIC';

                         

                        Can be done for session, database and/or instance.

                        Recreate all indexes to use binary_ci option. The optimizer will not use the old db table indexes when nls_sort is set to binary_ci, causing slow searches.

                        Create index with 'NLS_SORT = BINARY_CI' option

                         

                        A preliminary test shows about the same speed for text searches as before.

                        Advantages: No FTS agent, no FTS setup in Remedy, no FTS license and most of all no indexer causing spikes in db all day long. (a problem even with a completed  index rebuild done)   

                         

                        Upgrading to 9.1 SP4 will be performed anyway.

                        1 of 1 people found this helpful
                        • 9. Re: FTS  reindexing is running for long time - more than a month
                          Tom Tellefsen

                          At 11.43 today we disabled the FTS agent and the FTS option.

                          We got tired of waiting and our customers agreed to have case sensitive searches until we reconfigure the database to use the Case Insensitive option.

                           

                          The customers are happy with the performance benefit and so are we in operations.

                           

                          The difference speaks for itself:

                          • 10. Re: FTS  reindexing is running for long time - more than a month
                            Theo Fondse

                            I would suggest thinking twice before making your Oracle DB case insensitive Tom Tellefsen. We did that last year with an ITSM 9.1.02 installation and the general DB performance was unacceptable. Make sure you do thorough performance testing on a case insensitive DB before taking it into production.

                             

                            BTW: Thanks for posting that last performance graph - it really does highlight a dramatic change!  :-)

                            • 11. Re: FTS  reindexing is running for long time - more than a month
                              Tom Tellefsen

                              Thanks for the advice.

                              The problem with Remedy text search is that they use the LIKE operator.

                              A typical search generates the following two sql's:

                              First:

                              SELECT *
                              FROM
                              (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1
                              FROM
                              (SELECT T229.C700030037, T229.C700001000, T229.C700001001, T229.C700030056, T229.C3, T229.C700030006, T229.C1
                              FROM T229
                              WHERE (T229.C700030028 LIKE 'apekatt' ) ORDER BY T229.C1 ASC) AR_SQL_Alias$1)
                              WHERE ((AR_RowNumber_Alias$1 > :2 ) AND (AR_RowNumber_Alias$1 < :3 ));

                              A limited table scan, but still very heavy.

                               

                              Then another, even heavier one:  Do not know why this count(*) is necessary. The other search gives the same count, so a new select(count) seems like a waste of time.

                              SELECT COUNT(*)
                              FROM T229
                              WHERE (T229.C700030028 LIKE 'apekatt');

                               

                              The C700030028 is a clob and a full table scan generates lost of disk reads.

                               

                              An alternative that has come up, is to make a special search form, use oracle text indexes and tailor our own searches using the CONTAINS operator.

                              Searches are fast, and can be even faster when limited by date range, type of customer requests and so one.

                               

                              create index idx_sporsmal on T229("C700030028") indextype is ctxsys.context;

                              select T229.C700030028 from T229 WHERE CONTAINS("C700030028", 'apekatt')>0;

                               

                              This select is case insensitive by default. We got both apekatt and Apekatt.

                              The index is quite large and it needs to be maintained for every insert to the table, but the search takes 4 seconds where Remedy takes 57 seconds.

                              15 million clobs in table. Average 4k size.

                               

                              Further testing will be required before we make a final decision.

                              • 12. Re: FTS  reindexing is running for long time - more than a month
                                Marcell Alzate

                                very interesting thread.

                                how did it ended?  Was oracle insensitive set.

                                We had done it (AR Remedy 8.1), but unfortunately I wasn't aware of the performance before it.

                                 

                                When searching in SmartIT 1.6 global search performance is awful (no sure if there is any other underlying issue, it is a dev environment) when searching in midtier form (directly by CI Name* in asset form) the respond is fast (no global search involved there I guess)

                                 

                                Judging by some validation, there is no FTS implemented for asset forms. in our environment, therefore I assume the search is always going towards the database rather than FTS index.