Message-ID: <4D503259.2000905@dalibo.com>
Date: Mon, 07 Feb 2011 18:56:41 +0100
From: Nicolas Thauvin <nicolas.thauvin@dalibo.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20101226 Icedove/3.0.11
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: G.De_Leeuw@eurofer.be, pgsql-admin@postgresql.org
Subject: Re: How to
References: <4D4E7861020000250003A4C9@gw.wicourts.gov>
In-Reply-To: <4D4E7861020000250003A4C9@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

-----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-----