Search Index table getting massive

[movieseals]

Hello again,

My search index table is getting massive.  We have over 50,000 products.  I ran a clean up program for the database and got the following warning: The table tb_search_index size is 116.72 Mb. Maybe you could clean unnecessary records from this table to reduce the size.

This table alone is over a third of the database size.  I have compressed it - and using InnoDB and Barracuda on MariaDB 10.3.  Since it is the Search Index we are talking about, I am not sure we can get rid of anything without affecting the search itself.  Any pointers?

Edited June 9, 2019 by movieseals

[Factor]

Looks like there are issues and fixes.  Might look through this post

https://github.com/PrestaShop/PrestaShop/issues/10530

https://github.com/PrestaShop/PrestaShop/issues/10884

just quick google finds folks with a tenth of your size with issues. 

Edited June 9, 2019 by Brent Dacus

[Factor]

https://docs.thirtybees.com/native-modules/elastic-search/

 

maybe be you need ES.

[Factor]

You could take a table back up and try a rebuild.

https://www.templatemonster.com/help/prestashop-1-6-x-how-to-rebuild-search-index.html

 

[Factor]

General recommendation.  I might be helpful if we knew more about your setup. 

OS

Ram

Cpu

Cpanel, Plesk, no panel etc

PHP version

stuff like that.

then we can look for more exact info of recommendations.  I haven't installed ES myself but willing to help look for info that might then be a guide for all.

[movieseals]

1 minute ago, Brent Dacus said:

You could take a table back up and try a rebuild.

https://www.templatemonster.com/help/prestashop-1-6-x-how-to-rebuild-search-index.html

 

Thanks for the references.  I did try a rebuild and it got back to awful in a matter of days.  Looking into the other possible solutions.

I did try Elastic Search but that module does not seem to work properly.

Edited June 9, 2019 by movieseals
Adding extra infos

[dynambee]

The Elastic Search module will probably take some effort to get working properly but once you do get it working the search on your site will become incredibly fast. At least as fast and probably faster than any site you have visited.

I’m working on a site now that will have ~100k items and eventually probably 200k items and will definitely be implementing the ES module on it. Unfortunately I’m not yet ready to start work on that part of the site.

[movieseals]

OK.  I installed Elastic Search 6.8.0 on my server.  Everything seems to work normally, access via port 9200, which i enabled in the firewall.  From the terminal, everything connects and Elastic Search is active and working.

I now have installed the Elastic Search module for TB.  It does see the version of Elastic Search.  I have set the server to connect as follows:https://localhost:9200

I setup and followed all the other parameters in the instructions.  Everything seems to work except two things:

My theme requires search to be in the hook displaynav instead of displaytop - I was thinking of adding the following after the displayTop instructions:

public function hookDisplayNav()

{

return $this->hookDisplayTop();

}

Second thing, more worrisome, nothing seems to be indexing.  It spins and spins and spins but nothing happens. Index in Database remains empty.  See attachment.

[movieseals]

Here is the situation with the database, despite almost an hour of "indexing"...

[movieseals]

curl is active on the server, and all the java dependencies, everything is up to the latest version.

[movieseals]

I tried to change the server settings to 127.0.0.1:9200 as it does correspond to my localhost and it seemed to have worked for someone here in the forum.  No dice.

[movieseals]

OS: CentOS

Ram: 30 GB

Cpu: Intel(R) Xeon(R) CPU E3-1231 v3 @ 3.40GHz (8 processors)

Cpanel

PHP version: 7.1

MariaDB 10.3.15, InnoDB, all rows Compressed

Thanks for the help!

Edited June 9, 2019 by movieseals

[dynambee]

Try a slightly older version of ES. The module was written to work with 5.4 and 6.0. It may work with newer versions or it may not.

[musicmaster]

Another approach is too look at your blacklist. Thirty Bees/Prestashop maintains a list of common words that are not indexed because they are too common.  The English version begins with "a|about|above|after|again|against|all|am|an|and|any|are|aren|as|at|be|because|been|before|being". In other languages that blacklist is not always set.

The blacklist is in the tb_configuration_lang database table. To find it you look first in the tb_configuration_table for PS_SEARCH_BLACKLIST. With that id you look in the tb_configuration_lang table.

[movieseals]

3 minutes ago, musicmaster said:

Another approach is too look at your blacklist. Thirty Bees/Prestashop maintains a list of common words that are not indexed because they are too common.  The English version begins with "a|about|above|after|again|against|all|am|an|and|any|are|aren|as|at|be|because|been|before|being". In other languages that blacklist is not always set.

The blacklist is in the tb_configuration_lang database table. To find it you look first in the tb_configuration_table for PS_SEARCH_BLACKLIST. With that id you look in the tb_configuration_lang table.

There is an extensive blacklist there, wich I guess should not be indexed in the tb_search_index table.

[musicmaster]

How big is your tb_product_lang table?

Did you have a look at the Preferences->Search entry in your backoffice. Maybe you can set some weights to zero...

[movieseals]

8 minutes ago, musicmaster said:

How big is your tb_product_lang table?

Did you have a look at the Preferences->Search entry in your backoffice. Maybe you can set some weights to zero...

How big is your tb_product_lang table? 58.3 MB

Did you have a look at the Preferences->Search entry in your backoffice. Maybe you can set some weights to zero...  Will have a look

[Factor]

1 hour ago, movieseals said:

Ram: 30 GB

Cpu: Intel(R) Xeon(R) CPU E3-1231 v3 @ 3.40GHz (8 processors)

Now we are geeking out good..

While you are checking the stuff music man suggested

You might glean info here

https://www.server-world.info/en/note?os=CentOS_7&p=elasticstack&f=1

Also did you tune MariaDB config?  Like with Mysqltuner?  If you have MariaDB tuned you site can really fly.  Since you have tons of memory.  Is your server managed or unmanaged.

In cPanel are you using the default EA Prefork setup?  Or did you convert it to MPM event?

Edited June 9, 2019 by Brent Dacus
update link

[movieseals]

Yes, I did tune MariaDB with mysqltuner/  Server is unmanaged, with some limited support.  In cpanel, I have changed to MPM_Event.

[movieseals]

I will uninstall Elastic Search 6 and try installing 5.

[movieseals]

I installed Elastic Search 5.  I get a Failed connect to 127.0.0.1:9200; Connection refused, which was not a problem with version 6.  I checked the firewall and the port is authorized both ways.

[Factor]

The doc I read side it could take up to 5 to 10 seconds for it to Start up fully.

Edited June 9, 2019 by Brent Dacus
sorry wrong number

[movieseals]

I saw that.  It is still failing.

[Factor]

In ssh term with this you get?

curl -X GET "localhost:9200/"

 

journalctl -u elasticsearch

anything cool

[movieseals]

With version 6 I would get this with that command:

 

[root@odedi54851 ~]# curl -X GET "localhost:9200/"
{
  "name" : "2ZwYVKY",
  "cluster_name" : "elasticsearch",
  "cluster_uuid" : "OTZDSTDgQieIL1C7AL4rZw",
  "version" : {
    "number" : "6.8.0",
    "build_flavor" : "default",
    "build_type" : "rpm",
    "build_hash" : "65b6179",
    "build_date" : "2019-05-15T20:06:13.172855Z",
    "build_snapshot" : false,
    "lucene_version" : "7.7.0",
    "minimum_wire_compatibility_version" : "5.6.0",
    "minimum_index_compatibility_version" : "5.0.0"
  },
  "tagline" : "You Know, for Search"
}

 

With version 5, I get a cannot connect.  However, everything is fine in the firewall.  I just reinstalled 6 to see if anything had changed, and lo and behold, it still works and I can connect.

[movieseals]

The logs only indicated the failures (until around 16:30 when I reinstalled 6):

-- Logs begin at Fri 2019-06-07 17:40:08 EDT, end at Sun 2019-06-09 16:45:10 EDT. --
Jun 09 10:39:34 odedi54851.mywhc.ca systemd[1]: Started Elasticsearch.
Jun 09 15:42:15 odedi54851.mywhc.ca systemd[1]: Stopping Elasticsearch...
Jun 09 15:42:15 odedi54851.mywhc.ca systemd[1]: Stopped Elasticsearch.
Jun 09 15:43:57 odedi54851.mywhc.ca systemd[1]: Starting Elasticsearch...
Jun 09 15:43:57 odedi54851.mywhc.ca systemd[1]: Started Elasticsearch.
Jun 09 15:44:01 odedi54851.mywhc.ca systemd[1]: elasticsearch.service: main process exited, code=exited, status=1/FAILURE
Jun 09 15:44:01 odedi54851.mywhc.ca systemd[1]: Unit elasticsearch.service entered failed state.
Jun 09 15:44:01 odedi54851.mywhc.ca systemd[1]: elasticsearch.service failed.
Jun 09 16:37:50 odedi54851.mywhc.ca systemd[1]: Started Elasticsearch.