Tables keep getting too large for my small server

[virtualpaul]

Those tables keep getting large even though the store is not even opened yet.  Is there a way to prevent this:

         ps_layered_filter_block.ibd = 574MB
         ps_connections.ibd = 18MB
         ps_guest.ibd = 17MB

I read that some statistic modules (or are they called extensions?) can be deactivated/removed to help this but I am not sure which one.  The ones suggested are not even in my list of installed modules.

[Nickz]

What kind of data is the ps_layered_filter_block.ibd = 574MB holding?

[Rhobur]

You can uninstall any module providing data for PS statistics, the ones that say it adds data to the Stats dashboard, and rely on Google Analytics or similar.

You can also periodically empty the ps_connections tables and the ps_guest table which can grow very big.

[virtualpaul]

12 hours ago, Rhobur said:

You can uninstall any module providing data for PS statistics, the ones that say it adds data to the Stats dashboard, and rely on Google Analytics or similar.

You can also periodically empty the ps_connections tables and the ps_guest table which can grow very big.

The problem is how to know which modules to uninstall.  I have no idea how to find which ones.

[virtualpaul]

22 hours ago, Nickz said:

What kind of data is the ps_layered_filter_block.ibd = 574MB holding?

I don't know.  I am still quite a newbie.

[Nickz]

we all start at cero so don't worry

[Rhobur]

I have said above, 

Quote

the ones that say it adds data to the Stats dashboard

 

please pay attention!
E.g. Best Categories (Adds a list of the best categories to the Stats dashboard.), Best Customers (Adds a list of the best customers to the Stats dashboard.) etc.

[pinwheel]

The more products you add and categories the more combinations are saved to your database. This will have the effect of your database growing. You can add the clear cache script in a cron to shrink this.

[musicmaster]

On 3/6/2021 at 11:46 PM, Nickz said:

What kind of data is the ps_layered_filter_block.ibd = 574MB holding?

That is puzzling me too. It belongs to the Faceted search module. But the layered_filter_block table is rather new: it first appeared in Prestashop 1.7.6.

My suggestion would be: make a complaint at the Prestashop Github and try to install an older version of the module. 

[musicmaster]

Some additional research provided that Prestashop makes an entry in ps_layered_filter_block for each category the first time that you visit it. 

The table contains for each category a json structure and a hash. It looks like they are used to retrieve the product data for the page.

[ietax]

I resume this discussion for a problem
ps_layered_price_index for my shop is huge and I can't activate the module.

I have 7 languages and it generates a price reange for each product combined with each country and each language.
so for each product It generate 1715 rows in the table.
And I have 20000 products !!!

 

How can i Solve? thanks

[Danny]

On 3/6/2021 at 6:53 PM, virtualpaul said:

Those tables keep getting large even though the store is not even opened yet.  Is there a way to prevent this:

         ps_layered_filter_block.ibd = 574MB
         ps_connections.ibd = 18MB
         ps_guest.ibd = 17MB

I read that some statistic modules (or are they called extensions?) can be deactivated/removed to help this but I am not sure which one.  The ones suggested are not even in my list of installed modules.

Try to optimize tables throught phpmyadmin. Just the automatic process often help.

Danny

[tivicrdotcom]

We encountered a similar issue where certain tables were being filled with excessive, irrelevant entries. This came to light when we were migrating to a new server.

The problematic tables were:

layered_category
layered_filter_block
connections
connections_source

To address this, we exported the db without those tables but they were essential for the system, so the way we fixed it, was simple. We exported them without the data, just the structure and this solved the issue.

To go myphpadmin and select the table, then Custom - display all possible options
Compression: gzipped
Format-specific options: structure

This exports only the table structure, leaving out the data. The site continued to function, which confirmed the tables were filled with irrelevant data. If the data were necessary, the site would have broken. In your case where you are not migrating but rather concerned about storage, I would do this:

Make a backup of the db, you may have to use the above method to back it up because those tables make the db huge which result in time out. So export it with Custom - display all possible options and export all tables but the large one, you can check mark which ones you want to export, so uncheck that table.

Then do the above step (exporting structure only) with that single table, now you will have two dbs, the main for the store and the individual for that table (structure only).

Now you open the main db for the store and delete that table. Now import the one with the data structure only and you will end up with one db, including the table that was causing trouble but this time is empty, it will just contain structure so it won't cause any issues.

Hope this helps, it took us 24 hours to come up with the solution but the site is fully functional now. Cheers!

AJ