From: Nicolas Thauvin <nicolas.thauvin@dalibo.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: G.De_Leeuw@eurofer.be
Cc: pgsql-admin@postgresql.org
Subject: Re: How to
Date: Mon, 07 Feb 2011 18:56:41 +0100
Message-ID: <4D503259.2000905@dalibo.com> (raw)
In-Reply-To: <4D4E7861020000250003A4C9@gw.wicourts.gov>
References: <4D4E7861020000250003A4C9@gw.wicourts.gov>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 06/02/2011 17:30, Kevin Grittner wrote:
> Guy Deleeuw wrote:
>
> My hardware : lapto hp envy I7 processor with a ssd disk
>
>> Memory :
>> total used free shared buffers cached
>> Mem: 8129356 2866516 5262840 0 387172 948132
>> -/+ buffers/cache: 1531212 6598144
>> Swap: 15625208 0 15625208
>
> It looks like everything you're running, including caching the entire
> database, or at least the active part of it, fits in less than 3 GB
> on your 8 GB machine.
>
>> shared_buffers = 24MB
>
> Given your hardware, I would bump that to 2GB or so.
>
> I would also set effective_cache_size to 7GB.
Here I think it is too much because when the 2 GB of shared memory will
be used, only 6 GB of RAM will be left and maybe not entirely used for
file system cache. My way to find effective_cache_size value is to put
it to 50% of the amount of ram, drop the cache after tuning, leaving the
server live for a while and have a look at the output of free to find
the final value.
> I would set work_mem
> to at *least* 50MB; if you're only using a few connections, you could
> easily bump that to 100MB to 200MB. Fully cached, especially if it's
> all on PostgreSQL shared memory, seq_page_cost and random_page_cost
> both to 0.05. Between the SSD and having all data in shared memory,
> you might want to go even lower. There is some other general tuning
> to be done, but the above will make the most difference for read-only
> queries, which is what you're asking about.
>
> [nested views, using subqueries]
>
> You should probably look at reqorking the v_stock view to use joins
> instead of subqueries -- they often optimize much better.
>
> I didn't spend a lot of time with the EXPLAIN ANALYZE output, because
> if you configure PostgreSQL to get costing factors more in line with
> actual costs for that hardware, you're likely to see totally
> different plans. And I can pretty much guarantee that if you tune
> the costs *and* rework that view to use JOINs, you'll get completely
> different plans which will perform *much* better.
>
> I hope this helps.
>
> -Kevin
>
- --
Nicolas Thauvin
DBA
http://www.dalibo.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iQIcBAEBAgAGBQJNUDJZAAoJEDa2VZzWZKpN/L0P/i1/C7VRz1+vo1itkO3/VD9+
AMvErAsEh5TDfDM8/a4pZ6f6MVZzDxBf5+MB1kOTInUzhj48mS5+XHYJbdmzeRHy
tNCCHhGGQ7M6l7W3cOCKh1tytumPuGzCw4rzFLi0/3IwBwi52AfnW+ibRSjXxGxU
2heAOwDc6amxTaocthuYna4lN+tgp23R3fENl6p+/2cGgN2WwXH1UytAUyH2OTFQ
fxUZmyn7VUDnWb6u/vLsjVtAPz5Zq7Syc+VA0F886RFVguqau0tpHVI35Cqfu6rj
Rd9LTC5+FkD1HiXGBWimEzJX6RfPaQ/Yq5TzVWqIZcUm7DIREEwZhfp+fiUeZzV+
XRK1aaTuREGNXaRp/ISsVrJI2Hp9lpX+qLrggWRNZpSJaSa03foS5kYBxA8Nnmbh
M6qZIKTOC0gt0FiNdhONe8+kblEsLUMlEWS3RO6FXyHqCUsBi7kQOc0/07GLY52K
m0lbWJ2QUEGc2uto1z1bReF+DK2+pxHVIZ0RF4yv+0OcM/ERVi/kn6pTHslhMOwP
YFpAGaQ+3AflIvwq0WulqbnStuoMpDlCypic1s6sDshkkFMnQczDGhj0cI6fl+tP
yPvuM14tpZV148LwZ1OGR7DoCi4cHbWuXcOuJ0R4hWDHUXtcmtsSetn4MBC8VS2O
Ln484THmHEmLAmmu/rt6
=Rdav
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: nicolas.thauvin@dalibo.com, Kevin.Grittner@wicourts.gov, G.De_Leeuw@eurofer.be
Subject: Re: How to
In-Reply-To: <4D503259.2000905@dalibo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox