On 7 Jun 2004 at 16:38, Rod Taylor wrote: > On Mon, 2004-06-07 at 16:12, Dan Langille wrote: > > I grep'd postgresql.conf: > > > > #effective_cache_size = 1000 # typically 8KB each > > #random_page_cost = 4 # units are one sequential page fetch cost > > This would be the issue. You haven't told PostgreSQL anything about your > hardware. The defaults are somewhat modest. > > http://www.postgresql.org/docs/7.4/static/runtime-config.html > > Skim through the run-time configuration parameters that can be set in > postgresql.conf. > > Pay particular attention to: > * shared_buffers (you may be best with 2000 or 4000)
I do remember increasing this in the past. It was now at 1000 and is now at 2000. see http://rafb.net/paste/results/VbXQcZ87.html > * effective_cache_size (set to 50% of ram size if dedicated db > machine) The machine has 512MB RAM. effective_cache_size was at 1000. So let's try a 256MB cache. Does that the match a 32000 setting? I tried it. The query went to 1.5s. At 8000, the query was 1s. At 2000, the query was about 950ms. This machine is a webserver/database/mail server, but the FreshPorts database is by far its biggest task. > * random_page_cost (good disks will bring this down to a 2 from a > 4) I've got mine set at 4. Increasing it to 6 gave me a 1971ms query. At 3, it was a 995ms. Setting it to 2 gave me a 153ms query. How interesting. For camparison, I reset shared_buffers and effective_cache_size back to their original value (both at 1000). This gave me a 130-140ms query. The disks in question is: ad0: 19623MB <IC35L020AVER07-0> [39870/16/63] at ata0-master UDMA100 I guess that might be this disk: http://www.harddrives4less.com/ibmdes6020ua2.html I invite comments upon my findings. Rod: thanks for the suggestions. > > > -- > Rod Taylor <rbt [at] rbt [dot] ca> > > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL > PGP Key: http://www.rbt.ca/signature.asc > > -- Dan Langille : http://www.langille.org/ BSDCan - http://www.bsdcan.org/ ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
