Jump to content

Welcome, Guest!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

  • 0
movieseals

Search Index table getting massive

Question

Posted (edited)

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 by movieseals

Share this post


Link to post
Share on other sites

Recommended Posts

  • 1

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.

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)
1 minute ago, Brent Dacus said:

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 by movieseals
Adding extra infos

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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.

Screen Shot 2019-06-09 at 12.34.56 PM.png

Share this post


Link to post
Share on other sites
  • 0

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

Screen Shot 2019-06-09 at 12.36.39 PM.png

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)

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 by movieseals

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0
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.

Share this post


Link to post
Share on other sites
  • 0

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...

Share this post


Link to post
Share on other sites
  • 0
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

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)
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 by Brent Dacus
update link

Share this post


Link to post
Share on other sites
  • 0

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

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0
Posted (edited)

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

Edited by Brent Dacus
sorry wrong number

Share this post


Link to post
Share on other sites
  • 0

In ssh term with this you get?

curl -X GET "localhost:9200/"

 

journalctl -u elasticsearch

anything cool

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...