Re: Memcached for Database server

On 05/17/2011 01:38 PM, Satoshi Nagayasu wrote:
> Hi,
>
> 2011/05/17 14:31, Adarsh Sharma wrote:
>> Rick Genter wrote:
>>> On May 16, 2011, at 10:09 PM, Adarsh Sharma wrote:
>>>
>>>
>>>> Dear all,
>>>>
>>>> I need to research on Memcache in the next few days.
>>>>
>>>> What I want to know is it worth to have memcahed enable in our
>>>> Mysql/ Postgres Production Servers.
>>>> We have databases from 20 to 230 GB and it's not the OLTP just a
>>>> simple OLAP where data is fetched and stored in some meaningful format.
>>>>
>>>>
>>>> What are benefits& why we used memcahed?
>>>>
>>>> What are the bottlenecks to meet?
>>>>
>>>
>>> You need to read about memcached. Memcached is not something you
>>> "enable". You have to program to it.
>>>
>>
>> Thanks Rick, just one question..
>>
>> At what stage we need memcached & what is the purpose of using it.
>>
>> I just want to know whether it is worth to use memcahced or not as per
>> our requirements.
>
> I just built a software to enable query caching for PostgreSQL
> with using memcached, which adds a proxy layer.
>
> http://pgsnaga.blogspot.com/2011/03/postgresql-query-cache-pqc.html

Much like with memcached, it looks like you still have to handle your
own cache invalidation with your cache daemon, and it can return
outdated or inconsistent results. Your examples clearly show that. It'd
be nice if the google code front page clearly pointed out that it's not
a fully transparent cache in that it can return stale or inconsistent
data and the app has to be aware of that.

How do you handle statements that rely on current_timestamp, random(),
etc? What about if their reliance is via a function? Is that just an
understood limitation of the cache, that it'll cache even queries that
don't really make sense to cache?

--
Craig Ringer