Thread: tablespace + RAM disk?
We are using 7.4.5 on Solaris 9. We have a couple tables (holding information about network sessions, for instance) which don't need to persist beyond thelife of the server, but while the server is running they are heavily hit, insert/update/delete. Temporary tables won't work for us because they are per-connection, and we are using a thread pool, and session data couldbe accessed from multiple connections. Would 8.0 tablespaces, with a tablespace placed on a RAM disk be a potential solution for this? I have used RAM disks fordisk caches in the past, but I don't know if there are any special issues with defining a tablespace that way. Thanks. - DAP ---------------------------------------------------------------------------------- David Parker Tazz Networks (401) 709-5130
David, > We have a couple tables (holding information about network sessions, for > instance) which don't need to persist beyond the life of the server, but > while the server is running they are heavily hit, insert/update/delete. See the thread this last week on Memcached for a cheaper solution. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
Oh! I sort of started paying attention to that in the middle...and couldn't make head or tail out of it. Will search back to the beginning.... Thanks. - DAP >-----Original Message----- >From: Josh Berkus [mailto:josh@agliodbs.com] >Sent: Friday, November 19, 2004 7:35 PM >To: pgsql-performance@postgresql.org >Cc: David Parker >Subject: Re: [PERFORM] tablespace + RAM disk? > >David, > >> We have a couple tables (holding information about network sessions, >> for >> instance) which don't need to persist beyond the life of the server, >> but while the server is running they are heavily hit, >insert/update/delete. > >See the thread this last week on Memcached for a cheaper solution. > >-- >--Josh > >Josh Berkus >Aglio Database Solutions >San Francisco >
But, I'm also still interested in the answer to my question: is there any reason you could not put an 8.0 tablespace on a RAM disk? I can imagine doing it by having an initdb run at startup somehow, with the idea that having a mix of tablespaces in a database would make this harder, but I haven't read enough about tablespaces yet. The problem with trying to mix a RAM tablespace with a persistent tablespace would seem to be that you would have to recreate select data files at system startup before you could start the database. That's why an initdb seems cleaner to me, but...I should stop talking and go read about tablespaces and memcached. I'd be interested to hear if anybody has tried this. And I will also check out memcached, too, of course. Thanks for the pointer. - DAP >-----Original Message----- >From: pgsql-performance-owner@postgresql.org >[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of >David Parker >Sent: Friday, November 19, 2004 8:34 PM >To: josh@agliodbs.com; pgsql-performance@postgresql.org >Subject: Re: [PERFORM] tablespace + RAM disk? > >Oh! I sort of started paying attention to that in the >middle...and couldn't make head or tail out of it. Will search >back to the beginning.... > >Thanks. > >- DAP > >>-----Original Message----- >>From: Josh Berkus [mailto:josh@agliodbs.com] >>Sent: Friday, November 19, 2004 7:35 PM >>To: pgsql-performance@postgresql.org >>Cc: David Parker >>Subject: Re: [PERFORM] tablespace + RAM disk? >> >>David, >> >>> We have a couple tables (holding information about network >sessions, >>> for >>> instance) which don't need to persist beyond the life of >the server, >>> but while the server is running they are heavily hit, >>insert/update/delete. >> >>See the thread this last week on Memcached for a cheaper solution. >> >>-- >>--Josh >> >>Josh Berkus >>Aglio Database Solutions >>San Francisco >> > >---------------------------(end of >broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster >
On Fri, 19 Nov 2004 23:18:51 -0500, David Parker
<dparker@tazznetworks.com> wrote:
> But, I'm also still interested in the answer to my question: is there
> any reason you could not put an 8.0 tablespace on a RAM disk?
>
> I can imagine doing it by having an initdb run at startup somehow, with
> the idea that having a mix of tablespaces in a database would make this
> harder, but I haven't read enough about tablespaces yet. The problem
> with trying to mix a RAM tablespace with a persistent tablespace would
> seem to be that you would have to recreate select data files at system
> startup before you could start the database. That's why an initdb seems
> cleaner to me, but...I should stop talking and go read about tablespaces
> and memcached.
I think there might be a problem with recovery after crash. I haven't tested
it but I guess pgsql would complain that databases which existed before
crash (or even server reboot) no longer exist. And I see two options, either
it would complain loudly and continue, or simply fail... Unless there would
be option to mark database/schema/table as non-PITR-logged (since data
is expendable and can be easily recreated)... :)
Having tablespaces on RAM disks (like tmpfs), hmm, it could be useful,
say to put TEMPORARY tables there. Since they will be gone nonetheless,
its a nice place for them.
Side question: Do TEMPORARY tables operations end up in PITR log?
Regards,
Dawid
PS: To pgmemchache I go!
David, > But, I'm also still interested in the answer to my question: is there > any reason you could not put an 8.0 tablespace on a RAM disk? Some people have *talked* about trying it, but nobody yet has reported back. I can see a few potential problems: 1) The query planner would not be aware, and could not be made aware short of hacking the source, that one tablespace has different access speeds than the others; 2) After a crash, you might be unable to recover that tablespace, and PG would refuse to bring the system back up without it. However, the best thing to do is to try it. Good luck, and do a write-up for us! -- Josh Berkus Aglio Database Solutions San Francisco
Dawid Kuroczko wrote: > Side question: Do TEMPORARY tables operations end up in PITR log? No. -Neil