Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 73C8FB5DC23 for ; Mon, 22 Aug 2011 13:48:46 -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 56726-04 for ; Mon, 22 Aug 2011 16:48:39 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm17.access.bullet.mail.mud.yahoo.com (nm17.access.bullet.mail.mud.yahoo.com [66.94.237.218]) by mail.postgresql.org (Postfix) with SMTP id 6E57CB5DBF0 for ; Mon, 22 Aug 2011 13:48:38 -0300 (ADT) Received: from [66.94.237.127] by nm17.access.bullet.mail.mud.yahoo.com with NNFMP; 22 Aug 2011 16:48:37 -0000 Received: from [66.94.237.96] by tm2.access.bullet.mail.mud.yahoo.com with NNFMP; 22 Aug 2011 16:48:37 -0000 Received: from [127.0.0.1] by omp1001.access.mail.mud.yahoo.com with NNFMP; 22 Aug 2011 16:48:37 -0000 X-Yahoo-Newman-Id: 270614.84689.bm@omp1001.access.mail.mud.yahoo.com Received: (qmail 28840 invoked from network); 22 Aug 2011 16:48:36 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=sbcglobal.net; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:Message-ID:From:To:References:Subject:Date:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE; b=gklFQnV0p6qAxZ1HpDoEa+sGWRj0nfbTtjbvnXCHsSEIivPE1jPYE+rWo8RP5sMy117U1BDUXuMMHh+afhS4HoSVmGhdeoY6YMYCqNvDjQCKVxayFrXckvie0ex4GfUvYRRy98Fo//Pc8iuCvSS+4uyETTPD75pXBmiFSwF+9KY= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sbcglobal.net; s=s1024; t=1314031716; bh=XrM2850DXvRqRBdBvO9tbKngvVy+cIjyiqlE+Js+M0I=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:Message-ID:From:To:References:Subject:Date:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE; b=fuzwrHYQeJVdkjWHprYcUv2OedmK4j9VGw9j/4KG4GTAa3vx4HHV8o7VA42VwREyQDyaAnLFIeCHBoxmvQ/joKKeUYk1JyD/rP7OYJLMgjD7tEedIPOD+vvew1ATKE3AErwZ8qmtTG4smy0JbjrFSRqcazTlIAotPPjMKZr/GaQ= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: VodJVfwVM1l8X2qHQFgFWtXmvO901tJD6xZvEH6JEUvgsas s8S1tTnTaT8pKZ.yzgQ65T9ZRSFghPD36EfrKUc_kY8eOkwxTbQbBzEu_B1Z zGGLrxlYbkQECh5youztS2umA96T48mY4lv1_V1hWYwKfN3ZXEiKBl22IBiF aTMyF8Q82fON_99y7skFU.OBHU_33r1jDbp.eDg6.OLLjCV1qqJFp8Idjjmn Af1f3GfnK7u1uthHzLHpVRa.jl3vOMTFS46pFNYsGaVkd6dRTRgcOqAS7snR N6gJLCS8h.5S24Gs9EcR4Z9TowPyPNPhsi2t2TnOLnOK9WsRxmOExsMCpq0D fhaXTZxuoGxXLEZfwI8k0jjMZsfIt0I_qbdt.o4yllFxts4Vu.eND.pfVrWD KQCGza_JpqHWQjJ2Mh3RIF5tf2ybjuK6mnIZLdF1wT_KYUaoWb2tTYoXZ_Nl sWFKLu40BGrIPMaw5950pdqCppYQq X-Yahoo-SMTP: EtaTb4uswBBSx2FARGS8Y34d19mAO_hWIqN6lfRu55otbg-- Received: from BERNICE (midgems@71.135.45.172 with login) by smtp101.sbc.mail.bf1.yahoo.com with SMTP; 22 Aug 2011 09:48:35 -0700 PDT Message-ID: <2DF9D6EED638438A995A442C05D54688@BERNICE> From: "Midge Brown" To: "Greg Smith" , References: <4E515A69.6050709@2ndQuadrant.com> Subject: Re: settings input for upgrade Date: Mon, 22 Aug 2011 09:48:40 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0072_01CC60B0.ABED54B0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5931 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 201108/246 X-Sequence-Number: 44693 This is a multi-part message in MIME format. ------=_NextPart_000_0072_01CC60B0.ABED54B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thank you so much for the input, and the detail provided.=20 I'll be making the configuration changes, probably over the course of = the week, checking the affect after each (as reminded by Scott Marlowe). = I was pushed to put the new version into production over the weekend, = which at least may provide me with some accurate feedback, and so will = see what happens for a bit before addressing the disk/drive layout.=20 -Midge ----- Original Message -----=20 From: Greg Smith=20 To: pgsql-performance@postgresql.org=20 Sent: Sunday, August 21, 2011 12:20 PM Subject: Re: [PERFORM] settings input for upgrade On 08/18/2011 05:55 PM, Midge Brown wrote: DB1 is 10GB and consists of multiple tables that I've spread out so = that the 3 most used have their data and indexes on 6 separate RAID1 = drives, the 3 next busiest have data & index on 3 drives, and the = remaining tables and indexes are on the RAID10 drive. The WAL for all is = on a separate RAID1 drive.=20 DB2 is 25GB with data, index, and WAL all on separate RAID1 drives. DB3 is 15GB with data, index, and WAL on separate RAID1 drives. Anytime you have a set of disks and a set of databases/tables to lay = out onto them, there are two main options to consider: -Put all of them into a single RAID10 array. Performance will be high = now matter what subset of the database is being used. But if one = particular part of a database is really busy, it can divert resources = away from the rest. -Break the database into fine-grained pieces and carefully lay out = each of them on disk. Performance of any individual chunk will be = steady here. But if only a subset of the data is being used, server = resources will be idle. All of the disks that don't have data related = to that will be unused. Consider two configurations following these ideas: 1) 12 disks are placed into a large RAID10 array. Peak transfer rate = will be about 600MB/s on sequential scans. 2) 6 RAID1 arrays are created and the database is manually laid out = onto those disks. Peak transfer rate from any one section will be = closer to 100MB/s. Each of these is optimizing for a different use scenario. Here's the = best case for each: -One user is active, and they're hitting one of the database sections. = In setup (1) they might get 600MB/s, the case where it shows the most = benefit. In setup (2), they'd only get 100MB/s. -10 users are pounding one section of the database; 1 user is hitting = a different section. In setup (2), all 10 users will be fighting over = access to one section of the disk, each getting (at best) 10MB/s of its = transfers. The nature of random I/O means that it will likely be much = worse for them. Meanwhile, the user hitting the other database section = will still be merrily chugging away getting their 100MB/s. Had setup = (1) been used, you'd have 11 users fighting over 600MB/s, so at best = 55MB/s for each. And with the random mix, it could be much worse. Which of these is better? Well, (1) is guaranteed to use your = hardware to its fullest capability. There are some situations where = contention over the disk array will cause performance to be lower for = some people, compared to if they had an isolated environment split up = more like (2). But the rest of the time, (2) will have taken a large = number of disks and left them idle. The second example shows this = really well. The mere fact that you have such a huge aggregate speed = available means that the big array really doesn't necessarily suffer = that badly from a heavy load. It has 6X as much capacity to handle = them. You really need to have a >6:1 misbalance in access before the = carefully laid out version pulls ahead. In every other case, the big = array wins. You can defend (2) as the better choice if you have really compelling, = hard data proving use of the various parts of the data is split quite = evenly among the expected incoming workload. If you have response time = latency targets that require separating resources evenly among the = various types of users, it can also make sense there. I don't know if = the data you've been collecting from your older version is good enough = to know that for sure or not. In every other case, you'd be better off just dumping the whole pile = into a single, large array, and letting the array and operating system = figure out how to schedule things best. That why this is the normal = practice for building PostgreSQL systems. The sole exception is that = splitting out the pg_xlog filesystem can usually be justified in a = larger array. The fact that it's always sequential I/O means that = mixing its work with the rest of the server doesn't work as well as = giving it a dedicated pair of drives to write to, where it doesn't ever = stop to seek somewhere else. =20 wal_buffers =3D 32MB This might as well drop to 16MB. And you've already gotten some = warnings about work_mem. Switching to a connection pooler would help = with that, too. autovacuum_analyze_threshold =3D 250=20 autovacuum_naptime =3D 10min autovacuum_vacuum_threshold =3D 250 vacuum_cost_delay =3D 10ms This strikes me as more customization than you really should be doing = to autovacuum, if you haven't been running on a recent version of = PostgreSQL yet. You shouldn't ever need to touch the thresholds for = example. Those only matter on really small tables; once something gets = big enough to really matter, the threshold part is really small compared = to the scale factor one. And the defaults are picked partly so that = cleanup of the system catalog tables is done frequently enough. You're = slowing that cleanup by moving the thresholds upward so much, and that's = not a great idea. =20 For similar reasons, you really shouldn't be touching = autovacuum_naptime unless there's really good evidence it's necessary = for your environment. Changing things such that regular vacuums executed at the command line = happen with a cost delay like this should be fine though. Those will = happen using twice as many resources as the autovacuum ones, but not run = as fast as possible as in the normal case. deadlock_timeout =3D 3s=20 You probably don't want to increase this. When you reach the point = where you want to find slow lock issues by turning on log_lock_waits, = you're just going to put it right back to the default again--or lower = it. --=20 Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us ------=_NextPart_000_0072_01CC60B0.ABED54B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thank you so much for the input, and = the detail=20 provided.
 
I'll be making the configuration = changes,=20 probably over the course of the week, checking the affect after = each (as=20 reminded by Scott Marlowe). I was pushed to put the new version = into=20 production over the weekend, which at least may provide me = with some=20 accurate feedback, and so will see what happens for a bit before = addressing=20 the disk/drive layout. 
 
-Midge
 
----- Original Message -----
From:=20 Greg=20 Smith
To: pgsql-performance@postgr= esql.org=20
Sent: Sunday, August 21, 2011 = 12:20=20 PM
Subject: Re: [PERFORM] settings = input for=20 upgrade

On 08/18/2011 05:55 PM, Midge Brown wrote:
 
DB1 is 10GB and = consists of=20 multiple tables that I've spread out so that the 3 most used = have their=20 data and indexes on 6 separate RAID1 drives, the 3 next busiest have = data=20 & index on 3 drives, and the remaining tables and indexes are on = the=20 RAID10 drive. The WAL for all is on a separate RAID1 drive.=20
DB2 is 25GB with data, index, and = WAL all on=20 separate RAID1 drives.
DB3 is 15GB with data, index, and = WAL on=20 separate RAID1 drives.

Anytime you have = a set of=20 disks and a set of databases/tables to lay out onto them, there are = two main=20 options to consider:

-Put all of them into a single RAID10 = array. =20 Performance will be high now matter what subset of the database is = being=20 used.  But if one particular part of a database is really busy, = it can=20 divert resources away from the rest.

-Break the database into=20 fine-grained pieces and carefully lay out each of them on disk. =20 Performance of any individual chunk will be steady here.  But if = only a=20 subset of the data is being used, server resources will be idle.  = All of=20 the disks that don't have data related to that will be = unused.

Consider=20 two configurations following these ideas:

1) 12 disks are = placed into a=20 large RAID10 array.  Peak transfer rate will be about 600MB/s on=20 sequential scans.

2) 6 RAID1 arrays are created and the = database is=20 manually laid out onto those disks.  Peak transfer rate from any = one=20 section will be closer to 100MB/s.

Each of these is optimizing = for a=20 different use scenario.  Here's the best case for = each:

-One user=20 is active, and they're hitting one of the database sections.  In = setup=20 (1) they might get 600MB/s, the case where it shows the most = benefit.  In=20 setup (2), they'd only get 100MB/s.

-10 users are pounding one = section=20 of the database; 1 user is hitting a different section.  In setup = (2),=20 all 10 users will be fighting over access to one section of the disk, = each=20 getting (at best) 10MB/s of its transfers. The nature of random I/O = means that=20 it will likely be much worse for them.  Meanwhile, the user = hitting the=20 other database section will still be merrily chugging away getting = their=20 100MB/s.  Had setup (1) been used, you'd have 11 users fighting = over=20 600MB/s, so at best 55MB/s for each.  And with the random mix, it = could=20 be much worse.

Which of these is better?  Well, (1) is = guaranteed=20 to use your hardware to its fullest capability.  There are some=20 situations where contention over the disk array will cause performance = to be=20 lower for some people, compared to if they had an isolated environment = split=20 up more like (2).  But the rest of the time, (2) will have taken = a large=20 number of disks and left them idle.  The second example shows = this really=20 well.  The mere fact that you have such a huge aggregate speed = available=20 means that the big array really doesn't necessarily suffer that badly = from a=20 heavy load.  It has 6X as much capacity to handle them.  You = really=20 need to have a >6:1 misbalance in access before the carefully laid = out=20 version pulls ahead.  In every other case, the big array = wins.

You=20 can defend (2) as the better choice if you have really compelling, = hard data=20 proving use of the various parts of the data is split quite evenly = among the=20 expected incoming workload.  If you have response time latency = targets=20 that require separating resources evenly among the various types of = users, it=20 can also make sense there.  I don't know if the data you've been=20 collecting from your older version is good enough to know that for = sure or=20 not.

In every other case, you'd be better off just dumping the = whole=20 pile into a single, large array, and letting the array and operating = system=20 figure out how to schedule things best.  That why this is the = normal=20 practice for building PostgreSQL systems.  The sole exception is = that=20 splitting out the pg_xlog filesystem can usually be justified in a = larger=20 array.  The fact that it's always sequential I/O means that = mixing its=20 work with the rest of the server doesn't work as well as giving it a = dedicated=20 pair of drives to write to, where it doesn't ever stop to seek = somewhere=20 else.
 =20
 wal_buffers =3D=20 32MB

This=20 might as well drop to 16MB.  And you've already gotten some = warnings=20 about work_mem.  Switching to a connection pooler would help with = that,=20 too.


 autovacuum_analyze_threshold =3D=20 250=20

 autovacuum_naptime =3D=20 10min

 autovacuum_vacuum_threshold =20 =3D 250

 vacuum_cost_delay =3D=20 10ms


This strikes me as more = customization than you really should be doing to autovacuum, if you = haven't=20 been running on a recent version of PostgreSQL yet.  = You shouldn't=20 ever need to touch the thresholds for example.  Those only matter = on=20 really small tables; once something gets big enough to really matter, = the=20 threshold part is really small compared to the scale factor one.  = And the=20 defaults are picked partly so that cleanup of the system catalog = tables is=20 done frequently enough.  You're slowing that cleanup by moving = the=20 thresholds upward so much, and that's not a great idea.  =

For=20 similar reasons, you really shouldn't be touching autovacuum_naptime = unless=20 there's really good evidence it's necessary for your environment.

Changing = things such=20 that regular vacuums executed at the command line happen with a cost = delay=20 like this should be fine though.  Those will happen using twice = as many=20 resources as the autovacuum ones, but not run as fast as possible as = in the=20 normal case.

 deadlock_timeout =3D=20 3s =

You=20 probably don't want to increase this.  When you reach the point = where you=20 want to find slow lock issues by turning on log_lock_waits, you're = just going=20 to put it right back to the default again--or lower it.

--=20
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   =
Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
------=_NextPart_000_0072_01CC60B0.ABED54B0--