Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dUx2m-0003tP-MJ for pgsql-performance@arkaria.postgresql.org; Tue, 11 Jul 2017 15:25:44 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dUx2m-0003kY-4S for pgsql-performance@arkaria.postgresql.org; Tue, 11 Jul 2017 15:25:44 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dUx2l-0003jq-8Q for pgsql-performance@postgresql.org; Tue, 11 Jul 2017 15:25:43 +0000 Received: from mail-qk0-x229.google.com ([2607:f8b0:400d:c09::229]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dUx2d-0005Fa-PR for pgsql-performance@postgresql.org; Tue, 11 Jul 2017 15:25:41 +0000 Received: by mail-qk0-x229.google.com with SMTP id p21so4535389qke.3 for ; Tue, 11 Jul 2017 08:25:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=aCpp1P6xYFIm7mnukbuY99cnZbOPaZkwOj+2h4ihq0w=; b=l9toEB+JHJKmLGgdoO2ZWYX5p7+a8Bo7pXzFnkIGsMX3sS5+BOiG/J1SqVrNMIkLZ7 816N48NPFAfzwaNWyGjaJk2bcUyyykrXq7QFPgO31xAuZICQJCSBgPKMD2B3XSAxUuxz jhNBb7mdS3axxYk75tje7rtntLgaRVPfNtgaGy79S7yxuoRx9/FAHyAfz9b0j7uIpmF1 w37i0r0pobkd+fbNNJUfErqgP8mM0o4AsHONxHAIpsrZJBHbFdy0+JY2sAN5bDthlB48 nkaYbpxcL5P/C43NalKXStWbZKkzv6LYjJZARq+9jn3QzSi0R4kC2yjQDqu5Bo05PgQ4 //Gw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=aCpp1P6xYFIm7mnukbuY99cnZbOPaZkwOj+2h4ihq0w=; b=Hacb4ZofIvkwHVD8aowdqme7qqlDSDiER87zvHRSJFVAaV5O5OVC3c6H2wBKaSiyq6 Mv4YCaiY2nuWCZR+lBgK/JlcfJZEH5YRt23CTa01Lgs2tX4FYdy/r6Qt4KVchxcCtMk/ 5Ki6V7+T/sBmMdblTYyfJHEwXOEm9uW7aE6qouAfNqkMm9GoAMx579vLaQ38pF2zAHA9 s/fICrxpt8f95Glg1X9eKACiCKQe2GVLkZ9b8+l6NAieCTioWutRb2JRSB/VSIoAb2t7 euMv3x/D9pT/4TDB7FbEwDczajUcTlw7ynw5K7VSIXeq+PFEgjY081Waj+Y7Yo9YaQhf J2fg== X-Gm-Message-State: AIVw1107VwhGIBvK17o528C1Fe9ODIktBVbwroJ2Bm6TNrDxm/WvYlU0 GV+Ot2mQF1TDvZACuRe5PzHWJs0dmw== X-Received: by 10.237.36.143 with SMTP id t15mr641340qtc.180.1499786734748; Tue, 11 Jul 2017 08:25:34 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.157.11 with HTTP; Tue, 11 Jul 2017 08:25:14 -0700 (PDT) In-Reply-To: References: From: Charles Nadeau Date: Tue, 11 Jul 2017 17:25:14 +0200 Message-ID: Subject: Re: Very poor read performance, query independent To: Igor Neyman Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a11420e0890486c05540c4f46" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a11420e0890486c05540c4f46 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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.nadeau@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 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 > > > > No, they should not be equal. > > From the docs: > > > > effective_cache_size (integer) > > Sets the planner's assumption about the effective size of the disk cache > that is available to a single query. This is factored into estimates of t= he > cost of using an index; a higher value makes it more likely index scans > will be used, a lower value makes it more likely sequential scans will be > used. When setting this parameter you should consider both PostgreSQL's > shared buffers and the portion of the kernel's disk cache that will be us= ed > for PostgreSQL data files. Also, take into account the expected number of > concurrent queries on different tables, since they will have to share the > available space. This parameter has no effect on the size of shared memor= y > allocated by PostgreSQL, nor does it reserve kernel disk cache; it is use= d > only for estimation purposes. The system also does not assume data remain= s > in the disk cache between queries. The default is 4 gigabytes (4GB). > > So, I=E2=80=99d set shared_buffers at 24GB and effective_cache_size at 64= GB. > > > > Regards, > > Igor > > > --=20 Charles Nadeau Ph.D. http://charlesnadeau.blogspot.com/ --001a11420e0890486c05540c4f46 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Igor,

The sum of effective_cache_size a= nd shared_buffer will be higher than the physical memory I have. Is it OK?<= /div>
Thanks!

Charles

On Tue, Jul 11, 2017 at 4:34 P= M, Igor Neyman <ineyman@perceptron.com> wrote:

=C2=A0

From: Charles Nadeau [mailto:charles.nadeau@gmail= .com]
Sent: Tuesday, July 11, 2017 6:43 AM
To: Igor Neyman <ineyman@perceptron.com>
Cc: Andreas Kretschmer <andreas@a-kretschmer.de>; pgsql-performance@postgresq= l.org
Subject: Re: [PERFORM] Very poor read performance, query independent=

=C2=A0

Igor,

=C2=A0

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!

=C2=A0

Charles<= /u>

=C2=A0

No, they should not be equal.<= u>

From the docs:

=C2=A0

effective_cache_size (integer)

Sets the planner's assumption about the effective size of the disk cach= e that is available to a single query. This is factored into estimates of t= he cost of using an index; a higher value makes it more likely index scans = will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you= should consider both PostgreSQL's shared buffers and the portion of th= e kernel's disk cache that will be used for PostgreSQL data files. Also= , take into account the expected number of concurrent queries on different tables, since they will have to share t= he available space. This parameter has no effect on the size of shared memo= ry allocated by PostgreSQL, nor does it reserve kernel disk cache; it is us= ed only for estimation purposes. The system also does not assume data remains in the disk cache between que= ries. The default is 4 gigabytes (4GB).

So, I=E2=80=99d set shared_buffers at= 24GB and effective_cache_size at 64GB.

=C2=A0

Regards,

Igor

=C2=A0




--
Charles Nadeau Ph.D= .
http:= //charlesnadeau.blogspot.com/
--001a11420e0890486c05540c4f46--