Death by 10,000 queries

Hello Fellow Zenners,
This is my second post in this forum so pardon if it's in the wrong place.

I am running Zen-Cart hosted by FutureQuest with a large number of products (829 to be exact) and a whole bunch of attributes (various product types, like shoes, shirts, decor, etc, etc). I've also installed Stock by Attributes and a few other add-ons such as the improved attribute editor, as well as some nice fluffy stuff like Lightbox.

I am seeing extremely bad performance. The worst case I have seen is a shopping cart with ~20 items in it, most of them with 2 or 3 attributes, and PHP actually kills the process after 30 seconds. To investigate this I am storing # of queries in the cookie and it turns out that the page is actually doing 13528 queries before dying. Turns out there are actually only 389 unique queries out of that set, not sure about how many updates vs. selects.

Can someone please shed some light on what can be done here? What I can do? I am a software developer by trade and can put in some custom caching if it comes down to it however that could easily turn into a nightmare.

I could really use some help.

I'm using query caching (file-based), and recently did OPTIMIZE TABLE on all my tables, with little/no effect. mytop seems to think my indexes are okay, i.e. apparently not doing table scans, but I'm not a MySQL expert.

Regards,
Mike

There are some known inefficiencies with duplicate queries and related procedural code in the current version of Zen Cart.

The v1.4 code currently in development has vastly improved the efficiencies around queries used to retrieve product data and process cart contents ... improving performance more than 400% in many cases.

In the meantime, as you suggested, improving caching will provide some relief, esp since, as you also mention, there are many duplicated queries. A well-tuned and not over-loaded MySQL server will be able to naturally cache most of those queries and serve data from cache instead of re-retrieving from database tables. Allocating significant RAM and setting the MySQL memory model to suit, and setting the my.cnf parameters to use the RAM efficiently for caching will often provide huge performance gains on the MySQL database server ... without having to do any coding.

There have been a couple posts in the last couple years by people who have implemented caching methods external to MySQL itself. While I don't particularly recommend the use of 3rd-party caching tools, you're certainly free to do as you wish with your own site.

In the meantime, the v1.4 code continues to perform well on databases which were formerly bogged down using v1.3 code.

If you care to PM me with a link to a copy of your database to see how well it runs in the current v1.4, I'll give you back some query counts and execution time results from a few tests ... although it sounds like you're using some contributions that may cause the test results to only be approximated.

Sorry to poke my nose in but I have similar issues and have a script running on our dedicated server that shows all of the slow queries.

The worst offenders are 2 large Zen Cart shops that have attributes defined and over 1000 products in each.

Any tips to speed this up would be great as Mysql has died a few times recently as processes spiral out of control. I have followed all of the advice in the faq section but need more.

I'd be willing to use a 3rd party program to bring the server load down if you could mention a few names. I won't take them as being recommendations but I may be able to find a solution that works well and suits my needs.

Thanks

Dr:

Im one of those sites with the "Fat DB from hell" situations where I have to disable many/most "new product" type options, to keep the site alive and working.

If you'd like to use my kit to play with, Ajeh might have some feedback on how large mine -can- get depending on how were spinning things.

I think..in the end for then store in development, were going to end up with at least..32,000 categories, and at least 45,000 products.

Which is just under 10% of what we used to have in earlier spins of "how to display auto parts where -1- item is used on 350 different vehicle combinations and each description has to be specific".

I drink lots of coffee in the admin catalog, waiting.

Cannot wait for 1.4 :)

Hi DrByte,
Thanks for your quick reply. Apologies for not jumping back on this thread sooner.

Do you by any chance have a link to any of the posts where people are using caching external to MySQL? I don't think upgrading to 1.4 is an option at this point due to the level of customization I have done on Zen-Cart (mostly small stuff but it makes me nervous in the first place, plus it is still dev code).

Also, could you suggest MySQL optimizations which should improve my performance or cache hits? I will have to talk to my host about this.

Finally, are you familiar with FutureQuest and know the relative performance of their shared hosting databases?

Regards,
Mike

mpercy:

Do you by any chance have a link to any of the posts where people are using caching external to MySQL?No, sorry ... haven't done a search on the forum for that yet.

mpercy:

I don't think upgrading to 1.4 is an option at this point due to the level of customization I have done on Zen-Cart (mostly small stuff but it makes me nervous in the first place, plus it is still dev code).I wasn't offering to upgrade your site. I was offering to load a copy of your database on my dev area and give you back some benchmark performance differences between v1.3 and 1.4 using your data.

mpercy:

Also, could you suggest MySQL optimizations which should improve my performance or cache hits? I will have to talk to my host about this.I just did in my earlier post, above.

mpercy:

Finally, are you familiar with FutureQuest and know the relative performance of their shared hosting databases?Sorry, no.

The categories menu is one of the worst offenders in terms of efficiency. Runs one query to get the top-level products, then an additional query for each set of subcategories at each level. Then runs yet another query to find out if categories have subcats (huh?). And then the coup de grâce: the hundreds of individual queries it runs to get the sums for the category product counts.

Disabling category counts will help. Even better would be to replace the stock category functions with more efficient versions (which will hopefully appear in 1.39 or 1.4). It should be possible (and fairly simple) to get all the necessary information to build the category menu from parsing the recordset from just one query that retrieves all the categories at once (and, if you want product counts, one additional query for all products-to-categories).

The **CSS Flyout menu** mod and **Expanded Category List** mod both make use of this more efficient design, and installing and using one of them should reduce the number of queries your store is making per page (perhaps dramatically if you have lots of products and categories).

Expanded Category List does, however, still use the inefficient zen stock method of counting category products, so that should be disabled by changing line 67 in category_list.php from this:

$box_categories_array[$icount]['count'] = zen_count_products_in_category($category_id) ;

to this:

$box_categories_array[$icount]['count'] = 0;

To use Expanded Category List without showing the individual products, just comment out line 81:

$this->build_products($category_id, $category_link,$level);

Ya..I have no problems in the store itself, as I have little or no use for any category sidebox.

The admin category function is the icepick under my fingernails. Its about 3min/page until I get a few levels down and it gets quicker with fewer recursive(?) lookups to perform.

I have a make/model/etc selector that I will be using in my header once Zen Cart Cons ulting is complete with it, a demo of the selector can be seen here:

http://store.speedtoys.com/~dswarzman/

Just tried this MagneticOne Performance mod (which I can't seem to find mentioned anywhere in the forums, though it seems to be sold by various outfits):

magneticone.com/store/Zen.Cart.Modules/Zen.Cart.Performance.module

I was pretty skeptical, but for the price, it was worth a try. Seems to actually do what it says. Homepage went from 444 queries to 73. Query time dropped from .32 seconds to .07, and page rendering time dropped commensurately. On larger stores or larger pages, increases could probably be dramatically better.

What it does: caches "chunks" of the HTML code for whatever cache duration you specify (I've got it at 15 minutes) so that ZenCart doesn't need to regenerate that code on every page load. Side effect: feature sections like "New Products" on homepage do not change on every page load, only every time cache is regenerated (or when cache is manually emptied). A small price to pay for dramatically faster page loads.

Note also that it's Zend encoded, so you can't look at the or tweak the code, and it appears (haven't verified this) to lock itself onto a single domain, so you can't use it on both a development and production server. I was also disturbed to find it trying to send emails, presumably to the vendor, at one point.

Nonetheless, worth investigating.

Hi Guys,

I have a question about how a huge amount of crosslinked categories might be affecting my Google Indexing, not just my performance (which has also slowed down).

As the topic is related but different, I created a new thread here: http://www.zen-cart.com/forum/showthread.php?p=507300#post507300

I hope you don't mind that I posted the link on this thread... this is the best thread I could find on category structure and am hoping you experts might be able to help.

Best wishes,

Todd