4 Replies Latest reply on Feb 13, 2020 8:29 AM by M K

    Too many records in ft_pending table for one single record

    M K
      Share This:

      Hi All,

       

      AR Version : 8.1.00

       

      ft_pending is pounded with too many records ( for e,g, 1000's ) for one single record. Issue is only with the SRM:Request and CHG:Infrastructure records.

       

      select count(*), entryId from ft_pending

      group by entryId

      order by count(*) desc

       

      No of records        Entry ID

      ====================================

      21105                     000000000132859

      21103                     000000000132858

      21101                     000000000132857

      1407                       CRQ000000041674

      1406                       CRQ000000041672

      1406                       CRQ000000041673

      88                           000000000132888

      72                           NULL

      41                           000000000132825

      38                           000000000132884

       

       

      For E.g. if I take the first entry ID from the above result : 000000000132859

       

      select count(*), operationType from ft_pending

      where entryId = '000000000132859'

      group by operationType

       

      No of records      operationType

      =============================

      19698                     1

      7164                       2

       

      Schemaid = 1758 ==> SRM:Request

       

      At the moment the number of records is as follows for one single Service request record.

           

      No of recordsFieldidSchemaidEntryidRequest Number
      2455100005231758000000000132859REQ000000133784
      2455100025061758000000000132859REQ000000133784
      24552000000071758000000000132859REQ000000133784
      24552000000121758000000000132859REQ000000133784
      24552600000011758000000000132859REQ000000133784
      24553008903101758000000000132859REQ000000133784
      245510000000011758000000000132859REQ000000133784
      245510000000141758000000000132859REQ000000133784
      245510000002511758000000000132859REQ000000133784
      245510000032971758000000000132859REQ000000133784
      245510000032981758000000000132859REQ000000133784
      245510000032991758000000000132859REQ000000133784
      245510000033001758000000000132859REQ000000133784
      245510000033011758000000000132859REQ000000133784
      245510000033021758000000000132859REQ000000133784

       

      No errors in the arftinx.log related to this entry.

       

      Would be helpful if you could share some ideas.

       

      Thanks

      M

        • 1. Re: Too many records in ft_pending table for one single record
          LJ LongWing

          Douglas Reif,

          Would you be able to explain this in a way that makes sense?

          • 2. Re: Too many records in ft_pending table for one single record
            Douglas Reif

            I can only assume that there was some activity that touched these records.  You can look at API/SQL/Filter logging,   identify the INSERTS into ft_pending that match these schemaIDs/FieldIds  and you should be able to track down what is modifying these records and causing them to be reindexed.

            Could be an escalation, DSO,  or just user activity.  Could be a Push fields action updating a bunch of records in the form all at once.

            With the number of records there, I would guess it might be an escalation.

            This seems to be as-designed since in 8.1, any updates/creates/deletes to an FTIndexed field will cause an entry into ft_pending.

            The good news, for many of us, is that this was one of the efficiencies added to 9.x FTS.  We no longer allow the creation of the subsequent entries into ft_pending if the same form/field/operation is already being indexed.   So when you are able to upgrade to 9.x+  this will no longer be a problem.  

            Until then, you may have to do one of the following:  remove some of your FTS/MFS indexes,  identify and modify escalations that are modifying FTindexed records so that they don't run faster than the indexer's ability to index them.

             

            One thing I didn't really mention was whether indexing was keeping up.  You may be seeing a lot of these still in ft_pending because the indexing is not keeping up with the amount of data in the ft_pending table.  This is another reason to look at the INSERTs in ft_pending and find out what is adding all the work.   You might be able to keep up if you could simply stop some of the huge dumps of records into ft_pending from, perhaps, escalations or filter push fields.

            DougR

            4 of 4 people found this helpful
            • 3. Re: Too many records in ft_pending table for one single record
              M K

              Thanks Douglas,  You are right, I managed to find out that the reason, In my case being Service Request (REQ000000133784) (Parent) and Change request (CRQ000000041673) (Child), for some reason whenever a change request is cancelled, the cancellation entries keeps re-appearing in the Application Pending form and approval engine is processing the cancellation entries. Even though the Change request is in cancelled status, new entries are created in the application for cancellation. This in turn is touching the Service Request (REQ000000133784) (Parent) and Change request (CRQ000000041673) (Child) record, triggering the indexing (ft_pending table.

               

              Not sure, why these cancellation entries are reappearing in the application pending form.

              • 4. Re: Too many records in ft_pending table for one single record
                M K

                Thank you very much Douglas. Managed to fix and It was due to an Incorrect workflow customization.

                1 of 1 people found this helpful