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

2017-07-11 Thread Charles Nadeau
Igor, I reduced the value of random_page_cost to 4 but the read speed remains low. Regarding effective_cache_size and shared_buffer, do you mean they should be both equal to 64GB? Thanks for suggestions! Charles On Mon, Jul 10, 2017 at 8:35 PM, Igor Neyman wrote: > > >

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

2017-07-11 Thread Charles Nadeau
Jeff, I used fio in a quick benchmarking script inspired by https://smcleod.net/benchmarking-io/: #!/bin/bash #Random throughput echo "Random throughput" sync fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=test --bs=4M --iodepth=256 --size=10G

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

2017-07-11 Thread Charles Nadeau
After reducing random_page_cost to 4 and testing more, I can report that the aggregate read throughput for parallel sequential scan is about 90MB/s. However the throughput for sequential scan is still around 4MB/s. One more question: if a query uses more than one table, can more than one table be

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

2017-07-11 Thread Igor Neyman
From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Tuesday, July 11, 2017 10:34 AM To: Charles Nadeau Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query

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

2017-07-11 Thread Igor Neyman
From: Charles Nadeau [mailto:charles.nad...@gmail.com] Sent: Tuesday, July 11, 2017 6:43 AM To: Igor Neyman Cc: Andreas Kretschmer ; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent Igor, I

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

2017-07-11 Thread Charles Nadeau
Rick, I applied the change you recommended but it didn't speed up the reads. One thing I forgot to mention earlier is the speed of the backup made with the COPY operations seems almost normal: I have read speed of up to 85MB/s. Thanks for your help! Charles On Mon, Jul 10, 2017 at 5:25 PM, Rick

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

2017-07-11 Thread Charles Nadeau
Igor, The sum of effective_cache_size and shared_buffer will be higher than the physical memory I have. Is it OK? Thanks! Charles On Tue, Jul 11, 2017 at 4:34 PM, Igor Neyman wrote: > > > *From:* Charles Nadeau [mailto:charles.nad...@gmail.com] > *Sent:* Tuesday, July

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

2017-07-11 Thread Igor Neyman
From: Charles Nadeau [mailto:charles.nad...@gmail.com] Sent: Tuesday, July 11, 2017 11:25 AM To: Igor Neyman Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent Attention: This email was sent from someone outside of

Re: [PERFORM] vacuum analyze affecting query performance

2017-07-11 Thread rverghese
Thanks for the info! -- View this message in context: http://www.postgresql-archive.org/vacuum-analyze-affecting-query-performance-tp5970681p5970830.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list

Re: [PERFORM] vacuum analyze affecting query performance

2017-07-11 Thread Albe Laurenz
rverghese wrote: > We are on Postgres 9.5, and have been running a daily vacuum analyze on the > entire database since 8.2 > The data has grown exponentially since, and we are seeing that queries are > now being significantly affected while the vacuum analyze runs. The query > database is a Slony

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

2017-07-11 Thread Mark Kirkwood
Hmm - how are you measuring that sequential scan speed of 4MB/s? I'd recommend doing a very simple test e.g, here's one on my workstation - 13 GB single table on 1 SATA drive - cold cache after reboot, sequential scan using Postgres 9.6.2: bench=# EXPLAIN SELECT count(*) FROM

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

2017-07-11 Thread Jeff Janes
On Tue, Jul 11, 2017 at 4:02 AM, Charles Nadeau wrote: > Jeff, > > I used fio in a quick benchmarking script inspired by https://smcleod.net/ > benchmarking-io/: > > #!/bin/bash > #Random throughput > echo "Random throughput" > sync > fio --randrepeat=1

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

2017-07-11 Thread Jeff Janes
On Tue, Jul 11, 2017 at 4:42 PM, Joshua D. Drake wrote: > On 07/11/2017 04:15 PM, Merlin Moncure wrote: > >> On Mon, Jul 10, 2017 at 9:03 AM, Charles Nadeau >> wrote: >> >>> I’m running PostgreSQL 9.6.3 on Ubuntu 16.10 (kernel 4.4.0-85-generic).

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

2017-07-11 Thread Merlin Moncure
On Mon, Jul 10, 2017 at 9:03 AM, Charles Nadeau wrote: > I’m running PostgreSQL 9.6.3 on Ubuntu 16.10 (kernel 4.4.0-85-generic). > Hardware is: > > *2x Intel Xeon E5550 > > *72GB RAM > > *Hardware RAID10 (4 x 146GB SAS 10k) P410i controller with 1GB FBWC (80% > read/20%

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

2017-07-11 Thread Joshua D. Drake
On 07/11/2017 04:15 PM, Merlin Moncure wrote: On Mon, Jul 10, 2017 at 9:03 AM, Charles Nadeau wrote: I’m running PostgreSQL 9.6.3 on Ubuntu 16.10 (kernel 4.4.0-85-generic). Hardware is: *2x Intel Xeon E5550 *72GB RAM *Hardware RAID10 (4 x 146GB SAS 10k) P410i