Re: [GENERAL] In memory Database for postgres
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
Re: [GENERAL] In memory Database for postgres
If you need to run some database really fast, try to put only all your indexes onto ram disk. Look here... http://www.linux.com/feature/142658 They use SSD to store indexes (not data) for postgresql. A think the same conclusions should apply for ram disk too. And in wrost case (power off for RAM disk or wear out for SSD) you need only a reindex to build your indexes again Scott Marlowe napsal(a): On Mon, Apr 13, 2009 at 2:06 PM, aravind chandu wrote: Hello, Thanks for your reply,but what I am actually looking for is database should be an in-memory database and at the same i want to store that data into disk so that data won't be lost when the system restarts or in case of power failure. Can you guys tell me the procedure how to do this? your help will he greatly appreciated. But that's just the point people have been making. A small enough db will be cached completely in memory, and the only time you'll have to access the disks is the first read, and during writes, which can be made to happen mostly after the fact and not influence the rest of the db. You're trying to reinvent a wheel to solve a non-existent problem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
On Mon, 2009-04-13 at 17:36 -0300, Emanuel Calvo Franco wrote: > >Thanks for your reply,but what I am actually looking for is > > database should be an in-memory database and at the same i want to store > > that data into disk so that data won't be lost when the system restarts or > > in case of power failure. Can you guys tell me the procedure how to do this? > > your help will he greatly appreciated. > > If you want the writes to be preserved across shutdown, the writes must go to disk. If that's too expensive, and you are only concerned about preserving the writes after a clean shutdown, you can turn off fsync (but then your data will be corrupt after a crash). The reads will usually come from memory anyway (as long as you have enough memory), even if the tables are stored on disk. So what's wrong with just using normal tables? > So you can make the tables on memory and with a trigger update on disk... > you can call this inverted materialized views (because in general you > update the views on memory but not in the disk). How does that help? Don't you have the same number of disk writes that way? > But there is a problem... if you update on memory and a shutdown > occurs and the trigger didn't start... you lost this record :( I must > say that you > Triggers are transactional. Either they all fire, and all the updates happen, or none do. Aravind, Scott asked the most important question: what problem are you trying to solve? Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
On Mon, Apr 13, 2009 at 2:06 PM, aravind chandu wrote: > > > Hello, > > Thanks for your reply,but what I am actually looking for is > database should be an in-memory database and at the same i want to store > that data into disk so that data won't be lost when the system restarts or > in case of power failure. Can you guys tell me the procedure how to do this? > your help will he greatly appreciated. But that's just the point people have been making. A small enough db will be cached completely in memory, and the only time you'll have to access the disks is the first read, and during writes, which can be made to happen mostly after the fact and not influence the rest of the db. You're trying to reinvent a wheel to solve a non-existent problem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
2009/4/13 aravind chandu : > > > Hello, > > Thanks for your reply,but what I am actually looking for is > database should be an in-memory database and at the same i want to store > that data into disk so that data won't be lost when the system restarts or > in case of power failure. Can you guys tell me the procedure how to do this? > your help will he greatly appreciated. > > Thanks, > Avin. > Oh, well... there is a way but is not recomended. So you can make the tables on memory and with a trigger update on disk... you can call this inverted materialized views (because in general you update the views on memory but not in the disk). But there is a problem... if you update on memory and a shutdown occurs and the trigger didn't start... you lost this record :( I must say that you That's because i prefer have a database in disk and have updatable views in memory :) -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support & Admin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
2009/4/12 John R Pierce : > 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,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.019..9354.014 rows=100 loops=1) Total runtime: 22836.532 ms (2 rows) ubuntu=# explain analyze insert into test_ram_temp values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7507.349 rows=100 loops=1) Total runtime: 12773.371 ms (2 rows) ubuntu=# explain analyze insert into test_disk values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7948.205 rows=100 loops=1) Total runtime: 16902.042 ms (2 rows) ubuntu=# explain analyze insert into test_disk_temp values (generate_series(1,100),random()::text); QUERY PLAN - Result (cost=0.00..0.02 rows=1 width=0) (actual time=0.018..8135.287 rows=100 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
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 To: avin_frie...@yahoo.com Cc: postgresql Forums 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 : > 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 : >> 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. >> >> >
Re: [GENERAL] In memory Database for postgres
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
On Mon, Nov 17, 2008 at 7:13 PM, Robert Treat <[EMAIL PROTECTED]> 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
Re: [GENERAL] In memory Database for postgres
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
2008/11/18 Scott Marlowe <[EMAIL PROTECTED]>: > On Mon, Nov 17, 2008 at 3:02 PM, Blazej <[EMAIL PROTECTED]> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
On Mon, Nov 17, 2008 at 3:02 PM, Blazej <[EMAIL PROTECTED]> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 <[EMAIL PROTECTED]>: > 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 <[EMAIL PROTECTED]>: >> 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. >> >> > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
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 <[EMAIL PROTECTED]>: > 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. > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In memory Database for postgres
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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. <<
Re: [GENERAL] In memory Database for postgres
On Mon, Nov 17, 2008 at 12:11 PM, aravind chandu <[EMAIL PROTECTED]> 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
Re: [GENERAL] In memory Database for postgres
2008/11/18 aravind chandu <[EMAIL PROTECTED]>: > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
