From: Josh Berkus <josh@agliodbs.com>
To: Samuel J. Sutjiono <ssutjiono@wc-group.com>
To: pgsql-sql@postgresql.org
Subject: Re: Transactional vs. Read-only (Retrieval) database
Date: Fri, 12 Apr 2002 10:12:04 -0700
Message-ID: <web-1320478@davinci.ethosmedia.com> (raw)
In-Reply-To: <00ce01c1e231$929619f0$110a010a@headquarters.wcgroup.com>
Samuel,
> About managing disk access and memory, do you know how to ensure the
> "read-only (to do the search on)" tables(i.e. product catalog) loaded
> and
> stay loaded in memory (provided buffer size is big enough for those
> tables)
> ? Are you familiar with RAMDISK ?
Yeah, I know about doing this with MySQL. However, Postgres is a
transactional database (MySQL is not), and as such it's a little
trickier. My suggestion would be to create the RAMDISK as a mounted
volume, and then copy the tables there. For that matter, I'd put your
transaction log (WAL_FILES) on a different RAMDISK, which would speed
up reads and writes considerably.
As far as I know, nobody has ever tried this before. It would be
extremely risky ... if you lost power, the entire database would be
dead and not retrieveable except from backup ... but in theory could be
very, very fast. A much less experimental approach would be to install
a really good RAID array for your machine on a very fast SCSI
controller, or two.
Where you go from here depends on what you have more of ... time or
money. If it's time, please experiment with RAMDISKs and write up your
tests for Techdocs. You can get a considerable amount of help from the
lists if you promise to write up your results as a HOWTO. If you have
more money than time, hire PostgreSQL Inc. or RedHat to help you build
the server and maximize performance.
If you have neither time nor money, I'd say use MySQL on a RAMDISK for
the read-only tables and do your joins in your interface code. The
RAMDISK technique is well documented and tested on MySQL, and after all
that's what MySQL was built for ... fast, read-only access.
> On different subject, do you know how to monitor the number of
> connections
> in PostgreSQL ?
Depends on how you're connecting.
-Josh Berkus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, ssutjiono@wc-group.com
Subject: Re: Transactional vs. Read-only (Retrieval) database
In-Reply-To: <web-1320478@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox