Re: PostgreSQL as a local in-memory cache

От: Chris Browne
Тема: Re: PostgreSQL as a local in-memory cache
Дата: ,
Msg-id: 87bpbcpabg.fsf@cbbrowne-laptop.afilias-int.info
(см: обсуждение, исходный текст)
Ответы: Re: PostgreSQL as a local in-memory cache  (Steve Wampler)
Список: pgsql-performance

Скрыть дерево обсуждения

Re: PostgreSQL as a local in-memory cache  (Chris Browne, )
 Re: PostgreSQL as a local in-memory cache  (Steve Wampler, )
  Re: PostgreSQL as a local in-memory cache  (Craig James, )
 Re: PostgreSQL as a local in-memory cache  ("", )
 Re: PostgreSQL as a local in-memory cache  (Chris Browne, )
  Re: PostgreSQL as a local in-memory cache  (Jaime Casanova, )
  Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
 Re: PostgreSQL as a local in-memory cache  ("", )
  Re: PostgreSQL as a local in-memory cache  (Mark Kirkwood, )
  Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
   Re: PostgreSQL as a local in-memory cache  (Jonathan Gardner, )
  Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
  Re: PostgreSQL as a local in-memory cache  (Greg Smith, )
   Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
    Re: PostgreSQL as a local in-memory cache  (Jonathan Gardner, )
     Re: PostgreSQL as a local in-memory cache  ("Pierre C", )
   Re: PostgreSQL as a local in-memory cache  (Jonathan Gardner, )
  Re: PostgreSQL as a local in-memory cache  (Alvaro Herrera, )
  Re: PostgreSQL as a local in-memory cache  (Josh Berkus, )
   Re: PostgreSQL as a local in-memory cache  (Jonathan Gardner, )
    Re: PostgreSQL as a local in-memory cache  (Balkrishna Sharma, )
   Re: PostgreSQL as a local in-memory cache  (Craig James, )

"" <> writes:
> My question is how can I configure the database to run as quickly as
> possible if I don't care about data consistency or durability? That
> is, the data is updated so often and it can be reproduced fairly
> rapidly so that if there is a server crash or random particles from
> space mess up memory we'd just restart the machine and move on.

For such a scenario, I'd suggest you:

- Set up a filesystem that is memory-backed.  On Linux, RamFS or TmpFS
  are reasonable options for this.

- The complication would be that your "restart the machine and move
  on" needs to consist of quite a few steps:

  - recreating the filesystem
  - fixing permissions as needed
  - running initdb to set up new PG instance
  - automating any needful fiddling with postgresql.conf, pg_hba.conf
  - starting up that PG instance
  - creating users, databases, schemas, ...

When my desktop machine's not dead [as it is now :-(], I frequently
use this very kind of configuration to host databases where I'm doing
functionality testing on continually-freshly-created DBs and therefore
don't actually care if they get thrown away.

I have set up an "init.d"-style script which has an extra target to do
the database "init" in order to make the last few steps mentioned as
quick as possible.

  ~/dbs/pgsql-head.sh init

goes an extra mile, using sed to rewrite postgresql.conf to change
defaults.

I expect that, if running on a ramdisk, you'd want to fiddle some of
the disk performance parameters in postgresql.conf.

It's certainly worth trying out the ramdisk to see if it helps with
this case.  Note that all you'll lose is durability under conditions
of hardware outage - PostgreSQL will still care as much as always
about data consistency.

[Thinking about wilder possibilities...]

I wonder if this kind of installation "comes into its own" for more
realistic scenarios in the presence of streaming replication.  If you
know the WAL files have gotten to disk on another server, that's a
pretty good guarantee :-).
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/internet.html
"MS  apparently now  has a  team dedicated  to tracking  problems with
Linux  and publicizing them.   I guess  eventually they'll  figure out
this back fires... ;)" -- William Burrow <>


В списке pgsql-performance по дате сообщения:

От: Yeb Havinga
Дата:
Сообщение: Re: B-Heaps
От: Jaime Casanova
Дата:
Сообщение: Re: PostgreSQL as a local in-memory cache