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]

look what I found

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

[movieseals]

3 minutes ago, Brent Dacus said:

look what I found

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

Read that, followed it to the letter, everything seems to work except the actual indexing...  The frustrating part is that there is no error message, no way of knowing if anything is happening.  It just spins and spins and spins.  Nothing happens in the database. Yet, on the server side, everything appears to be working fine.

Edited June 9, 2019 by movieseals

[Factor]

Sounds like you did it correctly.  Not sure I don't have a test box to play on currently.

[Factor]

this?

https://github.com/thirtybees/elasticsearch/issues/56#issuecomment-424992260

 

[movieseals]

The argument makes sense but the code at line 377 of my version does not seem to accommodate that.  I did try, same results.  This fix is from 2018.  Perhaps more was added since?

[Factor]

yeah looks like it

https://github.com/thirtybees/elasticsearch/issues/60

maybe take out some searchable items like above. 

this to?

https://github.com/thirtybees/elasticsearch/issues/62#issuecomment-467309492

Edited June 9, 2019 by Brent Dacus

[movieseals]

I wish I was getting to 33%. 🙂

I have disabled all the caches, will try again.

[movieseals]

I have reset the module.  I have disabled all caches.  I put the site in debug mode and get nothing.

My feeling is that the connection is just not happening somehow.

[dynambee]

6 hours ago, movieseals said:

My feeling is that the connection is just not happening somehow.

Try connecting from the command line with CURL:

curl -GET http://127.0.0.1:9200
or
curl -XGET http://127.0.0.1:9200
or
curl -IGET http://127.0.0.1:9200

You can also try something like this to make sure your ES server is running:

sudo /etc/init.d/elasticsearch status

There should also be a logs directory just below your main elasticsearch directory, or it could be /var/log/elasticsearch. You can also check the system logs, sometimes they will have helpful info.

[movieseals]

1 hour ago, dynambee said:

Try connecting from the command line with CURL:

curl -GET http://127.0.0.1:9200
or
curl -XGET http://127.0.0.1:9200
or
curl -IGET http://127.0.0.1:9200

You can also try something like this to make sure your ES server is running:

sudo /etc/init.d/elasticsearch status

There should also be a logs directory just below your main elasticsearch directory, or it could be /var/log/elasticsearch. You can also check the system logs, sometimes they will have helpful info.

The CURL works in terminal - I get a confirmation that elasticsearch is running and ready to go.  I finally got the module to give me an error after hours of trying stuff: 

 

Error!

Unable to connect with the Elasticsearch server. Has the connection been configured?


The weird part is, from the server side, everything appears to be fine!

 

[root@odedi54851 ~]# sudo /etc/init.d/elasticsearch status
● elasticsearch.service - Elasticsearch
   Loaded: loaded (/usr/lib/systemd/system/elasticsearch.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2019-06-09 16:37:50 EDT; 9h ago
     Docs: http://www.elastic.co
 Main PID: 735517 (java)
   CGroup: /system.slice/elasticsearch.service
           ├─735517 /bin/java -Xms1g -Xmx1g -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75 -XX:+UseCMSInitiatingOccupancyOnly -Des.networkaddress.cache.ttl=60 -Des.netw...
           └─735616 /usr/share/elasticsearch/modules/x-pack-ml/platform/linux-x86_64/bin/controller

 

And the CURL command gets me this:

 

Jun 09 16:37:50 odedi54851.mywhc.ca systemd[1]: Started Elasticsearch.
[root@odedi54851 ~]# curl -XGET http://127.0.0.1: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"

So everything is running fine on the server!

Edited June 10, 2019 by movieseals
Adding info

[movieseals]

I tried with or without https (just in case and for good measure!):

http://127.0.0.1:9200

https://127.0.0.1:9200

https://localhost:9200

http://localhost:9200

Regardless of the combinations, I get a connection error as if nothing has been configured at all!  I tried with or without Ajax proxy.  I really cannot tell what I am doing wrong!

Edited June 10, 2019 by movieseals

[movieseals]

I truly appreciate everybody's help.  Just been trying to figure this out for over 9 hours...  I am able to connect with Redis and Memcache to the same localhost, granted through different ports, so that cannot be the issue.  I know the port for elastic search is open, the server says so.

Edited June 10, 2019 by movieseals

[musicmaster]

Are you sure your Elastic 5 is running at port 9200? Couldn't it be that it chose another port because there was some remainder of the 6 version still occupying that port?

In my installation the search uses also about 2.5 times the size of tb_product_lang. I guess that is the normal proportion. Elastic may be faster, but I doubt it will use less space.

[dynambee]

1 hour ago, movieseals said:

And the CURL command gets me this:

 

Jun 09 16:37:50 odedi54851.mywhc.ca systemd[1]: Started Elasticsearch.
[root@odedi54851 ~]# curl -XGET http://127.0.0.1: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"

So everything is running fine on the server!

A bunch of messages back you mentioned that you installed ES 5. Hopefully by that you meant 5.4? That is the earliest version supported by the plugin. In any case, based on the CURL results you posted above, port 9200 still has ES 6.8 running on it. Not sure where ES 5.4 is but it won't be on 9200.

I'm actually not even sure that it is supported to run two ES servers on one system but having never tried it I can't say for sure. Either way, I would remove both the installed ES servers and start fresh with ES 5.4 and see how that goes.

[movieseals]

No, the port is clean.  Nothing else on it.

I went on github to get the latest version of the module.  Things got worst.  Now I get an error message: [PrestaShop] Fatal error in module file :/home/zbookstore/public_html/modules/elasticsearch/elasticsearch.php:
require_once(): Failed opening required '/home/zbookstore/public_html/modules/elasticsearch/vendor/autoload.php' (include_path='/home/zbookstore/public_html/vendor/pear/archive_tar:/home/zbookstore/public_html/vendor/pear/console_getopt:/home/zbookstore/public_html/vendor/pear/pear-core-minimal/src:/home/zbookstore/public_html/vendor/pear/pear_exception:.:/opt/cpanel/ea-php71/root/usr/share/pear')

I am going to have to look at something else - this thing is buggy as hell and clearly cannot do the job!  Over 12 hours on it and still not even close!  It makes no sense that a module be so complicated to setup!!!

[movieseals]

I reinstalled 6.8 because that was the only one that had some sign of life in that stupid module.  5 will not connect at all, even after wiping everything.  This is clearly not a workable solution.  I tried about half a dozen times. It just won't do anything with 5. Elasticsearch, as promising as it sounds, is clearly not ready for primetime in Thirtybees!

Edited June 10, 2019 by movieseals

[dynambee]

8 minutes ago, movieseals said:

I reinstalled 6.8 because that was the only one that had some sign of life in that stupid module.  5 will not connect at all, even after wiping everything.  This is clearly not a workable solution.  I tried about half a dozen times. It just won't do anything with 5. Elasticsearch, as promising as it sounds, is clearly not ready for primetime in Thirtybees!

The Thirtybees module is not compatible with Elasticsearch 5. It is compatible with Elasticsearch 5.4. These are very different things. It should also work with 6.0 but I don't know of anyone who has tried it with a version as new as 6.8 and there is no way to know if it will run. It is best to get it working with a version that is known to work before trying anything else.

I understand it gets very frustrating when a whole ton of time is spent trying to get something to work. Sometimes it is best to step away for a bit and then try again with rested mind & eyes. It is very much worth it to get the module working but as I mentioned in my original reply, it will probably take some effort.

[dynambee]

If you really want to stay with ES 6.8 then doing something like what is described in this thread might make it work.

[Factor]

Honestly maybe you should see if @lesley or @Traumflug

has any pointers.

And what versions are best and all.

[dynambee]

I've been playing with the ES module on TB 1.0.8.

My host (Cloudways) offers ES 5.4 and 6.5. I tried to get it working with 6.5 but even following the suggestions I found on this forum I was unable to get ES to index with 6.5. I think there have been too many changes between 6.0 (which the module is in theory compatible with) and 6.5/6.8/newer for it to work. Even with only the 8 or 10 sample products in the store the indexing didn't work.

However when I rolled back to ES 5.4 the indexing worked perfectly & was nearly instant.

Now I have a different problem, the front end parts of the ES module do not seem to function with TB 1.0.8 with Bleeding Edge upgrades. Later today I will do some more testing on this and see if I can narrow down exactly why.

[movieseals]

I tried for many days now.  I give up.  It is not normal that a plugin has to have anyone jump through so many hoops without any clear path.

I tried versions 5, 6 and 7 (I tried the composer.json trick another poster mentionned in here), I tried several things for days.  Nothing works.  They do say it is a beta but it does not work and there are no clear indications as to why in the errors logs.

Is there any other alternatives to the native search engine that actually works with TB?

[movieseals]

I managed to reinstall the module and it seems to work, although it still refuses to connect.

The only version of elasticsearch I can manage to install on the server that actually connects properly on port 9200 is 6.8.0.  No other version will work: neither before or after (7.1.1).

I tried to delete the vendor folder, set a composer.json with 6.8.0, run composer update then composer install, and I get this error:

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - The requested package elasticsearch/elasticsearch 6.8.0 exists as elasticsearch/elasticsearch[0.4.x-dev, 1.0.x-dev, 2.0.x-dev, 5.0.x-dev,6.0.x-dev, 6.5.x-dev, 6.7.x-dev, 7.0.x-dev, dev-loekvangool-patch-1, dev-master, dev-sarwarbhuiyan-patch-1, dev-sarwarbhuiyan-patch-2, dev-sarwarbhuiyan-patch-2-1, dev-travis, v0.4.0, v0.4.1, v0.4.2, v0.4.3, v0.4.4, v0.4.5, v1.0, v1.0.1, v1.0.2, v1.1.0, v1.2.0, v1.2.1, v1.2.2, v1.3.0, v1.3.1, v1.3.2, v1.3.3, v1.3.4, v1.4.0, v1.4.1, v2.0.0, v2.0.0-beta1, v2.0.0-beta2, v2.0.0-beta3, v2.0.0-beta4, v2.0.0-beta5, v2.0.1, v2.0.2, v2.0.3, v2.1.0, v2.1.1, v2.1.2, v2.1.3, v2.1.4, v2.1.5, v2.2.0, v2.2.1, v2.2.2, v2.2.3, v2.3.0, v2.3.1, v2.3.2, v2.4.0, v5.0.0, v5.1.0, v5.1.1, v5.1.2, v5.1.3, v5.2.0, v5.3.0, v5.3.1, v5.3.2, v5.4.0, v6.0.0, v6.0.0-beta1, v6.0.1, v6.1.0, v6.5.0, v6.7.0, v6.7.1, v7.0.0, v7.0.1] but these arerejected by your constraint.

So clearly the 6.8 package is not there...

Like stated, I tried various other versions but they will not connect on port 9200 server side.

As for the elasticsearch module, it used to see the elasticsearch version, now all its states is: Elasticsearch version:Unknown

It still refused to connect to anything, regardless, claiming: Unable to connect with the Elasticsearch server. Has the connection been configured?

Yet, if I run curl -X GET "localhost:9200/", I get the following:

{
  "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"
}

Which is apparently what needs to be returned when the connection works... 😞

This has been an incredibly frustrating ordeal.  In the meantime I found this:

https://webkul.com/blog/prestashop-elastic-search/

It seems a little more well thought out and appears to be compatible with PS 1.6 - which most of the time works with TB.  However, my level of success with developers being willing to fix things for TB versions has been pretty low so I am not sure if I want to risk $200 plus on a module considering the struggles I have encountered so far with elasticsearch.

So currently looking for alternatives to the default search engine that actually works with TB without all the elasticsearch headaches.  Any suggestions?

[dynambee]

Have you tried using a version of Elasticsearch that is *actually* supported by the module? That would be 5.4 or 6.0.

I have various problems with the module but it does connect and index with ES 5.4 without trouble. I cannot currently test it with 6.0 but it was tested against both versions over the development of the module so I suspect it will work.

The thing with ES is that the way ES clients & servers communicate with each other changes a bit with each version. This is normal, expected behavior. As the server versions increase new features are added and the communication protocols change.

Using unsupported server versions (ie not 5.4 or 6.0) is much more likely to lead to problems such as not being able to index properly. There is a certain amount of backwards compatibility, of course, but it is always best to start with something that is known to have worked in the past.

If you have used 5.4 and 6.0 and still cannot get the client to connect to the server then it is likely a server configuration issue. Firewall problems, port problems, something like that. I say this because I, personally, have tested ES 5.4 with TB 1.0.8 and 1.0.8 "bleeding edge" and it does connect and index.

[movieseals]

For those who might have ran into the same issues I did, here is how I fixed all my issues:

I tried several prestashop elastic search modules after my failures with the free TB elastic search module and the various hoops it is associated with.  These modules either had the same limitations as the TB one, or they just plain did not work.  I found this one however, which actually is compatible with elasticsearch up to the current version 7:
https://addons.prestashop.com/en/search-filters/19527-elasticsearchconnector.html

The module is pricey but well built and worth every penny in my honest opinion.  The only thing that does not work for some reason with TB is the function for error logging.  The developer actually had to disable it entirely.  Everything else works like a charm.  I was able to use the latest version of elastic search, which is blazing fast.  The module allows you to use the default search engine if you want while indexing but once the indexing is done, since it is not using the DB, you can actually delete the content of the default search engine tables - and in my case, clear several hundreds of MB from my DB!  I now have a DB light as a feather and I tremendously improved the speed of my website and queries.  And if somebody searches something, no matter what, it is lightning fast!!!  Very impressive.  The module requires some tweaking to insure the best results in search, it is so encompassing that you actually need to limit what it searches at times to get more accurate, focused results.  But it is worth the time investment to figure it out.  Also, if you have a lot of products like me, it takes a while to index but once it is done, there is practically nothing left to do but run a cron job to automatically index new products.  I am very happy with the end result and I stopped the never ending ballooning of my Database.

That being said, I do want to thank everyone who tried to help me.  This forum is lightyears ahead of any other ecommerce platform in terms of helpfulness and response.  I really, really appreciate it.  Thank you all.

Edited September 11, 2019 by movieseals

[wakabayashi]

@movieseals thanks for your update. It's a bit sad, that you had to switch away from the tb module, but ofc I understand your decision. Can I see your elasticsearch somewhere in action? Does this module offer facetted navigation/filter?