Site Slow - Bluehost says SQL queries are too big?

[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.

[phlipper]

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'

[dbltoe]

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.

[CJPinder]

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.

[phlipper]

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!

[CJPinder]

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.

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?

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.

[Dan123]

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

[Dan123]

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.

[d1rage5]

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.