agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Chris Albertson <chrisja@jps.net>
To: Joe Conway <jconway2@home.com>
Cc: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>
Subject: Re: performance
Date: Tue, 04 Apr 2000 23:28:01 -0700
Message-ID: <38EADCF1.FFD8F30F@jps.net> (raw)
References: <01BF9E7F.E867DFA0@JEC-NT1>
I'm working with some large tables too. Around 10x your size and due to become
maybe 20x larger. The good news is that Linux will use all the "extra" RAM it
has for a disk cache. You don't have to do anything. Postgresql has it's
own cache too. Use the "-B" option to make this buffer cache large. I use
-B10000. Also use the "-F" option to turn off the fsync and buy an UPS for
the computer. The biggest performance boost I got is when I discoved that the
COPY command is an order of magnitude faster then INSERT. Experiment with
indexies to speed querries. Experiment usually there are several ways to
write a query. One way may be faster.
The "top" display is a big help while tunning your system. Your goal is
to get the CPU(s) to near 100% utilization. If there is much idle CPU time
it means you are I/O bound and could use more RAM or a bigger -B value.
Joe Conway wrote:
>
> Hello,
>
> I'm currently working with a development database, PostgreSQL 6.5.2 on RedHat 6.1 Linux. There is one fairly large table (currently ~ 1.3 million rows) which will continue to grow at about 500k rows per week (I'm considering various options to periodically archive or reduce the collected data). Is there anything I can do to cache some or all of this table in memory in order to speed queries against it? The physical file is about 130 MB. The server is a dual Pentium Pro 200 with 512 MB of RAM.
>
> Any suggestions would be appreciated.
>
> Joe Conway
>
> p.s. I tried to search the archives, but it did not return any results with even the simplest of searches.
--
--Chris Albertson home: chrisja@jps.net
Redondo Beach, California work: calbertson@logicon.com
view thread (16+ messages) latest in thread
Message-ID: <38EADCF1.FFD8F30F@jps.net>
Permalink: ../38EADCF1.FFD8F30F@jps.net/
Also on: postgresql.org/message-id/38EADCF1.FFD8F30F@jps.net
reply
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-admin@postgresql.org
Cc: chrisja@jps.net, jconway2@home.com
Subject: Re: performance
In-Reply-To: <38EADCF1.FFD8F30F@jps.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox