MIME-Version: 1.0
In-Reply-To: 
 <CADFyZw4m8e5dO-FXqW0Xcf1NP_P=7Vn7qCfNrb__puzgTiPxEA@mail.gmail.com>
References: 
 <CADFyZw7aGoD0AaStxdyHByR5Qta=M5wx0v=iptKLhPUp+EOKvA@mail.gmail.com>
 <CAMkU=1z92pimpoDV1oTpd4AvSHS0CrLzQDFSxw1Z5cpmmjEOkw@mail.gmail.com>
 <CADFyZw7Am=mv-6RXOr_C2oxEhPyKjnbvFgaEPq+-aKw+j9oDrw@mail.gmail.com>
 <CAMkU=1zD=SNFF=U-HtRHcu2dAdqyorD6o_UCQoYHuoh-gcs_uA@mail.gmail.com>
 <CADFyZw4RTBtWUeo-6Q_GcsqawkF+gSe4JGEpASUE6msiu=01xA@mail.gmail.com>
 <DM5PR07MB28101CDEC281CFA491B455E5DAAF0@DM5PR07MB2810.namprd07.prod.outlook.com>
 <DM5PR07MB2810C1580B17D1BF15B15AE5DAAF0@DM5PR07MB2810.namprd07.prod.outlook.com>
 <CADFyZw5pOiXpo_4zt2LeGneNuB91XHeOhL=9SzOQ36gWTX9rxA@mail.gmail.com>
 <CAGTBQpY+0eUrRJjpYB+DN5gvTBK+nARyxMkiTdLi1UpP0kX--Q@mail.gmail.com>
 <CADFyZw4m8e5dO-FXqW0Xcf1NP_P=7Vn7qCfNrb__puzgTiPxEA@mail.gmail.com>
From: Scott Marlowe <scott.marlowe@gmail.com>
Date: Tue, 18 Jul 2017 19:08:14 -0600
Message-ID: 
 <CAOR=d=0=WTazc5BrRSxTtbK2BWF9L+PqCnLJ9d1rmdrStx3x6g@mail.gmail.com>
Subject: Re: Very poor read performance, query independent
To: Charles Nadeau <charles.nadeau@gmail.com>
Cc: Claudio Freire <klaussfreire@gmail.com>,
 Igor Neyman <ineyman@perceptron.com>,
	Jeff Janes <jeff.janes@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Tue, Jul 18, 2017 at 3:20 AM, Charles Nadeau
<charles.nadeau@gmail.com> wrote:
> Claudio,
>
> Find attached the iostat measured while redoing the query above
> (iostat1.txt). sda holds my temp directory (noop i/o scheduler), sdb the
> swap partition (cfq i/o scheduler) only and sdc (5 disks RAID0, noop i/o
> scheduler) holds the data. I didn't pay attention to the load caused by 12
> parallel scans as I thought the RAID card would be smart enough to
> re-arrange the read requests optimally regardless of the load. At one moment
> during the query, there is a write storm to the swap drive (a bit like this
> case:
> https://www.postgresql.org/message-id/AANLkTi%3Diw4fC2RgTxhw0aGpyXANhOT%3DXBnjLU1_v6PdA%40mail.gmail.com).

My experience from that case (and  few more) has led me to believe
that Linux database servers with plenty of memory should have their
swaps turned off. The Linux kernel works hard to swap out little used
memory to make more space for caching active data.

Problem is that whatever decides to swap stuff out gets stupid when
presented with 512GB RAM and starts swapping out things like sys v
shared_buffers etc.

Here's the thing, either your memory is big enough to buffer your
whole data set, so nothing should get swapped out to make room for
caching.

OR your dataset is much bigger than memory. In which case, making more
room gets very little if it comes at the cost of waiting for stuff you
need to get read back in.

Linux servers should also have zone reclaim turned off, and THP disabled.

Try running "sudo swapoff -a" and see if it gets rid of your swap storms.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance