shop causing excessive server load

[roadkill]

I am running my shop on an -allmost- dedicated server (2003 iis, 3 ghz, 0,99GB ram) with Mysql 5

 

Since the shop was moved to this server we have had a pain with MySQL 5 and queries containing lef join. After fixing those problems the shop seemed to be running smoothly until recently.

 

MySQL_nt.exe, php.exe and sqlserver.exe cause large CPU load/peaks, up to 100%.

 

I started monitoring the MySQL queries and noticed multiple queries exceeding 1 second querytime for example this one:

 

select p.products_image, pd.products_name, p.products_date_added, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '4' and p2c.categories_id = '47' order by pd.products_name limit 168, 12

 

Does someone have an insight in this? Could it be compatibility problems between mysql 4 and mysql 5 ?

 

Downgrading to MySQL 4 is our last option, but we'd like to keep that option as a very last option.

 

Thanks for any help :thumbsup:

[dijol]

im the same dude, just moved a oscommerce site to my server and its killing it!

[Jan Zonjee]

Does someone have an insight in this? Could it be compatibility problems between mysql 4 and mysql 5 ?

Add indexes to improve perfomance (especially the one on specials is helping dramatically). These were distributed with RC2a:

alter table banners add index idx_banners_group (banners_group);
alter table banners_history add index idx_banners_history_banners_id (banners_id);
alter table currencies add index idx_currencies_code (code);
alter table customers add index idx_customers_email_address (customers_email_address);
alter table customers_basket add index idx_customers_basket_customers_id (customers_id);
alter table customers_basket_attributes add index idx_customers_basket_att_customers_id (customers_id);
alter table orders add index idx_orders_customers_id (customers_id);
alter table orders_products add index idx_orders_products_orders_id (orders_id);
alter table orders_products add index idx_orders_products_products_id (products_id);
alter table orders_status_history add index idx_orders_status_history_orders_id (orders_id);
alter table orders_products_attributes add index idx_orders_products_att_orders_id (orders_id);
alter table orders_products_download add index idx_orders_products_download_orders_id (orders_id);
alter table products add index idx_products_model (products_model);
alter table products_attributes add index idx_products_attributes_products_id (products_id);
alter table reviews add index idx_reviews_products_id (products_id);
alter table reviews add index idx_reviews_customers_id (customers_id);
alter table specials add index idx_specials_products_id (products_id);
alter table zones add index idx_zones_to_geo_zones_country_id (zone_country_id);

 

Thread on the topic: A Store Speed Optimization in Progress, Step by step from a vanilla install!

 

Output Queries Debug Contribution to see how many queries a page generates and how long it takes to execute them (find excessive amount of queries or slow ones).

 

Optimize tep_get_tax_rate() method

 

Optimize categories box

 

tep_get_category_tree optimization (only helpful on the admin side)

[roadkill]

Thank you for you tips/suggestions, Jan Zonjee.

Using those indexes on the tables really improved the performance and the improved tax class helped aswell!

[Guest]

just to let others know:

our server got killed by the cache-system. with nealry 10.000 items it produced about 9000 files in tha cache folder so our server got heavy load 2-3 24H a day. after deactivating the cache function it just dropped to 0.x and now all is fine. it just even got any slower even there where few more sql-queries on the run now.