Jump to content

500,000+ rows on ps_guest sounds too big?

Andrew H
 Share

Recommended Posts

Andrew H Newbie

Andrew H

Posted
Posted

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.

Link to comment
Share on other sites
vekia Apprentice

vekia

Posted
Posted

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
Link to comment
Share on other sites
Andrew H Newbie

Andrew H

Posted
  • Author
Posted

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
Link to comment
Share on other sites
Andrew H Newbie

Andrew H

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
  • 1 month later...
carras Newbie

carras

Posted
Posted

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.

Link to comment
Share on other sites
bellini13 Newbie

bellini13

Posted
Posted

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.

Link to comment
Share on other sites
  • 4 months later...
bellini13 Newbie

bellini13

Posted
Posted

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?

Link to comment
Share on other sites
  • 4 months later...
vekia Apprentice

vekia

Posted
Posted

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

Link to comment
Share on other sites
  • 11 months later...
  • 1 year later...
plum shop online Newbie

plum shop online

Posted
Posted

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

Link to comment
Share on other sites
  • 5 months later...
me-and-prestashop Newbie

me-and-prestashop

Posted
Posted

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.

Link to comment
Share on other sites
  • 2 weeks later...
me-and-prestashop Newbie

me-and-prestashop

Posted
Posted

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
Link to comment
Share on other sites
  • 2 years later...
bnadauld Enthusiast

bnadauld

Posted
Posted
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?

Link to comment
Share on other sites
  • 1 month later...
bnadauld Enthusiast

bnadauld

Posted
Posted (edited)
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
Link to comment
Share on other sites
  • 4 months later...
  • 1 year later...
num47 Newbie

num47

Posted
Posted

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
Link to comment
Share on other sites
  • 2 weeks later...
bnadauld Enthusiast

bnadauld

Posted
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)
Link to comment
Share on other sites
Puppo Rookie

Puppo

Posted
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
Link to comment
Share on other sites
bnadauld Enthusiast

bnadauld

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

Link to comment
Share on other sites
  • 2 years later...
Maurice Contributor

Maurice

Posted
Posted

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

Link to comment
Share on other sites
PrestaHeroes USA Mentor

PrestaHeroes USA

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

Link to comment
Share on other sites
Zohaib-fk Proficient

Zohaib-fk

Posted
Posted

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.

 

Link to comment
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
Go to topic listing
×
×
  • Create New...