SQL Cache Method

[kimhuff]

How does a person know which method to choose. The more info section says it depends on the size of your store or more importantly the products/category section. I wouldn't say our store is huge, but we do have about 200 products and probably 20 or so categories. Would this seem like a large or small store?

Thanks,

Kim

[irascible]

I have also wondered this; and curious if it has any effect on the load times of my store. I used the sql cache method for a large store (in DB).

[FluffyTigger]

The one that has the most effect would be caching at the server level set up by your host. I do this on my servers, it cache the result directly in RAM, which really does decrease the load time significantly. You have to kinda ask around to see how much they allocate for this as it does take a bit of RAM. For a 400 items store, currently I allocate about 10MB for that store in addition to allocation for other web sites on the server. As the server run, I monitor the number of cache dump that occur, when it doesn't happen a lot, that mean results are well cached. A few hundred products make you an average store, large store would be a few thousand products at the very least.

Between DB and file, there isn't necessarily a right answer, it depend on the load of the server you're on and the setup of your host. If their web server is overloaded but have a separate DB server then DB caching might improve performance. It could be the other way around. For the store that I manage, we don't have the condition to utilize either method for performance gain and I turned caching off in ZC. Tried both, didn't really notice a real difference.

Caching work by remembering the result of sql queries, that help if you have an insane amount of items (say 5,000+) where the time to search for and fetch things could take a bit of time, and if you're on a kinda overloaded server where the performance time of getting result from the cache is faster than from the DB. Also, this would be where your host don't use mySQL caching in RAM, or they're not allocating enough RAM for that purpose, thereby causing a lot of cache dumping.

[irascible]

FluffyTiger,

Thanks, quite informative. Is there a way through, say, cpanel/phpmyadmin to find out if MySQL has caching to RAM enabled and how much is allocated?

[FluffyTigger]

SHOW STATUS LIKE '%qcache%'

SHOW VARIABLES LIKE '%query_cache%'

High number of low memory prunes are bad.

[irascible]

SHOW STATUS LIKE '%qcache%'

SHOW VARIABLES LIKE '%query_cache%'

High number of low memory prunes are bad.

Thanks again!