Обсуждение: Temporary, In-memory Postgres DB?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/07/07 09:03, Gauthier, Dave wrote:
> This is a real longshot, but here goes...
>
>
>
> Is there such a thing as a temporary, probably in-memory, version of a
> Postgres DB?  Sort of like SQLite, only with the features/function of
> PG?  A DB like this would exist inside of, and for the duration of, a
> script/program that created it, then vanish when the script/program
> ends.
>
>
>
> Probably not, but if not, then this would be (IMO) a great addition to
> have, something that'd really make it distinct from MySQL .  I'd use
> SQLite, but I want to have stored functions and other "real" database
> features that it just doesn't have.

If you have enough RAM, and your database is small enough, the OS
will eventually cache the whole thing.

I know that's not exactly what you're talking about, but I think
it's as close as you'll get.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHMdcAS9HxQb37XmcRAqD9AJ4usfOq49ApqnLOz9advUnRmc7q2QCdFa8s
xAL+tMf4Xu4T4hGhvUCzomA=
=QmE5
-----END PGP SIGNATURE-----

On 11/7/07, Gauthier, Dave <dave.gauthier@intel.com> wrote:
>
> This is a real longshot, but here goes...
>
> Is there such a thing as a temporary, probably in-memory, version of a
> Postgres DB?  Sort of like SQLite, only with the features/function of PG?  A
> DB like this would exist inside of, and for the duration of, a
> script/program that created it, then vanish when the script/program ends.

Mount a ramdisk, initdb there, run db from there.

Conversely, create the db normally, mount a ram disk, create a
tablespace there, create a db and it's tables there.

The second method might not be optimal because if you don't cleanly
remove the db / tablespace postgresql might have some issues starting
up after a reboot.

Yes, I'm thinking of a small DB (could fit into mem).  And the notion
that all the data would reside in memory makes sense.  But what about
the metadata?  And there is the question of the "initdb" and all the
stuff it creates.  That goes to disk, yes? no?

Another question, but first my tenuous understanding of how dbs are
created, up for critique...

- "initdb" creates (on disk) all the stuff you need to have before you
"createdb".
- "createdb" creates a db (puts it in the place you designated with
"initdb")
- "create schema" can be used to create multiple schemas in a singel DB.

- "drop schema" can be used to get rid of a schema within a db
- "dropdb" can be used to get rid of a db that was created with
"createdb"
Q: How does one get rid of whatever gets created with "initdb"? Is it
(gulp) just a "rm -r" ?

I do have access to scratch disks which, in effect, could be used as a
temporary storage area.  IOW, if something goes wrong, and I don't get
to delete a db that was created on the scratch disk, it'll get cleaned
up for me overnight.  It's just a question of how long it'll take to
initdb + createdb + create a db model + load.

Thanks for the expert advise !

-dave





-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson
Sent: Wednesday, November 07, 2007 10:17 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Temporary, In-memory Postgres DB?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/07/07 09:03, Gauthier, Dave wrote:
> This is a real longshot, but here goes...
>
>
>
> Is there such a thing as a temporary, probably in-memory, version of a
> Postgres DB?  Sort of like SQLite, only with the features/function of
> PG?  A DB like this would exist inside of, and for the duration of, a
> script/program that created it, then vanish when the script/program
> ends.
>
>
>
> Probably not, but if not, then this would be (IMO) a great addition to
> have, something that'd really make it distinct from MySQL .  I'd use
> SQLite, but I want to have stored functions and other "real" database
> features that it just doesn't have.

If you have enough RAM, and your database is small enough, the OS
will eventually cache the whole thing.

I know that's not exactly what you're talking about, but I think
it's as close as you'll get.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHMdcAS9HxQb37XmcRAqD9AJ4usfOq49ApqnLOz9advUnRmc7q2QCdFa8s
xAL+tMf4Xu4T4hGhvUCzomA=
=QmE5
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Ron Johnson <ron.l.johnson@cox.net> writes:
> On 11/07/07 09:03, Gauthier, Dave wrote:
>> Is there such a thing as a temporary, probably in-memory, version of a
>> Postgres DB?

> If you have enough RAM, and your database is small enough, the OS
> will eventually cache the whole thing.

Or put it on a ramdisk filesystem.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/07/07 09:58, Tom Lane wrote:
> Ron Johnson <ron.l.johnson@cox.net> writes:
>> On 11/07/07 09:03, Gauthier, Dave wrote:
>>> Is there such a thing as a temporary, probably in-memory, version of a
>>> Postgres DB?
>
>> If you have enough RAM, and your database is small enough, the OS
>> will eventually cache the whole thing.
>
> Or put it on a ramdisk filesystem.

But doesn't that just add more overhead and reduce the amount of
memory that the OS can cache things in?


- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHMeykS9HxQb37XmcRArErAJ47+9oq1/fTZZ4AXrLnL2qGo6E29gCgqVhP
DoZuWDTpWE4Rks3tjAWa0mQ=
=fuHr
-----END PGP SIGNATURE-----

On 11/7/07, Ron Johnson <ron.l.johnson@cox.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 11/07/07 09:58, Tom Lane wrote:
> > Ron Johnson <ron.l.johnson@cox.net> writes:
> >> On 11/07/07 09:03, Gauthier, Dave wrote:
> >>> Is there such a thing as a temporary, probably in-memory, version of a
> >>> Postgres DB?
> >
> >> If you have enough RAM, and your database is small enough, the OS
> >> will eventually cache the whole thing.
> >
> > Or put it on a ramdisk filesystem.
>
> But doesn't that just add more overhead and reduce the amount of
> memory that the OS can cache things in?

Didn't say it was the smart thing to do.  Just that you could do it.

I think if one is looking at in memory databases, PostgreSQL is NOT
the first choice really.

Ron Johnson <ron.l.johnson@cox.net> writes:
> On 11/07/07 09:58, Tom Lane wrote:
>> Or put it on a ramdisk filesystem.

> But doesn't that just add more overhead and reduce the amount of
> memory that the OS can cache things in?

It's very possibly not a win, but the kinds of people who ask this
question at all do not understand the concept of caching, so I'm
sure they'll be happier with a solution where the data demonstrably
never hits disk ;-)

A case where it could be a win is where you are thrashing the DB with
heavy update load.  Even if everything is cached there will be a pretty
serious amount of disk write traffic, which'd possibly interfere with
other system activity.

            regards, tom lane

I understand caching.

Here's the reason I'm inquiring into this line of thought...

I already have a "big" on-disk DB with lots and lots of data, and lots
of stored functions and a data model that works with DB loaders and
other code that queries out data.  I also have users that want all of
that, except for the particular data content.  They basically want to
load a DB with data that's in their scratch area without polluting
what's in the "main" DB. The cardinality of this "personal, scratch
data" will be orders of magnitude smaller than what's in the main (could
all fit in memory).  And once loaded,  they would be able to run all the
same DB load and query tools that work on the main DB, just redirect to
the small, personal DB.

This would be a good app for SQLite, but SQLite can't do a lot of the
things that are running in the main DB (like stored procedures).

It's become clear that PG cannot do a pure in-memory DB like SQLite.
It's why I initially called this a "longshot" and the answer to my
question is probably "no".  But enabling something like a pure in-memory
(and temporary) DB for small apps that can reap all the wonderful
features of PG would make it very attractive for some users.  Just
something to think about for future development.

One question I had earlier that I don't think got answered was how to
undo an "initdb".  "dropdb" drops a DB, but how do I undo an "initdb"?


-dave




-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, November 07, 2007 12:05 PM
To: Ron Johnson
Cc: pgsql-general@postgresql.org
Subject: Re: DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory
Postgres DB?)

Ron Johnson <ron.l.johnson@cox.net> writes:
> On 11/07/07 09:58, Tom Lane wrote:
>> Or put it on a ramdisk filesystem.

> But doesn't that just add more overhead and reduce the amount of
> memory that the OS can cache things in?

It's very possibly not a win, but the kinds of people who ask this
question at all do not understand the concept of caching, so I'm
sure they'll be happier with a solution where the data demonstrably
never hits disk ;-)

A case where it could be a win is where you are thrashing the DB with
heavy update load.  Even if everything is cached there will be a pretty
serious amount of disk write traffic, which'd possibly interfere with
other system activity.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

In response to "Gauthier, Dave" <dave.gauthier@intel.com>:
>
> One question I had earlier that I don't think got answered was how to
> undo an "initdb".  "dropdb" drops a DB, but how do I undo an "initdb"?

rm -rf the directory in which you put the initdb.

--
Bill Moran
http://www.potentialtech.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Undo an initdb?  Probably the same way you undo unlinking an SQLite
database.

Maybe being wrapped in my own little niche I just don't know enough
about the wide world of hyperfeaturitis, but making "temporary DB"
as a feature seems a little vague.

It doesn't really take that long to create a new database
(especially if it's scripted!), and it's even faster if you make the
"temporary DB" a schema off a public database.

On 11/07/07 11:27, Gauthier, Dave wrote:
> I understand caching.
>
> Here's the reason I'm inquiring into this line of thought...
>
> I already have a "big" on-disk DB with lots and lots of data, and lots
> of stored functions and a data model that works with DB loaders and
> other code that queries out data.  I also have users that want all of
> that, except for the particular data content.  They basically want to
> load a DB with data that's in their scratch area without polluting
> what's in the "main" DB. The cardinality of this "personal, scratch
> data" will be orders of magnitude smaller than what's in the main (could
> all fit in memory).  And once loaded,  they would be able to run all the
> same DB load and query tools that work on the main DB, just redirect to
> the small, personal DB.
>
> This would be a good app for SQLite, but SQLite can't do a lot of the
> things that are running in the main DB (like stored procedures).
>
> It's become clear that PG cannot do a pure in-memory DB like SQLite.
> It's why I initially called this a "longshot" and the answer to my
> question is probably "no".  But enabling something like a pure in-memory
> (and temporary) DB for small apps that can reap all the wonderful
> features of PG would make it very attractive for some users.  Just
> something to think about for future development.
>
> One question I had earlier that I don't think got answered was how to
> undo an "initdb".  "dropdb" drops a DB, but how do I undo an "initdb"?
>
>
> -dave
>
>
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Wednesday, November 07, 2007 12:05 PM
> To: Ron Johnson
> Cc: pgsql-general@postgresql.org
> Subject: Re: DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory
> Postgres DB?)
>
> Ron Johnson <ron.l.johnson@cox.net> writes:
>> On 11/07/07 09:58, Tom Lane wrote:
>>> Or put it on a ramdisk filesystem.
>
>> But doesn't that just add more overhead and reduce the amount of
>> memory that the OS can cache things in?
>
> It's very possibly not a win, but the kinds of people who ask this
> question at all do not understand the concept of caching, so I'm
> sure they'll be happier with a solution where the data demonstrably
> never hits disk ;-)
>
> A case where it could be a win is where you are thrashing the DB with
> heavy update load.  Even if everything is cached there will be a pretty
> serious amount of disk write traffic, which'd possibly interfere with
> other system activity.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHMgEJS9HxQb37XmcRApJ9AJ98fxi/RecoS+MUZimzGEk5zYP15QCg7Iz/
VtVm5BMgjWsV+71AFH8M88g=
=uTCV
-----END PGP SIGNATURE-----

On 11/7/07, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> Yes, I'm thinking of a small DB (could fit into mem).  And the notion
> that all the data would reside in memory makes sense.  But what about
> the metadata?  And there is the question of the "initdb" and all the
> stuff it creates.  That goes to disk, yes? no?

It goes where you tell it to.

initdb -D /mnt/ramdisk/data

et voila!

> Another question, but first my tenuous understanding of how dbs are
> created, up for critique...
>
> - "initdb" creates (on disk) all the stuff you need to have before you
> "createdb".
> - "createdb" creates a db (puts it in the place you designated with
> "initdb")

Or in a tablespace you've created since then

> - "create schema" can be used to create multiple schemas in a singel DB.
>
> - "drop schema" can be used to get rid of a schema within a db
> - "dropdb" can be used to get rid of a db that was created with
> "createdb"
> Q: How does one get rid of whatever gets created with "initdb"? Is it
> (gulp) just a "rm -r" ?

yep

shut down the db first, then clean out the directory.

> I do have access to scratch disks which, in effect, could be used as a
> temporary storage area.  IOW, if something goes wrong, and I don't get
> to delete a db that was created on the scratch disk, it'll get cleaned
> up for me overnight.  It's just a question of how long it'll take to
> initdb + createdb + create a db model + load.

For a small db like you're talking about only a minute or two.

If you have a "real" pg db that you don't want to scramble, then do
all your initdb under a different unprivaleged system account.  Just
point it to a different port.

> What am I exposing myself to if I have dozens (maybe a hundred max) PG
> databases running on the same server?  (v8.2.0)

First off, run 8.2.5 if you can, not 8.2.0.  8.2.0 has a few nasty
bugs you don't want to get bitten by.

Generally, running dozens to hundreds of instances of pgsql on one
machine is a bad idea.  Unless you have a very good business case for
it, it's better to run multiple dbs inside one instance, which is no
big deal at all.  I've run intranet pg machines, under pgsql 7.2 with
well over 100 individual databases with no problems at all.

Am 2007-11-07 10:03:24, schrieb Gauthier, Dave:
> Is there such a thing as a temporary, probably in-memory, version of a
> Postgres DB?  Sort of like SQLite, only with the features/function of
> PG?  A DB like this would exist inside of, and for the duration of, a
> script/program that created it, then vanish when the script/program
> ends.

I have done this before but it requires very much memory
if you need it writable and you must vaccmizer very often.


You need a shellscript which replace the "startupscript" for the
PostgreSQL in which you

  1)  create the RAMDISK
  2)  then decompress the previously build data.tar.bz2
  3)  start the PostgreSQL

and replace the shutdownscript with your own shellscript which do

  1)  stop write access to the PostgreSQL
  2)  vacuumizer the database
  3)  shutdown the PostgreSQL
  4)  make a backup of the previously created data.tar.bz2
  4)  compress the datadir to data.tar.bz2

I run an Opteron 140 with 8 GByte of memory and sometimes I have
problems with too less memory...  but unfortunatly I have not found
a Singel-Opteron Mainboard which support more then 8 GByte of memory
where I prefere to use 16-32 GByte...

Thanks, Greetings and nice Day
    Michelle Konzack
    Tamay Dogan Network
    Open Hardware Developer
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSN LinuxMichi
0033/6/61925193    67100 Strasbourg/France   IRC #Debian (irc.icq.com)

Sounds like a lot of work.  I don't want to do anything risky.  And they
probably won't give me a ramdisk anyway.

Being able to run a small but full featured, purely in-memory DB (sort
of like SQLite) would probably fit a niche that Postgres competitors
don't address.  So I guess this is just a wish list / suggestion matter
at this point.

-dave

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michelle
Konzack
Sent: Saturday, November 17, 2007 11:48 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Temporary, In-memory Postgres DB?

Am 2007-11-07 10:03:24, schrieb Gauthier, Dave:
> Is there such a thing as a temporary, probably in-memory, version of a
> Postgres DB?  Sort of like SQLite, only with the features/function of
> PG?  A DB like this would exist inside of, and for the duration of, a
> script/program that created it, then vanish when the script/program
> ends.

I have done this before but it requires very much memory
if you need it writable and you must vaccmizer very often.


You need a shellscript which replace the "startupscript" for the
PostgreSQL in which you

  1)  create the RAMDISK
  2)  then decompress the previously build data.tar.bz2
  3)  start the PostgreSQL

and replace the shutdownscript with your own shellscript which do

  1)  stop write access to the PostgreSQL
  2)  vacuumizer the database
  3)  shutdown the PostgreSQL
  4)  make a backup of the previously created data.tar.bz2
  4)  compress the datadir to data.tar.bz2

I run an Opteron 140 with 8 GByte of memory and sometimes I have
problems with too less memory...  but unfortunatly I have not found
a Singel-Opteron Mainboard which support more then 8 GByte of memory
where I prefere to use 16-32 GByte...

Thanks, Greetings and nice Day
    Michelle Konzack
    Tamay Dogan Network
    Open Hardware Developer
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSN LinuxMichi
0033/6/61925193    67100 Strasbourg/France   IRC #Debian (irc.icq.com)

Michelle Konzack wrote:

> I run an Opteron 140 with 8 GByte of memory and sometimes I have
> problems with too less memory...  but unfortunatly I have not found
> a Singel-Opteron Mainboard which support more then 8 GByte of memory
> where I prefere to use 16-32 GByte...

Tyan makes a server motherboard with a single Opteron socket and 8 DIMM
slots: http://www.tyan.com/product_board_detail.aspx?pid=229

--
Guy Rouillier