Roy, The obvious really bad ones I have noted below.
Ken On Thu, Jul 26, 2007 at 12:57:15PM -0500, Roy Walker wrote: > Here is the config lines: > > max_connections = 100 > shared_buffers = 400MB Could be as much as 25% of RAM or 2GB. > temp_buffers = 32MB > work_mem = 1MB Running EXPLAIN ANALYZE for the logged queries will let you know if this value is too small. In particular, it is used to evaluate whether or not a hash/merge join can be used. You may need to raise it depending on what your query analysis shows. > maintenance_work_mem = 128MB Bump this up to 256MB or 512MB or more. Otherwise maintaenance actions can become disk I/O bound. > max_fsm_pages = 204800 This needs to be large enough to handle the size of your DB. > > Didn't change any of these as for my testing I don't have autovacuum > enabled. You definitely need to enable autovacuum. Poor plans due to poor statistics can hamstring your performance. > #vacuum_cost_delay = 0 # 0-1000 milliseconds > #vacuum_cost_page_hit = 1 # 0-10000 credits > #vacuum_cost_page_miss = 10 # 0-10000 credits > #vacuum_cost_page_dirty = 20 # 0-10000 credits > #vacuum_cost_limit = 200 # 0-10000 credits > You may need the bgwriter to smooth out checkpoint I/O. Check to see if you are getting checkpoint errors in your logs. > #bgwriter_delay = 200ms # 10-10000ms between rounds > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers > scanned/round > #bgwriter_lru_maxpages = 5 # 0-1000 buffers max > written/round > #bgwriter_all_percent = 0.333 # 0-100% of all buffers > scanned/round > #bgwriter_all_maxpages = 5 # 0-1000 buffers max > written/round > > #wal_buffers = 64kB Bump this to 256kB. > > #commit_delay = 0 # range 0-100000, in > microseconds > #commit_siblings = 5 # range 1-1000 > > checkpoint_segments = 32 # in logfile segments, min 1, > 16MB each > #checkpoint_timeout = 5min # range 30s-1h > > #random_page_cost = 4.0 > > autovacuum = off # enable autovacuum subprocess? Should be on. > # 'on' requires > stats_start_collector > # and stats_row_level to also be > on On, and stats_row_level should be on too. > #autovacuum_naptime = 1min # time between autovacuum runs > #autovacuum_vacuum_threshold = 500 # min # of tuple updates before > # vacuum > #autovacuum_analyze_threshold = 250 # min # of tuple updates before > # analyze > #autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before > # vacuum > #autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before > # analyze > #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced > vacuum > # (change requires restart) > #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for > # autovacuum, -1 means use > # vacuum_cost_delay > #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for > # autovacuum, -1 means use > # vacuum_cost_limit > > -----Original Message----- > From: > [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > s.org] On Behalf Of Peter Nixon > Sent: Thursday, July 26, 2007 9:53 AM > To: FreeRadius users mailing list > Subject: Re: SQLIPPool performance issue > > On Thu 26 Jul 2007, Kenneth Marshall wrote: > > Roy, > > > > It sounds like you may need to adjust the DB parameters. The defaults, > > even in 8.2, are still fairly conservative. Would you post your > current > > settings for things like: > > > > max_connections > > shared_buffers > > work_mem > > maintenance_work_mem > > max_fsm_pages > > vacuum_cost_* > > bgwriter_* > > wal_buffers > > commit_delay > > commit_siblings > > checkpoint_segments > > checkpoint_timeout > > random_page_cost > > effective_cache_size > > autovacuum > > autovacuum_* > > > > Basically, anything you have changed from the default configuration > > file. Proper choices for these parameters can make a huge difference > > in baseline performance. > > Yep. My guess is, on that box, if he is running a default Postgresql > config > he should get 10-100 times greater performance after tuning it correctly > for > the ram and cpu setup.. > > Cheers > > -- > > Peter Nixon > http://peternixon.net/ > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
