500,000+ rows on ps_guest sounds too big?

[Andrew H]

I have performance issues with my site and on my last installation of Prestahop, I had a similar problem (and that was caused by large database files).

 

ps_guest has 513,386 rows and is 30MB in size and I would imagine is the cause (or not helping) my performance issues?

 

Is it safe to empty this table? What does ps_guest do?

 

 

I have a couple of issues related to performance, the site randomly hangs (I think this is when there are a few people simultaneously browsing the site) and in the back office, the "customers" page is really slow to load.

[vekia]

this table is related to the stats in your store, you can EMPTY this table but then you will lose all stats

[Andrew H]

thanks for your response -does this mean I'll permanently lose stats or just everything pre-dating the emptying. I assume the size of this table is too big?

[vekia]

well, it's normal, each row in this table is related to the guests / customers visits on your website, it is for stat purposes.

If you have got big traffic, you will have a lot of datas there. and number of the entries there will grow.

 

if you remove all entries there - your stats will not show these visits.

 

before any changes in the database - make sure that you've got database backup

[Andrew H]

ok thanks, I was just hoping this helped fix a performance issue on the site... Will take your advice and backup first.

[Andrew H]

Just to confirm emptying the ps_guest database in my case has made a HUGE improvement to performance, the site opens in about 1 second compared to 5+ before. Also my customers page opens super fast (<1 second) in the back office where previously it was taking about 10-15 seconds!

 

I think a lot of the performance problems that people refer to are often caused by large database tables that on shared hosting I think will be even more of a problem. It has been the cause of performance problems for me on older installations of prestashop as well.

[vekia]

WOW! what a difference!

glad to hear that your store now works as you expect

may i know what ps version you use?

[Andrew H]

Thanks! It's *nearly* working as I expect....

 

I also found that disabling datamining also makes a noticeable improvement -although the obvious downside is reduced statistics. 95% of the time now, the performance is just what you'd expect.

 

Since this post, I've found an unrelated random performance issue posted elsewhere, but getting there!

 

Using V1.5.3 Have to be honest, v1.1 (though basic) was easier to manage in terms of speed/stability and seemed less affected by stats being turned or not.

[carras]

Does this mean that if you turn off stats you would get a better server response?

[vekia]

if your server is slow the answer is: yes

[carras]

Thanks Vekia,

 

Could you explain how to disable the staticstis properly?

 

Would you recommend me to update to the latest version for better performance? (i'm on 1.541)

 

PS: I do not consider my server slow (is share hosting, but not a bad one) but I'd consider sacrifice PS stats por better performance, I have most I need on analytics. Thanks.

[vekia]

modules > data mining for statistics

----

disable / uninstall this addon

[bellini13]

as an aside...a service like google analytics is a good replacement for the back office stats.  off load the storage and metrics to a free third party service.

 

also, having a table with 500,000 rows should not cause a performance issue, assuming the table is structured properly.  I run large corporate size databases with millions of records.  I'll assume at some point the table indexes were deleted or corrupted, causing your issues.

[dbeezer99]

I hope some one can shed some light on this...my PS stats shows these stats:

 

http://prntscr.com/2pnkus

 

But in google analytics show these stats below HUGE difference!

 

http://prntscr.com/2pnl9o

[bellini13]

your first screen shot does not show up.  your google analytics shows 34 visits, what is prestashop showing?

[dbeezer99]

heres scren shot :

 

http://prntscr.com/2psvrg

 

But in case it doesnt work it showed 3,121 visitors

 

?? I hope that is true but doubt it? I wonder how the stats work so i could know if they were accurate. FYI...I use the google analytics module

[bellini13]

I assume we are comparing the same date range in both stat modules (2/4/2014 all day)?

 

I can't say why there would be such a descrepancy, and which system is correct.  Have you looked at your hosts access log or web stats (awstats for example) to determine how many actual visitors you received?

[jimmyc]

Does disabling stats also remove the monthly - yearly sales records? I am assuming not but just checking if anyone knows. Thanks in advance. Trying to speed up the server a little.

[vekia]

Does disabling stats also remove the monthly - yearly sales records? I am assuming not but just checking if anyone knows. Thanks in advance. Trying to speed up the server a little.

 

yes, it remoes ALL stats

[Nikhil Kumar]

PLease list me the Problems occur if we delete and disable stats.. and empty ps-guest table..

 

Thanks in Advance

[plum shop online]

Hi, Ambassadors, my shop is www.plumshoponline.com

 

I have the 1.6.1.3 CLOUD version of prestashop and my Server response time its really slow. At: https://developers.google.com/speed/pagespeed/insights/ it tells me my server response time is: 4.7 seconds when ideally should be under 200ms.

 

I have deactivated all modules from anylisis and stats from my backoffice but it is still slow.

Can anyone please help me solve this problem and make my server responde time faster. It is affecting a lot my seo

 

I have contact the prestashop team as well to see if they can help me with a support plan and they said i need to contact an agency because they can not fixed that.

 

please help!!!

[me-and-prestashop]

I have a Prestashop 1.6 with a huge database (almost 1 GB!). I can see that ps_connections and ps_guest has more than 2 million rows each. Also ps_connections_source and ps_page_viewed is pretty large. Looking at the date_add on each row I can see rows all the way back to 2014.

 

Is it safe to delete rows from e.g. 2014 and 2015 in these tables? (and keep rows from 2016 and 2017)

 

I'd like to reduce the database size.

[me-and-prestashop]

If any one experience slow page loads I would also suggest using https://tools.pingdom.com to test the site. If image sizes (banners) is to large it will show. And also if sosial plugins slows down page loads.

[me-and-prestashop]

OK. I copied the the Prestashop 1.6 to my localhost (Mamp) and ran this SQL query in the test environment (phpMyAdmin):

# Delete old connection data (only used for stats)
# change 2016-02-01 00:00:00 according to you needs
DELETE c, cs
  FROM ps_connections c
  LEFT JOIN ps_connections_source cs ON (c.id_connections = cs.id_connections)
    WHERE c.date_add < '2016-01-01 00:00:00';
    
OPTIMIZE TABLE ps_connections, ps_connections_source;

It reduced the ps_connections table from 2233686 to 1422691 rows (302.5 to 186.3 MiB) and the ps_connections_source from 183.4 to 102 Mib.

 

The site worked fine both before and after this operation.

 

I found the SQL queries here on GitHub

 

I plan on reducing the ps_guests table as well. It has more than 2423360 rows (241.4 MiB). Will try this (phpMyAdmin):

# Delete all guest without entry in ps_customer table
DELETE g
 FROM ps_guest g
 LEFT JOIN ps_customer c ON (g.id_customer = c.id_customer)
   WHERE c.id_customer IS NULL;
     
OPTIMIZE TABLE ps_guest;

Julienbourdeau's queries was a big help. To delete these rows manually would be a pain.

[bnadauld]

On 4/20/2017 at 9:48 PM, me-and-prestashop said:

OK. I copied the the Prestashop 1.6 to my localhost (Mamp) and ran this SQL query in the test environment (phpMyAdmin):

# Delete old connection data (only used for stats)
# change 2016-02-01 00:00:00 according to you needs
DELETE c, cs
  FROM ps_connections c
  LEFT JOIN ps_connections_source cs ON (c.id_connections = cs.id_connections)
    WHERE c.date_add < '2016-01-01 00:00:00';
    
OPTIMIZE TABLE ps_connections, ps_connections_source;

It reduced the ps_connections table from 2233686 to 1422691 rows (302.5 to 186.3 MiB) and the ps_connections_source from 183.4 to 102 Mib.

 

The site worked fine both before and after this operation.

 

I found the SQL queries here on GitHub

 

I plan on reducing the ps_guests table as well. It has more than 2423360 rows (241.4 MiB). Will try this (phpMyAdmin):

# Delete all guest without entry in ps_customer table
DELETE g
 FROM ps_guest g
 LEFT JOIN ps_customer c ON (g.id_customer = c.id_customer)
   WHERE c.id_customer IS NULL;
     
OPTIMIZE TABLE ps_guest;

Julienbourdeau's queries was a big help. To delete these rows manually would be a pain.

did the ps_guest reduction sql statement work or help?

[bnadauld]

On 10/22/2019 at 3:22 PM, bnadauld said:

conne

I got the reduction in size for both tables (ps_connections & ps_guest) with the following (in myphpadmin Version: 5.6.32):

ps_connections

# Delete old connection data (only used for stats)
# change 2018-01-01 00:00:00 according to you needs
# myphpadmin uses American dates (Year-Month-Day)
DELETE c, cs
FROM ps_connections c
LEFT JOIN ps_connections cs ON (c.id_connections = cs.id_connections)
WHERE c.date_add < '2019-06-01 00:00:00';
OPTIMIZE TABLE ps_connections;

ps_guest

# Delete all guest without entry in ps_customer table
DELETE g
FROM ps_guest g
LEFT JOIN ps_customer c ON (g.id_customer = c.id_customer)
WHERE c.id_customer IS NULL;
OPTIMIZE TABLE ps_guest;

Maybe this will help someone???

Edited December 16, 2019 by bnadauld (see edit history)

[Puppo]

works perfect also with PS 1.7.6.5

[num47]

I have updated my prestashop to the latest version 1.7.7.4

Most of My visitor to my site are bots and hence bots are getting visitors ID and increasing the size of my database. Can anyone help me cleaning these unnecessary data?

 

Thanks you

[bnadauld]

i installed a module on both of my sites. it does the same (and more) as the sql scripts i posted earlier on this thread..... Every now and again i hit the clean buttons.

I get tones of bots too. Sped the sites up - but note: you'll kill any customers checkouts so i press the clean button when i'm not anticipating many orders.

!!!!Def backup your db before you install it and press any buttons!!! 

 

https://mypresta.eu/modules/administration-tools/database-optimization.html

 

Edited May 24, 2021 by bnadauld (see edit history)

[Puppo]

3 hours ago, bnadauld said:

i installed a module on both of my sites. it does the same (and more) as the sql scripts i posted earlier on this thread..... Every now and again i hit the clean buttons.

I get tones of bots too. Sped the sites up - but note: you'll kill any customers checkouts so i press the clean button when i'm not anticipating many orders.

!!!!Def backup your db before you install it and press any buttons!!! 

 

https://mypresta.eu/modules/administration-tools/database-optimization.html

@bnadauldjust a warning from my experiences with mypresta.eu Modules: great modules but no support!

If you have a problem you need a lot of luck to get a answer, most times there is no answer. So if you use a module from there you have to be ready to support your self.. 

I don't know what happened and why this is so. I think @vekia is the owner there and he was a really active Part in this forum but since some time he is not anymore here. We use many of his great modules but as I already said, you are alone after buying.. PS: I also offered to pay for Support... no answer..

Edit again: i checked the Profile of @Vekia and i looks like he is still logging in the forum..

Edited May 24, 2021 by Puppo (see edit history)

[bnadauld]

On 5/24/2021 at 2:45 PM, Puppo said:

@bnadauldjust a warning from my experiences with mypresta.eu Modules: great modules but no support!

If you have a problem you need a lot of luck to get a answer, most times there is no answer. So if you use a module from there you have to be ready to support your self.. 

I don't know what happened and why this is so. I think @vekia is the owner there and he was a really active Part in this forum but since some time he is not anymore here. We use many of his great modules but as I already said, you are alone after buying.. PS: I also offered to pay for Support... no answer..

Edit again: i checked the Profile of @Vekia and i looks like he is still logging in the forum..

i agree but this module really works for me - im on a shared hosting and the amount of logged bots (in _psguest) was making the sites completely unusable. hope you have the same luck

[Maurice]

Hello in the year 2023  i still truncate ps_guest in different shop with many traffic and i never get problems

but i read on some forum that truncate ps_guest is not safe , i m not agree only because i never get problem but if someone know the reason

why is not safe appreciate a comment

Regards

[El Patron]

On 8/6/2013 at 6:28 AM, Andrew H said:

I have performance issues with my site and on my last installation of Prestahop, I had a similar problem (and that was caused by large database files).

 

ps_guest has 513,386 rows and is 30MB in size and I would imagine is the cause (or not helping) my performance issues?

 

Is it safe to empty this table? What does ps_guest do?

 

 

I have a couple of issues related to performance, the site randomly hangs (I think this is when there are a few people simultaneously browsing the site) and in the back office, the "customers" page is really slow to load.

it is safe to truncate.  always first download a backup of the table, then truncate it

this is also true for _connections, there are 3 of them

[Zohaib-fk]

Hi,

You can try some of the SQL queries as per your choice to empty tables. When my database size grows to 3 GB I use these SQL commands.

TRUNCATE TABLE ps_connections;
TRUNCATE TABLE ps_connections_source;
TRUNCATE TABLE ps_connections_page;
TRUNCATE TABLE ps_guest;
TRUNCATE TABLE ps_log;
TRUNCATE TABLE ps_referrer;
TRUNCATE TABLE ps_referrer_shop;
TRUNCATE TABLE ps_referrer_cache;
TRUNCATE TABLE ps_pagenotfound;
TRUNCATE TABLE ps_mail;
TRUNCATE TABLE ps_statssearch;
TRUNCATE TABLE ps_smarty_cache;
TRUNCATE TABLE ps_smarty_last_flush;
TRUNCATE TABLE ps_smarty_lazy_cache;

Also, I have a visitors geolocation module that helps to track visitors' IP addresses and other geolocation details.