MySQL query locking up

[badger1010]

Our hosting company has had to restart MySQL 4 times in the last 3 days due to our site causing problems.
They are not overly happy about this.

They tell me this is causing the problem:

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` = 0 LIMIT 1


It is causing the table ps_guest to be locked hence all future queries failing and backing up.

The query seems to be being generated by this file:

www/classes/Customer.php

on lines 393->397.

We're using V1.2.5 of PS.

Any help gratefully received.

[rocky]

My guess is that your ps_connections table is too big. I suggest that you try making a backup of the ps_connections, ps_connections_page and ps_guest tables, then emptying them. Do you consider it important to keep track of every time someone accessed your website and which pages they viewed?

[badger1010]

Rocky,

Thank you for your response.

Just checked in phpMyAdmin:

ps_connections = 9.6 MiB
ps_connections_page = 17.1 MiB
ps_connections_source = 18.7 MiB
ps_guest = 2.4 MiB

Is this excessive?

What information do these tables hold and provide to us?
Is there a module that can be uninstalled to stop them being used if they are not essential?

Thanks.

[rocky]

Yep, those looks pretty big. Unfortunately, I'm not aware of any way to disable those stats being generated without modifying code. You can go to Stats > Settings and change "Clean automatically" to "Week", for example, so that all stats older than a week are automatically deleted. I think that only deletes from ps_connections_page though, so ps_connections_source and ps_connections may remain big. You will need to manually delete older entries from them.

[badger1010]

What information are these tables providing?

[rocky]

The ps_connections table is used to keep track of when the customer has logged in. The last connection time is displayed in the Connections column on the Customers tab. The connection that led to an order is also displayed when you view an order on the Orders tab. The ps_connections_pages table keeps track of which pages were viewed in a session. The ps_guest table is used to keep track of people who have visited your site, but haven't signed up.

[nob2b]

Hi Rocky,

 

I also was told from host admin about the excessive usage of cpu as below:

 

---------------------------

CPU Usage - %51.28

MEM Usage - %2.64

Number of MySQL procs (average) - 0.91

Top Process %CPU 163.00 /usr/bin/php /home/account name/public_html/product.php

Top Process %CPU 158.00 /usr/bin/php /home/account name/public_html/product.php

Top Process %CPU 132.00 /usr/bin/php /home/account name/public_html/category.php

------------------------------------------

I did as you told , I cleared the below 3 tables:

ps_connections

ps_connections_page

ps_guest

 

1.Do you think that I have to clear table "ps_connections_source" too?

 

2.Does category block relate to these 3 tables? I was also warned that category use too much cpu.

 

3.Where can I find the "Stats > Settings and change "Clean automatically" to "Week" " with 1.4.4 version?

 

Thank you for your suggestion.

[mohitsareen]

HI

 

My ps_guest table is pretty big with about 1.7 M records in it. I want to know if i empty this table what information i will loose.

 

Will i Loose the carts of that have not been converted to orders.

 

Will a guest user loose the cart he has created if he will come next time?

 

Any advise will be helpful.

[mohitsareen]

The ps_connections table is used to keep track of when the customer has logged in. The last connection time is displayed in the Connections column on the Customers tab. The connection that led to an order is also displayed when you view an order on the Orders tab. The ps_connections_pages table keeps track of which pages were viewed in a session. The ps_guest table is used to keep track of people who have visited your site, but haven't signed up.

 

So I would like to understand about the ps_guest table. Where can i see this information in the admin panel that this table contains.

Any thoughts?