In memory Database for postgres

2008/11/18 aravind chandu <avin_friends@yahoo.com>:

Hello,

Hi!

I guess most of you guys heard about In Memory Database.I have
a small question regarding it.I need to create an In Memory Database for
postgresql through which I have to perform various operations on postgresql
database(queries,procedures,programs using pqxx API etc...).I didn't have
any idea of how to start and where to start this issue.Please comment on
this issue,so that it will be really helpful to me .

The fact aside that it's a "bad idea" (tm): you could have the data files
reside in a RAM disk. Which OS are you planning to do this on?

Thanks,
Avin.

Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

On Mon, Nov 17, 2008 at 12:11 PM, aravind chandu <avin_friends@yahoo.com> wrote:

Hello,

I guess most of you guys heard about In Memory Database.I have
a small question regarding it.I need to create an In Memory Database for
postgresql through which I have to perform various operations on postgresql
database(queries,procedures,programs using pqxx API etc...).I didn't have
any idea of how to start and where to start this issue.Please comment on
this issue,so that it will be really helpful to me .

It's more important to ask yourself the question "what problem am I
trying to solve?" Without knowing that, it's hard to say whether or
not an in memory database is a good idea.

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of aravind chandu
Sent: Monday, November 17, 2008 11:11 AM
To: postgresql Forums
Subject: [GENERAL] In memory Database for postgres

Hello,

I guess most of you guys heard about In Memory Database.I
have a small question regarding it.I need to create an In Memory
Database for postgresql through which I have to perform various
operations on postgresql database(queries,procedures,programs using
pqxx API etc...).I didn't have any idea of how to start and where to
start this issue.Please comment on this issue,so that it will be really
helpful to me .

If you have lots of memory on your system, PostgreSQL will hold the
frequently used tables in memory. So the burning question is:

What do you really want to do this for?

There are existing in memory database systems with a free license like
PostgreSQL:

FastDB:

http://www.garret.ru/databases.html

MonetDB:

http://monetdb.cwi.nl/

FastDB is an embedded, single writer - multiple reader type solution.

MonetDB is a column oriented database, especially suitable for database
warehouse designs.

There are additional solutions found in this article:

http://en.wikipedia.org/wiki/In-memory_database

Now, I think it would be very nice to have in-memory or column oriented
tables in PostgreSQL, but that would be a heavy-duty major difficult
project.

If you are volunteering to take that on, I suspect it is going to be
harder than you think, and if you want someone else to do it, I guess it
is not on the horizon yet.

<<

In my opinion very nice solution is building part of PostgreSQL
database in memory - below it is instruction how to build PostgreSQL
schema in memory in Linux. I tested this with my ROLAP solution for
recalculation MOLAP cubes in memory and then join with master cube
(this speeds up proces about 10 times!!! - but in other solution may
be different).

In grub (or other bootloader) you must declare ramdisk and then in OS:

mke2fs /dev/ram0
mkdir /mnt/ram0
mount /dev/ram0 /mnt/ram0
mkdir /mnt/ram0/pgspace

chown postgres:postgres /mnt/ram0/pgsapce

The "/mnt/ram0/pgsapce" folder must be empty (ERROR: directory
"/mnt/ram0" is not empty)

And then you may create tables (in this schema of course) and write to them.

Of course you must delete schema before shutdown PostgreSQL and OS - I
dont't now how resolve problem with error when the schema was not
deleted? - I have no time to think about it maybe anybody know how to
restore db when the in memory schema was damaged?

Regards,
Blazej

2008/11/17 aravind chandu <avin_friends@yahoo.com>:

Sorry I forgot about create tablespace script - this is the SQL script:

CREATE TABLESPACE ram_space LOCATION '/mnt/ram0/pgspace';

And then:

CREATE TABLE (...) TABLESPACE ram_space;

and table is in memory.

Regards,
Blazej

2008/11/17 Blazej <bl.oleszkiewicz@gmail.com>:

On Mon, Nov 17, 2008 at 3:02 PM, Blazej <bl.oleszkiewicz@gmail.com> wrote:

In my opinion very nice solution is building part of PostgreSQL
database in memory - below it is instruction how to build PostgreSQL
schema in memory in Linux. I tested this with my ROLAP solution for
recalculation MOLAP cubes in memory and then join with master cube
(this speeds up proces about 10 times!!! - but in other solution may
be different).

Just wondering if you compared it to how fast it runs if you've got
lots of shared_buffers and everything fits into memory. That would be
an interesting comparison.

On Mon, 17 Nov 2008, Scott Marlowe wrote:

Just wondering if you compared it to how fast it runs if you've got
lots of shared_buffers and everything fits into memory. That would be
an interesting comparison.

With a large increase in work_mem as well to speed up sorting. If the
bottleneck is write speed on the intermediate results mentioned, then it
may very well be the case that the best way to accelerate this workload is
with a RAM-based tablespace. But in some cases tuning shared_buffers and
work_mem way upwards is all it takes.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

2008/11/18 Scott Marlowe <scott.marlowe@gmail.com>:

On Mon, Nov 17, 2008 at 3:02 PM, Blazej <bl.oleszkiewicz@gmail.com> wrote:

In my opinion very nice solution is building part of PostgreSQL
database in memory - below it is instruction how to build PostgreSQL
schema in memory in Linux. I tested this with my ROLAP solution for
recalculation MOLAP cubes in memory and then join with master cube
(this speeds up proces about 10 times!!! - but in other solution may
be different).

Just wondering if you compared it to how fast it runs if you've got
lots of shared_buffers and everything fits into memory. That would be
an interesting comparison.

They are two small thing becouse I do this how I have described above:
(1) shared_buffers is SHARED;
(2) I needed extremaly speed up so if table stored in hdd it must be
sync (sooner or later - even if sync is off), so this operation is
very slowly on hdd when it compares to ram_disk.

Regards,
Blazej

On Monday 17 November 2008 17:02:54 Blazej wrote:

Of course you must delete schema before shutdown PostgreSQL and OS - I
dont't now how resolve problem with error when the schema was not
deleted? - I have no time to think about it maybe anybody know how to
restore db when the in memory schema was damaged?

based on some similar, uh, experiences i've run across, i'd think easiest
would be to keep a script around with truncate commands for all your tables,
then when you restart, you run that script, which will "fix" your schema for
you. This assumes you're keeping the default table space on hdd, if you lose
the system catalogs, the right answer is "initdb"

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com

On Mon, Nov 17, 2008 at 7:13 PM, Robert Treat
<xzilla@users.sourceforge.net> wrote:

On Monday 17 November 2008 17:02:54 Blazej wrote:

Of course you must delete schema before shutdown PostgreSQL and OS - I
dont't now how resolve problem with error when the schema was not
deleted? - I have no time to think about it maybe anybody know how to
restore db when the in memory schema was damaged?

based on some similar, uh, experiences i've run across, i'd think easiest
would be to keep a script around with truncate commands for all your tables,
then when you restart, you run that script, which will "fix" your schema for
you. This assumes you're keeping the default table space on hdd, if you lose
the system catalogs, the right answer is "initdb"

Heck, you could run PITR to another pgsql instance on the local hard
drives for cheap, and then if things go horribly wrong, you just
reinit the ram based instance and restore it from the hard drive one.

One shouldn't act / believe / have faith that the in store version of
the db is durable. Of course it's not, no machine stays up all the
time without any errors. Even mainframes occasionally suffer
downtime, even if it's some guy hitting the big red switch on accident
during a customer tour of the datacenter.

On 2008-11-17 23:26, Blazej wrote:

CREATE TABLESPACE ram_space LOCATION '/mnt/ram0/pgspace';
And then:
CREATE TABLE (...) TABLESPACE ram_space;
and table is in memory.

And when your server will loose power or hang now then your database
will not start after reboot.

I'd rather start a new, temporary database instance on ramdisk.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

Hello,

I created in-memory database but the problem is all the data will be accessed from main memory .when ever the system is restarted the entire data that is in the tables will lost.Is there any way to dump all the data in to local hard disk before restarting the system or any similar method to save the data in to a permanent storage.

Thanks,
Avin.

________________________________
From: Blazej <bl.oleszkiewicz@gmail.com>
To: avin_friends@yahoo.com
Cc: postgresql Forums <pgsql-general@postgresql.org>
Sent: Monday, November 17, 2008 4:26:46 PM
Subject: Re: [GENERAL] In memory Database for postgres

Sorry I forgot about create tablespace script - this is the SQL script:

CREATE TABLESPACE ram_space LOCATION '/mnt/ram0/pgspace';

And then:

CREATE TABLE (...) TABLESPACE ram_space;

and table is in memory.

Regards,
Blazej

2008/11/17 Blazej <bl.oleszkiewicz@gmail.com>:

aravind chandu wrote:

I created in-memory database but the problem is all the
data will be accessed from main memory .when ever the system is
restarted the entire data that is in the tables will lost.Is there any
way to dump all the data in to local hard disk before restarting the
system or any similar method to save the data in to a permanent storage.

memory is volatile, disk is persistent.

if you want persistent databases, I recommend storing them on disk.

2009/4/12 John R Pierce <pierce@hogranch.com>:

aravind chandu wrote:

           I created in-memory database but the problem is all the data
will be accessed from main memory .when ever the system is restarted the
entire data that is in the tables will lost.Is there any way to dump all the
data in to local hard disk before restarting the system or any similar
method to save the data in to a permanent storage.

memory is volatile, disk is persistent.

if you want persistent databases, I recommend storing them on disk.

ubuntu=# create table test_ram (i integer, name text) tablespace ram_space;
CREATE TABLE
ubuntu=# create temp table test_ram_temp (i integer, name text)
tablespace ram_space;
CREATE TABLE
ubuntu=# create temp table test_disk_temp (i integer, name text);
CREATE TABLE
ubuntu=# create table test_disk (i integer, name text);
CREATE TABLE

ubuntu=# explain analyze insert into test_ram values
(generate_series(1,1000000),random()::text);
QUERY PLAN
---------------------------------------------------------------------------------------------
Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.019..9354.014
rows=1000000 loops=1)
Total runtime: 22836.532 ms
(2 rows)

ubuntu=# explain analyze insert into test_ram_temp values
(generate_series(1,1000000),random()::text);
QUERY PLAN
---------------------------------------------------------------------------------------------
Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7507.349
rows=1000000 loops=1)
Total runtime: 12773.371 ms
(2 rows)

ubuntu=# explain analyze insert into test_disk values
(generate_series(1,1000000),random()::text);
QUERY PLAN
---------------------------------------------------------------------------------------------
Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7948.205
rows=1000000 loops=1)
Total runtime: 16902.042 ms
(2 rows)

ubuntu=# explain analyze insert into test_disk_temp values
(generate_series(1,1000000),random()::text);
QUERY PLAN
---------------------------------------------------------------------------------------------
Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.018..8135.287
rows=1000000 loops=1)
Total runtime: 13716.049 ms
(2 rows)

So, let's see in a brief:

standard table on ram: 22836.532
standard table on disk: 16902.042

temp table on ram: 12773.371
temp table on disk: 13716.049

--
Emanuel Calvo Franco
Sumate al ARPUG !
(www.postgres-arg.org -
www.arpug.com.ar)
ArPUG / AOSUG Member
Postgresql Support & Admin

Hi, where did you put your WAL?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/In-memory-Database-for-postgres-tp1917268p5760845.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general