Re: Queries seldomly take >4s while normally take <1ms?

On Tue, Apr 9, 2013 at 12:37 PM, Steven Schlansker <steven(at)likeness(dot)com>wrote:

>
> On Apr 9, 2013, at 11:25 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> wrote:
>
> > One of the most common causes I've seen for this is linux's vm.*dirty*
> settings to get in the way. Like so many linux kernel "optimizations" this
> one looks good on paper but gives at best middling improvements with
> occasional io storms that block everything else. On big mem machines doing
> a lot of writing IO I just set these to 0. Also tend to turn off swap as
> well as it's known to get in the way as well.
> >
> > settings for /etc/sysctl.conf
> > vm.dirty_background_ratio = 0
> > vm.dirty_ratio = 0
> >
>
> I'll +1 on the "you have to tune your Linux install" advice.
>
> I found the "PostgreSQL 9.0 High Performance" book to be worth its weight
> in gold. A few days spent with the book and research on mailing lists
> improved our PostgreSQL performance multiple times over, and responsiveness
> under load by orders of magnitude.
>
>
> http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Smith/dp/184951030X
>

Yep. That's probably the single most useful performance tuning book anyone
working with dbs can own. Even if you don't run postgresql, the hardware
tuning and testing section is fantastic.