Thanks for the information! Are there any rule-of-thumb starting points for these values that you use when setting up servers? I'd at least like a starting point for testing different values. For example, I'm sure setting a default work_mem of 100MB is usually overkill - but is 5MB usually a reasonable number? 20MB? My system does not have a huge number of concurrent users, but they are hitting large tables. I'm not sure what numbers people usually use here successfully. For maintenance_work_mem, I turned off autovacuum to save on performance, but run a vacuum analyze once an hour. My current database characteristics are heavy insert (bulk inserts every 5 minutes) and medium amount of selects on large, heavily indexed tables. For temp_buffers - any rule of thumb starting point? What's the best way to evaluate if this number is adjusted correctly? For random_page_cost - is the default of 4 pretty good for most drives? Do you usually bump it up to 3 on modern servers? I've usually done internal RAID setups, but the database I'm currently working on is hitting a SAN over fiber. I realize that these values can vary a lot based on a variety of factors - but I'd love some more advice on what good rule-of-thumb starting points are for experimentation and how to evaluate whether the values are set correctly. (in the case of temp_buffers and work_mem especially) On Tue, 02 Jan 2007 18:49:54 +0000, "Richard Huxton" <dev@xxxxxxxxxxxx> said: > Jeremy Haile wrote: > > What is a decent default setting for work_mem and maintenance_work_mem, > > considering I am regularly querying tables that are tens of millions of > > rows and have 2-4 GB of RAM? > > Well, work_mem will depend on your query-load. Queries that do a lot of > sorting should benefit from increased work_mem. You only have limited > RAM though, so it's a balancing act between memory used to cache disk > and per-process sort memory. Note that work_mem is per sort, so you can > use multiples of that amount in a single query. You can issue a "set" to > change the value for a session. > > How you set maintenance_work_mem will depend on whether you vacuum > continually (e.g. autovacuum) or at set times. > > > Also - what is the best way to determine decent settings for > > temp_buffers and random_page_cost? > > With all of these, testing I'm afraid. The only sure thing you can say > is that random_page_cost should be 1 if all your database fits in RAM. > > -- > Richard Huxton > Archonet Ltd
