[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: [PERFORM] Configuration for a new server.
From: "Benjamin Krajmalnik" <kraj () servoyant ! com>
Date: 2011-01-31 23:55:32
Message-ID: F4E6A2751A2823418A21D4A160B689887B0E2A () fletch ! stackdump ! local
[Download RAW message or body]
Scott,
I don't know if you received my private email, but just in case you did not I am \
posting the infomration here.
I have a new set of servers coming in - Dual Xeon E5620's, 96GB RAM, 18 spindles (1 \
RAID1 for OS - SATA, 12 disk RAID10 for data - SAS, RAID-1 for logs - SAS, 2 hot \
spares SAS). They are replacing a single Dual Xeon E5406 with 16GB RAM and 2x RAID1 \
- one for OS/Data, one for Logs.
Current server is using 3840MB of shared buffers.
It will be running FreeBSD 8.1 x64, PG 9.0.2, running streaming replication to a like \
server.
I have read the performance tuning book written by Greg Smith, and am using it as a \
guide to configure it for performance.
The main questions which I have are the following:
Is the 25% RAM for shared memory still a good number to go with for this size server?
There are approximately 50 tables which get updated with almost 100% records updated \
every 5 minutes - what is a good number of autovacuum processes to have on these? \
The current server I am replacing only has 3 of them but I think I may gain a benefit \
from having more.
Currently I have what I believe to be an aggressive bgwriter setting as follows:
bgwriter_delay = 200ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round
bgwriter_lru_multiplier = 10 # 0-10.0 multipler on buffers scanned/round
Does this look right?
I have the following settings:
work_mem = 64MB # min 64kB
maintenance_work_mem = 128MB # min 1MB
And, of course, some of the most critical ones - the WAL settings. Right now, in \
order to give the best performance to the end users due to the size of the current \
box, I have a very unoptimal setting in my opinion
fsync = off # turns forced synchronization on or off
#synchronous_commit = on # immediate fsync at commit
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
full_page_writes = on # recover from partial page writes
wal_buffers = 16MB
#wal_buffers = 1024KB # min 32kB
# (change requires restart)
# wal_writer_delay = 100ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
checkpoint_segments = 1024
checkpoint_timeout = 60min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
checkpoint_completion_target = 0.1
checkpoint_warning = 45min # 0 disables
These are values which I arrived to by playing with them to make sure that the end \
user performance did not suffer. The checkpoints are taking about 8 minutes to \
complete, but between checkpoints the disk i/o on the data partition is very minimal \
- when I had lower segments running a 15 minute timeout with a .9 completion target, \
the platform was fairly slow vis-à-vis the end user.
The above configuration is using PG 8.4.
Thanks in advance for any insight.
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:x="urn:schemas-microsoft-com:office:excel" \
xmlns:p="urn:schemas-microsoft-com:office:powerpoint" \
xmlns:a="urn:schemas-microsoft-com:office:access" \
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" \
xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" \
xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" \
xmlns:b="urn:schemas-microsoft-com:office:publisher" \
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" \
xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" \
xmlns:odc="urn:schemas-microsoft-com:office:odc" \
xmlns:oa="urn:schemas-microsoft-com:office:activation" \
xmlns:html="http://www.w3.org/TR/REC-html40" \
xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" \
xmlns:rtc="http://microsoft.com/officenet/conferencing" xmlns:D="DAV:" \
xmlns:Repl="http://schemas.microsoft.com/repl/" \
xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" \
xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" \
xmlns:ppda="http://www.passport.com/NameSpace.xsd" \
xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" \
xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" \
xmlns:ds="http://www.w3.org/2000/09/xmldsig#" \
xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" \
xmlns:udc="http://schemas.microsoft.com/data/udc" \
xmlns:xsd="http://www.w3.org/2001/XMLSchema" \
xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" \
xmlns:ec="http://www.w3.org/2001/04/xmlenc#" \
xmlns:sp="http://schemas.microsoft.com/sharepoint/" \
xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" \
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" \
xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" \
xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" \
xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" \
xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" \
xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" \
xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" \
xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" \
xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" \
xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" \
xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" \
xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" \
xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" \
xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" \
xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" \
xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type \
content="text/html; charset=iso-8859-1"><meta name=Generator content="Microsoft Word \
12 (filtered medium)"><style><!-- /* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div \
class=WordSection1><p class=MsoNormal>Scott,<o:p></o:p></p><p class=MsoNormal>I \
don’t know if you received my private email, but just in case you did not I am \
posting the infomration here.<o:p></o:p></p><p \
class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I have a new set of servers \
coming in – Dual Xeon E5620’s, 96GB RAM, 18 spindles (1 RAID1 for OS \
– SATA, 12 disk RAID10 for data – SAS, RAID-1 for logs – SAS, 2 hot \
spares SAS). They are replacing a single Dual Xeon E5406 with 16GB RAM and 2x RAID1 \
– one for OS/Data, one for Logs.<o:p></o:p></p><p class=MsoNormal>Current \
server is using 3840MB of shared buffers.<o:p></o:p></p><p \
class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>It will be running FreeBSD \
8.1 x64, PG 9.0.2, running streaming replication to a like server.<o:p></o:p></p><p \
class=MsoNormal>I have read the performance tuning book written by Greg Smith, and am \
using it as a guide to configure it for performance.<o:p></o:p></p><p \
class=MsoNormal>The main questions which I have are the following:<o:p></o:p></p><p \
class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Is the 25% RAM for shared \
memory still a good number to go with for this size server?<o:p></o:p></p><p \
class=MsoNormal>There are approximately 50 tables which get updated with almost 100% \
records updated every 5 minutes – what is a good number of autovacuum processes \
to have on these? The current server I am replacing only has 3 of them but I think I \
may gain a benefit from having more.<o:p></o:p></p><p class=MsoNormal>Currently I \
have what I believe to be an aggressive bgwriter setting as follows:<o:p></o:p></p><p \
class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>bgwriter_delay = 200ms \
# 10-10000ms between rounds<o:p></o:p></p><p class=MsoNormal>bgwriter_lru_maxpages = \
1000 # 0-1000 max buffers written/round <o:p></o:p></p><p \
class=MsoNormal>bgwriter_lru_multiplier = 10 # 0-10.0 multipler on buffers \
scanned/round<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p \
class=MsoNormal>Does this look right?<o:p></o:p></p><p \
class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I have the following \
settings:<o:p></o:p></p><p class=MsoNormal>work_mem = 64MB # \
min 64kB<o:p></o:p></p><p class=MsoNormal>maintenance_work_mem = 128MB # \
min 1MB<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>And, \
of course, some of the most critical ones – the WAL settings. Right now, in \
order to give the best performance to the end users due to the size of the current \
box, I have a very unoptimal setting in my opinion <o:p></o:p></p><p \
class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>fsync = off \
# turns forced synchronization on or off<o:p></o:p></p><p \
class=MsoNormal>#synchronous_commit = on # immediate fsync at \
commit<o:p></o:p></p><p class=MsoNormal>#wal_sync_method = fsync # the \
default is the first option<o:p></o:p></p><p class=MsoNormal> \
# supported by the operating system:<o:p></o:p></p><p class=MsoNormal> \
# open_datasync<o:p></o:p></p><p class=MsoNormal> \
# fdatasync<o:p></o:p></p><p class=MsoNormal> \
# fsync<o:p></o:p></p><p class=MsoNormal> # \
fsync_writethrough<o:p></o:p></p><p class=MsoNormal> \
# open_sync<o:p></o:p></p><p class=MsoNormal>full_page_writes = on \
# recover from partial page writes<o:p></o:p></p><p class=MsoNormal>wal_buffers = \
16MB<o:p></o:p></p><p class=MsoNormal>#wal_buffers = 1024KB # min \
32kB<o:p></o:p></p><p class=MsoNormal> # \
(change requires restart)<o:p></o:p></p><p class=MsoNormal># wal_writer_delay = 100ms \
# 1-10000 milliseconds<o:p></o:p></p><p class=MsoNormal> \
<o:p></o:p></p><p class=MsoNormal>#commit_delay = 0 # range \
0-100000, in microseconds<o:p></o:p></p><p class=MsoNormal>#commit_siblings = 5 \
# range 1-1000<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p \
class=MsoNormal># - Checkpoints -<o:p></o:p></p><p \
class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>#checkpoint_segments = 128 \
# in logfile segments, min 1, 16MB each<o:p></o:p></p><p \
class=MsoNormal>checkpoint_segments = 1024<o:p></o:p></p><p \
class=MsoNormal>checkpoint_timeout = 60min # range \
30s-1h<o:p></o:p></p><p class=MsoNormal>#checkpoint_completion_target = 0.5 # \
checkpoint target duration, 0.0 - 1.0<o:p></o:p></p><p \
class=MsoNormal>checkpoint_completion_target = 0.1<o:p></o:p></p><p \
class=MsoNormal>checkpoint_warning = 45min # 0 disables<o:p></o:p></p><p \
class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>These are values which I \
arrived to by playing with them to make sure that the end user performance did not \
suffer. The checkpoints are taking about 8 minutes to complete, but between \
checkpoints the disk i/o on the data partition is very minimal – when I had \
lower segments running a 15 minute timeout with a .9 completion target, the platform \
was fairly slow vis-à-vis the end user.<o:p></o:p></p><p \
class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>The above configuration is \
using PG 8.4.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p \
class=MsoNormal>Thanks in advance for any insight.<o:p></o:p></p></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic