Re: cmysql

Alexey Borzov wrote:

Hi, Mika Tuupola wrote:

    Regarding the userland implementation of query cache for
    MySQL. How does it differ from the query cache natively
    provided by MySQL?

MySQL's builtin query cache is a bad joke mostly done by fine MySQL folk to look good in benchmarks.

    Do you have any pointers to and article or similar which points
    out why the query cache sucks?

No, I don't have a link to any such article. I can give several points, though (some of them were already raised in the thread). 1) Query still has to travel to the server and the results have to travel back to the client. That may require network traffic if they are on different boxes and this always require starting a new database server thread. Userland cache is better in that regard.

Only better in this regard if your userland checks are faster then the mysql server checks

2) To get good results you still have to manually mark queries that need caching and those that are not. If you don't do this, cache will be trashed. No advantages over userland cache.

Or you turn your cache properly, on reporting db we use a large cache and are able to keep the queries for all frequent filter combinations in cache

3) High-level sites do not rely on MySQL's builtin query caching but roll out their own solutions, see memcache for example, developed by livejournal.com. Maybe their programmers have a clue?

A roll your own solution based on write invalidation will have the same problem. Livejournals solution works because your only updating the data in 30seconds to 5 minute incements, and of course that memcache is a lot faster then a db or any sort of userland implementation.

And of course the benefit of userland cache is that it can be made DB-independent. I know that everyone and his dog (and NASA as well) uses MySQL, but still. Now this is a plus, but we do already have cache_lite for this sort of thing.

One can also consult the archives of PostgreSQL mailing lists where its developers explained their reservations to adding a cache feature directly to the server. Well thats undertandable, a query cache speeds up the Heavy read, low write case, which isn't really the postgres market. Then take into account how to make things interact with triggers et al, and they probally feel that there are better solutions for there users, and its not worth the time. They could be wrong or right many different factors affect database performance and any sort of write invalidated cache will slow down a heavy transactional environment.

I would mainly like to point out that a userland cache (memcache, this one, etc) aren't the same thing as a mysql query cache. They shouldn't be sold as the same thing, userland caches can't garentee non stale data. And caches on a database server won't help the heavy write case (they may even hurt it). Userland caches can help the heavy write case but only when used in memcache fashion (use this data for the next x period). If you try to use it like the mysql query cache, you can get stale data, and even slower performance then the query cache since your not written in C, and now your mucking more with queries at hte php, and have increased levels of abscartion since you are using arrays to how your data instead of native C structure. Plus you have lots of file serialization overhead, or if you want better performance the joy that shmop is in php. Now that doesn't mean it doesn't work, but i really wouldn't want to sell it as a query cache. Since a query cache garentees no stale data. Its really a general memory cache (like say memcache only a ton slower) with convience hooks into a query api. In my opinion if you need this functionality, you should really look at memcache. And saying that doesn't work in my shared hosting environment just means your insane, if your a high use site, you can afford $70-200 a month for your own server. And btw the mysql query cache is a huge help for sites that say use postnuke, so saying its just for benchmarks isn't true. However its main help (same with any cache that uses write invalidation) is in further speeding up the already fast few write many read case. This email got so long winded, but it really boils down to this. Nothing in userland should be called or pretend to be a query cache, its just asking for trouble. More working on userland cacheing is a plus but it should really be down under the asumption that you'll be server stale data, its just a matter for what time period. -josh