Magento Forum

   
Suspended on Bluehost: MySQL / server load
 
websitebob
Member
 
Total Posts:  33
Joined:  2008-04-16
 

I installed Magento using Simple Scripts on BlueHost, which has always been a good host for my clients. This is my second Magento site.

This second site was, however, suspended for excessive server load due to MySQL usage. I’m wondering if there’s anything I can do to reduce the load, so I don’t have to find another host...though I suspect this will be an issue with most other services.

The site operates on the current Magento (1.1.8) on PHP5. Its not a high traffic site - 50-100 page views a day. Occassionally, a viewer will dig deep into the site which offers nearly 250 products and boost the page views. But, even this type of traffic and the occasional Googlebot should not be placing that much of a load on servers. Also, I am not using URL rewrite or any features, except I recently employed the new Google feature....which could be checking back and forth on products listed with Google.

Any suggestions?
Thanks in advance. Websitebob

_________________________

Here’s the message from BlueHost:

Server load has been high in part from excessive MySQL usage by this account. This account has examined 3,968,415,951,117,046,784 rows (496,051,993,889,630,848/day), taking 101 CPU seconds (12/day). Anything above about 5 billion rows or 70,000 CPU seconds per day is considered excessive for a shared hosting environment.

Please reduce mysql usage or find a new host. Source data from which above calculations were based… User CPU_time Rows_examined domain_mag01 101.272695 3968415951117046958 Most referenced databases today in ~/tmp/mysql_slow_queries…

The top 10 busiest tables on this account are as follows…
313039 domain_mag01.catalog_product_entity
111217 domain_mag01.core_url_rewrite
91715 domain_mag01.catalog_category_entity_varchar
87056 domain_mag01.catalog_product_entity_text
63227 domain_mag01.catalog_product_entity_varchar
35986 domain_mag01.catalog_category_entity
30384 domain_mag01.catalog_category_entity_int
22400 domain_mag01.catalog_product_entity_int
20260 domain_mag01.catalogindex_minimal_price
19999 domain_mag01.catalog_product_website

 
Magento Community Magento Community
Magento Community
Magento Community
 
websitebob
Member
 
Total Posts:  33
Joined:  2008-04-16
 

I deleted products entered with Google Base in the Magento Admin panel and also at Google Base, but my CPanel still shows the server load in the red (9.48 CPU). There are 250 products on this site. Also using a Spanish language pack, which may be behind the URL rewrite.

I have a second client on BlueHost with Magento with 40 products and their server load is in the green (2.7 CPU).

Again- Visitor traffic is not huge, but I’m open to suggestions on configurations or hosting.

Thanks in advance.

websitebob

 
Magento Community Magento Community
Magento Community
Magento Community
 
websitebob
Member
 
Total Posts:  33
Joined:  2008-04-16
 

Go figure… at 90 page visits so far today, the server load is in the green at 3.6 CPU.... So, I’m still monitoring.

 
Magento Community Magento Community
Magento Community
Magento Community
 
turbo1
Sr. Member
 
Avatar
Total Posts:  296
Joined:  2008-08-19
Los Angeles, CA
 

@websitebob

That seems really high - my site gets about 20,000 hits a day and my SQL usage is normally .43 - never more than 2.  It sounds like they either have too many users on one server, or not enough memory/cpu.  Magento IS resource intensive, but that seems a little crazy if you ask me!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Michael
Enthusiast
 
Total Posts:  826
Joined:  2007-08-31
 
websitebob -

Server load has been high in part from excessive MySQL usage by this account. This account has examined 3,968,415,951,117,046,784 rows (496,051,993,889,630,848/day)

I cannot even imagine how to generate these crazy numbers.

websitebob -

Most referenced databases today in ~/tmp/mysql_slow_queries…

Can you try to get more details on slow queries from your host support ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
websitebob
Member
 
Total Posts:  33
Joined:  2008-04-16
 

Thanks for the replies. The comparison on traffic also helps.

I will ask for additional data, though a week has now passed with the holidays, etc. 

In the meantime, the only difference I can think of was removing Google Base...I was testing a large number of products on Google Base, though I wasn’t getting any additional impressions.

Thanks again. Will see what we can get.

websitebob

 
Magento Community Magento Community
Magento Community
Magento Community
 
JLHC
Mentor
 
Avatar
Total Posts:  1287
Joined:  2008-05-09
Tampa, FL
 

Yes you should try removing Google Base if you are testing large number of products on it.
Be sure to update us on this. wink

 
Magento Community Magento Community
Magento Community
Magento Community
 
websitebob
Member
 
Total Posts:  33
Joined:  2008-04-16
 

Thanks for the feedback.

I have experienced no further problems. I did completely uninstall Google Base.  I am not saying it was the problem, but noting that I was testing a large number at one time.

I still haven’t found where BlueHost referenced those insane numbers. I accessed the mysql_slow_queries log (In the CPanel its located in the TMP folder under File Manager). There were only 4 dates with minor instances- one of was when I backed up the website and another may have been while building the site.

I’ve asked BlueHost for the cpu_exceeded log as it was not present and could not be reviewed.

I’ll pass that on if I find anything. Thanks again.

websitebob

 
Magento Community Magento Community
Magento Community
Magento Community
 
JLHC
Mentor
 
Avatar
Total Posts:  1287
Joined:  2008-05-09
Tampa, FL
 

Glad that it is working fine now. Do keep us updated. wink

 
Magento Community Magento Community
Magento Community
Magento Community
 
Thomas Asher
Jr. Member
 
Total Posts:  1
Joined:  2008-09-10
 

hi, I am having a very similar problem on my bluehost server.  The site i have is quite small and thus has very limited traffic.  In fact I did a complete re stall because of this issue.  Not much coming from bluehost or magento.  Both have basically said, solve this your self.  I know bluehost is a shared server but for the limited traffic I have this should not be a problem.  Here is the error message I get, this has led to my account being suspended.  This happens whenever adding a product to my cart..

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 7680 bytes) in /home/thenigh3/public_html/shop/app/code/core/Mage/Directory/Model/Country.php on line 66

Any help on this would be great.  I enjoy the e-commerce side of things and the simplicity of magento, but is it just to buggy to work with?

Thankyou

 
Magento Community Magento Community
Magento Community
Magento Community
 
Crucial Web Host
Guru
 
Avatar
Total Posts:  364
Joined:  2007-11-08
Phoenix, AZ
 

Hi Thomas,

Thomas Asher - 02 January 2009 06:42 AM

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 7680 bytes) in /home/thenigh3/public_html/shop/app/code/core/Mage/Directory/Model/Country.php on line 66

This is a memory allocation error and simply means that your server has run out of memory and is unable to allocate the necessary memory for your request.

It’s unfortunate that Magento often gets blamed for these issues, when server configuration is the real issue much of the time.  I think it’s safe to say that not all shared hosting is going to work for Magento.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Johno_d
Jr. Member
 
Total Posts:  9
Joined:  2008-10-01
 

I’m actually having similar problems.
It looks like it happens a few times a month and it’s always at 6am or 6pm… and runs for another few hours. Hundreds of millions of requests… (not trillions like above but still a lot) would be made by magento within these few hours; and some request actually take more than 15 seconds… It’s been happening since I put Magento on the server. 

Here’s the emails from my host manager:


###############
I woke this morning to very high server load created by mysql. Did a mysql process check and found it is in relation to ###
Appears the SELECT query isn’t terminating and running for hours, also it is creating multiples so many processes are running over the top of each other. Probably someone hacking a search?
I have just ‘Killed’ all of these processes and server load back to normal. Could you please look into this?
###############

###############
It’s just started doing the same again, appears there is a definite gap between the Query’s, around 300>400 seconds almost like it is a cron job? See attached.
###############

###############
It’s started up again. I have noticed that it runs at about 7>8am & 7>8pm (every 12 hours). Can you find anything in the scripts perhaps? Just trying to give you as much information as i can to help.
###############

###############
Just letting you know #db_user# started at around 6 > 6.05pm today. I will keep an eye on the process but i have deleted the processes for now.
###############

At some point the processes were slowing down the server so much that the response times went up to 1000ms…
The shop is not even live yet and is not getting any visitors at all.
It has maybe 20 products at the moment.

The server is really descent as well, so it’s not because of that. I’m running a lot of other websites with the zend framework and it works perfectly well.

Does anybody know about this?
Thanks in advance!

Here’s 1 of the SQL commands it was running:

*********************************
SELECT `e` . * , `_table_website_id`.`website_id` , IFNULL_table_news_from_date.value_table_news_from_date_default.value ) AS `news_from_date` , IFNULL_table_news_to_date.value_table_news_to_date_default.value ) AS `news_to_date` , IFNULL_table_name.value_table_name_default.value ) AS `name` , IFNULL_table_short_description.value_table_short_description_default.value ) AS `short_description` , IFNULL_table_description.value_table_description_default.value ) AS `description` , `_table_price`.`value` AS `price` , IFNULL_table_thumbnail.value_table_thumbnail_default.value ) AS `thumbnail` , `_table_special_price`.`value` AS `special_price` , IFNULL_table_special_from_date.value_table_special_from_date_default.value ) AS `special_from_date` , IFNULL_table_special_to_date.value_table_special_to_date_default.value ) AS `special_to_date` , IFNULL_table_status.value_table_status_default.value ) AS `status` , IFNULL_table_visibility.value_table_visibility_default.value ) AS `visibility`
FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_website` AS `_table_website_idON _table_website_id.product_id e.entity_id )
AND (
_table_website_id.website_id =1
)
INNER JOIN `catalog_product_entity_datetime` AS `_table_news_from_date_defaultON _table_news_from_date_default.entity_id e.entity_id )
AND (
_table_news_from_date_default.attribute_id '66'
)
AND 
_table_news_from_date_default.store_id =0
LEFT JOIN 
`catalog_product_entity_datetime` AS `_table_news_from_dateON _table_news_from_date.entity_id e.entity_id )
AND (
_table_news_from_date.attribute_id '66'
)
AND (
_table_news_from_date.store_id =1
)
LEFT JOIN `catalog_product_entity_datetime` AS `_table_news_to_date_defaultON _table_news_to_date_default.entity_id e.entity_id )
AND (
_table_news_to_date_default.attribute_id '67'
)
AND 
_table_news_to_date_default.store_id =0
LEFT JOIN 
`catalog_product_entity_datetime` AS `_table_news_to_dateON _table_news_to_date.entity_id e.entity_id )
AND (
_table_news_to_date.attribute_id '67'
)
AND (
_table_news_to_date.store_id =1
)
INNER JOIN `catalog_product_entity_varchar` AS `_table_name_defaultON _table_name_default.entity_id e.entity_id )
AND (
_table_name_default.attribute_id '45'
)
AND 
_table_name_default.store_id =0
LEFT JOIN 
`catalog_product_entity_varchar` AS `_table_nameON _table_name.entity_id e.entity_id )
AND (
_table_name.attribute_id '45'
)
AND (
_table_name.store_id =1
)
INNER JOIN `catalog_product_entity_text` AS `_table_short_description_defaultON _table_short_description_default.entity_id e.entity_id )
AND (
_table_short_description_default.attribute_id '47'
)
AND 
_table_short_description_default.store_id =0
LEFT JOIN 
`catalog_product_entity_text` AS `_table_short_descriptionON _table_short_description.entity_id e.entity_id )
AND (
_table_short_description.attribute_id '47'
)
AND (
_table_short_description.store_id =1
)
INNER JOIN `catalog_product_entity_text` AS `_table_description_defaultON _table_description_default.entity_id e.entity_id )
AND (
_table_description_default.attribute_id '46'
)
AND 
_table_description_default.store_id =0
LEFT JOIN 
`catalog_product_entity_text` AS `_table_descriptionON _table_description.entity_id e.entity_id )
AND (
_table_description.attribute_id '46'
)
AND (
_table_description.store_id =1
)
INNER JOIN `catalog_product_entity_decimal` AS `_table_priceON _table_price.entity_id e.entity_id )
AND (
_table_price.attribute_id '49'
)
AND (
_table_price.store_id =0
)
INNER JOIN `catalog_product_entity_varchar` AS `_table_thumbnail_defaultON _table_thumbnail_default.entity_id e.entity_id )
AND (
_table_thumbnail_default.attribute_id '61'
)
AND 
_table_thumbnail_default.store_id =0
LEFT JOIN 
`catalog_product_entity_varchar` AS `_table_thumbnailON _table_thumbnail.entity_id e.entity_id )
AND (
_table_thumbnail.attribute_id '61'
)
AND (
_table_thumbnail.store_id =1
)
LEFT JOIN `catalog_product_entity_decimal` AS `_table_special_priceON _table_special_price.entity_id e.entity_id )
AND (
_table_special_price.attribute_id '50'
)
AND (
_table_special_price.store_id =0
)
LEFT JOIN `catalog_product_entity_datetime` AS `_table_special_from_date_defaultON _table_special_from_date_default.entity_id e.entity_id )
AND (
_table_special_from_date_default.attribute_id '51'
)
AND 
_table_special_from_date_default.store_id =0
LEFT JOIN 
`catalog_product_entity_datetime` AS `_table_special_from_dateON _table_special_from_date.entity_id e.entity_id )
AND (
_table_special_from_date.attribute_id '51'
)
AND (
_table_special_from_date.store_id =1
)
LEFT JOIN `catalog_product_entity_datetime` AS `_table_special_to_date_defaultON _table_special_to_date_default.entity_id e.entity_id )
AND (
_table_special_to_date_default.attribute_id '52'
)
AND 
_table_special_to_date_default.store_id =0
LEFT JOIN 
`catalog_product_entity_datetime` AS `_table_special_to_dateON _table_special_to_date.entity_id e.entity_id )
AND (
_table_special_to_date.attribute_id '52'
)
AND (
_table_special_to_date.store_id =1
)
INNER JOIN `catalog_product_entity_int` AS `_table_status_defaultON _table_status_default.entity_id e.entity_id )
AND (
_table_status_default.attribute_id '69'
)
AND 
_table_status_default.store_id =0
LEFT JOIN 
`catalog_product_entity_int` AS `_table_statusON _table_status.entity_id e.entity_id )
AND (
_table_status.attribute_id '69'
)
AND (
_table_status.store_id =1
)
INNER JOIN `catalog_product_entity_int` AS `_table_visibility_defaultON _table_visibility_default.entity_id e.entity_id )
AND (
_table_visibility_default.attribute_id '74'
)
AND 
_table_visibility_default.store_id =0
LEFT JOIN 
`catalog_product_entity_int` AS `_table_visibilityON _table_visibility.entity_id e.entity_id )
AND (
_table_visibility.attribute_id '74'
)
AND (
_table_visibility.store_id =1
)
WHERE (
e.entity_type_id '4'
)
AND (
IFNULL_table_news_from_date.value_table_news_from_date_default.value ) <= '2008-10-24'
)
AND (
(
IFNULL_table_news_to_date.value_table_news_to_date_default.value ) >= '2008-10-24'
)
OR (
_table_news_to_date.value IS NULL
)
)
AND (
IFNULL_table_status.value_table_status_default.value )
IN )
)
AND (
IFNULL_table_visibility.value_table_visibility_default.value )
IN 2)
)
ORDER BY `_table_news_from_date`.`valueDESC
*********************************

 
Magento Community Magento Community
Magento Community
Magento Community
 
Johno_d
Jr. Member
 
Total Posts:  9
Joined:  2008-10-01
 

Up. Does anybody know about his?

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top