Subject: Re: Performance
Mime-Version: 1.0 (Apple Message framework v1082)
Content-Type: text/plain; charset=us-ascii
From: Ogden <lists@darkstatic.com>
In-Reply-To: <4DA4BFA2.5060601@fuzzy.cz>
Date: Tue, 12 Apr 2011 16:19:32 -0500
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <B93DFBBB-DA56-4044-A508-0B7E4A2CFD28@darkstatic.com>
References: <DF2D5436-117C-4D02-9B3C-A55723B7DDE1@darkstatic.com>
	<20110412171855.GA14292@tux>
	<FC3A3A2B-3ECB-41BA-8F94-356D6FED3695@darkstatic.com>
	<4DA496FA.3070908@fuzzy.cz>
	<8F22D592-23C1-4A3C-94A5-48363332ADD3@darkstatic.com>
	<4DA4BFA2.5060601@fuzzy.cz>
To: Tomas Vondra <tv@fuzzy.cz>


On Apr 12, 2011, at 4:09 PM, Tomas Vondra wrote:

> Dne 12.4.2011 20:28, Ogden napsal(a):
>>=20
>> On Apr 12, 2011, at 1:16 PM, Tomas Vondra wrote:
>>=20
>>> Dne 12.4.2011 19:23, Ogden napsal(a):
>>>>=20
>>>> On Apr 12, 2011, at 12:18 PM, Andreas Kretschmer wrote:
>>>>=20
>>>>> Ogden <lists@darkstatic.com> wrote:
>>>>>=20
>>>>>> I have been wrestling with the configuration of the dedicated =
Postges 9.0.3
>>>>>> server at work and granted, there's more activity on the =
production server, but
>>>>>> the same queries take twice as long on the beefier server than my =
mac at home.
>>>>>> I have pasted what I have changed in postgresql.conf - I am =
wondering if
>>>>>> there's any way one can help me change things around to be more =
efficient.
>>>>>>=20
>>>>>> Dedicated PostgreSQL 9.0.3 Server with 16GB Ram
>>>>>>=20
>>>>>> Heavy write and read (for reporting and calculations) server.=20
>>>>>>=20
>>>>>> max_connections =3D 350=20
>>>>>> shared_buffers =3D 4096MB =20
>>>>>> work_mem =3D 32MB
>>>>>> maintenance_work_mem =3D 512MB
>>>>>=20
>>>>> That's okay.
>>>>>=20
>>>>>=20
>>>>>>=20
>>>>>>=20
>>>>>> seq_page_cost =3D 0.02                    # measured on an =
arbitrary scale
>>>>>> random_page_cost =3D 0.03=20
>>>>>=20
>>>>> Do you have super, Super, SUPER fast disks? I think, this =
(seq_page_cost
>>>>> and random_page_cost) are completly wrong.
>>>>>=20
>>>>=20
>>>> No, I don't have super fast disks. Just the 15K SCSI over RAID. I
>>>> find by raising them to:
>>>>=20
>>>> seq_page_cost =3D 1.0
>>>> random_page_cost =3D 3.0
>>>> cpu_tuple_cost =3D 0.3
>>>> #cpu_index_tuple_cost =3D 0.005           # same scale as above - =
0.005
>>>> #cpu_operator_cost =3D 0.0025             # same scale as above
>>>> effective_cache_size =3D 8192MB=20
>>>>=20
>>>> That this is better, some queries run much faster. Is this better?
>>>=20
>>> I guess it is. What really matters with those cost variables is the
>>> relative scale - the original values
>>>=20
>>> seq_page_cost =3D 0.02
>>> random_page_cost =3D 0.03
>>> cpu_tuple_cost =3D 0.02
>>>=20
>>> suggest that the random reads are almost as expensive as sequential
>>> reads (which usually is not true - the random reads are =
significantly
>>> more expensive), and that processing each row is about as expensive =
as
>>> reading the page from disk (again, reading data from disk is much =
more
>>> expensive than processing them).
>>>=20
>>> So yes, the current values are much more likely to give good =
results.
>>>=20
>>> You've mentioned those values were recommended on this list - can =
you
>>> point out the actual discussion?
>>>=20
>>>=20
>>=20
>> Thank you for your reply.=20
>>=20
>> http://archives.postgresql.org/pgsql-performance/2010-09/msg00169.php =
is how I first played with those values...
>>=20
>=20
> OK, what JD said there generally makes sense, although those values =
are
> a bit extreme - in most cases it's recommended to leave =
seq_page_cost=3D1
> and decrease the random_page_cost (to 2, the dafault value is 4). That
> usually pushes the planner towards index scans.
>=20
> I'm not saying those small values (0.02 etc.) are bad, but I guess the
> effect is about the same and it changes the impact of the other cost
> variables (cpu_tuple_cost, etc.)
>=20
> I see there is 16GB of RAM but shared_buffers are just 4GB. So there's
> nothing else running and the rest of the RAM is used for pagecache? =
I've
> noticed the previous discussion mentions there are 8GB of RAM and the =
DB
> size is 7GB (so it might fit into memory). Is this still the case?
>=20
> regards
> Tomas


Thomas,

By decreasing random_page_cost to 2 (instead of 4), there is a slight =
performance decrease as opposed to leaving it just at 4. For example, if =
I set it 3 (or 4), a query may take 0.057 seconds. The same query takes =
0.144s when I set random_page_cost to 2. Should I keep it at 3 (or 4) as =
I have done now?

Yes there is 16GB of RAM but the database is much bigger than that. =
Should I increase shared_buffers?

Thank you so very much

Ogden=