consuming excessive resources on server

[bedum]

I am using V1.4.4.1. Today i receive the warning email again from the hosting server mentioning that my account is currently consuming excessive resources on their database servers.

 

"If your usage of database queries is not significantly reduced within three (3) business days, we will be forced to suspend your account, as our hosting accounts are not intended for such extreme usage of queries."

 

 

I have checked the Stat and cannot find any suspicious user IP. So i go to the cache folder to check and i was shocked to see there are plenty of files in the cache folder. e..g under /cache/cachefs/f folder, it has more than 80,000 files. And there are more files from /cache/cachefs/g, /cache/cachefs/h.... and more..

Is this the reason why the hosting server is complaining?

 

I am currently having these configuration:

- Force compile = No

- Cache = Yes

-Ciphering algorithm -Use Rijndael with mcrypt lib.

-Caching - Use cache = Enable

 

 

- Caching System = File System

- Directory depth = 1

 

Can someone please help to advise. Thanks thanks thanks

[doekia]

Smarty cache and BO optimization will not help you there.

 

Hint to find the culprit:

1. - Modules: some third party modules get really bad designed they run unoptimized query, some even open some extra sql connection.
   
2. - For some obscure reason (hosting server) sql connections does not get close properly. The connection stays active until they time out. You need to liaise with your hosting company to know if this is the case. The mail you have received is certainly an automaticaly generated one.
   
3. - Your shop starts to get popular and your contract in terms of database usage does not cope with the traffic
   

If you are facing point 2, liaise to me I'll give you some piece of code that usually address the problem. I'm not posting it since this is not a "let's try" curing medicine.

 

Best,

(d)oekia

PS: And last, remove the files in cachefs, necessary files will get recreated on demand. Also, check that you have a valid robots.txt specially containing the google rules.

[bedum]

Thanks for the reply. Yes please give the code so that i find the root cause and solve the problem

I should not have any third party modules except the e..g Facebook like a lot, Blog and homepage carousel slider.

 

Yes i am deleting those files, currently just able to delete 10k of files in a folder only. I am going to leave it whole night

[cedricfontaine]

Just don't use the CacheFS. It never deletes files and yes it's probably the reason you have problem. I don't use CacheFS for this reason.

[doekia]

The mail spotted specifically database resources - nothing to do really with the CacheFS

Has for deleting, temporiraly put some file such as pfm (console) or phpshell if you do not have SSH access,

Go to the cachefs directory, run the os command directly (such as rm ) Will clear the files in a snap

You may also if the ftp distant server allow such run the command on yourself rather than letting you ftp client do a recursive glob and walk thru

 

No I will give the script only if the excess resource usage in the database relate to number of connection that linger for too long until timeout. Sorry, but you don't say to a doctor I want that medicine. He diagnose and prescribe what fits your illness.

[bedum]

I have mange to delete all the cache files..

Also, i have uninstalled all the unnecessary modules.

 

So i am lost now as i don know what to proceed.. Should i wait for another email from Hosting Server to inform me that the excessive usage problem has been resolved? Probably they would send me that my account has been suspended..

 

I tried to check from the STAT, but i don see a feature to check which country does the user come from... may be a hacker is interested to buy my cakes?

Or can i know which table in the database has used up the most usage?

[doekia]

Should i wait for another email from Hosting Server to inform me that the excessive usage problem has been resolved? Probably they would send me that my account has been suspended..

Why not liaise with them to have more input? The message you have received is an auto-generated message. If you still exceed quota again an automated task will drop your service. Those are regular counter-measure in place in hosting company. Liase with them.

 

I am assuming you are in a shared SQL server. If not you may take a look at running the command

"SHOW FULL PROCESSLIST" from you sql client (phpmyadmin or the like)

This will display all queries actually running against the database. Could give you some hint.

[bedum]

I receive these commands from the hosting server which they mentioned is the culprit to my problem.

I went to check the table ps_connections and ps_connections_page, there are so so so many lines of record in my database. I believe these 2 tables are used for the STAT purposes. These 2 tables took up almost 9 MB of size in my database.

Can you advice?

 

 

1) update ps_configuration set value = 'S',date_upd = 'S' where name = 'S' limit N

 

2) insert into ps_connections_page (id_connections,id_page,time_start) values ('S','S','S')1

 

3) select value from ps_configuration where name='S' limit N

 

4) select t.name, count(pt.id_tag) as times from ps_product_tag pt left join ps_tag t on (t.id_tag = pt.id_tag) left join ps_product p on (p.id_product = pt.id_product) where t.id_lang = N and p.active = N and p.id_product in ( select cp.id_product from ps_category_group cg left join ps_category_product cp on (cp.id_category = cg.id_category) where cg.id_group = N ) group by t.id_tag order by times desc limit N, N

 

5) select p.price, pa.price as attribute_price, p.ecotax , pa.ecotax as attribute_ecotax from ps_product p left join ps_product_attribute pa on pa.id_product_attribute = N where p.id_product = N limit N

 

6) select *, ( if ('S' >= from and 'S' <= to, N, N) + if (id_group = N, N, N) + if (id_country = N, N, N) + if (id_currency = N, N, N) + if (id_shop = N, N, N)) as score from ps_specific_price where id_product in (N2) and id_shop in (N2) and id_currency in (N2) and id_country in (N2) and id_group in (N2) and from_quantity <= N and ( (from = 'S' or 'S' >= from) and (to = 'S' or 'S' <= to) ) order by score desc, from_quantity desc limit N

 

7) select priority from ps_specific_price_priority where id_product = N limit N

 

8) select if(count(id_product_attribute), sum(pa.quantity), p.quantity) as total from ps_product p left join ps_product_attribute as pa on pa.id_product = p.id_product where p.id_product = N and id_product_attribute = N group by p.id_product limit N

 

9) select h.name as hook, m.id_module, h.id_hook, m.name as module, h.live_edit from ps_module m left join ps_hook_module hm on hm.id_module = m.id_module left join ps_hook h on hm.id_hook = h.id_hook and m.active = N order by hm.position

[doekia]

1) update ps_configuration set value = 'S',date_upd = 'S' where name = 'S' limit N

This one is usually blocadvertising - check the name in the where - not a big deal - if compulsory drop the block from modules

2) insert into ps_connections_page (id_connections,id_page,time_start) values ('S','S','S')1

This one is from the stats - should you use ganalytivs - uninstall the stats data miner.

You could clear some stats history (prune/optimize)

3) select value from ps_configuration where name='S' limit N

Normal PS stuff

 

4) select t.name, count(pt.id_tag) as times from ps_product_tag pt left join ps_tag t on (t.id_tag = pt.id_tag) left join ps_product p on (p.id_product = pt.id_product) where t.id_lang = N and p.active = N and p.id_product in ( select cp.id_product from ps_category_group cg left join ps_category_product cp on (cp.id_category = cg.id_category) where cg.id_group = N ) group by t.id_tag order by times desc limit N, N

normal PS stuff

5) select p.price, pa.price as attribute_price, p.ecotax , pa.ecotax as attribute_ecotax from ps_product p left join ps_product_attribute pa on pa.id_product_attribute = N where p.id_product = N limit N

Normal

6) select *, ( if ('S' >= from and 'S' <= to, N, N) + if (id_group = N, N, N) + if (id_country = N, N, N) + if (id_currency = N, N, N) + if (id_shop = N, N, N)) as score from ps_specific_price where id_product in (N2) and id_shop in (N2) and id_currency in (N2) and id_country in (N2) and id_group in (N2) and from_quantity <= N and ( (from = 'S' or 'S' >= from) and (to = 'S' or 'S' <= to) ) order by score desc, from_quantity desc limit N

?!? Are you using the alpha 1.5 in production?

7) select priority from ps_specific_price_priority where id_product = N limit N

Normal

8) select if(count(id_product_attribute), sum(pa.quantity), p.quantity) as total from ps_product p left join ps_product_attribute as pa on pa.id_product = p.id_product where p.id_product = N and id_product_attribute = N group by p.id_product limit N

Normal

9) select h.name as hook, m.id_module, h.id_hook, m.name as module, h.live_edit from ps_module m left join ps_hook_module hm on hm.id_module = m.id_module left join ps_hook h on hm.id_hook = h.id_hook and m.active = N order by hm.position

Normal

 

Got puzzled. Which version of PS is this shop running?

 

As regarding stats table. You can delete all records from ps_pagenotfound:

TRUNCATE PREFIX_pagenotfound;
OPTIMIZE TABLE PREFIX_pagenotfound;

PREFIX_ on you site is "ps_"

 

There are other tables that can growth a lot should you have a lot of traffic.

ps_connections

do a delete for past periode (delete from ps_connections where data_add < '2011-07-01'; this clear connections information until jully - then optimise ps_connections

 

ps_cart

Use the BO, select all cart from the cart, delete (those cart that cannot be deleted because or order attached to will not be deleted). After, OPTIMIZE ps_cart;

 

 

You database will shrink but assuming that what they consisder excess of resource is the number of queries. This means you have too much hit compared to your contract a PS page is unsing a single connection but can easily generate 500 queries per page fetched

 

 

Hope this help,

If you are not a SQL expert, be warned that tampering the db can ruin you shop in case of mistake. Ask a skillfull person to do it, make a backup first.

[Mister Denial]

Hi doekia,

 

I have a quick question regarding your comment on the ps_cart table: is that sure that you can select ALL carts and that those with attached orders will NOT be deleted, even if they were selected?

 

Also, users that did truncate their ps_connections table, should they also run optimize? Even on InnoDB? I read that InnoDB usually does not require optimization, but is that true?

 

Thanks in advance for these clarifications, your help is much appreciated!

 

Dan

[doekia]

I was assuming that your shop received upgrade from a legacy version. When that is the case the engine is maintained as MyISAM. True InnoDB does not require so much optimization (but provides no harm to do it).

 

It is safe to select all cart for pruning. Only orphans will be deleted.

SNIPPET from classes/Cart.php

public function delete()
{
 if ($this->OrderExists()) //NOT delete a cart which is associated with an order
  return false;
 $uploadedFiles = Db::getInstance()->ExecuteS('
...