Rebuild Search Index

Discussion in 'Installation and setup (Kayako Download)' started by acenetryan, Aug 1, 2012.

  1. acenetryan

    acenetryan New Member

    We recently upgraded to Kayako v4. After the upgrade, we were no longer able to use the search drop down under Tickets. To clarify, we can use the "Ticket ID Lookup", but we cannot use the "Creator/Replier" or "Quick Search" fields. For now, our staff has been going to the Users tab and performing a search here if we want to find all tickets for a given user.

    When we migrated, we ran the "Rebuild Search Index" function from within the admin area. This doesn't appear to have resolved the issue. Today, I thought I would try to re-run this from the command line in screen so I can track its progress.

    php ./console/index.php /Tickets/RebuildIndex/Start/

    First, it looks like this script is suffering from an overflow problem:

    Code:
    [OK]: 100 processed, total: 314,000 (~19 %) in 4.135 seconds; total elapsed: 2.74 hours; mem usage: 2,093,614.17 KiB...
    [OK]: 100 processed, total: 314,100 (~19 %) in 34.588 seconds; total elapsed: 2.75 hours; mem usage: 2,094,269.25 KiB...
    [OK]: 100 processed, total: 314,200 (~19 %) in 105.541 seconds; total elapsed: 2.78 hours; mem usage: 2,094,924.77 KiB...
    [OK]: 100 processed, total: 314,300 (~19 %) in 11.530 seconds; total elapsed: 2.78 hours; mem usage: 2,095,579.88 KiB...
    [OK]: 100 processed, total: 314,400 (~19 %) in 6.922 seconds; total elapsed: 2.78 hours; mem usage: 2,096,234.96 KiB...
    [OK]: 100 processed, total: 314,500 (~19 %) in 6.291 seconds; total elapsed: 2.78 hours; mem usage: 2,096,890.45 KiB...
    [OK]: 100 processed, total: 314,600 (~19 %) in 4.376 seconds; total elapsed: 2.79 hours; mem usage: -2,096,758.51 KiB...
    [OK]: 100 processed, total: 314,700 (~19 %) in 5.613 seconds; total elapsed: 2.79 hours; mem usage: -2,096,103.30 KiB...
    [OK]: 100 processed, total: 314,800 (~19 %) in 3.281 seconds; total elapsed: 2.79 hours; mem usage: -2,095,447.89 KiB...
    [OK]: 100 processed, total: 314,900 (~19 %) in 5.606 seconds; total elapsed: 2.79 hours; mem usage: -2,094,792.66 KiB...
    [OK]: 100 processed, total: 315,000 (~19 %) in 4.908 seconds; total elapsed: 2.79 hours; mem usage: -2,094,137.40 KiB...
    [OK]: 100 processed, total: 315,100 (~19 %) in 6.061 seconds; total elapsed: 2.79 hours; mem usage: -2,093,481.61 KiB...
    Second, why does this script consume so much memory? When does it finally start freeing up some of the RAM it's using? We have 1,693,928 ticket posts:

    Code:
    [OK]: 1,693,928 ticket posts found.
    I let this run for just under 4 hours before having to kill it because our server started hitting swap. As soon as I killed it, 2.5GB of RAM got released. The process was only 20% complete. It seems I can expect to need at least 12GB of RAM for this process alone if this RAM consumption continues at the same rate.

    Code:
    [OK]: 100 processed, total: 343,500 (~20 %) in 4.959 seconds; total elapsed: 3.81 hours; mem usage: -1,907,355.42 KiB...
    [OK]: 100 processed, total: 343,600 (~20 %) in 7.320 seconds; total elapsed: 3.81 hours; mem usage: -1,906,700.24 KiB...
    [OK]: 100 processed, total: 343,700 (~20 %) in 7.421 seconds; total elapsed: 3.81 hours; mem usage: -1,906,044.75 KiB...
    [OK]: 100 processed, total: 343,800 (~20 %) in 3.140 seconds; total elapsed: 3.81 hours; mem usage: -1,905,389.25 KiB...
    [OK]: 100 processed, total: 343,900 (~20 %) in 3.434 seconds; total elapsed: 3.81 hours; mem usage: -1,904,734.48 KiB...
    Obviously, search is a function that we need to iron out. Is this a problem with the Rebuild Index function? If I saw RAM freed up at any point, I would consider performing fewer posts per run, but I used the default 100 posts and things did not go smoothly.
     
  2. Gary McGrath

    Gary McGrath Staff Member

    Hi there,

    If you use the console reindex, if you have to interupt it, you can use this to "start" where the indexer was stopped.

    php ./console/index.php /Tickets/RebuildIndex/Start/10002

    Where 10002 is the number of the post the indexer last got to. ( make sure that you select no when the indexer asks if you want to flush the search engine index )

    Gary
     
    acenetryan likes this.
  3. acenetryan

    acenetryan New Member

    Excellent suggestion, Gary. Much appreciated.
     
  4. cginest

    cginest Established Member

    Is there not a better way to do this? I'm looking at doing some optimization of our environment and I think this will have to be done. We have nearly 10 GB worth of tickets, so I could see this taking up days and days and eating up lots of RAM.
     
  5. Gary McGrath

    Gary McGrath Staff Member

    I am afraid there is no easier way, a rebuild of the ticket search index is going to be slow and painful, there is an awful lot of data which needs to be processed to build the index from scratch. The above suggestion just lets you stage it in "parts" so you can better manage the process.

    Gary
     
  6. Karl Metum

    Karl Metum Established Member

    A quick related question.. The script asks for this:

    Code:
    Type "DELETE" to delete all existing ticket post search engine records (recommended if offset = 0), or hit return to skip
    What I noticed when I chose the "Delete" option is that it takes way to long time to truncate the swsearchindex table (I'm guessing here).
    Would it not be faster if I just truncated the table directly from the database?
    The database size is ~8 GB by the way.
     
  7. scrupul0us

    scrupul0us Member

    The purge only deletes rows of type = 1, truncate would drop the entire table which is why they don't use truncate...

    Obviously truncate would be wildly faster but as it's an all or nothing command it won't work here :(
     
  8. Karl Metum

    Karl Metum Established Member

    Trying to rebuild the index but it finishes around 47% with a message saying "Segmentation fault". I tried it a couple of times but I can't get passed it. Any ideas?
     
  9. Gary McGrath

    Gary McGrath Staff Member

    Hi Karl,

    A Segmentation fault is actually an error from MySQL. does your MySQL error log show any useful information?

    Are you rebuilding your tickets or KB index?

    If you contact support at support.kayako.com we can take a look for you, it might be a case of we need to truncate the swsearchindex, and do a rebuild from empty

    Gary
     
  10. Karl Metum

    Karl Metum Established Member

    Thanks Gary. That's what I did. I truncated that table and rebuilt it. After 47% I got that message. From there I continued the rebuild process by supplying the last indexed ticketpostid and after only 1 % I got the same error message.
    We are not logging anything in mysql from what I can see.. The log files are empty.

    I should probably just try again (truncate and rebuild) and hope that the error message does not appear again.
     
  11. Gary McGrath

    Gary McGrath Staff Member

    What version of Kayako are you currently using?
    if you check your database, what engine is set for the swsearchindex table?

    Gary
     
  12. Karl Metum

    Karl Metum Established Member

    The latest, 4.69.0. Database engine is set to MyISAM.
     
  13. Gary McGrath

    Gary McGrath Staff Member

    And what version of MySQL are you running? ( sorry should have asked before too )

    Gary
     
  14. Karl Metum

    Karl Metum Established Member

    No problem, I appreciate that you are willing to help me :)

    $ mysql --version
    mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.1
     
  15. Gary McGrath

    Gary McGrath Staff Member

    meh, I was hoping it would be 5.6.

    Currently the swsearchindex is using MyISAM, in 5.6, you can change the engine to InnoDB, which is more efficient and less error prone.

    In your case, I think MySQL is not liking some of the values which are attempting to be inserted into the DB.

    can you raise a ticket with support directly, as I think we might need to take a look at your DB.

    Gary
     
  16. Karl Metum

    Karl Metum Established Member

    Oh I see, I'm actually planning on upgrading to 5.6. Then I'll Truncate the table, change the engine to InnoDB and then rebuild search index. If that won't work I'll raise a ticket.

    Thanks for you help Gary!
     
  17. Karl Metum

    Karl Metum Established Member

    Yup. Still the same error message. Will raise a ticket now.
     

Share This Page