Re: Scaling concerns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux