Global Search and Basic search Too much slow

Hello,

I am finding my [color=#0000ff]global search[/color] , [color=#0000ff]Basic Search and few other redirections[/color] are [color=#ff0000]too much slow[/color], [color=#ff0000]100+ seconds some times[/color] :blink: (Please see attached Images)

I have set Owner and all permissions as per instructed in installation guide.

Sometimes it gives blank screen after taking too much time.

Please direct toward resolution

Thanks in advance.

How big is your database?

Is it fast sometimes, or always slow?

Do you see any special errors in your logs during those long queries?

How big is your AOD Index directory? (In Linux, try

du -h  modules/AOD_Index/Index/

from your SuiteCRM root directory)

1 Like

Hi pgr,

Thanking for response,

initially this problem was not existing, it is appearing from last 1 week,

The size of my Index directory is 1 GB approx and increasing

Please direct toward solution.

Thanks

Maybe 1GB Index is huge, maybe it’s not, depends on how many records you have in your database…

I asked you a bunch of questions and you only answered one… this makes my attempts to help out in these forums very cumbersome and ineffective…

[color=#0000ff]Hi pgr,[/color]

[color=#0000ff]Thanking for the reply,[/color] :slight_smile:

[color=#ff0000]Right now my global search is totally stopped working, throwing blank white screen :pinch: [/color] [color=#0000ff]The size of my database is 1.7GB and Index directory is of 3.3GB[/color], [color=#0000ff]I am running on Version 7.4.3 [/color]

I debugged cron and I got following errors.

Firest I got

[color=#ff0000]PHP Notice: Undefined variable: reminders in /var/www/html/development/suiteCRM/modules/Reminders/Reminder.php on line 241[/color]

I go to the above file on line number 241 and declared :[color=#0000ff] $reminders = ‘’;[/color] at beginning of [color=#0000ff]getUnsentEmailReminders()[/color] function declaration

than after I started to get following errors from my cron (to get these error I set MAILTO parameter in crontab file and remover “> dev/null 2>&1” part from cron)

PHP Fatal error: Uncaught exception ‘Zend_Search_Lucene_Exception’ with message ‘Index is under processing now’ in /var/www/html/development/suiteCRM/modules/AOD_Index/Lib/Zend/Search/Lucene.php:280
Stack trace:
[color=#ff0000]#0 /var/www/html/development/suiteCRM/modules/AOD_Index/Lib/Zend/Search/Lucene.php(529): Zend_Search_Lucene::getActualGeneration(Object(Zend_Search_Lucene_Storage_Directory_Filesystem))
#1 /var/www/html/development/suiteCRM/modules/AOD_Index/AOD_Index.php(318): Zend_Search_Lucene->__construct(‘modules/AOD_Ind…’)
#2 /var/www/html/development/suiteCRM/modules/AOD_Index/AOD_Index.php(237): AOD_Index->getLuceneIndex()
#3 /var/www/html/development/suiteCRM/custom/modules/Schedulers/Ext/ScheduledTasks/scheduledtasks.ext.php(392): AOD_Index->commit()
#4 /var/www/html/development/suiteCRM/custom/modules/Schedulers/Ext/ScheduledTasks/scheduledtasks.ext.php(357): performLuceneIndexing()
#5 [internal function]: aodIndexUnindexed(Object(SchedulersJob))
#6 /var/www/html/development/suiteCRM/modules/SchedulersJobs/SchedulersJob.p in /var/www/html/development/suiteCRM/modules/AOD_Index/Lib/Zend/Search/Lucene.php on line 297[/color]

[color=#0000ff]my suitecrm Logs are showing following lines[/color]

[color=#ff0000]Fri Mar 3 14:16:02 2017 [10693][1][FATAL] Job 1df64ce4-4b84-fd4c-82bf-58b92df1eb7d (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:17:01 2017 [10702][1][FATAL] Job 863e995d-5e17-5034-3efe-58b92d2cd8c1 (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:18:02 2017 [10712][1][FATAL] Job 2fb06526-ad92-51e2-d632-58b92d9b2d60 (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:19:02 2017 [10718][1][FATAL] Job e99a0f5f-7040-e6ad-1e79-58b92d1d1fe2 (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:20:01 2017 [10724][1][FATAL] Job 688158d6-0298-5971-f459-58b92ec173b4 (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:20:35 2017 [10688][1][FATAL] Query Failed: FROM cases WHERE cases.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM cases WHERE cases.deleted = 0’ at line 1
Fri Mar 3 14:21:01 2017 [10734][1][FATAL] Query Failed: FROM cases WHERE DATE(cases.date_modified) = Curdate() AND cases.date_modified > ‘2016-07-11 12:34:20’ AND cases.date_entered <> cases.date_modified AND cases.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM cases WHERE DATE(cases.date_modified) = Curdate() AND cases.date_modified ’ at line 1
Fri Mar 3 14:22:01 2017 [10741][1][FATAL] Job 89708f20-56b3-b956-7b98-58b92eb70593 (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:23:02 2017 [10748][1][FATAL] Job d3800a2c-81b7-ba70-1984-58b92e42bf5d (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:24:01 2017 [10758][1][FATAL] Job 75c2c7a7-d2af-3871-22bd-58b92f73e811 (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:25:02 2017 [10769][1][FATAL] Job f15e2ae3-0224-0e41-d3da-58b92ff3fede (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:26:01 2017 [10781][1][FATAL] Job 71f2b82f-1989-5766-5467-58b92f73b83b (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:26:38 2017 [10734][1][FATAL] Query Failed: FROM cases WHERE cases.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM cases WHERE cases.deleted = 0’ at line 1
Fri Mar 3 14:27:01 2017 [10803][1][FATAL] Query Failed: FROM cases WHERE DATE(cases.date_modified) = Curdate() AND cases.date_modified > ‘2016-07-11 12:34:20’ AND cases.date_entered <> cases.date_modified AND cases.deleted = 0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM cases WHERE DATE(cases.date_modified) = Curdate() AND cases.date_modified ’ at line 1
Fri Mar 3 14:28:01 2017 [10813][1][FATAL] Job 7a7c71c5-f5d3-7073-6fd1-58b92fd1bd88 (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:28:43 2017 [10819][1e420e07-a864-eadd-29e6-57025d777a54][FATAL] SugarPHPMailer encountered an error: Invalid address:
Fri Mar 3 14:28:43 2017 [10819][1e420e07-a864-eadd-29e6-57025d777a54][FATAL] SugarPHPMailer encountered an error: You must provide at least one recipient email address.
Fri Mar 3 14:29:02 2017 [10825][1][FATAL] Job ead6096b-eeee-466d-6f85-58b93078bfcb (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:30:01 2017 [10838][1][FATAL] Job 76a634b5-8e6e-066f-b913-58b9304ca993 (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:30:33 2017 [10838][1][FATAL] SugarPHPMailer encountered an error: Invalid address:
Fri Mar 3 14:30:33 2017 [10838][1][FATAL] SugarPHPMailer encountered an error: You must provide at least one recipient email address.
Fri Mar 3 14:31:02 2017 [10876][1][FATAL] Job 44cd0e17-4aee-daa0-a4b7-58b9309fbbeb (Run Report Generation Scheduled Tasks) failed in CRON run
Fri Mar 3 14:32:01 2017 [10883][1][FATAL] Job 7a594908-41bf-d69a-5677-58b931dd0b34 (Run Report Generation Scheduled Tasks) failed in CRON run
[/color]

[color=#0000ff]my apache error log stating[/color]

[color=#ff0000][Fri Mar 03 14:34:07.779349 2017] [:error] [pid 10902] [client client_ip:51425] PHP Notice: Undefined index: width in /var/www/html/development/suiteCRM/include/ListView/ListViewSmarty.php on line 102, referer: http://my_ip/development/suiteCRM/index.php
[Fri Mar 03 14:34:09.413191 2017] [:error] [pid 10903] [client client_ip:52155] PHP Notice: Undefined index: DISTRICTINDIADROPDOWN_C in /var/www/html/development/suiteCRM/include/InlineEditing/InlineEditing.php on line 342, referer: http://my_ip/development/suiteCRM/index.php
[Fri Mar 03 14:34:09.413250 2017] [:error] [pid 10903] [client client_ip:52155] PHP Notice: Undefined index: link in /var/www/html/development/suiteCRM/include/InlineEditing/InlineEditing.php on line 377, referer: http://my_ip/development/suiteCRM/index.php
[Fri Mar 03 14:34:15.545020 2017] [:error] [pid 10906] [client client_ip:52157] PHP Notice: Undefined index: email2 in /var/www/html/development/suiteCRM/modules/Home/controller.php on line 96, referer: http://my_ip/development/suiteCRM/index.php
[Fri Mar 03 14:34:15.545063 2017] [:error] [pid 10906] [client client_ip:52157] PHP Notice: Undefined index: LBL_DISTRICTINDIADROPDOWN in /var/www/html/development/suiteCRM/modules/Home/controller.php on line 101, referer: http://my_ip/development/suiteCRM/index.php
[Fri Mar 03 14:34:15.545071 2017] [:error] [pid 10906] [client client_ip:52157] PHP Notice: Undefined index: LBL_DISTRICTINDIADROPDOWN in /var/www/html/development/suiteCRM/modules/Home/controller.php on line 104, referer: http://my_ip/development/suiteCRM/index.php
[Fri Mar 03 14:34:20.315532 2017] [:error] [pid 10864] [client client_ip:51714] PHP Notice: Undefined index: FOLLOWUPREMINDER_C in /var/www/html/development/suiteCRM/include/InlineEditing/InlineEditing.php on line 342, referer: http://my_ip/development/suiteCRM/index.php?action=index&module=Cases&saved_search_select=6c0142c7-ade7-0083-5808-58a6b8ff41ef&saved_search_select_name=Ankita-Followup-Reminder&orderBy=FOLLOWUPREMINDER_C&sortOrder=ASC&query=true&searchFormTab=advanced_search&showSSDIV=no
[Fri Mar 03 14:34:20.315596 2017] [:error] [pid 10864] [client client_ip:51714] PHP Notice: Undefined index: link in /var/www/html/development/suiteCRM/include/InlineEditing/InlineEditing.php on line 377, referer: http://my_ip/development/suiteCRM/index.php?action=index&module=Cases&saved_search_select=6c0142c7-ade7-0083-5808-58a6b8ff41ef&saved_search_select_name=Ankita-Followup-Reminder&orderBy=FOLLOWUPREMINDER_C&sortOrder=ASC&query=true&searchFormTab=advanced_search&showSSDIV=no
[Fri Mar 03 14:34:30.985379 2017] [:error] [pid 10891] [client client_ip:52158] PHP Notice: Undefined index: DISTRICTINDIADROPDOWN_C in /var/www/html/development/suiteCRM/include/InlineEditing/InlineEditing.php on line 342, referer: http://my_ip/development/suiteCRM/index.php
[Fri Mar 03 14:34:30.985450 2017] [:error] [pid 10891] [client client_ip:52158] PHP Notice: Undefined index: link in /var/www/html/development/suiteCRM/include/InlineEditing/InlineEditing.php on line 377, referer: http://my_ip/development/suiteCRM/index.php[/color]

(in above log I have replaced original IPs with other names)

[color=#0000ff]I have placed my cron in www-data crontab (sudo crontab -u www-data -e) and ultimately cron is failing to run.[/color]

Please direct toward solution. :slight_smile:

Thanks

[b][color=#0000ff]Forget to mention: My lucene Index showing last successful run never and in “Running” state for a long time, I have also tried rebuild scheduler but no benifit till yet.

Thanks
[/color][/b]

You’ve now skipped 19 releases since 7.4.3!.. there were plenty of bugs fixed in those releases, you might be getting any one of them.

Your database is quite huge. I would start by considering if that size appropriate for your business size, and your use, and how long you’ve had SuiteCRM, or if something went wrong or is in need of maintenance. Maybe try finding the size of individual tables to see if anything catches your eye.

Then, the Index. Your Index is surely too big. In my test instance I have a 200MB database and a 1.2Mb Index to go with it. this makes sense - a much smaller Index than the data it’s indexing. In your case it’s larger than your data! That, along with all the errors in the logs, and the fact that it never finishes and search doesn’t work, leaves you with a very high likelihood of having a corrupted Index.

You can try reindexing (delete everything and start over, you can find posts explaining how to). But it’s going to take a long time, so schedule it properly and make sure you trim your database before you do it.

1 Like

Hi guys, thought I’d post this one in here as well.

I have a SuiteCRM instance running 7.7.4. AOD is running smoothly. Indexes are done nightly. Index size around 2.5M.

When I do a global search it takes around 5 seconds before results are shown. Process apache2 shoots up to 100% while performing the search.

When I use the “basic” non-AOD-global search, it takes less time and uses less processor resources.

What’s going on here? I thought the point of AOD was to make global search faster via indexing?

Is there a way to speed AOD up? define which modules to index? (I know you can exclude modules in Admin/Global Search, but that has no (immediate?) effect on AOD.)

thanks for your help!