Обсуждение: 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.

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

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>:
> 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 .
>
> Thanks,
> Avin.
>
>

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>:
> 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>:
>> 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 .
>>
>> Thanks,
>> Avin.
>>
>>
>

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.

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

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.

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

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.

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

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

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.