public inbox for [email protected]
help / color / mirror / Atom feedFrom: Scott Marlowe <[email protected]>
To: Charles Nadeau <[email protected]>
Cc: Claudio Freire <[email protected]>
Cc: Igor Neyman <[email protected]>
Cc: Jeff Janes <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Very poor read performance, query independent
Date: Tue, 18 Jul 2017 19:08:14 -0600
Message-ID: <CAOR=d=0=WTazc5BrRSxTtbK2BWF9L+PqCnLJ9d1rmdrStx3x6g@mail.gmail.com> (raw)
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>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
On Tue, Jul 18, 2017 at 3:20 AM, Charles Nadeau
<[email protected]> 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...).
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Very poor read performance, query independent
In-Reply-To: <CAOR=d=0=WTazc5BrRSxTtbK2BWF9L+PqCnLJ9d1rmdrStx3x6g@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox