Thanks for the help from everyone on this. Further investigation with
the suggested statistics and correlating that with some IO graphs
pretty much nailed the problem down to checkpoint IO holding things
up, and tuning the checkpoint segments and completion target (128 and
0.9 seemed to be the best
On 2008-04-22 23:46, David Wilson wrote:
> Upping the segments to 50, timeout to 30m and completion target to
> 0.9 has improved average copy time to between 2 and 10 seconds, which
> is definitely an improvement.
I'd up them to 128 (or even 256) and set completion target back to 0.5.
But make su
On Tue, 2008-04-22 at 18:46 -0400, David Wilson wrote:
> I certainly expect some slowdown, given that I have indices that I
> can't drop (as you indicate above). Having been watching it now for a
> bit, I believe that the checkpoint settings were the major cause of
> the problem, however. Changing
Greg Smith <[EMAIL PROTECTED]> writes:
> ...This is a bit out of my area, but after reading the rest of this thread I
> wonder whether raising the default_statistics_target parameter a bit might
> reduce the instances of bad plans showing up.
On the evidence so far, it doesn't seem that David's
On Tue, 22 Apr 2008, David Wilson wrote:
Is there a way to get the size of a specific index, on that note?
select pg_size_pretty(pg_relation_size('index_name')) works for me.
There's a neat article on other things you can look at like this at
http://andreas.scherbaum.la/blog/archives/282-tab
On Tue, 22 Apr 2008, David Wilson wrote:
My guess at this point is that I'm just running into index update
times and checkpoint IO. The only thing that still seems strange is
the highly variable nature of the COPY times- anywhere from <1.0
seconds to >20 seconds, with an average probably around
On Tue, Apr 22, 2008 at 7:33 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> What have you got shared_buffers set to? If it's not enough to cover
> the working set for your indexes, that might be the (other) problem.
>
shared_buffers = 1536MB
Is there a way to get the size of a specific index, on
"David Wilson" <[EMAIL PROTECTED]> writes:
> My guess at this point is that I'm just running into index update
> times and checkpoint IO. The only thing that still seems strange is
> the highly variable nature of the COPY times- anywhere from <1.0
> seconds to >20 seconds, with an average probably
On Tue, Apr 22, 2008 at 6:10 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "David Wilson" <[EMAIL PROTECTED]> writes:
>
> Are you loading any tables that are the targets of foreign key
> references from other tables being loaded? If so, I'd bet on
> Scott's theory being correct with respect to the
"David Wilson" <[EMAIL PROTECTED]> writes:
> Foreign keys are definitely indexed (actually referencing a set of
> columns that the foreign table is UNIQUE on).
Are you loading any tables that are the targets of foreign key
references from other tables being loaded? If so, I'd bet on
Scott's theor
On Apr 22, 2008, at 4:46 PM, David Wilson wrote:
On Tue, Apr 22, 2008 at 5:18 PM, Scott Marlowe <[EMAIL PROTECTED]
> wrote:
Try upping your checkpoint segments. Some folks find fairly large
numbers like 50 to 100 to be helpful. Each segment = 16Megs, so be
sure not to run your system out of
On Tue, Apr 22, 2008 at 5:18 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
>
> Try upping your checkpoint segments. Some folks find fairly large
> numbers like 50 to 100 to be helpful. Each segment = 16Megs, so be
> sure not to run your system out of drive space while increasing it.
>
Ahh, muc
On Tue, Apr 22, 2008 at 3:15 PM, David Wilson <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 22, 2008 at 5:04 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> > Normally, after the first 50,000 or so the plan won't likely change
> > due to a new analyze, so you could probably just analyze after 50k or
On Tue, Apr 22, 2008 at 5:04 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> Normally, after the first 50,000 or so the plan won't likely change
> due to a new analyze, so you could probably just analyze after 50k or
> so and get the same performance. If the problem is a bad plan for the
> inse
On Tue, Apr 22, 2008 at 2:59 PM, David Wilson <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 22, 2008 at 4:38 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> > The best bet is to issue an "analyze table" (with your table name in
> > there, of course) and see if that helps. Quite often the real issue
On Tue, Apr 22, 2008 at 4:38 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> The best bet is to issue an "analyze table" (with your table name in
> there, of course) and see if that helps. Quite often the real issue
> is that pgsql is using a method to insert rows when you have 10million
> of t
On Tue, Apr 22, 2008 at 2:31 PM, David Wilson <[EMAIL PROTECTED]> wrote:
> I have a fairly simple table (a dozen real/integer columns, a few
> indexes, one foreign key reference) with ~120m rows. Periodically the
> table is truncated or dropped and recreated and the data is
> regenerated (slight
I have a fairly simple table (a dozen real/integer columns, a few
indexes, one foreign key reference) with ~120m rows. Periodically the
table is truncated or dropped and recreated and the data is
regenerated (slightly different data, of course, or the exercise would
be rather pointless). The regene
18 matches
Mail list logo
