Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Long query and page loading

Hello!

I'm working on making sure our upgrade works successfully. One thing I've noticed on the latest version is that clicking into / out of tickets causes a query to be initiated that takes about a minute to finish (sometimes more).  Jumping out and into another ticket seems to be fine, and this seems to not slow anything down.

The query is:

-- Command: Query
-- Time: 13 (at time of snap)
-- State: Sending data
SELECT A1.`id`, A1.`title`, A1.`body`, A1.`format` FROM `ost_ticket_thread` A1
            LEFT JOIN `ost__search` A2 ON (A1.`id` = A2.`object_id` AND A2.`object_type`='H')
            WHERE A2.`object_id` IS NULL AND (A1.poster <> 'SYSTEM')
            AND (LENGTH(A1.`title`) + LENGTH(A1.`body`) > 0)
            ORDER BY A1.`id` DESC

Again, I'm not sure this is affecting anything other than showing the spinning "loading" wheel in the internet browser's tab... is this an issue or not something I should be worried about?  Does this have anything to do with attachments?

Thank you!

Comments

  • Ok - this does appear to be affecting reassigning and selecting departments... on those queries, the database says "waiting for table level lock" until the "sending data" for that query above is done. What is going on?
  • Please help us to help you by reading and following the posting guidelines located in this thread: Please read before requesting assistance.  The more information you give us the better we will be able to assist you. Thank you.
  • Apologies.

    About this osTicket Installation
    Server Information

    osTicket Version v1.9.7 (4be5782) 
    Web Server Software Microsoft-IIS/7.5 
    MySQL Version 5.5.37 
    PHP Version 5.3.28 

    PHP Extensions

    gdlib  Used for image manipulation and PDF printing  
    imap  Used for email fetching  
    xml  XML API  
    xml-dom  Used for HTML email processing  
    json  Improves performance creating and processing JSON  
    mbstring  Highly recommended for non western european language content  
    phar  Highly recommended for plugins and language packs  
    fileinfo  Used to detect file types for uploads  

    PHP Settings

    cgi.fix_pathinfo  "1" is recommended if AJAX is not working  
    date.timezone  America/Denver  

    Database Information and Usage

    Schema ossupport (localhost)  
    Schema Signature b26f29a6bb5dbb3510b057632182d138  
    Space Used 40262.14 MiB 
    Space for Attachments 
  • edited April 2015

    I'm getting these PHP errors as well in my error log.


    [06-Apr-2015 19:40:35 America/Denver] PHP Fatal error:  Uncaught exception 'Exception' with message 'Unable to index content' in ...osticket19\include\class.search.php:633

    Stack trace:

    #0 ...osticket19\include\class.search.php(507): MysqlSearchBackend->__index(Array)

    #1 [internal function]: MysqlSearchBackend->IndexOldStuff(Array, NULL)

    #2 ...osticket19\include\class.signal.php(98): call_user_func_array(Array, Array)

    #3 ...osticket19\scp\autocron.php(58): Signal::send('cron', Array)

    #4 {main}

      thrown in ...osticket19\include\class.search.php on line 633

    [06-Apr-2015 19:44:18 America/Denver] PHP Fatal error:  Uncaught exception 'Exception' with message 'Unable to index content' in ...osticket19\include\class.search.php:633

    Stack trace:

    #0 ...osticket19\include\class.search.php(507): MysqlSearchBackend->__index(Array)

    #1 [internal function]: MysqlSearchBackend->IndexOldStuff(Array, NULL)

    #2 ...osticket19\include\class.signal.php(98): call_user_func_array(Array, Array)

    #3 ...osticket19\scp\autocron.php(58): Signal::send('cron', Array)

    #4 {main}

      thrown in ...osticket19\include\class.search.php on line 633

    [06-Apr-2015 19:44:18 America/Denver] PHP Fatal error:  Uncaught exception 'Exception' with message 'Unable to index content' in ...osticket19\include\class.search.php:633

    Stack trace:

    #0 ...osticket19\include\class.search.php(507): MysqlSearchBackend->__index(Array)

    #1 [internal function]: MysqlSearchBackend->IndexOldStuff(Array, NULL)

    #2 ...osticket19\include\class.signal.php(98): call_user_func_array(Array, Array)

    #3 ...osticket19\scp\autocron.php(58): Signal::send('cron', Array)

    #4 {main}

      thrown in ...osticket19\include\class.search.php on line 633

    [06-Apr-2015 19:46:41 America/Denver] PHP Fatal error:  Uncaught exception 'Exception' with message 'Unable to index content' in ...osticket19\include\class.search.php:633

    Stack trace:

    #0 ...osticket19\include\class.search.php(507): MysqlSearchBackend->__index(Array)

    #1 [internal function]: MysqlSearchBackend->IndexOldStuff(Array, NULL)

    #2 ...osticket19\include\class.signal.php(98): call_user_func_array(Array, Array)

    #3 ...osticket19\scp\autocron.php(58): Signal::send('cron', Array)

    #4 {main}

      thrown in ...osticket19\include\class.search.php on line 633

    [07-Apr-2015 08:21:33 America/Denver] PHP Fatal error:  Uncaught exception 'Exception' with message 'Unable to index content' in ...osticket19\include\class.search.php:633

    Stack trace:

    #0 ...osticket19\include\class.search.php(507): MysqlSearchBackend->__index(Array)

    #1 [internal function]: MysqlSearchBackend->IndexOldStuff(Array, NULL)

    #2 ...osticket19\include\class.signal.php(98): call_user_func_array(Array, Array)

    #3 ...osticket19\scp\autocron.php(58): Signal::send('cron', Array)

    #4 {main}

      thrown in ...osticket19\include\class.search.php on line 633

    [07-Apr-2015 08:21:33 America/Denver] PHP Fatal error:  Uncaught exception 'Exception' with message 'Unable to index content' in ...osticket19\include\class.search.php:633

    Stack trace:

    #0 ...osticket19\include\class.search.php(507): MysqlSearchBackend->__index(Array)

    #1 [internal function]: MysqlSearchBackend->IndexOldStuff(Array, NULL)

    #2 ...osticket19\include\class.signal.php(98): call_user_func_array(Array, Array)

    #3 ...osticket19\scp\autocron.php(58): Signal::send('cron', Array)

    #4 {main}

      thrown in ...osticket19\include\class.search.php on line 633

    [07-Apr-2015 08:21:34 America/Denver] PHP Fatal error:  Uncaught exception 'Exception' with message 'Unable to index content' in ...osticket19\include\class.search.php:633

    Stack trace:

    #0 ...osticket19\include\class.search.php(507): MysqlSearchBackend->__index(Array)

    #1 [internal function]: MysqlSearchBackend->IndexOldStuff(Array, NULL)

    #2 ...osticket19\include\class.signal.php(98): call_user_func_array(Array, Array)

    #3 ...osticket19\scp\autocron.php(58): Signal::send('cron', Array)

    #4 {main}

      thrown in ...osticket19\include\class.search.php on line 633

    [07-Apr-2015 08:21:34 America/Denver] PHP Fatal error:  Uncaught exception 'Exception' with message 'Unable to index content' in ...osticket19\include\class.search.php:633

    Stack trace:

    #0 ...osticket19\include\class.search.php(507): MysqlSearchBackend->__index(Array)

    #1 [internal function]: MysqlSearchBackend->IndexOldStuff(Array, NULL)

    #2 ...osticket19\include\class.signal.php(98): call_user_func_array(Array, Array)

    #3 ...osticket19\scp\autocron.php(58): Signal::send('cron', Array)

    #4 {main}

      thrown in ...osticket19\include\class.search.php on line 633

  • I think I may have figured out what is going on, but would like some clarification if possible.

    I believe this query is indexing ticket notes (threads).  It appears to be running randomly when using the system.  I know this function is generated in class.search.php for Threads search (indexing?). 
    We are upgrading from 1.6 ST to 1.9 (latest) and so it's needing to build (and populate) these new index tables with our gazillion tickets and notes/threads. 
    I believe I see in class.search.php that it should be limiting the "batch" of each of these to either 20 or 60, but it appears to be "indexing" thousands of entries at a time (watching how our ost__search table is growing).

    So my question is:

    Is it actually indexing things? (I've never seen indexing, so I have to guess this is what is happening)
    Is this search indexing working the way it should based on the errors provided above?
    Once everything is indexed, will it stop running these table-locking queries (or at least decrease them from the current 1 to 3 minute queries to 1 to 2 seconds?)
    How does the "index" start occur (is it by user activity?)
    How much is supposed to index with each query when the system is working correctly?  Is there a way to force it to index all at one time, rather than do some indexing based on user activity, OR is it possible to limit the indexing to only a couple rows at a time so it doesn't lock down the whole system while it's doing this?

    Thank you for any help or clarification!!
  • @holodoctor1 I haven't had any negative speed issues w/OsTicket but I did with another application I was using.  A query would run past 120 seconds which wasn't going to cut it in a production environment with other users and processes going on at the same time.  Via phpMyAdmin I created a few indexes on tables that had a lot of data and that were also joining other tables.  I was able to bring a 120 second execution down to < 0.5
  • Unfortunately I'm not so deep into the osTicket code, but I'll point this threads to the developers since they should know best ;)
  • I've already sent it to the devs. :)
  • Guess they'll then receive it twice :D ;)
  • Thank you for checking on this - Don't know if this helps, but it's showing the table being locked until the others finish.
    image
  • Good morning - just wanted to see if the devs had a chance to look into this.  Our system can't be used effectively until this is resolved.

    Thank you!
  • I do not see a reply from the devs here.  I will ping them again.
  • edited April 2015
    @holodoctor1,

    My guess is that you have a one minute scheduled cron and several users using the system. It looks like the search indexer runs on both the autocron and on the scheduled cron. I would recommend we disable the indexer from the autocron — at least on your system. Perhaps you could add / change this code in the `class.search.php` to disable the indexer for the autocron:

        /** 

         * Cooperates with the cron system to automatically find content that is

         * not index in the _search table and add it to the index.

         */

        function IndexOldStuff($signal) {

            if ($signal['autocron'])

                return;


            $class = get_class();

            $auto_create = function($db_error) use ($class) {


    That is, change the IndexOldStuff function definition to receive `$signal` and add the `if` statement to the function prologue.
  • It looks like that may have done it - weird though - I have no one minute cron set up and have the auto-cron off (that I know of!).

    Thank you for your help!
  • edited July 2015
    @greezybacon

    How can I disable indexoldstuff for regular cron calls (i.e. using a BAT file)?   This is still being kicked off whenever we try to run our cron, but is disabled when using autocron.
  • I think I've resolved this by changing reindex to false:

        function bootstrap() {
            if ($this->getConfig()->get('reindex', false))
                Signal::connect('cron', array($this, 'IndexOldStuff'));
        }

    What will changing this to false actually do?  This did prevent that db killing query from kicking off.
  • Sorry to revive this older thread, It was linked in a support thread I opened for basically the same issue.
    I am seeing the same issues as @holodoctor1

    I am coming from version 1.6RC2 to version 1.9.12, over 150,000 tickets.
    Upgrade process goes great, system is very responsive, but seems to slow down greatly when populating the ost__search table.

    the count on ost__search only seems to increase when someone either opens a ticket, replies, or closes a ticket, and it increases by a couple thousand each time. I left our system running all weekend and there was no change to ost__search row count. I do not mind the system being slow for a while, but it would be much better if there was some way to force the system to just fully index itself once upgraded to prevent any slowdowns once in production.

    The only thing preventing us from upgrading right now is this slow down that occurs sometimes when opening tickets, etc.

    If I could upgrade it over the weekend, and fully index it before Monday, that would be perfect.
    I look forward to hearing back from you guys.
    thanks
Sign In or Register to comment.