Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id A69879FB203 for ; Fri, 20 Oct 2006 05:12:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 63598-05 for ; Fri, 20 Oct 2006 08:12:54 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.178]) by postgresql.org (Postfix) with ESMTP id 838469FB1B8 for ; Fri, 20 Oct 2006 05:12:54 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so23086pyd for ; Fri, 20 Oct 2006 01:12:20 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=IhdzGjNmiKk/no0eLdoQaCdL08ViP9FRIsufsFwgE08M8MDnGNpTbOX3zB/ke577eQQQYyc/JCmx1istBIb8UKur9dcjdo34MND2iwGHsdzzubJiD1kXJ+5Cq8nc+Qeti3eqPCGeIPvOGqZ1D2oECHp4pce+hm3D2cXuywo2tz0= Received: by 10.35.79.3 with SMTP id g3mr127804pyl; Fri, 20 Oct 2006 01:12:19 -0700 (PDT) Received: by 10.35.33.2 with HTTP; Fri, 20 Oct 2006 01:12:19 -0700 (PDT) Message-ID: <7be3f35d0610200112r62185561uedb9276b784ec273@mail.gmail.com> Date: Fri, 20 Oct 2006 10:12:19 +0200 From: "Harald Armin Massa" To: pgsql-performance@postgresql.org Subject: Re: measuring shared memory usage on Windows In-Reply-To: <7be3f35d0610161330r4df390f2uecf8d411c861911c@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_15342_23463805.1161331939872" References: <7be3f35d0610160518h649c8f20pee9949a90796c346@mail.gmail.com> <6BCB9D8A16AC4241919521715F4D8BCEA357F5@algol.sollentuna.se> <7be3f35d0610161330r4df390f2uecf8d411c861911c@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.2 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/426 X-Sequence-Number: 21555 ------=_Part_15342_23463805.1161331939872 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Performance readers .... I follow up my promise: > I try my theories :)) and contrary to all wisdom from all PostgreSQL > tuning recommendations reconfigured shared memory nearly to the > minimum: 1000 for maximum of 400 concurrent connections. (800 would be > minimum). Single user performance was fine, now I am looking forward > to full user scenario tomorrow. > > I will keep you posted. > > Harald I went even further down, max_connections =3D 200 # shared_buffers =3D 400 # min 16 or max_connections*2, 8KB each to the minimum allowed value of shared_buffers. And the response times are better then ever before (with 10.000, 20.000, 5.000 and 40.000shared_buffer= s): An application-level response dropped from 16 / 9.5 seconds (first run / cached run) to 12 / 6.5 average runtime. That response time is mainly dependend on SQL performance, and esp. the drop is caused by this change. Moreover, the columns "swapped out memory" in task manager stay low at ~26k per postgres.exe, compared to ~106k as my shared_buffers where at 10.000. The "memory" column of postgres.exe in task manager process still grows up to >10.000K, while now "virtual memory" stays ~3.600k per process So: in this configuration / workload: -> windows 2k3 -> small (~0,4GB) Database -> rather complex queries -> ~1 GB memory -> running in virtual machine and -> windows xp -> small (~0,4GB) Database -> ~0,5 GB memory -> rather complex queries -> running native (laptops) I could verify a substantial speed gain in single and many user situations by lowering shared_buffers to the allowed minimum. Harald --=20 GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstra=DFe 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords. ------=_Part_15342_23463805.1161331939872 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Performance readers ....

I follow up my promise:
I try my theories :)) and co= ntrary to all wisdom from all PostgreSQL
tuning recommendations reconfigured shared memory nearly to the
mini= mum: 1000 for maximum of 400 concurrent connections. (800 would be
minim= um). Single user performance was fine, now I am looking forward
to full = user scenario tomorrow.

I will keep you posted.

Harald

I went e= ven further down,

max_connections =3D 200     &n= bsp;  #
shared_buffers =3D 400       = ;     # min 16 or max_connections*2, 8KB each
 to the minimum allowed value of shared_buffers. And the response times are= better then ever before (with=20 10.000, 20.000, 5.000 and 40.000 shared_buffers):

An application-lev= el response dropped from 16 / 9.5 seconds (first run / cached run) to 12 / = 6.5 average runtime. That response time is mainly dependend on SQL performa= nce, and esp. the drop is caused by this change.

Moreover, the columns "swapped out memory" in task manage= r stay low at ~26k per postgres.exe, compared to ~106k as my shared_buffers= where at 10.000.

The "memory" column of postgres.exe in t= ask manager process still grows up to > 10.000K, while now "virtual memory" stays ~3.600k per process
=
So: in this configuration / workload:
-> windows 2k3
-> sm= all (~0,4GB) Database
-> rather complex queries
-> ~1 GB memory= =20
-> running in virtual machine

and
-> windows xp
-> small (~0,4GB) Database
-> ~0,5 GB memory
-> rather complex queries
-> running nati= ve (laptops)

I could verify a substantial speed gain in single and m= any user situations by lowering shared_buffers to the allowed minimum.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald A= rmin Massa
Reinsburgstra=DFe 202b
70197 Stuttgart
0173/9409607
= -
Python: the only language with more web frameworks than keywords. ------=_Part_15342_23463805.1161331939872--