5 sites suddenly running very slow

DogTags:

What is the other host? (the one that had much better performance)

Thanks :smile:
Yeah, they get upset when you start naming names around here.

I don't have any active sites at the other host running Zen-Cart yet. been putting off converting one, and am working on getting it done.

Sunday Night Server load is about half what it is during the week

Server Up Time: 19:25:23 up 72 days, 19:47, 0 users, load average: 9.17, 10.36, 10.57

We'll see what happens tomorrow at lunch time.

DogTags:

What is the other host? (the one that had much better performance)

Thanks :smile:
Sorry, that site is on a dedicated server, not shared hosting.

Stuff4Toys:

Sunday Night Server load is about half what it is during the week

Server Up Time: 19:25:23 up 72 days, 19:47, 0 users, load average: 9.17, 10.36, 10.57

We'll see what happens tomorrow at lunch time.
With or without banners enabled?

Did you end up deleting the banners table and recreating it? Then testing? Then adding records back in one-by-one? (either by manually recreating new banners, or by inserting SQL from backups, or testing both methods in case records were corrupted)

Stuff4Toys:

I did this and the query times dropped to 1 to 12 seconds almost immediately. Now two days later it is amazing, like someone turned on a switch I am seeing

Parse Time: 0.700 - Number of Queries: 794 - Query Time: 0.223894582977

Just like it aught to be. Monday I will start turning on the banners again, one at a time.
I'd be testing it now to see what effect it has when turning them on immediately after seeing super performance with them off.
I'm guessing it'll be fine. And then when it suddenly crawls to a halt tomorrow afternoon when the server gets more load, you can scream to the hosting company that it's a problem with the server's scaling for load.

DrByte:

With or without banners enabled?

Did you end up deleting the banners table and recreating it? Then testing? Then adding records back in one-by-one? (either by manually recreating new banners, or by inserting SQL from backups, or testing both methods in case records were corrupted)
Just banners deleted from the Layout Page and disabled in the Banner Manager

DrByte:

I'd be testing it now to see what effect it has when turning them on immediately after seeing super performance with them off.
I'm guessing it'll be fine. And then when it suddenly crawls to a halt tomorrow afternoon when the server gets more load, you can scream to the hosting company that it's a problem with the server's scaling for load.
All four other domains are still giving the banner related slow queries. I forgot about two other sites for clients on this server. they are showing up in the slow query logs too.

I think I will leave them off for a day and see what happens. I'd rather sacrifice the banner ads then all the business we are loosing from Sloooooow server.

Checked with the other hosting company and they will only give the slow query logs by special request, then only a few at a time. they must have to manually parse my logs out of the whole bunch.

NEW DAY NEW STORY FROM THE HOST

Today senior tech support said that the WHOLE problem is that the queries are looking for table rows that don't exist. Here his proof. He picked this query that was taking 1.5 seconds to return a response and said that because "unique row not found" that it sat there looking and looking for something in that table for 1.5 seconds.

I said BS, there should be no entry for item number 2054 because there is NO meta tag description for that item, nor any of the other 5600+ items in the database. I take that back, there are 4 items in the database that have custom metatag descriptions. does that mean that those 4 items should load quicker?

EXPLAIN SELECT pd.products_name, p.products_model, p.products_price_sorter, p.products_tax_class_id, p.metatags_title_status, p.metatags_products_name_status, p.metatags_model_status, p.products_id, p.metatags_price_status, p.metatags_title_tagline_status, pd.products_description, p.product_is_free, p.product_is_call, mtpd.metatags_title, mtpd.metatags_keywords, mtpd.metatags_description FROM ( zen_products p, zen_products_description pd
)
LEFT JOIN zen_meta_tags_products_description mtpd ON mtpd.products_id = p.products_id AND mtpd.language_id = '1'
WHERE p.products_id = '2054'
AND p.products_id = pd.products_id
AND pd.language_id = '1'
[ Edit ] [ Skip Explain SQL ] [ Create PHP Code ]

• Options
  id select_type table type possible_keys key key_len ref rows Extra
  1 SIMPLE p const PRIMARY PRIMARY 4 const 1
  1 SIMPLE pd const PRIMARY PRIMARY 8 const,const 1
  1 SIMPLE mtpd const PRIMARY PRIMARY 8 const,const 0 unique row not foundGRRRRRRRRRR, I am getting crabby.

Just in case you are watching Senior tech, I just loaded one of those items with a meta tag description and it took 95 seconds.

I just tried another product_id and got this: This query only took 1.1 Seconds to query.

EXPLAIN SELECT p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id, p.products_quantity, p.products_weight, p.products_priced_by_attribute, p.product_is_free, p.products_qty_box_status, p.products_quantity_order_max, p.products_discount_type, p.products_discount_type_from, p.products_sort_order, p.products_price_sorter
FROM zen_products p, zen_products_description pd
WHERE p.products_status = '1'
AND p.products_id = '6946'
AND pd.products_id = p.products_id
AND pd.language_id = '1' [ Edit ] [ Skip Explain SQL ] [ Create PHP Code ]

• Options
  id select_type table type possible_keys key key_len ref rows Extra
  1 SIMPLE p const PRIMARY,idx_products_status_zen PRIMARY 4 const 1
  1 SIMPLE pd const PRIMARY PRIMARY 8 const,const 1

No "unique row not found" in that item but still a slow query.

Senior Tech also said it cannot be our fault or all of the "800" other clients on this server would be complaining.

He said I am the only one having problems.

In phpMyAdmin try this:

• find your products table
• click on the Structure icon
• scroll down to the indexes at the bottom
• find the idx_products_status_zen, and click on the Edit (pencil) icon
• in the first dropdown, change it to products_id, and the second pulldown to products_status
• save
• test

THIS MORNING ON MY BUSIEST SITE, WITH DOING nothing ON MY PART i GET:
Parse Time: 0.697 - Number of Queries: 794 - Query Time: 0.252222367081

GRRRRRRR

Look in the Tools ... Server Info ...

Does it show when the Server Up Time: for your server? Was it recently restarted?

Last Restart 74 Days Ago

DrByte:

In phpMyAdmin try this:

• find your products table
• click on the Structure icon
• scroll down to the indexes at the bottom
• find the idx_products_status_zen, and click on the Edit (pencil) icon
• in the first dropdown, change it to products_id, and the second pulldown to products_status
• save
• test
  Done ..... Testing

Oddly enough the sites have been serving up quite nicely today. Query times in the .3 to 1.5 range, an occasional 7. Certainly acceptable if it holds up.

It'll be interesting to see if the host coughs up any info about what they've done.

**ONE FINE DAY 24 hours of great access times and WHAMO!!!!! ** We actually had some customer come and spend money yesterday.

They rebooted the server last night and right back to 30 to 70 second query times. Looks like changing the indexes on the products table didn't help much.