Query Cache v1.0

05 Jan 2009, 10:56

#1

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Query Cache v1.0

Hello,

This is my first official contribution.

I've created Query Cache contribution that might help improve performance of Zen Cart. It prevents Zen Cart from executing the same queries many times, which can speed up Your store. I tested it on few stores and it usually reduces number of queries from 30% to 60%. Installation instructions and contribution can be found here:
http://www.data-diggers.com/query-cache
There's also screencast showing how to install this module. Please let me know what You think about it. In case of any issues please contact me via pm or via email ([email protected])

Note: there will be new version of this module, which will further reduce number of queries sent to MySQL.

05 Jan 2009, 12:15

#2

marg

Totally Zenned

Join Date:: Mar 2004
Posts:: 642
Plugin Contributions:: 0

Re: Query Cache v1.0

Thank You!
Amazing results! From 1060 down to 472 on one of my sites...

06 Jan 2009, 09:17

#3

data_digger

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Re: Query Cache v1.0

Happy to hear that:)
If You've found any bugs or have any suggestions please write and I'll try to fix bugs/implement new features.

09 Jan 2009, 23:30

#4

wastaboo

New Zenner

Join Date:: May 2007
Posts:: 40
Plugin Contributions:: 0

Re: Query Cache v1.0

:clap: Thank you i got a 60% nice job :lamo:

12 Jan 2009, 14:38

#5

data_digger

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Re: Query Cache v1.0

New version of Query Cache is available. In nutshell, it reduces query count by 80% ( v1.0 reduces query count by about 50%, so it's huge improvement). Results on Zen Cart Demo Store:

without Query Cache: 950+ queries
with Query Cache v1.5: about 200 queries

Download updated version here. Read my updated blog to find out more.

12 Jan 2009, 17:51

#6

evan70

New Zenner

Join Date:: Aug 2008
Posts:: 13
Plugin Contributions:: 0

Re: Query Cache v1.0

nice JOB :clap: work great at test site http://shop.kafeshop.cz ThnkZ:clap:

12 Jan 2009, 18:28

#7

yellow1912

Totally Zenned

Join Date:: Oct 2006
Posts:: 5,422
Plugin Contributions:: 0

Re: Query Cache v1.0

I wonder if this is the best approach to caching it. Why not editing the db class only, and then use the whole query string, md5 it to get a (fairly)u?

13 Jan 2009, 10:09

#8

data_digger

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Re: Query Cache v1.0

yellow1912:
I wonder if this is the best approach to caching it. Why not editing the db class only, and then use the whole query string, md5 it to get a (fairly)u?

I assume You're referring to changes in functions_categories.php?
Sure, it would be much more elegant way of doing this, but unfortunately (I tested it), it would be slow(parsing queries and argument extraction takes more time then executing it). In fact, I wanted to do it in similar way You wrote, but in performance contribution performance is more important then beautiful code.

13 Jan 2009, 13:50

#9

yellow1912

Totally Zenned

Join Date:: Oct 2006
Posts:: 5,422
Plugin Contributions:: 0

Re: Query Cache v1.0

I dont know why my comment got cut half way, anyhow what I meant is: if we treat each query string as an identifier (and by md5 the query content we would have a rather unique identifier for each query), then we can store each query result in a unique file?
I think the most time consuming would serialization and un-serialization of the object returned by the query (to save/restore the result), Maybe we can use some easier method to store it?

13 Jan 2009, 14:46

#10

data_digger

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Re: Query Cache v1.0

yellow1912:
I dont know why my comment got cut half way, anyhow what I meant is: if we treat each query string as an identifier (and by md5 the query content we would have a rather unique identifier for each query), then we can store each query result in a unique file?

Why would You like to treat query string with md5? I use whole query string as identifier (in array) in my class and it's quite fast.

Storing results in files is not a good idea imho. You never know if cached results are consistent with database. Also, 99% of queries execute in 0.01 sec or so (per query). Storing / restoring results of such queries would take much more time then executing it.
Besides, MySQL has it's own query cache (unfortunately hosting companies sometimes turn it off or forgot to turn it on) that does the same thing, only better, because results from cache are always consistent with database.

15 Jan 2009, 23:05

#11

marcopolo

Zen Follower

Join Date:: May 2008
Posts:: 497
Plugin Contributions:: 0

Re: Query Cache v1.0

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?

16 Jan 2009, 01:05

#12

data_digger

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Re: Query Cache v1.0

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.

16 Jan 2009, 03:16

#13

marcopolo

Zen Follower

Join Date:: May 2008
Posts:: 497
Plugin Contributions:: 0

Re: Query Cache v1.0

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

16 Jan 2009, 07:31

#14

data_digger

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Re: Query Cache v1.0

marcopolo:
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.

marcopolo:
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.

marcopolo:
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.

03 Feb 2009, 09:17

#15

jmacias

New Zenner

Join Date:: Feb 2009
Posts:: 1
Plugin Contributions:: 0

Re: Query Cache v1.0

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!!!

04 Feb 2009, 10:01

#16

data_digger

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Re: Query Cache v1.0

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

04 Feb 2009, 10:03

#17

data_digger

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Re: Query Cache v1.0

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.

04 Feb 2009, 15:15

#18

heathenmagic

Totally Zenned

Join Date:: May 2005
Posts:: 730
Plugin Contributions:: 0

Re: Query Cache v1.0

Wow! Your contributions are amazing. They work brilliantly. :clap:

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

05 Feb 2009, 10:35

#19

data_digger

Zen Follower

Join Date:: Jan 2009
Posts:: 222
Plugin Contributions:: 2

Re: Query Cache v1.0

HeathenMagic:
Wow! Your contributions are amazing. They work brilliantly. :clap:

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:)

06 Feb 2009, 14:39

#20

4thwave

Zen Follower

Join Date:: Sep 2007
Posts:: 125
Plugin Contributions:: 0

Re: Query Cache v1.0

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.