From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
To: Jonathan Hoover <jhoover@yahoo-inc.com>
Subject: Re: Disk Performance Problem on Large DB
Date: Thu, 04 Nov 2010 16:03:35 -0500
Message-ID: <4CD2D95702000025000372EC@gw.wicourts.gov> (raw)
In-Reply-To: <68C107706843A54BA7D9CAEF123ADF05010346C85A@SP2-EX07VS03.ds.corp.yahoo.com>
References: <68C107706843A54BA7D9CAEF123ADF05010346C85A@SP2-EX07VS03.ds.corp.yahoo.com>
"Jonathan Hoover" <jhoover@yahoo-inc.com> wrote:
> I have a simple database, with one table for now. It has 4
> columns:
>
> anid serial primary key unique,
> time timestamp,
> source varchar(5),
> unitid varchar(15),
> guid varchar(32)
>
> There is a btree index on each.
>
> I am loading data 1,000,000 (1M) rows at a time using psql and a
> COPY command. Once I hit 2M rows, my performance just drops out
Drop the indexes and the primary key before you copy in.
Personally, I strongly recommend a VACUUM FREEZE ANALYZE after the
bulk load. Then use ALTER TABLE to restore the primary key, and
create all the other indexes.
Also, if you don't mind starting over from initdb if it crashes
partway through you can turn fsync off. You want a big
maintenance_work_mem setting during the index builds -- at least
200 MB.
-Kevin
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: Kevin.Grittner@wicourts.gov, jhoover@yahoo-inc.com
Subject: Re: Disk Performance Problem on Large DB
In-Reply-To: <4CD2D95702000025000372EC@gw.wicourts.gov>
* 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