[PERFORM] Performance degradation from PostgreSQL 8.2.21 to PostgreSQL 9.3.2

2017-07-18 Thread fx TATEISHI KOJI 
Issuing exactly the same query as PostgreSQL 8.2.21 and PostgreSQL 9.3.2 will 
slow the response by 6.4 ms on average.
What could be the cause?
Measurement method is as follows.
・ PostgreSQL 8.2.21 installation
 ★Measurement
・ Export DUMP of PostgreSQL 8.2.21
・ PostgreSQL 8.2.21 uninstallation
・ PostgreSQL 9.3.2 installation
・ Dump import
 ★Measurement

[query]
select
 table4.a as col_0_0_,
 table4.a as col_1_0_,
 table4.a as col_2_0_,
 table4.b as col_0_1_,
 table4.c,
 table4.d
from
 table1,
 table2,
 table3,
 table4 
where
 table1.a=table2.a and
 table1.a="parameter$1" and
 table2.roleid=table3.roleid and
 table3.a="parameter$2" and
 table4.b='3' and
 table2.a=table4.a;


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

Re: [PERFORM] Very poor read performance, query independent

2017-07-18 Thread Scott Marlowe 
On Tue, Jul 18, 2017 at 3:20 AM, Charles Nadeau
 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

Re: [PERFORM] Very poor read performance, query independent

2017-07-18 Thread Justin Pryzby 
On Tue, Jul 18, 2017 at 02:13:58PM -0300, Claudio Freire wrote:
> On Tue, Jul 18, 2017 at 1:01 PM, Claudio Freire  
> wrote:
> > On Tue, Jul 18, 2017 at 6:20 AM, Charles Nadeau
> >  wrote:
> >> Claudio,
> >>
> >> 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).
> >> I can hardly explain it as there is plenty of memory on this server.
> >
> > That sounds a lot like NUMA zone_reclaim issues:
> >
> > https://www.postgresql.org/message-id/500616cb.3070...@2ndquadrant.com
> 
> I realize you have zone_reclaim_mode set to 0. Still, the symptoms are
> eerily similar.

Did you look at disabling KSM and/or THP ?

sudo sh -c 'echo 2 >/sys/kernel/mm/ksm/run'

https://www.postgresql.org/message-id/20170524155855.GH31097%40telsasoft.com
https://www.postgresql.org/message-id/CANQNgOrD02f8mR3Y8Pi=zfsol14rqnqa8hwz1r4rsndlr1b...@mail.gmail.com
https://www.postgresql.org/message-id/CAHyXU0y9hviyKWvQZxX5UWfH9M2LYvwvAOPQ_DUPva2b71t12g%40mail.gmail.com
https://www.postgresql.org/message-id/20130716195834.8fe5c79249cb2ff0d4270...@yahoo.es
https://www.postgresql.org/message-id/CAE_gQfW3dBiELcOppYN6v%3D8%2B%2BpEeywD7iXGw-OT3doB8SXO4_A%40mail.gmail.com
https://www.postgresql.org/message-id/flat/1436268563235-5856914.post%40n5.nabble.com#1436268563235-5856914.p...@n5.nabble.com
https://www.postgresql.org/message-id/cal_0b1tjozcx3lo3eve1rqgat%2bjj_q7w4pkj87wfwwxbtug...@mail.gmail.com
https://www.postgresql.org/message-id/556e2068.7070...@vuole.me
https://www.postgresql.org/message-id/1415981309.90631.YahooMailNeo%40web133205.mail.ir2.yahoo.com
https://www.postgresql.org/message-id/CAHyXU0yXYpCXN4%3D81ZDRQu-oGzrcq2qNAXDpyz4oiQPPAGk4ew%40mail.gmail.com
https://www.pythian.com/blog/performance-tuning-hugepages-in-linux/
http://structureddata.org/2012/06/18/linux-6-transparent-huge-pages-and-hadoop-workloads/

Justin


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

Re: [PERFORM] Very poor read performance, query independent

2017-07-18 Thread Claudio Freire 
On Tue, Jul 18, 2017 at 1:01 PM, Claudio Freire  wrote:
> On Tue, Jul 18, 2017 at 6:20 AM, Charles Nadeau
>  wrote:
>> Claudio,
>>
>> 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).
>> I can hardly explain it as there is plenty of memory on this server.
>
> That sounds a lot like NUMA zone_reclaim issues:
>
> https://www.postgresql.org/message-id/500616cb.3070...@2ndquadrant.com

I realize you have zone_reclaim_mode set to 0. Still, the symptoms are
eerily similar.


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

Re: [PERFORM] Very poor read performance, query independent

2017-07-18 Thread Claudio Freire 
On Tue, Jul 18, 2017 at 6:20 AM, Charles Nadeau
 wrote:
> Claudio,
>
> 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).
> I can hardly explain it as there is plenty of memory on this server.

That sounds a lot like NUMA zone_reclaim issues:

https://www.postgresql.org/message-id/500616cb.3070...@2ndquadrant.com


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

Re: [PERFORM] Very poor read performance, query independent

2017-07-18 Thread Charles Nadeau 
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).
I can hardly explain it as there is plenty of memory on this server. The
execution time of the query was 4801.1s (about 1h20min).
I reduced max_parallel_workers_per_gather to 2 and max_parallel_workers to
3, restarted postgresql then ran the query again while running iostat again
(iostat2.txt): The query ran much faster, 1992.8s (about 33min) instead of
4801.1s (about 1h20min) and the swap storm is gone! You were right about
the max_parallel_workers_per_gather!!
For the last test, I changed the scheduler on sdc to deadline (iostat3.txt)
keeping max_parallel_workers_per_gather=2 and max_parallel_workers=3 then
restarted postgresql. The execution time is almost the same: 1938.7s vs
1992.8s for the noop scheduler.

Thanks a lot for the suggestion, I'll keep my number of worker low to make
sure I maximize my array usage.

Charles

On Mon, Jul 17, 2017 at 10:56 PM, Claudio Freire 
wrote:

> On Fri, Jul 14, 2017 at 12:34 PM, Charles Nadeau
>  wrote:
> >  Workers Planned: 12
> >  Workers Launched: 12
> >  Buffers: shared hit=728798037
> read=82974833
> >  ->  Hash Semi Join
> > (cost=30059688.07..47951761.31 rows=220376 width=20) (actual
> > time=1268845.181..2007864.725 rows=7057357 loops=13)
> >Hash Cond: (flows.dstaddr =
> > flows_1.dstaddr)
> >Buffers: shared hit=728795193
> > read=82974833
> >->  Nested Loop
> > (cost=0.03..17891246.86 rows=220376 width=20) (actual
> time=0.207..723790.283
> > rows=37910370 loops=13)
> >  Buffers: shared
> hit=590692229
> > read=14991777
> >  ->  Parallel Seq Scan on
> flows
> > (cost=0.00..16018049.14 rows=55094048 width=20) (actual
> > time=0.152..566179.117 rows=45371630 loops=13)
> >Buffers: shared
> > hit=860990 read=14991777
> >  ->  Index Only Scan using
> > mynetworks_ipaddr_idx on mynetworks  (cost=0.03..0.03 rows=1 width=8)
> > (actual time=0.002..0.002 rows=1 loops=589831190)
> >Index Cond: (ipaddr
> >>=
> > (flows.srcaddr)::ip4r)
> >Heap Fetches: 0
> >Buffers: shared
> > hit=589831203
>
> 12 workers on a parallel sequential scan on a RAID-10 volume of
> rotating disks may not be a good idea.
>
> Have you measured average request size and average wait times with iostat?
>
> Run "iostat -x -m -d 60" while running the query and copy a few
> relevant lines (or attach the whole thing). I suspect 12 parallel
> sequential scans are degrading your array's performance to random I/O
> performance, and that explains the 10MB/s very well (a rotating disk
> will give you about 3-4MB/s at random I/O, and you've got 2 mirrors on
> that array).
>
> You could try setting the max_parallel_workers_per_gather to 2, which
> should be the optimum allocation for your I/O layout.
>
> You might also want to test switching to the deadline scheduler. While
> the controller may get more aggregate thoughput rearranging your I/O
> requests, high I/O latency will severly reduce postgres' ability to
> saturate the I/O system itself, and deadlines tends to minimize
> latency. I've had good results in the past using deadline, but take
> this suggestion with a grain of salt, YMMV.
>



-- 
Charles Nadeau Ph.D.
http://charlesnadeau.blogspot.com/
charles@hpdl380g6:~$ iostat -x -m -d 60
Linux 4.4.0-86-generic (hpdl380g6)  2017-07-18  _x86_64_(16 CPU)

Device: rrqm/s   wrqm/s r/s w/srMB/swMB/s avgrq-sz 
avgqu-sz   await r_await w_await  svctm  %util
sda   1.4515.243.854.91 0.68 0.47   269.64 
3.15  351.06  501.22  233.27   4.84   4.24
sdb   1.52   635.12   67.38   10.80 0.27 2.5473.58 
7.66   97.90   28.43  531.24   2.26  17.65
sdc   0.08 0.01   47.900.0115.62 0.00   667.83 
0.97   20.30   20.306.89   3.05  14.62

Device: