Site EXTREMELY slow after upgrade.

General Installation & Upgrade
1

I had CS do an update for me last night and the site is so slow it is now useless. I went from 2.0.11 to 2.1.4. Since I had several mods done to the cart I had the dev team go through and update the mods so they were compatible. But clicking on a link within the store takes minutes for it to load if it loads at all. The backend is extremely slow too. For example, updating the status of an order takes a minute or so…and then sometimes defaults to the old status. The odd thing is occasionally it runs normal speeds. I actually was going to post this hours ago then the site started running at normal speed. Then it got really slow again to completely unresponsive. Currently it is just extremely slow/unresponding. Even seeing fatal errors every so often. Example:


Fatal error: Out of memory (allocated 10747904) (tried to allocate 30720 bytes) in /home/fastdeca/public_html/shop/lib/templater/Smarty_Compiler.class.php on line 248



Obviously something isn’t right and I have already sent a ticket to CS but wondering if there is anything I should check now since it may be a while before anyone checks that ticket.

2

Hosting company says:

[QUOTE]It appears mysql is very busy on the server and is using a lot of cpu resources and memory. Current overall memory usage on the server is at 1.4GB.[/QUOTE]



My VPS Package only has 768MB of RAM (which I though should be more than enough…since it has be so far).



Does that sound normal?

3

Don’t know if this has anything to do with it, but…



In your config.local.php, how much memory do you have under



// Set maximum memory limit



And what kind of cache do you use? Find this under



//Cache backend

$config[‘cache_backend’] = ‘sqlite’;



(can be file, sqlite, mysql, shmem) … most seem to recommend sqlite

4

[quote name=‘Flow’]Don’t know if this has anything to do with it, but…



In your config.local.php, how much memory do you have under



// Set maximum memory limit



And what kind of cache do you use? Find this under



//Cache backend

$config[‘cache_backend’] = ‘sqlite’;



(can be file, sqlite, mysql, shmem) … most seem to recommend sqlite[/quote]



564M for for memory limit. “file” for Cache backend.

5

[quote name=‘IsItFast’]564M for for memory limit. “file” for Cache backend.[/quote]



You are with HostGator/Monster right?

6

Hi Jesse,



I’m with ServInt on their Essential VPS. I’m getting extremely frustrated with CS since they constantly point the finger at hosting every time I have a problem like this. When it is obvious that the problem is with the cart/coding they did. But it takes a week of emailing them back and forth before they finally understand. And my website is currently down because of this and it being the weekend it will probably next week before they even look at it now.



They tell me it tests fine on their server (which it does) but as soon as they put it on my server the problems are occurring. They also state it has to be my server since my old cart (that they moved) is also running slow/not responding. I can’t believe that someone that is in business can’t understand that the old cart is going to run slow too because the server is getting bombarded with load heavy quires and had nothing to do with the old site being slow (which it never was). It is just the server is constantly being overloaded by the new cart. In any case, here is what my host is saying:


[quote]I’ve taken a look at the server and currently the primary cause of your load (3.54) is the following mysql queries.



±-----±----------------±----------±---------------±--------±------±-----------------------------±-----------------------------------------------------------------------------------------------------+

| Id | User | Host | db | Command | Time | State | Info |

±-----±----------------±----------±---------------±--------±------±-----------------------------±-----------------------------------------------------------------------------------------------------+

| 2092 | leechprotect | localhost | leechprotect | Sleep | 24582 | | NULL |

| 6483 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 202 | Copying to tmp table on disk | SELECT SQL_CALC_FOUND_ROWS products., descr1.product as product, MIN(prices.price) as price, GROUP_ |

| 6486 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 195 | Locked | INSERT INTO cscart_product_popularity (product_id, viewed, total) VALUES (‘34218’, ‘1’, ‘3’) O |

| 6487 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 194 | Locked | INSERT INTO cscart_product_popularity (product_id, viewed, total) VALUES (‘33359’, ‘1’, ‘3’) O |

| 6488 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 192 | Locked | SELECT cscart_products., cscart_product_descriptions., MIN(cscart_product_prices.price) as price, |

| 6489 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 181 | Locked | SELECT cscart_products., cscart_product_descriptions., MIN(cscart_product_prices.price) as price, |

| 6492 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 161 | Locked | SELECT cscart_products., cscart_product_descriptions., MIN(cscart_product_prices.price) as price, |

| 6493 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 150 | Locked | SELECT SQL_CALC_FOUND_ROWS products., products_categories.position, descr1.product as product, MIN( |

| 6497 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 155 | Locked | SELECT cscart_products., cscart_product_descriptions., MIN(cscart_product_prices.price) as price, |

| 6498 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 134 | Locked | SELECT SQL_CALC_FOUND_ROWS products., products_categories.position, descr1.product as product, MIN( |

| 6499 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 141 | Locked | SELECT cscart_products., cscart_product_descriptions., MIN(cscart_product_prices.price) as price, |

| 6501 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 125 | Locked | SELECT cscart_products., cscart_product_descriptions., MIN(cscart_product_prices.price) as price, |

| 6502 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 120 | Locked | SELECT cscart_products., cscart_product_descriptions., MIN(cscart_product_prices.price) as price, |

| 6504 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 101 | Locked | SELECT cscart_products., cscart_product_descriptions., MIN(cscart_product_prices.price) as price, |

| 6505 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 95 | Locked | INSERT INTO cscart_product_popularity (product_id, viewed, total) VALUES (‘33359’, ‘1’, ‘3’) O |

| 6507 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 74 | Locked | INSERT INTO cscart_product_popularity (product_id, viewed, total) VALUES (‘33359’, ‘1’, ‘3’) O |

| 6508 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 60 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS products., descr1.product as product, MIN(prices.price) as price, descr1 |

| 6509 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 53 | Locked | INSERT INTO cscart_product_popularity (product_id, viewed, total) VALUES (‘33359’, ‘1’, ‘3’) O |

| 6510 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 32 | Locked | INSERT INTO cscart_product_popularity (product_id, viewed, total) VALUES (‘33359’, ‘1’, ‘3’) O |

| 6511 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 36 | Locked | SELECT cscart_products., cscart_product_descriptions., MIN(cscart_product_prices.price) as price, |

| 6512 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 13 | Locked | INSERT INTO cscart_product_popularity (product_id, viewed, total) VALUES (‘34218’, ‘1’, ‘3’) O |

| 6514 | root | localhost | NULL | Query | 0 | NULL | show processlist |

±-----±----------------±----------±---------------±--------±------±-----------------------------±-----------------------------------------------------------------------------------------------------+







Note that the first query is locking the table so all of the other queries are getting backed up.







Copying to tmp table on disk | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(prices.price) as price, GROUP_CONCAT(IF(products_categories.link_type = ‘M’, CONCAT(products_categories.category_id, ‘M’), products_categories.category_id)) as category_ids, popularity.total as popularity, cscart_seo_names.name as seo_name FROM cscart_products as products LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ‘EN’ LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = ‘’ OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN (‘A’, ‘H’) LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = ‘p’ AND cscart_seo_names.dispatch = ‘’ AND cscart_seo_names.lang_code = ‘EN’ WHERE 1 AND products.company_id = 0 AND popularity.total >= 1 AND (products.usergroup_ids = ‘’ OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN (‘A’) AND prices.usergroup_id IN (0, 0, 1) GROUP BY products.product_id ORDER BY popularity.total desc LIMIT 0, 3 |

| 6523 | fastdeca_adm214 | localhost | fastdeca_cs214 | Query | 127 | Locked | INSERT INTO cscart_product_popularity (product_id, viewed, total) VALUES (‘30015’, ‘1’, ‘3’) ON DUPLICATE KEY UPDATE viewed = viewed + 1, total = total + 3



This query is taking over 200 seconds to execute so far.

[/quote]

7

Looks as though its either relative the the MySQL version OR the cache type you are using. PM me root access and I’ll attempt to fix it.



J.

8

Yep something doesn’t sound right. We’re on the same ServInt plan and have had zero problems since Jesse set it up correctly. He will fix it.

9

The only thing I’m aware of that creates a temp table is a search. How many products do you have? Since temp tables are created in memory first, then transferred to disk if elapsed time and memory demands require it, then using ‘file’ as the caching method is probably creating quite a lot of disk I/O that’s getting queued up.



Try changing your backend cache to ‘sqlite’ and see if you see a difference. Having only 768MB of RAM configured in your VPS is way too small. If you have more than a couple of thousand products, you should probably be at 2GB minimum.



To effectively calculate your RAM requirements (for the cart, excluding overhead of the OS/VPS), take your memory_limit set in config.local.php and multiply it times the number of simultaneous users you want to support and then multiply that number times 3 (for all the ajax requests). Most sites in the cs-cart class of merchants usually have no more than 3 simultaneous users (excluding admin activity). So if you have your memory_limit set to 128MB, then you have:

128 * 3 * 3 = approx 1GB.

Then add in another 512MB for the OS/VPS.

10

I currently put the store back to the old cart as a temporary fix. Since it is the weekend I couldn’t have my site down until Monday (when CS may try and fix it). So things are working fine now.



Maybe I need to upgrade to a larger server as I’m running close to 5000 products now. The site on 2.0.11 seems fast but definitely could be faster. I’ve just been happy with it performing “ok” since I have quite a few custom scripts and so many products. I hate to pay for a premium hosting package for no gains. But if you guys think things will speed up things I’d be willing to upgrade.



Obviously something isn’t right somewhere with the 2.1.4 upgrade though…I don’t know if anything would fix that…see attachment. Can you guess when they did the upgrade?





11

Just occured to me that you are probably on mySQL 5.0 which is a rather old version if I can recall it correctly. Good chance that the modules loaded with Apache don’t ‘fit’ that version all to well, again server environment depending.



J.

12

[quote name=‘JesseLeeStringer’]Just occured to me that you are probably on mySQL 5.0 which is a rather old version if I can recall it correctly. Good chance that the modules loaded with Apache don’t ‘fit’ that version all to well, again server environment depending.



J.[/quote]



Well the host doesn’t think that’s the issue per their response:

[QUOTE]when mysql is upgraded apache needs to be recompiled against the new version so that the libraries are updated. If this was not done, it would be impossible for apache to talk to mysql. If you like we can try upgrading mysql and recompiling apache, but I do not think this is the reason for the slowness. Mysql version 5.0 is older but not the oldest and as far as I know it is still fully supported.

[/QUOTE]



But I have noticed I have 2 “extra” mysql database/users on the server. I have no idea why I would have 3 on there. They also vary quite a bit in size. Right now I’m not sure why CS keeps saying the database that is pulling all queries isn’t related to the cart when it clearly is when I look in the config.local.php file.

13

[quote name=‘IsItFast’]Well the host doesn’t think that’s the issue per their response[/quote]



Can’t help then.

14

Thanks for at least trying Jesse.

15

[quote]

But I have noticed I have 2 “extra” mysql database/users on the server. I have no idea why I would have 3 on there. They also vary quite a bit in size. Right now I’m not sure why CS keeps saying the database that is pulling all queries isn’t related to the cart when it clearly is when I look in the config.local.php file.

[/quote]



This doesn’t make a lot of sense and it would help greatly if you could include the references you’re referring to. I.e. the database definitions (without password of course) in your config.local.php file and then maybe a list of the database and database user names you’re referring to.



CS-cart has a habit of copying the DB to a new name and then doing the upgrade so they have a safer way to revert if needed (wouldn’t want to rely on the tools provided to everyone else). So you probably have one DB from your old environment and one from your new.



The config.local.php file MUST be pointed to the current database (one for the current version) or you’ll have all sorts of problems.

16

If i was a betting man, I’d upgrade off of 2.0.11 and I’d venture to say your issues would probably go away.

:cool::smiley:

17

[quote name=‘tbirnseth’]This doesn’t make a lot of sense and it would help greatly if you could include the references you’re referring to. I.e. the database definitions (without password of course) in your config.local.php file and then maybe a list of the database and database user names you’re referring to.



CS-cart has a habit of copying the DB to a new name and then doing the upgrade so they have a safer way to revert if needed (wouldn’t want to rely on the tools provided to everyone else). So you probably have one DB from your old environment and one from your new.



The config.local.php file MUST be pointed to the current database (one for the current version) or you’ll have all sorts of problems.[/quote]



You are correct. It appears the other 2 are probably from previous versions. I know the 2.0.11 is using one of them and the other one is probably from my original install years ago of v1.3. The 2.1.4 DB is almost 3 times the size of my 2.0.11 version (197mb vs. 68mb). Thanks for clearing that up.

18

[quote name=‘TonyK’]If i was a betting man, I’d upgrade off of 2.0.11 and I’d venture to say your issues would probably go away.

:cool::D[/quote]



Are you saying I should upgrade in steps?

19

If you had cs-cart upgrade you from whatever version to 2.1.4 then it should have been done correctly. If you are having problems, you should go back to the vendor (cs-cart in this case) and demand that they complete the work so that you have a site that behaves properly.

20

[quote name=‘tbirnseth’]If you had cs-cart upgrade you from whatever version to 2.1.4 then it should have been done correctly. If you are having problems, you should go back to the vendor (cs-cart in this case) and demand that they complete the work so that you have a site that behaves properly.[/quote]



Easier said than done. CS REALLY, I MEAN REALLY likes to point the finger over and over before they finally fix the problem. I’m not saying for sure that it is a CS issue this time but that is what they have done several times in the past. I’m assuming they did the upgrade properly.



The only other script that is running that is not CS related it leechprotect. I have read several articles online saying that can sometimes cause issues. But it’s odd that it isn’t causing issues with the old version.