Thread: [pgsql-general] In memory tables/databases
Hello, is there anything to emulate the MySQL memory table engine? A straight forward solution is to create a ramfs volume and mount/link content under /var/lib/postresql there. Then add some scripts to save/restore databases when the server restarts. I am wondering is there something else? Greetings, Alexander
"Alexander Todorov" <alexx.todorov@gmail.com> writes:
> is there anything to emulate the MySQL memory table engine?
> A straight forward solution is to create a ramfs volume and mount/link
> content under /var/lib/postresql there. Then add some scripts to
> save/restore databases when the server restarts.
> I am wondering is there something else?
As long as shared_buffers is high enough, there doesn't seem to be much
point in worrying about this; the incremental performance gain will be
minimal since everything will be in RAM anyway. Or do you think losing
the content of the database at server crash is a feature?
regards, tom lane
On 7/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > As long as shared_buffers is high enough, there doesn't seem to be much > point in worrying about this; the incremental performance gain will be > minimal since everything will be in RAM anyway. Yes it will be but this does not mean there will be no disk i/o operations. Database contents still have to be backed up on disk (unless there is a mechanism of delayed wrtite to disk which I am not aware of). The memory engine as designed by MySQL (my interpretation) is to avoid the disk operations. > Or do you think losing > the content of the database at server crash is a feature? Yes it is. Anything designed to live in memory should be used to hold non vital information. The loosing/recreation of this information is implied by design of the application. One example is bittorent trackers which maintain data about the connected peers. Since connections are created/destroyed and there are more selects than insert/updates these applications use memory tables. Greetings, Alexander.
"Alexander Todorov" <alexx.todorov@gmail.com> writes:
> On 7/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> As long as shared_buffers is high enough, there doesn't seem to be much
>> point in worrying about this; the incremental performance gain will be
>> minimal since everything will be in RAM anyway.
> Yes it will be but this does not mean there will be no disk i/o
> operations. Database contents still have to be backed up on disk
> (unless there is a mechanism of delayed wrtite to disk which I am not
> aware of).
It's called a checkpoint.
Assuming that you would actually like your changes to get saved
someplace, I doubt you are going to be able to improve efficiency
by replacing the existing write mechanisms by some ad-hoc
application-level backup procedure. That's why I asked if you
thought losing data at crash was a feature, as opposed to a severe
demerit that you put up with in the hope of gaining some performance
--- because unless that's what you think, it's probably not a real
useful path to pursue.
regards, tom lane
On 7/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > That's why I asked if you thought losing data at crash was a feature Yes it is. I don't want to actually save the data on disk.
Alexander Todorov escribió: > On 7/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >That's why I asked if you thought losing data at crash was a feature > Yes it is. I don't want to actually save the data on disk. So mount a ramdisk and initdb in there. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)
On 7/1/07, Alvaro Herrera <alvherre@commandprompt.com> wrote: > So mount a ramdisk and initdb in there. As I wrote in my first post that is the straight forward approach. The question was is there something else that exists in PostgreSQL and will do the same job.
Alexander Todorov escribió: > On 7/1/07, Alvaro Herrera <alvherre@commandprompt.com> wrote: > >So mount a ramdisk and initdb in there. > > As I wrote in my first post that is the straight forward approach. > The question was is there something else that exists in PostgreSQL and > will do the same job. What for, already there being a way? -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "The important things in the world are problems with society that we don't understand at all. The machines will become more complicated but they won't be more complicated than the societies that run them." (Freeman Dyson)
On Sun, Jul 01, 2007 at 03:55:11PM -0400, Alvaro Herrera wrote: > Alexander Todorov escribió: > > On 7/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > >That's why I asked if you thought losing data at crash was a > > >feature > > Yes it is. I don't want to actually save the data on disk. > So mount a ramdisk and initdb in there. You could also put a tablespace on a ramdisk and create the table there. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Sun, Jul 01, 2007 at 03:55:11PM -0400, Alvaro Herrera wrote:
>> So mount a ramdisk and initdb in there.
> You could also put a tablespace on a ramdisk and create the table
> there.
The fresh-initdb approach is more likely to work without any strange
corner cases. If you try a setup where the system catalogs are on
persistent storage but you have a tablespace on ramdisk, then after
restart you'll have pg_class entries referencing files that don't exist
anymore, which I believe will provoke errors.
Also, I doubt the OP wants his WAL on real storage either ...
regards, tom lane
David Fetter escribió: > On Sun, Jul 01, 2007 at 03:55:11PM -0400, Alvaro Herrera wrote: > > Alexander Todorov escribió: > > > On 7/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > >That's why I asked if you thought losing data at crash was a > > > >feature > > > Yes it is. I don't want to actually save the data on disk. > > So mount a ramdisk and initdb in there. > > You could also put a tablespace on a ramdisk and create the table > there. But this would still cause WAL traffic. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On 7/2/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > David Fetter <david@fetter.org> writes: > > On Sun, Jul 01, 2007 at 03:55:11PM -0400, Alvaro Herrera wrote: > >> So mount a ramdisk and initdb in there. > > > You could also put a tablespace on a ramdisk and create the table > > there. Thanks for this hint. That looks like what I was looking for. > > The fresh-initdb approach is more likely to work without any strange > corner cases. If you try a setup where the system catalogs are on > persistent storage but you have a tablespace on ramdisk, then after > restart you'll have pg_class entries referencing files that don't exist > anymore, which I believe will provoke errors. I believe error will occur if trying to access these objects. To avoid this pg_dump/pg_restore may be useful and recreating the tables/indexes after restart. This will emulate the MySQL behaviour where tables definitions is kept on disk and contents kept in memory.
"Alexander Todorov" <alexx.todorov@gmail.com> writes:
> On 7/2/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The fresh-initdb approach is more likely to work without any strange
>> corner cases. If you try a setup where the system catalogs are on
>> persistent storage but you have a tablespace on ramdisk, then after
>> restart you'll have pg_class entries referencing files that don't exist
>> anymore, which I believe will provoke errors.
> I believe error will occur if trying to access these objects. To avoid
> this pg_dump/pg_restore may be useful and recreating the
> tables/indexes after restart.
You might as well start with a freshly initdb'd cluster (all on ramdisk)
and do pg_restore from a full dump instead of a data-only dump. The
former will probably be faster as well as more foolproof.
regards, tom lane
On Sun, Jul 01, 2007 at 11:11:30PM +0200, Alexander Todorov wrote:
> The question was is there something else that exists in PostgreSQL and
> will do the same job.
Why re-invent the wheel, and make it square? But also, if you don't
care whether you keep your data, why on earth are you putting it in
an RDBMS? Is it because all your pre-built tools already speak SQL?
If you're really after performance, I'm not convinced a SQL-speaking
RDBMS (delivered by MySQL or Postgres or anyone else) is what you
actually need.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.
--George Orwell
On Jul 5, 2007, at 13:20 , Andrew Sullivan wrote: > On Sun, Jul 01, 2007 at 11:11:30PM +0200, Alexander Todorov wrote: >> The question was is there something else that exists in PostgreSQL >> and >> will do the same job. > > Why re-invent the wheel, and make it square? But also, if you don't > care whether you keep your data, why on earth are you putting it in > an RDBMS? Is it because all your pre-built tools already speak SQL? > If you're really after performance, I'm not convinced a SQL-speaking > RDBMS (delivered by MySQL or Postgres or anyone else) is what you > actually need. Look- there are plenty of scenarios where data one does not care about is linked (in a relational fashion) to data one does care about. One common example is a web session. If your database fails, then the sessions are really irrelevant in the future. Another example is a live queue or snapshot of other data (materialized views?). As long as the database is running, then the data is useful. Such a table can contain foreign keys but no triggers and may not have references to it from "non-temp" tables. Why not have a table type that writes no WAL and is truncated whenever postgres starts? Such a table could then be put in a ramdisk tablespace and there would be no transaction atomicity repercussions. Is there something I'm missing? Claiming that postgresql is simply the wrong tool is silly, especially since it is so close to having the desired behavior. Cheers, M
A.M. wrote:
>
> On Jul 5, 2007, at 13:20 , Andrew Sullivan wrote:
>
>> On Sun, Jul 01, 2007 at 11:11:30PM +0200, Alexander Todorov wrote:
>>> The question was is there something else that exists in PostgreSQL and
>>> will do the same job.
> Why not have a table type that writes no WAL and is truncated whenever
> postgres starts? Such a table could then be put in a ramdisk tablespace
> and there would be no transaction atomicity repercussions. Is there
> something I'm missing?
>
> Claiming that postgresql is simply the wrong tool is silly, especially
> since it is so close to having the desired behavior.
Use pg_memcache.
Sincerely,
Joshua D. Drake
P.S. I agree with you, but you are barking up a very tall tree and you
don't have a chainsaw. The fact is, global temp tables that could be
assigned a static amount of memory to use that would recycle based on
some parameter would be infinitely useful, but likely won't get anywhere.
>
> Cheers,
> M
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
> Why not have a table type that writes no WAL and is truncated whenever
> postgres starts? Such a table could then be put in a ramdisk tablespace
> and there would be no transaction atomicity repercussions. Is there
> something I'm missing?
Is this not in the TODO (if not already scheduled for next version ?)
Check ALTER TABLE SET PERSISTENCE ...
On Thursday 05 July 2007 13:52, Joshua D. Drake wrote:
> A.M. wrote:
> > On Jul 5, 2007, at 13:20 , Andrew Sullivan wrote:
> >> On Sun, Jul 01, 2007 at 11:11:30PM +0200, Alexander Todorov wrote:
> >>> The question was is there something else that exists in PostgreSQL and
> >>> will do the same job.
> >
> > Why not have a table type that writes no WAL and is truncated whenever
> > postgres starts? Such a table could then be put in a ramdisk tablespace
> > and there would be no transaction atomicity repercussions. Is there
> > something I'm missing?
> >
<snip>
>
> P.S. I agree with you, but you are barking up a very tall tree and you
> don't have a chainsaw. The fact is, global temp tables that could be
> assigned a static amount of memory to use that would recycle based on
> some parameter would be infinitely useful, but likely won't get anywhere.
>
I used to think that, but after looking into what the spec thinks global temp
tables should behave like, I'm less inclined to agree. Currently I'm back
to fancying something akin to Oracle's NoLogging option, where (in pg terms)
the oid of a table would be marked so any further DML type information
generated from that table is simply ignored.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL