Jump to content

500,000+ rows on ps_guest sounds too big?


Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • 1 month later...

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • 4 months later...

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?

Share this post


Link to post
Share on other sites

  • 4 months later...

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

  • 11 months later...
  • 1 year later...

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

Share this post


Link to post
Share on other sites

  • 5 months later...

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.

Share this post


Link to post
Share on other sites

  • 2 weeks later...

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.

  • Like 1

Share this post


Link to post
Share on other sites

  • 2 years later...
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?

Share this post


Link to post
Share on other sites

  • 1 month later...
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 by bnadauld (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites

  • 4 months later...
  • 1 year later...

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

  • Like 1

Share this post


Link to post
Share on other sites

  • 2 weeks later...
Posted (edited)

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

 

Untitled picture.png

Untitled picture2.png

Edited by bnadauld (see edit history)

Share this post


Link to post
Share on other sites

Posted (edited)
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 by Puppo (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites

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

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
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More