In theory question

On May 9, 2007, at 9:13 , Naz Gassiep wrote:

I've been having a look at memcached. I would like to ask, is there
any
reason that, theoretically, a similar caching system could be built
right into the db serving daemon?

This is all a bit above my head, but have you looked at pgmemcached?

http://people.freebsd.org/~seanc/pgmemcache/

Michael Glaesemann
grzm seespotcode net

On 09.05.2007 16:13, Naz Gassiep wrote:

This may be a question for -hackers, but I don't like disturbing them
unnecessarily.

I've been having a look at memcached. I would like to ask, is there any
reason that, theoretically, a similar caching system could be built
right into the db serving daemon?

I.e., the hash tables and libevent could sit on top of postmaster as an
optional component caching data on a per-query basis and only hitting
the actual db in the event of a cache miss?

I think this is close to what MySQL's query cache does. The question is
if this should be the job of the DBMS and not another layer. At least
the pgmemcache author and I think that it's better done outside the
DBMS. See http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for
the idea.

--
Regards,
Hannes Dorbath

On May 9, 2007, at 10:22 AM, Hannes Dorbath wrote:

On 09.05.2007 16:13, Naz Gassiep wrote:

This may be a question for -hackers, but I don't like disturbing them
unnecessarily.
I've been having a look at memcached. I would like to ask, is
there any
reason that, theoretically, a similar caching system could be built
right into the db serving daemon?
I.e., the hash tables and libevent could sit on top of postmaster
as an
optional component caching data on a per-query basis and only hitting
the actual db in the event of a cache miss?

I think this is close to what MySQL's query cache does. The
question is if this should be the job of the DBMS and not another
layer. At least the pgmemcache author and I think that it's better
done outside the DBMS. See http://people.FreeBSD.org/~seanc/
pgmemcache/pgmemcache.pdf for the idea.

I just read through that pdf. How does implementing a memcached
system with table triggers qualify as outside the database?

erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)

Naz Gassiep <naz@mira.net> writes:

I.e., the hash tables and libevent could sit on top of postmaster as an
optional component caching data on a per-query basis and only hitting
the actual db in the event of a cache miss?

How does the cache know when the database contents change?

regards, tom lane

Hannes Dorbath wrote:

I think this is close to what MySQL's query cache does. The question
is if this should be the job of the DBMS and not another layer. At
least the pgmemcache author and I think that it's better done outside
the DBMS. See
http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for the idea.

This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Naz Gassiep wrote:

Hannes Dorbath wrote:

I think this is close to what MySQL's query cache does. The question
is if this should be the job of the DBMS and not another layer. At
least the pgmemcache author and I think that it's better done outside
the DBMS. See
http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for the idea.

This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Then you wouldn't be able to eventually patent them ;)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Then you wouldn't be able to eventually patent them ;)

What an un-BSD licensish thing to say :P

This may be a question for -hackers, but I don't like disturbing them
unnecessarily.
I've been having a look at memcached. I would like to ask, is there any
reason that, theoretically, a similar caching system could be built
right into the db serving daemon?
I.e., the hash tables and libevent could sit on top of postmaster as an
optional component caching data on a per-query basis and only hitting
the actual db in the event of a cache miss?

I think this is close to what MySQL's query cache does. The question is
if this should be the job of the DBMS and not another layer. At least
the pgmemcache author and I think that it's better done outside the
DBMS. See http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf
for the idea.

I have always found MySQL's query cache to be utterly useless.

Think about it this way :

It only works for tables that seldom change.
It does not work for big tables (like the posts table of a forum) because
the cache would have to be huge.

So, the most frequent usage of MySQL's query cache is for dumb
applications who use, for instance, PHP, store their configuration in
MySQL, and reload it on each and every page with a SELECT * FROM
configuration.

In this case, you save the query time, but you don't save : the roundtrip
between PHP and the database, extracting query results, building objects
from them, time spent in ORMs, etc.

A much better solution is to do your own caching, for instance using
shared memory in the application server, and then you cache native
language objects. You not only save the query time, but also all the time
spent building those objects on every page load.

On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:

This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Then you wouldn't be able to eventually patent them ;)

I think you are overly optimistic ;-)

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Karsten Hilbert wrote:

On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:

This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Then you wouldn't be able to eventually patent them ;)

I think you are overly optimistic ;-)

You obviously haven't reviewed the US patent system. I just patented "A
textual method for conveying expression without using any standardized
language and only predetermined, but arbitrary symbols."

I noticed you used the ;-). You owe me 75.00.

Joshua D. Drake

Karsten

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua D. Drake wrote:

Karsten Hilbert wrote:

On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:

This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Then you wouldn't be able to eventually patent them ;)

I think you are overly optimistic ;-)

You obviously haven't reviewed the US patent system. I just patented "A
textual method for conveying expression without using any standardized
language and only predetermined, but arbitrary symbols."

That's already patented by Microsoft.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:

Joshua D. Drake wrote:

Karsten Hilbert wrote:

On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:

This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Then you wouldn't be able to eventually patent them ;)

I think you are overly optimistic ;-)

You obviously haven't reviewed the US patent system. I just patented "A
textual method for conveying expression without using any standardized
language and only predetermined, but arbitrary symbols."

That's already patented by Microsoft.

No they patented:

A textual method for conveying expression without using standardized
language and only predetermined, but arbitrary symbols."

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Wed, May 09, 2007 at 10:29:02AM -0700, Joshua D. Drake wrote:

Naz Gassiep wrote:

Hannes Dorbath wrote:

I think this is close to what MySQL's query cache does. The question
is if this should be the job of the DBMS and not another layer. At
least the pgmemcache author and I think that it's better done outside
the DBMS. See
http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for the idea.

This is exactly what I was asking about. So my theoretical idea has
already been implemented. Now if only *all* my ideas were done for me by
the time I came up with them :)

Then you wouldn't be able to eventually patent them ;)

You have no faith in the patent system ;)

Cheers,
D (remember, Mr. Naz is from a country where somebody patented the wheel.)
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

I have always found MySQL's query cache to be utterly useless.

Think about it this way :

It only works for tables that seldom change.
It does not work for big tables (like the posts table of a forum)
because the cache would have to be huge.

So, the most frequent usage of MySQL's query cache is for dumb
applications who use, for instance, PHP, store their configuration in
MySQL, and reload it on each and every page with a SELECT * FROM
configuration.

In this case, you save the query time, but you don't save : the
roundtrip between PHP and the database, extracting query results,
building objects from them, time spent in ORMs, etc.

A much better solution is to do your own caching, for instance
using shared memory in the application server, and then you cache
native language objects. You not only save the query time, but also
all the time spent building those objects on every page load.

This was actually my original question. In my web page, I cache the
config, user preferences and other static material in session vars and
only rerun the function that fetches them if the app ever changes them

If you are clever about db fetches in this way and store as much stuff
in session vars (which is just like storing it in a memcached instance
really) is there much or even any benefit in running memcached?

On 09.05.2007 17:30, Erik Jones wrote:

On 09.05.2007 16:13, Naz Gassiep wrote:
I think this is close to what MySQL's query cache does. The question
is if this should be the job of the DBMS and not another layer. At
least the pgmemcache author and I think that it's better done outside
the DBMS. See
http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for the idea.

I just read through that pdf. How does implementing a memcached system
with table triggers qualify as outside the database?

The point is to have the DBMS _invalidate_ an external Cache, not to
fill or use it.

Caching in that case should not be done for single SQL statements. You
should cache things that have been produced using that query, a rendered
part of an HTML page is an example.

Think of a news selection on your website, the pages changes when the
content of 2-3 tables in your database changes. Here you have the DBMS
clear the page from the cache and your application layer re-render it
and put the new version in the cache.

--
Regards,
Hannes Dorbath