Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BCF3BB5DC6E for ; Fri, 9 Sep 2011 13:34:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 76992-07 for ; Fri, 9 Sep 2011 16:34:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from blu0-omc4-s9.blu0.hotmail.com (blu0-omc4-s9.blu0.hotmail.com [65.55.111.148]) by mail.postgresql.org (Postfix) with ESMTP id A7A33B5DBFC for ; Fri, 9 Sep 2011 13:34:49 -0300 (ADT) Received: from BLU153-W20 ([65.55.111.136]) by blu0-omc4-s9.blu0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Fri, 9 Sep 2011 09:34:49 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_3e5b56d0-e1f9-4371-85fd-6b3a99c13e03_" X-Originating-IP: [174.93.168.109] From: Carlo Stonebanks To: Subject: Migrated from 8.3 to 9.0 - need to update config (re-post) Date: Fri, 9 Sep 2011 16:34:48 +0000 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 09 Sep 2011 16:34:49.0005 (UTC) FILETIME=[644F5DD0:01CC6F0E] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201109/88 X-Sequence-Number: 44864 --_3e5b56d0-e1f9-4371-85fd-6b3a99c13e03_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hello performance wizards! (Sorry for the re-post if this appears twice - I= see no evidence e-mailing to pgsql-perfomrance is working yet.) =20 My client has migrated his 8.3 hosted DB to new machines=20 running PG 9.0. It=92s time to look at the config settings.=20 =20 Immediately below are the config settings.=20 =20 The specifics of the DB and how it is used is below=20 that=2C but in general let me say that this is a full-time ETL system=2C wi= th only a=20 handful of actual =93users=94 and automated processes over 300 connections = running=20 =93import=94 programs 24/7. =20 I appreciate the help=2C =20 Carlo =20 The host system: =20 Intel=AE Xeon=AE Processor X5560 (8M Cache=2C 2.80 GHz=2C 6.40=20 GT/s Intel=AE QPI) x 2=2C dual quad core 48 GB RAM RAID 10=2C 6 X 600 GB 15krpm=20 SAS) LINUX Redhat/Centos=20 2.6.18-164.el5 =20 Sys admin says that battery-backup RAID controller and=20 consequent write settings should have no impact on performance. Is this=20 true? =20 Current config and my thoughts on what to do with it. If=20 it isn=92t mentioned here=2C the values are default=20 values: =20 #=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=20 max_connections =3D=20 300 shared_buffers =3D=20 500MB # At 48GB of RAM=2C could we go to 2GB =20 # - what is the impact on LINX config? effective_cache_size =3D=20 2457MB # Sys admin says assume 25% of 48GB =20 # is used by OS and other apps work_mem =3D=20 512MB # Complex reads are called many times a second=20 =20 # from each connection=2C so what should this be? maintenance_work_mem =3D=20 256MB # Should this be bigger - 1GB at least? checkpoint_segments =3D=20 128 # There is lots of write activity=3B this is high=20 =20 # but could it be higher? =20 #checkpoint_completion_target=20 not set=3B=20 # Recommendation appears to=20 be .9 for our 128 checkpoint segments =20 default_statistics_target =3D=20 200 # Deprecated? =20 #autovacuum_freeze_max_age=20 not set=3B=20 # recommendation is=20 1=2C000=2C000 for non-activity.=20 =20 # What is the metric for=20 wal_buffers setting? wal_buffers =3D=20 4MB # Looks low=2C recommendation appears to be 16MB.=20 =20 # Is it really "set it and forget it"? =20 #synchronous_commit not set=3B=20 # Recommendation is to turn=20 this off and leave fsync on =20 #fsync not set=3B=20 # Recommendation is to=20 leave this on =20 #wal_level not set=3B=20 # Do we only needed for=20 replication? =20 #max_wal_senders not set=3B=20 # Do we only needed for=20 replication? =20 # The issue of=20 vacuum/analyze is a tricky one. # Data imports are running=20 24/7. One the DB is seeded=2C the vast majority # of write activity is=20 updates=2C and not to indexed columns.=20 # Deletions are vary=20 rare. vacuum_cost_delay =3D=20 20ms =20 # The background writer has=20 not been addressed at all. # Can our particular setup=20 benefit from changing=20 # the bgwriter=20 values? bgwriter_lru_maxpages =3D=20 100 # This is the default=3B=20 =20 listen_addresses =3D=20 '*' port =3D=20 5432 log_destination =3D=20 'stderr' logging_collector =3D=20 on log_directory =3D=20 'pg_log' log_filename =3D=20 'postgresql-%a.log' log_truncate_on_rotation =3D=20 on log_rotation_age =3D=20 1d log_rotation_size =3D=20 0 log_line_prefix =3D=20 '%t' track_counts =3D=20 on #=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=20 =20 The DB is pretty large=2C and organized by schema. The=20 most active are: =20 1) =20 One =93Core=94 schema a. =20 100 tables b. =20 Typical row counts in the low=20 millions. c. =20 This represents the enterprise=92s core data.=20 d. =20 Equal read/write activity 2) =20 Multiple =93Import=94 schemas a. =20 Contain several thousand raw =93flat file=94=20 tables b. =20 Ragged column structure=2C up to hundreds of=20 columns c. =20 Erratic row counts=2C from dozens of rows to 1=20 million d. =20 Each table sequentially read once=2C only=20 status fields are written back 3) =20 One =93Audit=94 schema a. =20 A new log table is created every=20 month b. =20 Typical row count is 200=20 million c. =20 Log every write to the =93Core=94 d. =20 Almost entirely write operations=2C but the few=20 read operations that are done have to be fast owing to the size of the=20 tables e. =20 Links the =93Core=94 data to the =93Import=94=20 data =20 There are next to no =93users=94 on the system =96 each=20 connection services a constantly running import process which takes the inc= oming=20 =93import=94 data=2C analyzes the =93core=94 data and decides how to distil= the import=20 into the core. =20 Analytical Processes are not=20 report-oriented The =93Core=94 reads are mostly single row=20 results The =93Import=94 reads are 1=2C000 row=20 pages There is next to no use of aggregate=20 queries =20 Transactional Processes are a steady stream of=20 writes Not bursty or sporadic Overwhelmingly inserts and updates=2C next to no=20 deletes Each transaction represents 10 =96 50 writes to the =93core=94=20 schema =20 = --_3e5b56d0-e1f9-4371-85fd-6b3a99c13e03_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

Hello performance wizards!

<= p class=3D"MsoNormal"> =3B

(Sorry for the re-post if this appears twice - I see no evidence = e-mailing to pgsql-perfomrance is working yet.)

My client has migrated his 8.3 hosted DB to n= ew machines=20 running PG 9.0. It=92s time to look at the config settings.=20

Immediately below are the config settings.=20

The specifics of the DB and how it is used is= below=20 that=2C but in general let me say that this is a full-time ETL system=2C wi= th only a=20 handful of actual =93users=94 and automated processes over 300 connections = running=20 =93import=94 programs 24/7.

I appreciate the help=2C

Carlo

The host system:

Intel=AE Xeon=AE Processor X5560 (8M Cache=2C= 2.80 GHz=2C 6.40=20 GT/s Intel=AE QPI) x 2=2C dual quad core

48 GB RAM

RAID 10=2C 6 X 600 GB 15krpm=20 SAS)

LINUX Redhat/Centos=20 2.6.18-164.el5

Sys admin says that battery-backup RAID contr= oller and=20 consequent write settings should have no impact on performance. Is this=20 true?

Current config and my thoughts on what to do = with it. If=20 it isn=92t mentioned here=2C the values are default=20 values:

#=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=20

max_conne= ctions =3D=20 300

shared_bu= ffers =3D=20 500MB # At 48GB of RAM=2C could we go to 2GB

= =20 # - what is the impact on LINX config?

effective= _cache_size =3D=20 2457MB # Sys admin says assume 25% of 48GB

= =20 # is used by OS and other apps

work_mem = =3D=20 512MB # Complex reads are called many times a second=20

= =20 # from each connection=2C so what should this be?<= /p>

maintenan= ce_work_mem =3D=20 256MB # Should this be bigger - 1GB at least?

checkpoin= t_segments =3D=20 128 # There is lots of write activity=3B this is high=20

= =20 # but could it be higher? =20

#checkpoi= nt_completion_target=20 not set=3B

# Recomm= endation appears to=20 be .9 for our 128 checkpoint segments

default_s= tatistics_target =3D=20 200 # Deprecated?

#autovacu= um_freeze_max_age=20 not set=3B

# recomme= ndation is=20 1=2C000=2C000 for non-activity.

# What is= the metric for=20 wal_buffers setting?

wal_buffe= rs =3D=20 4MB # Looks low=2C recommendation appears to be 16MB.=20

= =20 # Is it really "set it and forget it"?

#synchron= ous_commit not set=3B=20

# Recomm= endation is to turn=20 this off and leave fsync on

#fsync no= t set=3B=20

# Recomm= endation is to=20 leave this on

#wal_leve= l not set=3B=20

# Do we = only needed for=20 replication?

#max_wal_= senders not set=3B=20

# Do we = only needed for=20 replication?

# The iss= ue of=20 vacuum/analyze is a tricky one.

# Data i= mports are running=20 24/7. One the DB is seeded=2C the vast majority

# of wri= te activity is=20 updates=2C and not to indexed columns.

# Deleti= ons are vary=20 rare.

vacuum_co= st_delay =3D=20 20ms

# The bac= kground writer has=20 not been addressed at all.

# Can ou= r particular setup=20 benefit from changing

# the bg= writer=20 values?

bgwriter_= lru_maxpages =3D=20 100 # This is the default=3B

listen_ad= dresses =3D=20 '*'

port =3D= =20 5432

log_desti= nation =3D=20 'stderr'

logging_c= ollector =3D=20 on

log_direc= tory =3D=20 'pg_log'

log_filen= ame =3D=20 'postgresql-%a.log'

log_trunc= ate_on_rotation =3D=20 on

log_rotat= ion_age =3D=20 1d

log_rotat= ion_size =3D=20 0

log_line_= prefix =3D=20 '%t'

track_cou= nts =3D=20 on

#=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=20


The DB is pretty large=2C and organized b= y schema. The=20 most active are:

= 1) = =20 One =93Core=94 schema

a.= = =20 100 tables

b.= = =20 Typical row counts in the low=20 millions.

c.= = =20 This represents the enterprise=92s core = data.=20

d.= = =20 Equal read/write activity

= 2) = =20 Multiple =93Import=94 schemas=

a.= = =20 Contain several thousand raw =93flat fil= e=94=20 tables

b.= = =20 Ragged column structure=2C up to hundred= s of=20 columns

c.= = =20 Erratic row counts=2C from dozens of row= s to 1=20 million

d.= = =20 Each table sequentially read once=2C onl= y=20 status fields are written back

= 3) = =20 One =93Audit=94 schema

a.= = =20 A new log table is created every=20 month

b.= = =20 Typical row count is 200=20 million

c.= = =20 Log every write to the =93Core=94

d.= = =20 Almost entirely write operations=2C but = the few=20 read operations that are done have to be fast owing to the size of the=20 tables

e.= = =20 Links the =93Core=94 data to the =93Impo= rt=94=20 data

There are next to no =93users=94 on the syste= m =96 each=20 connection services a constantly running import process which takes the inc= oming=20 =93import=94 data=2C analyzes the =93core=94 data and decides how to distil= the import=20 into the core.

Analytical Processes are not=20 report-oriented

The =93Core=94 reads are mostly single row=20 results

The =93Import=94 reads are 1=2C000 row=20 pages

There is next to no use of aggregate=20 queries

Transactional Processes are a steady stream o= f=20 writes

Not bursty or sporadic

Overwhelmingly inserts and updates=2C next to= no=20 deletes

Each transaction represents 10 =96 50 writes = to the =93core=94=20 schema

=
= --_3e5b56d0-e1f9-4371-85fd-6b3a99c13e03_--