PostgreSQL Tuning Results

Hi Everyone,

I have just completed a basic set of benchmarking on our new database
server. I wanted to figure out a good value for shared_buffers before we go
live.

We are a busy ecommerce-style website and so we probably get 10 or 20 to 1
read transactions vs. write transactions. We also don't have particularly
large tables.

Attached are the charts for select only and tpc-b runs. Also attached is an
OpenOffice.org spreadsheet with all the results, averages and charts. I
place all these attachments in the public domain, so you guys can use them
how you wish.

I installed pgbench, and set up a pgbench database with scale factor 1.

I then set shared_buffers to all the values between 2000 and 11000 and
tested select and tcp-b with each. I ran each test 3 times and averaged the
values. TPC-B was run after select so had advantages due to the buffers
already being filled, but I was consistent with this.

Machine:
256MB RAM, FreeBSD 4.7, EIDE HDD, > 1 Ghz

TPC-B config:
pgbench -c 64 -t 100 pgbench (Note: only 64 users here)

SELECT config:
pgbench -c 128 -t 100 -S pgbench (Note: full 128 users here)

I'm not sure why 8000 and 9000 are low on tpc-b, it's odd.

Anyway, from the attached results you can see that 4000 buffers gave the
best SELECT only performance, whereas the TPC-B stuff seemed to max out way
up at 10000 or so. Since there is a 20% gain in performance on TPC-B going
from 4000 buffers to 5000 buffers and only a 2% loss in performance for
SELECTs, I have configured my server to use 5000 shared buffers, eg. 45MB
RAM.

I am now going to leave it on 5000 and play with wal_buffers. Is there
anything else people are interested in me trying?

Later on, I'll run pg_autotune to see how its recommendation matches my
findings.

Chris

Hi Chris,

On Wed, 12 Feb 2003, Christopher Kings-Lynne wrote:

> Machine:
> 256MB RAM, FreeBSD 4.7, EIDE HDD, > 1 Ghz

Seems like a small amount of memory to be memory based tests with.

What about testing sort_mem as well. It would system to me that there
would be no negative to having infinite sort_mem given infinite memory,
though.

Gavin

> > Machine:
> > 256MB RAM, FreeBSD 4.7, EIDE HDD, > 1 Ghz
>
> Seems like a small amount of memory to be memory based tests with.

Perhaps, but I'm benchmarking for that machine, not for any other. The
results have to include the 256MB spec.

Also, the peak was 25MB of SHM, which still leave 231MB for the rest of the
system, so surely RAM is not the bottleneck here?

> What about testing sort_mem as well. It would system to me that there
> would be no negative to having infinite sort_mem given infinite memory,
> though.

Yeah, however I'm pretty sure that pgbench doesn't perform any sorts.

I reckon that sort_mem is the hardest thing to optimise1

Chris

Gavin Sherry wrote:

>Hi Chris,
>
>On Wed, 12 Feb 2003, Christopher Kings-Lynne wrote:
>
>
>
>>Machine:
>>256MB RAM, FreeBSD 4.7, EIDE HDD, > 1 Ghz
>>
>>
>
>Seems like a small amount of memory to be memory based tests with.
>
>What about testing sort_mem as well. It would system to me that there
>would be no negative to having infinite sort_mem given infinite memory,
>though.
>
>Gavin
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>

Be careful with sort_mem - this might lead to VERY unexpected results. I
did some testing on my good old Athlon 500 with a brand new IBM 120 Gigs
HDD. Reducing the sort_mem gave me significantly faster results when
sorting/indexing 20.000.000 randon rows.
However, it would be nice to see the results of concurrent sorts.

Hans

--
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at
<http://cluster.postgresql.at>, www.cybertec.at
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>

Christopher Kings-Lynne wrote:
>
>I reckon that sort_mem is the hardest thing to optimise1
>

Agreed... in part because it depends a lot on the query.

Also, if I understand correctly sort_mem not only affects sorts
but also hash table stuff as well, right? If that's true for
the new hash aggregates, I think this means large sort_mem settings
will become even more useful for data-warehouse-type applications.

One thing I've been wondering, is if "sort_mem" could be
per connection/backend-process instead of per sorting operation
so that sort_mem could be set more aggressivelly without running out
of memory so easily with large queries.

If that's not possible (i.e. one couldn't know how many simultaneous
sorts are needed beforehand), how about only let the first one or
two get all the memory and make the rest use a smaller one.

Anecdote:
I have a reasonably large data warehouse (1e6 to 1e8 rows
in various tables) with quite a bit of data (500k rows) added
each day. A lot of processing (7 hours/day) is spent loading
data and generating various aggregates. In a couple places
in the ETL part of the data warehouse code I have:

set sort_mem = 250000;
/// something that only needs a single sort
set sort_mem = 65536;
...
set sort_mem = 4096;
/// some ugly aggregate-creating join generated by a reporting tool
set sort_mem = 65536;

Ron

On Wed, 12 Feb 2003, [ISO-8859-1] Hans-J鱧緕竏鉗阡綺

代癇繙蜚齒鶯熏纃蓍迚艾跂痲崚燮鈬繝鱚齦踉鶤
粡齒辣纉鈑逋闖闌藻蓐闔旭蜚碣瘤鈬病渦秤苴
板町繖蜴齒鶯熏纃癘辣蜃鉗肅竅銓踟癈鱚齦踉緕
齒鶯蜴膀蜴粤鈑握旭握旭鱇鈔闔阯鶤

巣瘡踟鱚齦踉癇竢逅跂踟繝闔竇阨鈿癆
纔痺蜩濶鈑隨阨鈔蜥癆齒鶯鱚踈鼬阯蜴粹蜚迴鱚闥辣迴鴒銓蛹闕択闥闕繽鈬濶銓繖辣葹逋瘟逋闥纈齬瘰韈鈑
膚ы鈔纈齡瘤粡鈑蓍竢鴪繝蓍矚皷竅跛辣瘤癆齒鶯
鱚齦踉齡癇蓍蜴粡鼡繝黼蒹鱚繪鈑瘍繖齬瘰鞜竇碯緕釿碎閭齒鶯繖蜚阨趙鱚痲瘍瘟胙闕蜩覓鈔蒹蜚阨蜩瘍瘟粡聿纈緕韭痺紿竰縺鈑阡闥逼瘤癈縺跛鈬竇齠癇

壽蜩蜍纉絳闖癈鈿纈鱚纈迯瘰蜃蔗跛阯蒹矼纈閧蜚跂齠蜴膊癈蜒瘡踟齒鶯艱蜴齔跚蜴竏鼾瘡跂竏謫縺竏蜒蜩鈔蝟蜆跛齒鶯繖瘤緕阨纈艱緕筱蜃蔗囂瘡闢蜴粡糒瘡
竏謫纈迯瘰繖齷齡纃阨趙縺瘍蜴纃瘤緕繝纉黶鴒瘤肬齒鶯粹繙闥辣鱧絳阨阨趙
迪閭襾癇縺葹鱚鱚銓踟闥鈑轣諷癆粹纉逾鱇鼈
膚蒹齷齡纃窿辮蜴迚艾瘡齒續闕胚鶯蒹關迚闔矼竅竅跛皷鈑痲黼┤緕阨粹蜴鱇鈔闕纈齦黼髟緕瘡窿纉闔蔘鉉辣迴鴒
竓瀦鶯瘢頌闔若褥雪蜒續弘軍温郡雕鈬黻鱧
弟逾謗阯蜴蓍鈬鶴鳬艱瘡跚艾慥

>
>
>Actually, the results are completely expected once you know what's
>exactly is going on. I found it weird that my sorts were also slowing
>down with more sort memory until Tom or Bruce or someone pointed out to
>me that my stats said my sorts were swapping.
>
>

this way my first expectation but since the machine was newly booted and
had 1/2 gig of ram (nothing running but PostgreSQL) I couldn't believe
in that theory ...
Maybe but I couldn't verify that ...
Of course swapping is worse than anything else.

>This strikes me, too, as another area where mmap might allow the system
>to do a better job with less tuning. Basically, the sort is getting
>split into a bunch of smaller chunks, each of which is individually
>sorted, and then you merge at the end, right? So if all those individual
>chunks were mmaped, the system could deal with paging them out if and
>when necessary, and for the sorts you do before the merge, you could
>mlock() the area that you're currently sorting to make sure that it
>doesn't thrash.
>

As far as I have seen in the source code they use Knuth's tape
algorithm. It is based on dividing, sorting, and merging together.

>If the VM system accepts hints, you might also get some further
>optimizations because you can tell it (using madvise()) when you're
>doing random versus sequential access on a chunk of memory.
>
>cj
>

it is an interesting topic. the result of the benchmark is very clock
speed depedent (at least in case my of my data structure).

Hans

--
Cybertec Geschwinde &. Schoenig
Ludo-Hartmannplatz 1/14; A-1160 Wien
Tel.: +43/1/913 68 09 oder +43/664/233 90 75
URL: www.postgresql.at, www.cybertec.at, www.python.co.at, www.openldap.at

Our students are (unfortunately) on solaris x86 (unfortunate because I
have to do another round of testing before I deploy pgsql code for
them to hack).

Sadly, set_ps_display does not seem to have any effect in solaris
x86. At least ps only reports multiple postmaster processes and
arguments.

Is there some magic incantation to be chanted to get this to work ?

Thanks !

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

Sailesh Krishnamurthy <sailesh(at)cs(dot)berkeley(dot)edu> writes:
> Sadly, set_ps_display does not seem to have any effect in solaris
> x86. At least ps only reports multiple postmaster processes and
> arguments.

IIRC, you have to use the "other" version of ps to see the process
status on Solaris. I forget where it lives exactly, /usr/ucb maybe?

regards, tom lane

Tom Lane wrote:
> Sailesh Krishnamurthy <sailesh(at)cs(dot)berkeley(dot)edu> writes:
> > Sadly, set_ps_display does not seem to have any effect in solaris
> > x86. At least ps only reports multiple postmaster processes and
> > arguments.
>
> IIRC, you have to use the "other" version of ps to see the process
> status on Solaris. I forget where it lives exactly, /usr/ucb maybe?

Yep, monitoring chapter mentions it:

<productname>Solaris</productname> requires special handling. You must
use <command>/usr/ucb/ps</command>, rather than
<command>/bin/ps</command>. You also must use two <command>w</command>
flags, not just one. In addition, your original invocation of the
<application>postmaster</application> must have a shorter
<command>ps</command> status display than that provided by each
backend. If you fail to do all three things, the <command>ps</>
output for each backend will be the original
<application>postmaster</>
command line.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

"Ron Mayer" <ron(at)intervideo(dot)com> writes:
> One thing I've been wondering, is if "sort_mem" could be
> per connection/backend-process instead of per sorting operation
> so that sort_mem could be set more aggressivelly without running out
> of memory so easily with large queries.

Not very readily. To estimate the cost of a given operation that
needs working memory, the planner has to know how much memory that
operation will be permitted to use. If you try to make that depend
on how many such operations exist in the total plan, then you are
chasing your tail, because the number of such operations depends on
what the total plan looks like, which depends on which operations are
chosen, which depends on the cost estimates. The cost estimates are
pretty squishy anyway, but they'd become a complete joke if we didn't
know how much RAM each operation would be allowed ...

regards, tom lane

>>>>> "Bruce" == Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:

Bruce> Tom Lane wrote:
>> Sailesh Krishnamurthy <sailesh(at)cs(dot)berkeley(dot)edu> writes:
>> > Sadly, set_ps_display does not seem to have any effect in solaris
>> > x86. At least ps only reports multiple postmaster processes and
>> > arguments.
>>
>> IIRC, you have to use the "other" version of ps to see the process
>> status on Solaris. I forget where it lives exactly, /usr/ucb maybe?

Bruce> Yep, monitoring chapter mentions it:

Thanks !

I was pointed to it by Kevin Brown in private email. He also showed me
how to teach ddd to use the right command as a helper.

Now I need to figure out how to get our sysadmins to make this value a
global change when our students first start ddd - so that they can
easily attach to the right postgres backend while debugging.

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

Quick question for the group

I'm assuming that the PG_TEMP_FILES_DIR for BufFile temps is
automatically under the PGDATA directory. Is that correct ?

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

It is under each database directory, per-database.

---------------------------------------------------------------------------

Sailesh Krishnamurthy wrote:
>
> Quick question for the group
>
> I'm assuming that the PG_TEMP_FILES_DIR for BufFile temps is
> automatically under the PGDATA directory. Is that correct ?
>
> --
> Pip-pip
> Sailesh
> http://www.cs.berkeley.edu/~sailesh
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

unsubscribe