-----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----- -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
