We run an OS Commerce powered web page on our own dedicated server. In April of last year, we were forced to upgrade the server. At around that point, we started to notice that there were seemingly random website outages where our page could not be viewed from any computer and the only solution for this at the time was to do a hard restart on our server. This happened for about a month. That problem has been solved, but now we are faced with a different challenge. Our website is slowing down seemingly at random. Sometimes a page loads very quickly and other times it takes up to 2 minutes to load. There does not appear to be a pattern for this that we can figure out. The website is www.westerndepot.com. We have tried looking into our website health with Yslow and similar, but all queries that we have run show that our website rates an "A". It appears to be the waiting times that are fluctuating rather than the actual loading times. A recent Firebug diagnostic showed 23.9 second wait time with a 24.17 second load time.
The server has 64GB of RAM and the current max RAM used shows at 10GB. We are using SME Server 9.0. The server is composed of dual WD Caviar Black 1TB drives mirrored using software Raid under SME 9 with 833 GB free space remaining. The products_to_categories table has 18375 entries for 13923 products where 2427 products are inactive. There are 385 total categories.
Our MySQL configuration file is as follows:
character-set-server=utf8
collation-server=utf8_general_ci
key_buffer_size=2048M
preload_buffer_size=512M
query_cache_limit=64M
query_cache_size=512M
query_cache_type=1
query_prealloc_size=512M
read_buffer_size=2M
read_rd_buffer_size=4M
sort_buffer_size=2M
thread_cache_size=300
join_buffer_size=256K
table_open_cache=512K
tmp_table_size=256M
max_heap_table_size=256M
slow_query_log=1
max_connections=500
concurrent_insert=2
log_output=FILE
The following is an example of a slow query log.
# Query_time: 12.277704 Lock_time: 0.000193 Rows_sent: 1 Rows_examined: 39652497
SET timestamp=1427835801;
select count(distinct p.products_id) as total from products p left join manufacturers
m using(manufacturers_id) left join (select * from (select products_id, specials_new_products_price
, expires_date, status from specials where status = 1 order by products_id, specials_new_products_price,
expires_date) as t group by products_id) as s on p.products_id = s.products_id left join
products_to_notes ptn on ptn.products_id = p.products_id join products_description
pd on p.products_id = pd.products_id join (select products_id from
products_to_categories where (categories_id not in (728)) group by products_id) as
p2c on p.products_id = p2c.products_id where p.products_status = '1';
# Query_time: 11.654512 Lock_time: 0.000367 Rows_sent: 240 Rows_examined: 36425129
SET timestamp=1427835812;
select distinct m.manufacturers_name, p.products_model, p.products_image,
p.image_folder, p.image_display, p.products_quantity, p.hide_qty_over,
p.products_bundle, IF(s.status, s.expires_date, NULL) as expires_date,
m.manufacturers_id, p.products_id, pd.products_name, p.products_msrp,
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,
p.sold_in_bundle_only, p.products_type , pd.extra_value_id6 , pd.extra_value_id10
from products p left join manufacturers m using(manufacturers_id) left join
(select * from (select products_id, specials_new_products_price, expires_date,
status from specials where status = 1 order by products_id,
specials_new_products_price, expires_date) as t group by products_id) as s on
p.products_id = s.products_id left join products_to_notes ptn on ptn.products_id =
p.products_id join products_description pd on p.products_id = pd.products_id join
(select products_id from products_to_categories where (categories_id not in (728))
group by products_id) as p2c on p.products_id = p2c.products_id where
p.products_status = '1' order by products_name limit 2160, 240;
All slow queries come from "advanced search".
The site uses multiple sales per product contribution.
Any thoughts? Did we miss something simple? Any help, directions, thoughts, or contacts would be greatly appreciated.
