From: Scott Marlowe <scott.marlowe@gmail.com>
To: Alex Hochberger <alex@dsgi.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hard Drive Usage for Speeding up Big Queries
Date: Mon, 28 Jan 2008 09:19:48 -0600
Message-ID: <dcc563d10801280719mf411d0el7b2b49df2e5744fc@mail.gmail.com> (raw)
In-Reply-To: <D797CB07-C49D-4D35-B0FC-397F1E50A303@dsgi.us>
References: <20080125101607.45E7E2C9462@maia-2.hub.org>
<D797CB07-C49D-4D35-B0FC-397F1E50A303@dsgi.us>
On Jan 28, 2008 7:54 AM, Alex Hochberger <alex@dsgi.us> wrote:
> We are trying to optimize our Database server without spending a
> fortune on hardware. Here is our current setup.
>
> Main Drive array: 8x 750 GB SATA 2 Drives in a RAID 10 Configuration,
> this stores the OS, Applications, and PostgreSQL Data drives. 3 TB
> Array, 2 TB Parition for PostgreSQL.
> Secondary drive array: 2x 36 GB SAS 15000 RPM Drives in a RAID 1
> Configuration: the pg_xlog directory, checkpoints set to use about 18
> GB max, this way when massive numbers of small writes occur, they
> don't slow the system down. Drive failure loses no data. Checkpoints
> will be another matter, hope to keep under control with bgwriter
> tweaking.
>
SNIP
> However, the joins of two 50GB tables really just can't be solved in
> RAM without using drive space. My question is, can hardware speed
> that up? Would putting a 400 GB SAS Drive (15000 RPM) in just to
> handle PostgreSQL temp files help? Considering it would store "in
> process" queries and not "completed transactions" I see no reason to
> mirror the drive. If it fails, we'd simply unmount it, replace it,
> then remount it, it could use the SATA space in the mean time.
>
> Would that speed things up, and if so, where in the drive mappings
> should that partition go?
Do you have a maintenance window to experiment in? Try putting it on
the pg_xlog array to see if it speeds up the selects during one. Then
you'll know. I'm thinking it will help a little, but there's only so
much you can do with 50g result sets.
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-performance@postgresql.org
Cc: scott.marlowe@gmail.com, alex@dsgi.us
Subject: Re: Hard Drive Usage for Speeding up Big Queries
In-Reply-To: <dcc563d10801280719mf411d0el7b2b49df2e5744fc@mail.gmail.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