2 days ago our site at https://www.kwmagic.com started running very slowly.

After reading through the forums, I have

Optimized the Database

Turned off catagory count

turned on GZIP

Installed the add-on 'query-cache'

now it seems slower than ever... Bluehost says this is my problem and I should look at the slow-queries tmp/log

I did, but other than telling me everything is slow there is not much else there.

Hoping that someone can guide me in the right direction

thank-you

Phil

A little more research and it looks like once again I am being hammered by Slurp and google bot

would this cause the slowdown ?

I don't know about the slow problem. But I've had bluehost said that my sql queries took a long time and also told me to look in the temp/log folder too. They even shut down my website until I fixed the problem. The problem was because a lot of the sql queries were slow. I didn't bother finding out what was the true cause that made these queries slow. Instead I just removed all the queries that I don't necessary need to use. For example, I removed the whos online query and some other search results queries. After removing those queries, I haven't had a problem for over 1.5 years now.

I would suggest that you go look at those logs and see which queries are taking a long time to process and see if possible to just code out those queries or modify them a bit so that it doesn't have to scan so many tables.

This is not a very good solution but it worked for me. So its up to you to decide what sort of time you want to put into hunting the problem.

Thanks... I have been looking at the logs

it seems all queries are taking too long, the website takes over a minute to load.

this just started 2 days ago.

something is trying to transfer from i3DOTytimgDOTcom and taking forever.:wacko:

Phil,

In Admin go to the Configuration menu, select Logging and then set 'Display Page Parse Time' to 'True'. This will show the number of SQL queries and the time taken at the bottom of each page. This will make it a bit easier to diagnose the problem.

The next thing to do is the create a new database and install a fresh copy of Zen Cart into a sub-directory on your site. If the fresh install of Zen Cart runs slow then it is Bluehost's issue.

Regards,
Christian.

For some reason, I'm not able to find my older post where I had problems with sql queries taking a long time. Otherwise, I would post a link to that post and you can see the queries that I've modified. MODS do certain posts get deleted or something?

In any case, when you say it takes a long time, what is the seconds in the sql query logs say? 2 seconds, 9 seconds, 30 seconds? What is the average you are seeing? Maybe data_digger can come in and help out.

I think I would investigate the i3DOT problem first. I don't find anything in your source that's calling the URL, but the system is sure trying to load something from there.

Thanks for the replies, I am still having a ton of issues, I am going to try the above ideas this morning (it looks like they are blaming the poblems on my two zen cart websites):

I got this from Bluehost this morning:

It looks like https://www.canadamagicsupply.com/index.php and possibly https://www.kwmagic.com/index.php is responsible for causing cpu throttling the last time it was throttled.
[6:31:14 AM] Enter a user to check cpu throttling for or Ctrl+C to quit

kwmagicc

Writing kwmagicc's domains to domains.txt...
Generating cpud.log...

Last 10 of 11 lines from cpud.log...
/var/log/cpud:Wed Jun 9 05:50:24 2010 1276084224.612 User in Jail uid=4093 progs=1,1 mysql=0,0 non-mysql=1,1 mysqljail=0 rss=15.67Meg
/var/log/cpud:Wed Jun 9 05:50:24 2010 1276084224.962 User in Jail uid=4093 progs=1,0 mysql=0,0 non-mysql=1,0 mysqljail=0 rss=15.67Meg
/var/log/cpud:Wed Jun 9 05:50:25 2010 1276084225.477 User in Jail uid=4093 progs=1,0 mysql=0,0 non-mysql=1,0 mysqljail=0 rss=15.67Meg
/var/log/cpud:Wed Jun 9 05:50:25 2010 1276084225.963 User in Jail uid=4093 progs=1,0 mysql=0,0 non-mysql=1,0 mysqljail=0 rss=15.67Meg
/var/log/cpud:Wed Jun 9 05:50:26 2010 1276084226.456 User in Jail uid=4093 progs=1,0 mysql=0,0 non-mysql=1,0 mysqljail=0 rss=15.67Meg
/var/log/cpud:Wed Jun 9 05:50:26 2010 1276084226.954 User in Jail uid=4093 progs=1,0 mysql=0,0 non-mysql=1,0 mysqljail=0 rss=15.67Meg
/var/log/cpud:Wed Jun 9 05:50:27 2010 1276084227.458 User in Jail uid=4093 progs=1,0 mysql=0,0 non-mysql=1,0 mysqljail=0 rss=15.67Meg
/var/log/cpud:Wed Jun 9 05:50:27 2010 1276084227.975 User in Jail uid=4093 progs=1,0 mysql=0,0 non-mysql=1,0 mysqljail=0 rss=15.67Meg
/var/log/cpud:Wed Jun 9 05:50:28 2010 1276084228.457 User in Jail uid=4093 progs=1,0 mysql=0,0 non-mysql=1,0 mysqljail=0 rss=15.67Meg
/var/log/cpud:Wed Jun 9 05:50:28 2010 1276084228.986 User in Jail uid=4093 progs=1,0 mysql=0,0 non-mysql=1,0 mysqljail=0 rss=15.67Meg

Enter a timestamp pattern to search apache logs for (eg. 09:..:..):

05:50:..

Generating raw_logs.txt for the given timestamp...

Results...
6 https://www.canadamagicsupply.com/index.php

4 https://www.kwmagic.com/index.php
1 https://www.canadamagicsupply.com/
1 canadamagicsupply.com/

This is the information now at the bottom of the page:

Parse Time: 25.228 - Number of Queries: 170 - Query Time: 24.9976063934

Parse Time: 74.483 - Number of Queries: 170 - Query Time: 78.6684466778

and not getting better....

Could it be my images are too big ? - should I shrink them ?

I have installed a robots.txt file, thinking maybe it was bots that were clogging things up.

This is my latest slow querie file:

I really do not know how to read this...

Wed Jun 9 09:12:12 2010

Query_time: 6.390055 Lock_time: 0.000095 Rows_sent: 0 Rows_examined: 0

use kwmagicc_zencart;
select banners_id, banners_title, banners_image, banners_html_text, banners_open_new_windows, banners_url
from banners
where status = 1 and ( banners_group = 'topbanner') order by rand()

Wed Jun 9 09:12:20 2010

Query_time: 4.248514 Lock_time: 0.027797 Rows_sent: 0 Rows_examined: 0

use kwmagicc_zencart;
select m.manufacturers_name
from products p, manufacturers m
where p.products_id = '4117'
and p.manufacturers_id = m.manufacturers_id

Wed Jun 9 09:12:24 2010

Query_time: 3.759164 Lock_time: 0.000090 Rows_sent: 64 Rows_examined: 326

use kwmagicc_zencart;
select p.products_id, p.products_model, p.products_price_sorter, pd.products_name, p.products_sort_order
from products p, products_description pd, products_to_categories ptc
where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id= '1' and p.products_id = ptc.products_id and ptc.categories_id = '16' order by pd.products_name

Wed Jun 9 09:12:31 2010

Query_time: 6.233896 Lock_time: 0.050158 Rows_sent: 0 Rows_examined: 0

use kwmagicc_zencart;
select record_company_id, record_company_name
from record_company
order by record_company_name

Wed Jun 9 09:12:38 2010

Query_time: 2.699557 Lock_time: 0.610182 Rows_sent: 0 Rows_examined: 0

use kwmagicc_zencart;
update whos_online
set customer_id = '0',
full_name = '¥Spider',
ip_address = '66.249.68.110',
time_last_click = '1276096353',
last_page_url = '/index.php?main_page=index&currency=AUD&products_id=3819&sort=3d&page=2',
host_address = 'crawl-66-249-68-110.googlebot.com',
user_agent = 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)'
where session_id = '' and ip_address='66.249.68.110'

Wed Jun 9 09:12:40 2010

Query_time: 1.931884 Lock_time: 0.013673 Rows_sent: 0 Rows_examined: 0

use kwmagicc_zencart;
select featured_id
from featured
where status = '0'
and (((featured_date_available <= now() and featured_date_available != '0001-01-01') and (expires_date >= now()))
or ((featured_date_available <= now() and featured_date_available != '0001-01-01') and (expires_date = '0001-01-01'))
or (featured_date_available = '0001-01-01' and expires_date >= now()))

Wed Jun 9 09:12:43 2010

Query_time: 3.250303 Lock_time: 0.001561 Rows_sent: 0 Rows_examined: 18

use kwmagicc_zencart;
select sale_id
from salemaker_sales
where sale_status = '0'
and (((sale_date_start <= now() and sale_date_start != '0001-01-01') and (sale_date_end >= now()))
or ((sale_date_start <= now() and sale_date_start != '0001-01-01') and (sale_date_end = '0001-01-01'))
or (sale_date_start = '0001-01-01' and sale_date_end >= now()))

Wed Jun 9 09:12:46 2010

Query_time: 2.789732 Lock_time: 0.000093 Rows_sent: 1 Rows_examined: 1

use kwmagicc_zencart;
select products_name
from products_description
where products_id = '3819'
and language_id = '1'

Am I the only one seeing the series of "Transferring from i#----.com" with the final hangup on "transferring from i3----.com"?

Your parsing seems to be problems with your YouTube inbeds.

I'd take them all off and see what happens. My guess is the parse time will drop dramatically.

Google i3.ytimg(DOT)com It could be that security settings are part of the problem as well.

phlipper:

Parse Time: 74.483 - Number of Queries: 170 - Query Time: 78.6684466778

and not getting better....

Could it be my images are too big ? - should I shrink them ?

I have installed a robots.txt file, thinking maybe it was bots that were clogging things up.

Well, 170 SQL queries is quite small for a Zen Cart site (they can often get into the 1000's) so there is no way the query time should be 78 seconds. It should be more like 0.07 seconds. The queries shown in your slow query log should not be slow. To me this shows that the MySQL server is taking a very long time to process queries. This could be because the SQL server is overloaded or there is high CPU load on the server being caused by something else.

If there is a high CPU load then I doubt it is your site causing it. Your site will be spending most of its time waiting for the MySQL server to respond.

There is a remote possibility your site has been hacked, causing it to do strange things. If you want to be sure that your site is okay then take a full copy of it with FTP and use Winmerge to compare it against a clean set of installation files. Make sure that any differences are changes you have made and nothing unusual.

Personally, I think Bluehost have a problem with the server and they are passing the blame back to you because they don't have a clue.

Unfortunately shared website hosting can come with a lot of problems. You might expect the Zen Cart certified hosts to be better but I've tried 3 of them and they were all abysmal. In the end I moved my store over to a VPS with a well regarded host and it has functioned fast and flawlessly since.

Regards,
Christian.

Thanks for the feedback

They sent me an email that it is fixed part of the problem - but the rest is mine

I just tried it on pages that do not have the youtube links and this is the response:

Parse Time: 111.097 - Number of Queries: 200 - Query Time: 110.649474192

They are still saying that the problem is on our end, we have not modified the site in over a month and this problem started one week ago.

I do have an error that they said is causing part of the problem:

PHP Warning: Variable passed to each() is not an array or object in /home2/kwmagicc/public_html/includes/classes/shopping_cart.php on line 1716

But i do not know how to fix this intermitent error.

If business was better - i know i should be shopping for a new host!

phlipper:

They are still saying that the problem is on our end, we have not modified the site in over a month and this problem started one week ago.

Unfortunately the easiest thing for the host to do is to blame the customer and some hosts take this route.

Ask them to move you to a different server. Then you can see if the problem moves with you or if it is their server that is fubar.

phlipper:

I do have an error that they said is causing part of the problem:

PHP Warning: Variable passed to each() is not an array or object in /home2/kwmagicc/public_html/includes/classes/shopping_cart.php on line 1716

But i do not know how to fix this intermitent error.

What version of Zen Cart are you using?

phlipper:

If business was better - i know i should be shopping for a new host!
You need to look at it the other way around, business won't improve until the site is running fast and reliably. If pages take more than 2 seconds to load you will start losing business. By the time you get up to 60+ seconds per page you won't get any business.

Regards,
Christian.

Hello all,
I started to have the same problem, my queries take so long, sometimes up to 90 seconds. here are some of them:

Query_time: 6.545161 Lock_time: 0.000090 Rows_sent: 29 Rows_examined: 29

SELECT * FROM klyb_m.sessions

Query_time: 21.913455 Lock_time: 0.000062 Rows_sent: 109548 Rows_examined: 109548

SELECT * FROM klyb_m.ssu_cache

Query_time: 10.274185 Lock_time: 3.052121 Rows_sent: 1 Rows_examined: 0

use klyb_m;
select count(*) as count
from whos_online
where session_id = '255a7e78d756f34b1b11a8967f2fa534' and ip_address='91.195.124.10'

Query_time: 4.778319 Lock_time: 0.000138 Rows_sent: 65 Rows_examined: 227

use klyb_m;
select p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p2c.products_id = s.products_id
where p.products_status = 1
and p.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '2'
and p2c.categories_id = '9'

and update sessions set expiry, which is so long that I just could not dare to post it here. Also my ssu_cache table is huge, more then 100,000 rows. and I don't really know how to clear it.
What can be done to optimize and clear zen cart database???
Thank you!!!!!!!!!!!!!!!!

forgot to add, I am also on Bluehost, and I noticed that my site gets ofthen throlled after I send(php method) newsletters to my subscribers.

Just saw this post. I am with hostmonster / bluehost (they are one in the same now)

Back in June they made a lot of upgrades and didnt tell anyone what they were changing and if it would affect any customers.

I had a lot of ftp items that would request data from secure ftp connection run through a cron job and you guessed it they said it was my fault and that i did something.

I told them in big and nasty gram what were they trying to hide? I am the only one that has access to my site and admin or mod updates and to look at the dates of all my scripts as they had not changed for two years and its my fault?

They then changed there story and said they made some improvments and security upgrades and it must be causing the issues but it was still on me to change all my code to support there changes.

This was around the same time you had problems.

I would ask them what changes and security changes they made during that same time and dont let them give you the BS story about they dont know. ASK so who is in charge of theeir CHANGE MANAGMENT? that you would like to speak with them to see what changes were approved and rolled out so you can find out what changes were made that affects your site and that why in the heck you were never told of the changes.....

It may or may not help you but to let you know they have in the past years just made changes without knowing they made changes then have to roll everything back just because they had (HOPE HAD IS THE RIGHT WORD) that would add in some updates that were never approved just to get them installed.