pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBig Memory Boxes and pgtune
5+ messages / 5 participants
[nested] [flat]
* Big Memory Boxes and pgtune
@ 2016-10-28 15:44 Warner, Gary, Jr <gar@uab.edu>
2016-10-28 17:09 ` Re: Big Memory Boxes and pgtune Kevin Grittner <kgrittn@gmail.com>
2016-10-28 19:33 ` Re: Big Memory Boxes and pgtune Joshua D. Drake <jd@commandprompt.com>
0 siblings, 2 replies; 5+ messages in thread
From: Warner, Gary, Jr @ 2016-10-28 15:44 UTC (permalink / raw)
To: pgsql-performance
I've recently been blessed to move one of my databases onto a huge IBM P8 computer. Its a power PC architecture with 20 8-way cores (so postgres SHOULD believe there are 160 cores available) and 1 TB of RAM.
I've always done my postgres tuning with a copy of "pgtune" which says in the output:
# WARNING
# this tool not being optimal
# for very high memory systems
So . . . what would I want to do differently based on the fact that I have a "very high memory system"?
Suggestions welcome!
(There are several different databases, mostly related to our work in social media and malware analytics. The databases are smaller than available RAM. Around 80 million social media profiles with 700M or so links, growing by 10% a week or so. The malware database has extracted statistics and data about 17 million malware samples, growing by about 5% per week. The Social Media side has just shy of 100 'fetchers' that insert/update (but don't delete.) A few human analysts interact with the data, hitting some pretty heavy queries as they do link analysis and natural language processing, but mostly the database use is the "fetchers")
------------------------------------------------------------------------------
Gary Warner, Director CETIFS gar@uab.edu
Center for Emerging Technology Investigations Forensics & Security
The University of Alabama at Birmingham (UAB)
205.422.2113
------------------------------------------------------------------------------
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Big Memory Boxes and pgtune
2016-10-28 15:44 Big Memory Boxes and pgtune Warner, Gary, Jr <gar@uab.edu>
@ 2016-10-28 17:09 ` Kevin Grittner <kgrittn@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2016-10-28 17:09 UTC (permalink / raw)
To: Warner, Gary, Jr <gar@uab.edu>; +Cc: pgsql-performance
On Fri, Oct 28, 2016 at 10:44 AM, Warner, Gary, Jr <gar@uab.edu> wrote:
> I've recently been blessed to move one of my databases onto a
> huge IBM P8 computer. Its a power PC architecture with 20 8-way
> cores (so postgres SHOULD believe there are 160 cores available)
> and 1 TB of RAM.
> So . . . what would I want to do differently based on the fact
> that I have a "very high memory system"?
What OS are you looking at?
The first advice I would give is to use a very recent version of
both the OS and PostgreSQL. Such large machines are a recent
enough phenomenon that older software is not likely to be optimized
to perform well on it. For similar reasons, be sure to stay up to
date with minor releases of both.
If the OS has support for them, you probably want to become
familiar with these commands:
numactl --hardware
lscpu
You may want to benchmark different options, but I suspect that you
will see better performance by putting each database on a separate
cluster and using cpusets (or the equivalent) so that each cluster
uses a subset of the 160 cores and the RAM directly attached to the
subset.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Big Memory Boxes and pgtune
2016-10-28 15:44 Big Memory Boxes and pgtune Warner, Gary, Jr <gar@uab.edu>
@ 2016-10-28 19:33 ` Joshua D. Drake <jd@commandprompt.com>
2016-11-02 23:46 ` Re: Big Memory Boxes and pgtune Jim Nasby <Jim.Nasby@BlueTreble.com>
1 sibling, 1 reply; 5+ messages in thread
From: Joshua D. Drake @ 2016-10-28 19:33 UTC (permalink / raw)
To: Warner, Gary, Jr <gar@uab.edu>; pgsql-performance
On 10/28/2016 08:44 AM, Warner, Gary, Jr wrote:
> I've recently been blessed to move one of my databases onto a huge IBM P8 computer. Its a power PC architecture with 20 8-way cores (so postgres SHOULD believe there are 160 cores available) and 1 TB of RAM.
>
> I've always done my postgres tuning with a copy of "pgtune" which says in the output:
>
> # WARNING
> # this tool not being optimal
> # for very high memory systems
>
> So . . . what would I want to do differently based on the fact that I have a "very high memory system"?
The most obvious is that you are going to want to have (depending on
PostgreSQL version):
* A very high shared_buffers (in newer releases, it is not uncommon to
have many, many GB of)
* Use that work_mem baby. You have 1TB available? Take your average data
set return, and make work_mem at least that.
* IIRC (and this may be old advice), maintenance_work_mem up to 4GB. As
I recall it won't effectively use more than that but I could be wrong.
Lastly but most importantly, test test test.
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Big Memory Boxes and pgtune
2016-10-28 15:44 Big Memory Boxes and pgtune Warner, Gary, Jr <gar@uab.edu>
2016-10-28 19:33 ` Re: Big Memory Boxes and pgtune Joshua D. Drake <jd@commandprompt.com>
@ 2016-11-02 23:46 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2016-11-04 02:24 ` Re: Big Memory Boxes and pgtune Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jim Nasby @ 2016-11-02 23:46 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; Warner, Gary, Jr <gar@uab.edu>; pgsql-performance
On 10/28/16 2:33 PM, Joshua D. Drake wrote:
> * A very high shared_buffers (in newer releases, it is not uncommon to
> have many, many GB of)
Keep in mind that you might get very poor results if shared_buffers is
large, but not large enough to fit the entire database. In that case
buffer replacement will be *extremely* expensive. Some operations will
use a different buffer replacement strategy, so you might be OK if some
of the database doesn't fit in shared buffers; that will depend a lot on
your access patterns.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Big Memory Boxes and pgtune
2016-10-28 15:44 Big Memory Boxes and pgtune Warner, Gary, Jr <gar@uab.edu>
2016-10-28 19:33 ` Re: Big Memory Boxes and pgtune Joshua D. Drake <jd@commandprompt.com>
2016-11-02 23:46 ` Re: Big Memory Boxes and pgtune Jim Nasby <Jim.Nasby@BlueTreble.com>
@ 2016-11-04 02:24 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2016-11-04 02:24 UTC (permalink / raw)
To: Jim Nasby <Jim.Nasby@bluetreble.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Warner, Gary, Jr <gar@uab.edu>; pgsql-performance
On Wed, Nov 2, 2016 at 5:46 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 10/28/16 2:33 PM, Joshua D. Drake wrote:
>>
>> * A very high shared_buffers (in newer releases, it is not uncommon to
>> have many, many GB of)
>
>
> Keep in mind that you might get very poor results if shared_buffers is
> large, but not large enough to fit the entire database. In that case buffer
> replacement will be *extremely* expensive. Some operations will use a
> different buffer replacement strategy, so you might be OK if some of the
> database doesn't fit in shared buffers; that will depend a lot on your
> access patterns.
This. Especially on machines with fast CPUs / memory and SSDs
underneath, lots of buffers can sometimes just get in the way. The
linux kernel (and most other kernels) has hundreds, even thousands of
man hours put into the file caching code and it's often faster to let
the kernel do that job with the extra memory.
Only a benchmark of a production type load can tell you what to
expect, and only production itself will reveal the absolute truth.
Where I used to work we had 5TB databases on machines with anywhere
from 128GB to 512GB and honesly the extra memory didn't make a huge
difference. They had 8 disk RAID-5 arrays with controller caching
turned off, because it got in the way, and cranking up shared_buffers
didn't not make them faster. I think we settled on something under
10GB on most of them
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2016-11-04 02:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-28 15:44 Big Memory Boxes and pgtune Warner, Gary, Jr <gar@uab.edu>
2016-10-28 17:09 ` Kevin Grittner <kgrittn@gmail.com>
2016-10-28 19:33 ` Joshua D. Drake <jd@commandprompt.com>
2016-11-02 23:46 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2016-11-04 02:24 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox