From: Brian Hurt <bhurt@janestcapital.com>
To: cyber-postgres@midnightfantasy.com
Cc: pgsql-performance@postgresql.org
Subject: Re: 121+ million record table perf problems
Date: Fri, 18 May 2007 14:53:42 -0400
Message-ID: <464DF636.40104@janestcapital.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0705181215160.23563@cerberus.midnightfantasy.com>
References: <Pine.LNX.4.64.0705181215160.23563@cerberus.midnightfantasy.com>
cyber-postgres@midnightfantasy.com wrote:
> I need some help on recommendations to solve a perf problem.
>
> I've got a table with ~121 million records in it. Select count on it
> currently takes ~45 minutes, and an update to the table to set a value
> on one of the columns I finally killed after it ran 17 hours and had
> still not completed. Queries into the table are butt slow, and
>
This is way too long. I just did a select count(*) on a table of mine
that has 48 million rows and it took only 178 seconds. And this is on a
serious POS disk subsystem that's giving me about 1/2 the read speed of
a single off the shelf SATA disk.
As select count(*) has to read the whole table sequentially, the time it
takes is linear with the size of the table (once you get large enough
that the whole table doesn't get cached in memory). So I'd be surprised
if a 121 million record table took more than 500 or so seconds to read,
and would expect it to be less.
So my advice: vacuum. I'll bet you've got a whole boatload of dead
tuples kicking around. Then analyze. Then consider firing off a
reindex and/or cluster against the table. The other thing I'd consider
is dropping the money on some more hardware- a few hundred bucks to get
a battery backed raid card and half a dozen SATA drives would probably
do wonders for your performance.
>
> shared_buffers = 24MB
Up your shared buffers. This is a mistake I made originally as well-
but this is the total number of shared buffers used by the system. I
had originally assumed that the number of shared buffers used was this
times the number of backends, but it's not.
With 2G of memory, I'd start with shared buffers of 512MB, and consider
upping it to 768MB or even 1024MB. This will also really help performance.
> stats_start_collector = off
> stats_row_level = off
>
I think I'd also recommend turning these one.
Brian
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: bhurt@janestcapital.com, cyber-postgres@midnightfantasy.com
Subject: Re: 121+ million record table perf problems
In-Reply-To: <464DF636.40104@janestcapital.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