Catalog slowed to a crawl. MySQL?

[voldtaengler]

Ever since about two months ago, my catalog seems to running at crawling snail pace.

Haven't done any contribs since 2006-ish. Haven't changed anything in SQL in that time. Just all of a sudden started to crawl.

 

I've searched all across online and nothing has helped so far.

 

I am running Oscommerce 2.2 MS2 (from back in around 2005) and It's hosted on Godaddy.

 

The main website works and speeds are fine. It's anything catalog related that is bogged down. Even the admin page takes about 30-40 seconds to load.

 

I called godaddy to see if it is because im on a shared server and should switch to dedicated, but they said that i'm currently only on a server with 3 other people so it's almost like being on dedicated, and that they are experiancing no slow down times, which I then told them "Maybe it's because your directly connected to the servers?"

 

They said that may be a possibility, and the tech guy checked a non directly connected computer and duplicated my problem of the slowtime.

 

They said it may be something with the database SQL with oscommerce, but do not offer support with that, only hosting of it, not the cart itself, which is understandable.

 

So i've tried looking up some solutions which none work so far, and tested it on a server response time which gave me mind boggling 59 Secs.

 

Now that i'm pretty much out of possible solutions I offer my plea to anybody who is experianced with the matter, and any solution is grateful!

 

Also, If there is no solution to the matter, Would an upgrade to the newest cart help? Would that overwrite my database currently so I dont lose my 14,000+ customers? Orders and so forth?

 

Or can I do a fresh install of the newest cart, upload a backup of my current database and everything be ok?

Will it also make me lose my current rankings and whatnot in search engines as I'm currently number 1 for our keywords?

 

Sorry for all the questions. Even though i've had OSC for years, I'm still learning the ropes and back end of things along the way.

 

Thank you in advance for any possible solutions!!

 

-D

[Jan Zonjee]

Thank you in advance for any possible solutions!!

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)

[voldtaengler]

Jan,

Thank you very much. I will try everything you have suggested and see how it goes. Your help has been greatly appreciated! ;)

[Jan Zonjee]

I will try everything you have suggested and see how it goes.

Once I got your page loaded I notice you have a lot of categories. Try the categories box optimization first, I think that might be the biggest speed boost.

[♥FWR Media]

All great advice above as usual by Jan.

 

One thing that hasn't been mentioned ..

 

You seem to have had this site up a while .. have you ever analyzed > optimized the tables?

 

If not go into phpmyadmin and do so.

 

Backup your database always before running DB modifying operations:

 

1) select your database (you will see a long list of tables in the center with checkboxes on the left)

 

2) Scroll to the bottom and you will see (to the left by the bottom checkbox) an arrow pointing up next to Check All

 

Click check all and all the check boxes will fill with ticks.

 

Now look right a bit and you will see a dropdown menu (With Selected).

 

From the menu choose Analyze table ensure to wait until it completes.

 

3) go through the process of ticking the boxes again but this time from the dropdown choose Optimize table

[voldtaengler]

Alright, It seems to be currently running a bit faster now, still slower than normal but currently cut the time in half so making progress. thanks Jan. :^)

 

Robert, Have checked the tables the other day, a few warnings because of Duplicate like the gift voucher and specials thing, but unsure what to do with those issues. Too scared to do anything that's semi-foreign to my brain that will crash all my years work. :)

[♥FWR Media]

Robert, Have checked the tables the other day, a few warnings because of Duplicate like the gift voucher and specials thing

 

Do you mean you got errors in those tables when running an analyze or optimize routine? because if you do .. those issues will come back and bite you when you have 2! years work to lose.

[Dennisra]

The postings by Jan Zonjee are one of the best examples of truly helpful helpful postings I have seen in a long time. There is a method to a helpful post and this is the model.

[♥FWR Media]

The postings by Jan Zonjee are one of the best examples of truly helpful helpful postings I have seen in a long time. There is a method to a helpful post and this is the model.

 

Agree Dennisra, Jan does the vast majority of moderation here by himself and he gets little praise.