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

2017-07-10 Thread Jeff Janes
On Mon, Jul 10, 2017 at 7:03 AM, Charles Nadeau wrote: > > The problem I have is very poor read. When I benchmark my array with fio I > get random reads of about 200MB/s and 1100IOPS and sequential reads of > about 286MB/s and 21000IPS. > That doesn't seem right. Sequential is only 43% faster?

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

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

[PERFORM] vacuum analyze affecting query performance

2017-07-10 Thread rverghese
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 slave. So the question is,

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

2017-07-10 Thread Charles Nadeau
Andreas, Because the ratio between the Sequential IOPS and Random IOPS is about 29. Taking into account that part of the data is in RAM, I obtained an "effective" ratio of about 22. Thanks! Charles On Mon, Jul 10, 2017 at 5:35 PM, Andreas Kretschmer wrote: > > > Am 10.07.2017 um 16:03 schrieb

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

2017-07-10 Thread Andreas Kretschmer
Am 10.07.2017 um 16:03 schrieb Charles Nadeau: random_page_cost | 22 why such a high value for random_page_cost? Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make ch

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

2017-07-10 Thread Rick Otten
Although probably not the root cause, at the least I would set up hugepages ( https://www.postgresql.org/docs/9.6/static/kernel-resources.html#LINUX-HUGE-PAGES ), and bump effective_io_concurrency up quite a bit as well (256 ?). On Mon, Jul 10, 2017 at 10:03 AM, Charles Nadeau wrote: > I’m run

[PERFORM] Very poor read performance, query independent

2017-07-10 Thread Charles Nadeau
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% write) for Postgresql data only: Logical Drive: 3 Size: 273.4 GB Fault Tolerance: 1+0 Heads: 2