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