Increase the speed of prestashop by diabling stats

[ibndawood]

I have a store running on prestashop 1.5.4.1 with 2000+ products and 2000+ visitors everyday. Suddenly the no of visitors got reduced dramatically and I found out that my store was very slow. Further to my investigation I found out that there were many unclosed MySQL connections.

 

All unclsed MYSQL connections were there because of stats modules. This issue was identified and fixed in Prestashop 1.5.6.1 (http://www.prestashop.com/en/developers-versions/changelog/1.5.6.1)

 

I disabled stats, reset the connections and tested the store. Now the speed is up by 400%. I personally don't use any of the stats because I use Google Analytics extensively and I can pull stats by writing my own SQL query. So I disabled all stats. You can also disable stats from Modules->Modules. Click on Analytics and stats and disable the modules that you won't use.

 

Hope this will be helpful for other store owners.

Edited December 13, 2013 by ibndawood (see edit history)

[vekia]

in addition, here on board are several modules to clean stats/connection tables

one client store, after one year, size of connection table: 160mb  with 1000uu/day

[ibndawood]

Yes almost forgot the connections table.

[Dh42]

The connections table is an issue. I have had clients that have had over 1gig in that table. It makes exporting a database a true pain. But yeah, I generally recommend disabling stats for my clients, most of them are not needed with GA. 

[dbeezer99]

So will disabling the stats module also fix problem with tables? If there is alot data in tables and module is disabled then it wont access that table data so it ok its there and wont slow me down..is this correct or so i need to clear out the tables too? Thx !!

[Dh42]

Personally I would empty the connections and guest tables just to keep the database manageable. 

[ibndawood]

Yes truncating the connections table will help !

[PrestaToys]

Hello, thanks for the info.  I have disabled all the Stats Modules but have a few more suspects that I think could/should be disabled.  Please advise if you can.

 

These seems like Stats related even though they say Administration:

 

1) Artichow

2) Google Chart

3) Simple HTML Table Display

4) XML/SWF Charts

 

These also seem unnecessary

 

1) Customer Data Privacy Block

2) Layered Navigation Block

3) Merchant Expertise

4) Visifire

 

What do you guys think?  Kill em all?

 

Also, with the connections table, is this a regular maintenance thing, and can i just empty this table without having to touch any others?

 

Thanks again for all the help and great advice!

 

P

[Denver Prophit Jr.]

Personally I would empty the connections and guest tables just to keep the database manageable. 

Dh42, do you still recommend disabling all stats. And, if so, do you have a recipe list of all the modules that should be uninstalled?

[Dh42]

I don't really. I like disabling them all, but at the same time it is counter productive to some businesses. It really becomes a crux to what I recommend. I look at it on one hand like it is nice to know all of these stats, but at the same time if you are not going to have a hosting account that handle you knowing all of these stats and your site running well, what do you do? We are working on an integration that should be out later this year for segment.io that should help with this. But at the same time I am running into issues with where to port the data. GA won't take identifiable data. Sorry for the long diatribe but that is my thoughts. 

[Denver Prophit Jr.]

Have you examined and forked the GA API module? Seems like the whole presta core team should reevaluate stats. EVEN with memcache, 16GB dedicated mysql server running from an SSD drive, a well optimized smarty cache it is taking a minute or longer to access customer records on stores hosting well over 20k orders, 4k customer records and 1k products. It is insane.

 

When you say, "identifiable" data, are you referring to more work on the part of the store owner to track custom labels?

Edited April 22, 2015 by Denver Prophit Jr. (see edit history)

[Dh42]

Your sever is setup incorrectly. That should take milliseconds to return with a properly setup server with those specs. You records number is low for what I have worked with to be honest. 

 

 

I sound redundant in what I say but let me say it. 

 

Why are you running varnish?

 

Why are you running memcache?

 

When I say identifiable data, GA will not let you collate an email address or a name with what they browsed on your site. It is against the terms of service. Check out rule number 2 here, https://support.google.com/analytics/answer/2795983?hl=en

[Denver Prophit Jr.]

Is there a cookbook list of stats modules to disable?

[freeman78]

Hi Guys,

just interested to know, if i disable the stats modules do i need to empty the tables or are they automatically truncates??

Curious

[btsfreak1]

"Your sever is setup incorrectly. That should take milliseconds to return with a properly setup server with those specs. You records number is low for what I have worked with to be honest."

Not true. Some queries can easily exceed 20 seconds (!!!) execution time with connections tables holding a couple of million entries (on a highly optimized multicore VPS for the SQL instance)

(hint towards: select max(c.`date_add`) as last_visit from `ps_guest` g left join `ps_connections` c on c.id_guest = g.id_guest where g.`id_customer` = ? limit ? in the Customer class).

That has nothing to do with server setup but with bad SQL.

That might  have changed in 1.6.1, though.

I highly recommend anyone to disable stats module and truncate those tables (they wont be emptied when disabling the modules).