Query Cache v1.0

[marcopolo]

Without:
Parse Time: 9.906 - Number of Queries: 36662 - Query Time: 7.7387167594452
Parse Time: 9.690 - Number of Queries: 36662 - Query Time: 7.5770455304413
Parse Time: 9.829 - Number of Queries: 36662 - Query Time: 6.9318020951538

After v1.0

Parse Time: 5.313 - Number of Queries: 658 - Query Time: 3.5452961007996
Parse Time: 5.562 - Number of Queries: 658 - Query Time: 3.3493781889954
Parse Time: 5.797 - Number of Queries: 658 - Query Time: 3.5792606858063


After v1.5

Parse Time: 8.799 - Number of Queries: 624 - Query Time: 6.569669360321
Parse Time: 8.540 - Number of Queries: 624 - Query Time: 6.6397249739532
Parse Time: 9.801 - Number of Queries: 625 - Query Time: 7.7828902718048

These are great results, just one question. v1.0 gave me a better Parse time....how come?

[data_digger]

Your web server is probably quite slow (or overloaded), aggresive caching takes more time then executing query on database server.
Try to set QC_USE_PRODUCT_QUERY_DETECTION to FALSE.
It's in includes/extra_configures/query_cache.php
Change:

define('QC_USE_PRODUCT_QUERY_DETECTION',TRUE);

to

define('QC_USE_PRODUCT_QUERY_DETECTION',FALSE);

You should get now better results then with v1.0.

Please report back if it helped.

[marcopolo]

Yes that helped, new results are below...Queries went up slightly but the Parse Time is better. What is that setting do exactly?

Parse Time: 4.755 - Number of Queries: 724 - Query Time: 3.0285431182251

I had allot of issues when I first started using Zen Cart
see post: http://www.zen-cart.com/forum/showthread.php?t=105824

My parse and queries were crazy in the beginning (when I first installed Zen Cart) my server should be fast...I think! I have reduced my parse time considerably by experimenting with the. MY.CNF file. I ended up with he below, you seem to know this stuff very well, does it seem right to you?


[mysqld]
skip-innodb
skip-locking
max_connections=40
set-variable = innodb_buffer_pool_size=7M
set-variable = innodb_additional_mem_pool_size=1M
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
table_cache=512
thread_cache=32
key_buffer=128M

[data_digger]

Yes that helped, new results are below...Queries went up slightly but the Parse Time is better. What is that setting do exactly?

It seems strange that query count went up, it should still be lower then in v1.0.
The current version of Zen Cart executes A LOT of queries to get info on the same product, for example:

select products_type from products where products_id = '79'
select products_price, products_priced_by_attribute from products where products_id = '79'
select products_price, products_model, products_priced_by_attribute from products where products_id = '79'

All queries are simple and get info from the same table on the same product. It would be enough if Zen Cart executed one query that retrieves all columns

select * from products where products_id = '79'

And this setting does just that. Query Cache searches for this kind of queries and change them to "select *". Instead of executing 3 (or more) queries for each product, Zen Cart executes only one.
Unfortunately it requires some cpu time, and it has to balanced between database time and cpu time. On demo stores and few other stores it works well, but I knew from the beginning that in some cases it may not. So I introduced this switch to turn off such feature.

Parse Time: 4.755 - Number of Queries: 724 - Query Time: 3.0285431182251

That is still quite a lot(a lot!), both on web server side and database side.

I ended up with he below, you seem to know this stuff very well, does it seem right to you?

It might be fine, but I can't tell without knowing more about Your site(really, these settings can be very good, or very, very bad depending on how many products You have, how much traffic You get, how many times per day You get order etc.).

I can take a look on Your site if You want (I don't need any passwords to db ;) ). Check Your PM.

[jmacias]

Hi Data-Digger, there are many improvements that can be done in zencart.

I suggest to work in product_info/main_template_vars.php
The way it gets the product switches is HORRIBLE!!!

[data_digger]

If You can paste example of inefficient query I would be glad to look into it.

[data_digger]

New version of Query Cache is available. If You have lots of categories in Your store, this release is a must. Download new version:
data-diggers.com/contribs/query-cache/downloads/querycache-current.zip

Version 1.6 adds following features:

• Option to prefetch products_to_categories table for faster execution of zen_get_categories_products_list() function. You can turn ON/OFF this functionality by changing QC_ZEN_GET_CATEGORIES_PRODUCTS_LIST_PREFETCH switch in includes/extra_configures/query_cache.php. If Your store executes thousands of queries it's pretty good chance that this option will solve it.
• Query Cache can now work in Admin area too. Just enable QC_ENABLE_IN_ADMIN in admin/includes//extra_configures/query_cache.php.

By default new switches are set to false. If You have many (at least few hundreds) products linked to many categories prefetching products_to_categories table will probably improve performance. On one store this switch managed to reduce query count from 15 000 queries ( yes, 15 thousands queries!) to about 100 queries (99,3% less queries!).

If You'll find any bugs, please report back.

[HeathenMagic]

Wow! Your contributions are amazing. They work brilliantly.

P.S. maybe you should have a donation button on your website for us to send you a few coffees for your hard work

[data_digger]

Wow! Your contributions are amazing. They work brilliantly.

P.S. maybe you should have a donation button on your website for us to send you a few coffees for your hard work

Thanks!
As for 'donation' button... Naaah, I don't do it for money:), I do it for fun.

But if You sell coffee in Your shop You're free to send me a gift:)

[4thwave]

Hello,
I installed Query Cache (1.3.8a) and it did reduce the number of ueries from over a 1000 down to 226 HOWEVER!

Now the site is loading EXTREMELY slow. Instead of a 50 sec load time on a 56k it's coming in at around 1:50 sec's (I pured a new cup of coffee while I was waiting) I did perform the following and no change. http://www.j w b o o k s .org

Note: Query Cache v1.5 has feature that might slow down Your store if Your web server is very slow. In such case You can try to set QC_USE_PRODUCT_QUERY_DETECTION in includes/extra_configures/query_cache.php to FALSE.

I do think that a great deal of the problem is that I made the stupid mistake of putting my store on a secure GoDaddy server. (before ( found out how slow and overloaded their servers are) any help or suggestions would be greatly appreciated. Before I uninstall the mod. BTW I am using SImple SEO, Hover Box 3 and Reward Points. Maybe 1 or 2 of the mods will need to go as well as using some graphic compression.