From: tsuraan <tsuraan@gmail.com>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Scaling concerns
Date: Sun, 17 Dec 2006 22:36:50 -0600
Message-ID: <84fb38e30612172036s74183c5ey930eb4f608578f81@mail.gmail.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0612171856150.17147@westnet.com>
References: <84fb38e30612160926l609ec55cs352b2495289bae0c@mail.gmail.com>
<20061217032527.GL29195@andi-lap.la.revver.com>
<84fb38e30612171359r6288de69y2553be256019ba4b@mail.gmail.com>
<Pine.GSO.4.64.0612171856150.17147@westnet.com>
> http://www.powerpostgresql.com/Downloads/annotated_conf_80.html is a good
> intro to the various parameters you might set, with some valuable hints on
> the effective range you should be considering. I'd suggest you use that
> to identify the most likely things to increase, then read the manuals at
> http://www.postgresql.org/docs/8.2/interactive/runtime-config.html for
> more detail on what you're actually adjusting. To get you started,
> consider increasing effective_cache_size, checkpoint_segments, and
> work_mem; those are three whose defaults are very low for your
I'll play with these - I've definitely seen the (for me) confusing use
of seqscan rather than index scan that the annotated page says is
caused by too little effective_cache_size. That first link is really
great; I can't believe I've never seen it before.
> One big RAID 5 volume is probably the worst setup available for what
> you're doing. Luke already gave you a suggestion for testing write speed;
> you should run that test, but I wouldn't expect happy numbers there. You
I've run dstat with a really busy postgres and seen 94 MB read and
write simultaneously for a few seconds. I think our RAID cards have
16MB of RAM, so unless it was really freakish, I probably wasn't
seeing all cache access. I'll try the some tests with dd tomorrow
when I get to work.
> might be able to get by with the main database running like that, but
> think about what you'd need to do to add more disks (or reorganize the
> ones you have) so that you could dedicate a pair to a RAID-1 volume for
> holding the WAL. If you're limited by write performance, I think you'd
> find adding a separate WAL drive set a dramatically more productive
> upgrade than trying to split the app to another machine. Try it on your
> home machine first; that's a cheap upgrade, to add another SATA drive to
> there, and you should see a marked improvement (especially once you get
> the server parameters set to more appropriate values).
Is the WAL at the same location as the xlog (transaction log?)? The
checkpoint_segments doc says increasing that value is really only
useful if the xlog is separate from the data, so do I put both WAL and
xlog on the separate drive, or is that automatic (or redundant; I
don't know what I'm talking about...)?
> I'd also suggest that you'd probably be able to get more help from people
> here if you posted a snippet of output from vmstat and iostat -x with a
> low interval (say 5 seconds) during a period where the machine was busy;
> that's helpful for figuring out where the bottleneck on your machine
> really is.
I'll try to stress a machine and get some real stats soon.
> Do you have the exact text of the error? I suspect you're falling victim
> to the default parameters being far too low here as well, but without the
> error it's hard to know exactly which.
Well, I tried to repeat it on my home machine with 20 million rows,
and it worked fine in about two minutes. I'll have to see what's
going on on that other system...
Thanks for the help!
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: tsuraan@gmail.com, gsmith@gregsmith.com
Subject: Re: Scaling concerns
In-Reply-To: <84fb38e30612172036s74183c5ey930eb4f608578f81@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