Horton

Thread

  1. Re: Is There Any Way ....

    Ron <rjpeace@earthlink.net> — 2005-10-04T23:33:47Z

    pg is _very_ stupid about caching.  Almost all of the caching is left
to the OS, and it's that way by design (as post after post by TL has
pointed out).

That means pg has almost no ability to take application domain
specific knowledge into account when deciding what to cache.
There's plenty of papers on caching out there that show that
context dependent knowledge leads to more effective caching
algorithms than context independent ones are capable of.

(Which means said design choice is a Mistake, but unfortunately
one with too much inertia behind it currentyl to change easily.)

Under these circumstances, it is quite possible that an expert class
human could optimize memory usage better than the OS + pg.
   
If one is _sure_ they know what they are doing, I'd suggest using
tmpfs or the equivalent for critical read-only tables.  For "hot"
tables that are rarely written to and where data loss would not be
a disaster, "tmpfs" can be combined with an asyncronous writer
process push updates to HD.  Just remember that a power hit
means that 

The (much) more expensive alternative is to buy SSD(s) and put
the critical tables on it at load time.

Ron
 

-----Original Message-----
From: "Jim C. Nasby" <jnasby@pervasive.com>
Sent: Oct 4, 2005 4:57 PM
To: Stefan Weiss <spaceman@foo.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Is There Any Way ....

On Tue, Oct 04, 2005 at 12:31:42PM +0200, Stefan Weiss wrote:
> On 2005-09-30 01:21, Lane Van Ingen wrote:
> >   (3) Assure that a disk-based table is always in memory (outside of keeping
> > it in
> >       memory buffers as a result of frequent activity which would prevent
> > LRU
> >       operations from taking it out) ?
> 
> I was wondering about this too. IMO it would be useful to have a way to tell
> PG that some tables were needed frequently, and should be cached if
> possible. This would allow application developers to consider joins with
> these tables as "cheap", even when querying on columns that are not indexed.
> I'm thinking about smallish tables like users, groups, *types, etc which
> would be needed every 2-3 queries, but might be swept out of RAM by one
> large query in between. Keeping a table like "users" on a RAM fs would not
> be an option, because the information is not volatile.

Why do you think you'll know better than the database how frequently
something is used? At best, your guess will be correct and PostgreSQL
(or the kernel) will keep the table in memory. Or, your guess is wrong
and you end up wasting memory that could have been used for something
else.

It would probably be better if you describe why you want to force this
table (or tables) into memory, so we can point you at more appropriate
solutions.

  2. Re: Is There Any Way ....

    Jim C. Nasby <jnasby@pervasive.com> — 2005-10-05T00:01:04Z

    On Tue, Oct 04, 2005 at 07:33:47PM -0400, Ron Peacetree wrote:
> pg is _very_ stupid about caching.  Almost all of the caching is left
> to the OS, and it's that way by design (as post after post by TL has
> pointed out).
> 
> That means pg has almost no ability to take application domain
> specific knowledge into account when deciding what to cache.
> There's plenty of papers on caching out there that show that
> context dependent knowledge leads to more effective caching
> algorithms than context independent ones are capable of.
> 
> (Which means said design choice is a Mistake, but unfortunately
> one with too much inertia behind it currentyl to change easily.)
> 
> Under these circumstances, it is quite possible that an expert class
> human could optimize memory usage better than the OS + pg.

Do you have any examples where this has actually happened? Especially
with 8.x, which isn't all that 'stupid' about how it handles buffers?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  3. Re: Is There Any Way ....

    Douglas J. Trainor <trainor@transborder.net> — 2005-10-05T00:40:54Z

    Ron Peacetree sounds like someone talking out of his _AZZ_.
He can save his unreferenced flapdoodle for his SQL Server
clients.  Maybe he will post references so that we may all
learn at the feet of Master Peacetree.  :-)

     douglas

On Oct 4, 2005, at 7:33 PM, Ron Peacetree wrote:

> pg is _very_ stupid about caching.  Almost all of the caching is left
> to the OS, and it's that way by design (as post after post by TL has
> pointed out).
>
> That means pg has almost no ability to take application domain
> specific knowledge into account when deciding what to cache.
> There's plenty of papers on caching out there that show that
> context dependent knowledge leads to more effective caching
> algorithms than context independent ones are capable of.
>
> (Which means said design choice is a Mistake, but unfortunately
> one with too much inertia behind it currentyl to change easily.)
>
> Under these circumstances, it is quite possible that an expert class
> human could optimize memory usage better than the OS + pg.
>
> If one is _sure_ they know what they are doing, I'd suggest using
> tmpfs or the equivalent for critical read-only tables.  For "hot"
> tables that are rarely written to and where data loss would not be
> a disaster, "tmpfs" can be combined with an asyncronous writer
> process push updates to HD.  Just remember that a power hit
> means that
>
> The (much) more expensive alternative is to buy SSD(s) and put
> the critical tables on it at load time.
>
> Ron
>
>
> -----Original Message-----
> From: "Jim C. Nasby" <jnasby@pervasive.com>
> Sent: Oct 4, 2005 4:57 PM
> To: Stefan Weiss <spaceman@foo.at>
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Is There Any Way ....
>
> On Tue, Oct 04, 2005 at 12:31:42PM +0200, Stefan Weiss wrote:
>> On 2005-09-30 01:21, Lane Van Ingen wrote:
>>>   (3) Assure that a disk-based table is always in memory (outside of 
>>> keeping
>>> it in
>>>       memory buffers as a result of frequent activity which would 
>>> prevent
>>> LRU
>>>       operations from taking it out) ?
>>
>> I was wondering about this too. IMO it would be useful to have a way 
>> to tell
>> PG that some tables were needed frequently, and should be cached if
>> possible. This would allow application developers to consider joins 
>> with
>> these tables as "cheap", even when querying on columns that are not 
>> indexed.
>> I'm thinking about smallish tables like users, groups, *types, etc 
>> which
>> would be needed every 2-3 queries, but might be swept out of RAM by 
>> one
>> large query in between. Keeping a table like "users" on a RAM fs 
>> would not
>> be an option, because the information is not volatile.
>
> Why do you think you'll know better than the database how frequently
> something is used? At best, your guess will be correct and PostgreSQL
> (or the kernel) will keep the table in memory. Or, your guess is wrong
> and you end up wasting memory that could have been used for something
> else.
>
> It would probably be better if you describe why you want to force this
> table (or tables) into memory, so we can point you at more appropriate
> solutions.

  4. Re: Is There Any Way ....

    Joshua D. Drake <jd@commandprompt.com> — 2005-10-05T01:32:18Z

    Douglas J. Trainor wrote:

>
> Ron Peacetree sounds like someone talking out of his _AZZ_.
> He can save his unreferenced flapdoodle for his SQL Server
> clients.  Maybe he will post references so that we may all
> learn at the feet of Master Peacetree.  :-)

Although I agree that I would definitely like to see some test cases
for what Ron is talking about, I don't think that resorting to insults
is going to help the situation.

Ron, if you would please -- provide some test cases for what you are
describing I am sure that anyone would love to see them. We are all
for improving PostgreSQL.

Sincerely,

Joshua D. Drake

-- 
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

  5. Re: Is There Any Way ....

    Dario Pudlo <dario_d_s@unitech.com.ar> — 2005-10-05T11:16:46Z

    I'm sure there will be cases when some human assisted caching algorithm will
perform better than an mathetical statistical based design, but it will also
depend on the "human". And it probably will make thing worse when workload
changes and human doesn't realize. It must be considered that, today,
hardware cost is not the %90 of budget that it used to be. Throwing hardware
at the system can be as much expensive as throwing certified "it stuff".
(just think in coffee budget! :-) )

If you need to improve "user perception", you can do others things. Like
caching a table in your client  (with a trigger for any change on table X
updating a table called "timestamp_table_change" and a small select to this
table, you can easily know when you must update your client). If it is a
application server, serving http request, then "user perception" will be
sticked to bandwidth AND application server (some of them have cache for
request).

FYI, I don't recall a mechanism in MSSQL to cache a table in buffers. Oracle
has some structures to allow that. (you know) It uses his own buffer. Since
version 9i, you can set three different data buffers, one (recycled cache)
for low usage tables (I mean tables with blocks which don't have too much
chance to be queried again, like a very large historical table) , one for
high usage tables (keep cache), and the regular one (difference is in
algorithm). And you must also set a buffer cache size for tablespaces with
different block size. But there is no such thing as "create table x keep
entirenly in buffer". And above all things, oracle doc always states "first,
tune design, then tune queries, then start tunning engine".

greetings.