Serious performance issue - over 100K queries

[OopsIbrickedIT]

Clarification to previous post: The developers site is down.

[lankeeyankee]

You haven't mentioned how the site is hosted. Shared, VPN, dedicated? With that many products residing in so few categories there are going to be performance hits, there are so many queries because of so many products. It's pretty inefficient to have thousands of products in a single category. There are some tweaks you can make to MySQL and I would definitely look into having the DB on it's own SSD.

[OopsIbrickedIT]

Hi,

Hosting is sort of shared with guaranteed 2 cores minimum, based on my tests it's normally like 3-4 cores off peak times. Testing environment is VPS with 1 core and much more sluggish even though mysql 5.7 is on ssd with custom addons from hosting provider so this is not mainly a hardware issue. Also it runs php 7.01. Dedi 16 cores would possibly still be too slow!

Perhaps I have been unclear. Problem is that the categories are really splitted to tens of subcategories under main categories but when clicking main category it takes ages to load that. I have dropped the category load time to average of 10 seconds now but it is still not enough.

First thing when I started working with this I ordered a dedi mysql server on the same server room but hosting company has messed something up, support is terrible and I still don't have script access to that beast

So I think there is a way to make things load snappier besides huge hardware: Improve the way zen cart & template handles the category structure loading. Why go to bottom of the structure? Just show the next category and load only step by step.

Here is a screenshot of one category so you can see it is pretty much splitted and they do load fast. The main directory tree on the left side does not and that needs to be improved. Now I have said it dozens of times&ways and will not repeat myself anymore.


I have seen other shopping cart system with roughly same cpu-power and more products and they were actually pretty snappy loading. So if they have managed to do that it is possible. I will not promote and don't want to make transfer to that sytem unless it is a absolutely must. This is pretty crucial, now 55K products and sluggish in few categories.I have noticed that google is not exited about that too. It doesn't index them because they are too slow.

[shrimp-gumbo-mmmhhh]

ZC v1.5.0 PHP 5.6

I have added about 20000 products in category structure Main page-->subcategory1-->subcat2 8000 products
-->subcat3 5000 products
-->subcat4 7000 products

Now my category load time is 35 seconds at best. We are on the works to upgrade to 1.5.5f but it is still unfinished and our customers can't access to these new categories because of awfull page load times. I have done all the basic optimization tricks but they have had little help (-3s page load time)

There is the query cache plugin but it is designed for 1.3.9. Has anyone experiences has it helped on 1.5.0? Or is this template related?
Categories with few products loads under 0.8 seconds

Below is the sad truth how slowest categories load:
Attachment 18435

To be honest, it seems that you need to spend the time to break the 5000+ product categories up into small categories/subcategories. This will not only speed up your store but will help you get found on the search engines. I would suggest looking at other car parts websites for ideas....

[RixStix]

Do your products have attributes?

If so, do the attributes affect price or are they a just a selection?

If just a selection not affecting pricing, look in the attribute controller and make sure the "fuschia/discounted" box is unselected for attributes not affecting pricing.

[OopsIbrickedIT]

Do your products have attributes?

If so, do the attributes affect price or are they a just a selection?

If just a selection not affecting pricing, look in the attribute controller and make sure the "fuschia/discounted" box is unselected for attributes not affecting pricing.

We have only a few products with attributes and they don't affect the price but thanks for the tip.

To be honest, it seems that you need to spend the time to break the 5000+ product categories up into small categories/subcategories. This will not only speed up your store but will help you get found on the search engines. I would suggest looking at other car parts websites for ideas....

I have done it and spent the time We do have a subcategories under product make then there is type and model, model has only tens of products and type only few. Issue is not on that, testing site loads fast.

Anyways I upgraded on testing server the database to mariadb 10.1 (or 10.3 can't remember) and ran the old template on php 7.3 with ZC 1.5.6 and the page was blazingly fast, took under 0.2s to load even on heaviest category. Unfortunately we outsourced the new template making and seems it takes ages to complete so I am still looking for ways to make old site faster.

When I upgraded on test server I got db errors from admin activity log(I deleted it) and something else which I could not take down because I accidentally pressed ignore and my copy paste had failed. This leaves me suspecting that the 20 years old db has some errors in it. Is there a db maintenantence tool for zen cart? This could explain slow query times.

@shrimp-gumbo-mmmhhh I agree 100% for what you said about search engine visibility, now google and bing simply don't even index those slow loading categories. I have managed to make site overall load time to drop heavily (frontpage & most category pages) and google likes it but problem is also 40 000 products on slow loading categories needs to be indexed too by google.

So because we outsourced the template development and it has taken over month overtime I am a bit stuck here because I am missing a good & fast template & hired a developer which seems to take long time to make this new template happen. This is why I can not upgrade live server because I am missing template I can not put php 7.1 on, upgrade zc or make anything else like that.

[lankeeyankee]

You can look in your mysql slow query logs to see which queries are taking longest to see if there is anything that can be addressed. Also, there are a number of things to boost mysql performance if you have access to it's configuration file, search engines have lots of info. There are several tools available to query the db and make suggestions based on the results, again easily found in search engines. You can also try repairing tables from phpmyadmin. Or go to tools>store settings>optimize database. Until you move to newer versions I can't think of anything else to do.

[OopsIbrickedIT]

Thank you all for your input.

You can look in your mysql slow query logs to see which queries are taking longest to see if there is anything that can be addressed. Also, there are a number of things to boost mysql performance if you have access to it's configuration file, search engines have lots of info. There are several tools available to query the db and make suggestions based on the results, again easily found in search engines. You can also try repairing tables from phpmyadmin. Or go to tools>store settings>optimize database. Until you move to newer versions I can't think of anything else to do.

I all ways optimize database once in a week and checked the tables for errors and none was found. So I think this is somehow template related. I tried to make a part of the page aka left sideboxes cached with php but it did not work as supposed. So i will just wait for the new template so we can upgrade everything.

[DrByte]

Is the problem with how the categories sidebox builds things? Navigate to that category (so you have the page URL in your browser), and then in your Admin turn that sidebox off, and then refresh the store page. How many queries? How does that compare for other pages (get them before turning the sidebox off too). Does using the top categories-tabs nav instead end up being a viable alternate, even for temporary research?

You said it works speedy on your testing server, but slow on your live server. THIS IS A CLUE.
Are the query counts the same in both places?
What are the differences between the sites? How are the configure.php files different? Have you copied the live server's database to the test server to be sure you have all the same settings in both places? ... or how exactly are all the configuration-table settings different between the servers?

There's no automated script to do it, and it should rarely be an issue, but rebuilding the indexes on your database tables might be an option ... thinking here of just the tables starting with product* and categories* ... you might want to look at the mysql_zencart.sql file in zc_install to see what indexes "should" be present on each of those tables, and make sure they are in fact present on the server that's running slowly.

You didn't mention the results of looking at slow-query-logs generated by MySQL on the server.

You also mentioned that v1.5.6 and PHP 7 were lightning fast. There have been a lot of optimizations in Zen Cart since your old v1.5.0 (from 2011). Even in v1.5.2 we switched to mysqli and in the process optimized a bunch of query handling. Lots of other tweaks over the years.