Thread: Memcached for Database server
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? Thanks
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 meaningfulformat. > > > 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. -- Rick Genter rick.genter@gmail.com
Rick Genter wrote:
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.
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.
-- Rick Genter rick.genter@gmail.com
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 somemeaningful 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 Please take a look. Thanks, > >> -- >> Rick Genter >> rick.genter@gmail.com >> >> > -- NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>
On May 16, 2011, at 10:31 PM, 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 somemeaningful 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 can't tell you; you didn't state your requirements. I recommend that you go to the memcached web site and research it. http://www.memcached.org -- Rick Genter rick.genter@gmail.com
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
On 05/17/2011 01:31 PM, Adarsh Sharma wrote: > At what stage we need memcached & what is the purpose of using it. You might not need it. Depends on the nature of your app, its performance requirements, how strict it is about always getting consistent & current data, and how much money you have to throw at hardware. As for what the purpose of using it is: Read the documentation. http://code.google.com/p/memcached/wiki/FAQ memcached works best in environments where some data changes rarely and is queried extremely frequently. To use memcached, you must modify your software to check memcached for that data before querying postgresql for it. You must also modify your software to clear the memcached copy of the data when it changes the data in postgresql, otherwise you might get outdated copies of the data from memcached. Even then, you have to be very careful about managing the cache and avoiding race conditions if you store anything in memcached that you can't afford to have a bit out of date. In general, it's best for caching frequently queried things that don't change very often, don't change at all within a given user session, etc. If you want more help from the people here, spend some of your own time making an effort to more clearly explain what your app does, what your needs are, what language & platform you use, etc etc etc. -- Craig Ringer
2011/5/17 Craig Ringer <craig@postnewspapers.com.au>: > 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? there is also pgmemcache http://pgfoundry.org/projects/pgmemcache/ It is not a proxy but an extension to access memcache from within postgresql. You can use it to build your own querycache. > > -- > Craig Ringer > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
> 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? Probably we should cache the result of a query which containts no functions or a query which only contains immutable functions. Also we should be carefull about views, which may be a result of non immutable functions. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Hi, 2011/5/18 Tatsuo Ishii <ishii@postgresql.org>: >> 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? > > Probably we should cache the result of a query which containts no > functions or a query which only contains immutable functions. From my point of view, that's the trade off things. I think database doesn't need to handle all situations and conditions. In other words, "One size does not fit all." Honestly, I'm not interested in building a complex and huge tool. I love "Keep it simple, stupid" discipline. So, I just created a tiny tool, which would be useful in many situations, not *all* situations. In pqc, a programmer is still able to handle life cycle of the cache with using hints. I think it's enough to solve many performance issues. That's what I wanted to pqc. Of course, if rich customers want me to invest *more integrated* query cache for PostgreSQL, I will welcome them. :) Regards, -- NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>
On 20/05/11 13:00, Adarsh Sharma wrote: > Thanks Craig, > > I spend some time on Memcahced and your explaination also helps. > I think it is used only for applications where load is very high & users > issue read only queries to the database. Er, yes. You can't really cache queries that write to the database, and it doesn't make sense to cache queries where the answers change for every query. A cache is only useful where the same query returns the same result (for a while, at least) and is executed very, very often. > Can you give the Real Example where memcached is used heavily. Google can. You'll need to build your independent research skills if you're going to succeed at what you're doing; it will not work well for you to rely on others making all the effort to explain everything to you. You will usually get better results when you ask people for help if you can show them that you have already made an effort to discover the answer for yourself. > fore.g How Facebook, You tube use it . Five seconds on Google found: http://www.facebook.com/note.php?note_id=39391378919 -- Craig Ringer
Please reply to the list, not directly to me. My reply follows. On 20/05/11 14:47, Adarsh Sharma wrote: > But Sometimes when I got stucked , I have no other option. Like I > stucked from the past 4 days to solve the attached problem. Sure. Sometimes you get stuck, and that's what mailing lists are great for. All I was trying to say is that the more effort you can show people you've put in _before_ asking for help and the more effort you put into writing a clear and complete question when you do ask for help, the more effort they'll usually put into helping you. > The problem is Can we create and populate a dynamic table A from another > table B where > table A column names are values of one column of table B & > Table A column values are the values of 2nd column of table B. > > I will do it if I got the architecture to achieve this. You're trying to transform an EAV (Entity-Attribute-Value) schema, otherwise known as a key/value schema, into a real relational schema. http://en.wikipedia.org/wiki/Entity-attribute-value_model Like many relational databases, PostgreSQL isn't very well suited to that, because it expects relations (tables) to have a fairly fixed set of columns. It doesn't deal well with views that potentially have a different set of columns each time they're executed. However, PostgreSQL has a couple of ways around that: the 'RECORD' data type, and the 'hstore' data type. I have the feeling that what you want might - kind of - be possible by combining a crosstab query with the hstore record constructor in PostgreSQL 9.0 and above. That'll let you produce a view like: category_id Record_id fields 78 21 {Village:adasrpur, SOI:media, Heading:CM dies} etc. Come to think of it, it'd be possible to do in 8.4 and earlier in PL/PgSQL, but I'd have to have a play with that. Anyway, check out: http://www.postgresql.org/docs/current/static/tablefunc.html http://www.postgresql.org/docs/current/static/hstore.html in particular the crosstab(text,text) form and the hstore(record) constructor. Have a play. It'd be helpful to have your sample data in a useful format like a list of INSERT statements, a pg_dump, or the output of \COPY rather than just a plain text list. It'd make testing things easier. I'd like to have a play, but I can't be bothered making up a sample data set or converting yours by hand. BTW, Personally I think you're usually better off using hstore in the frist place ratherthan EAV, but you're probably already committed to an EAV model. -- Craig Ringer
On 20/05/11 17:33, Adarsh Sharma wrote:
> Craig Ringer wrote:
>>
>> category_id Record_id fields
>> 78 21 {Village:adasrpur, SOI:media, Heading:CM dies
>>
>
> Yes , but slight modification , I want Village, SOI Heading as column
> names and adasrpur,media and CM dies their values as there may 1000 of
> rows of it.
You're out of luck, then. You want a view with dynamic columns, and
PostgreSQL does not support that.
You can have have a *function* with a dynamic record as output, but then
you have to name the columns in the query. That's how crosstab works.
You can also have a result where all your dynamic data - the stuff where
you don't know the keys in advance - is a single column of a data type
like 'hstore' that contains key/value mappings. From the above, you
don't seem to want that.
I suspect that in your case, your best bet would be a two-query
solution. Figure out your WHERE clause, then run a query to find out
what keys match he WHERE clause. Use that in your application to produce
a crosstab query with an appropriate column list.
If you really need to do this as a view, I think you're stuffed. You'll
never get dynamic column lists in a view.
> I read hstore first time & find it difficult to understand because I
> don't want the output in one column :
>
> CREATE TABLE test (col1 integer, col2 text, col3 text);
> INSERT INTO test VALUES (123, 'foo', 'bar');
>
> SELECT hstore(t) FROM test AS t;
> hstore
> ---------------------------------------------
> "col1"=>"123", "col2"=>"foo", "col3"=>"bar"
> (1 row)
Why not? Is the problem converting a hstore value into a set of values
client-side? Something else?
Maybe it would help if you took a step back and explained why you need
this exacty format - a view with dynamic columns. It's a weird
requirement, and it makes me wonder if you're really looking for
something else and have settled on a dynamic-column view as the "only"
solution when there migh tbe an easier way.
What constraints are you under? What app is involved? What language(s)?
Is it old/unmaintained code, or something new and under development?
What role do you play in it?
--
Craig Ringer