Oh. I forgot to add: effective_cache_size = 196608 # 3x shared_buffers
Cheers Peter On Thu 26 Jul 2007, Peter Nixon wrote: > Well, I have a pretty small, single core SunFire x2100 with 2GB ram and > SATA disks as my DB server. Your 8 Core box with 8g of ram and hardware > RAID should therefore at least 4 times faster, possibly up to 10 times > faster. > > The major differences I have are: > > max_connections = 400 > shared_buffers = 65536 # Should be at > max_connections*2. 8KB each. > # Recommend 25% of RAM > work_mem = 10000 # min 64, size in KB > > Your shared_buffers are WAY to low for a box with 8GB ram. The Postgresql > tuning guide clearly recommends 25% of ram so you need to make that 2GB > instead of 400MB. I guess you will see significant performance gains. > > I would also enable autovacuum as the radippool and radacct tables are > constantly changing.. > > Let me know how it goes.. > > Cheers > > Peter > > On Thu 26 Jul 2007, Roy Walker wrote: > > Here is the config lines: > > > > max_connections = 100 > > shared_buffers = 400MB > > temp_buffers = 32MB > > work_mem = 1MB > > maintenance_work_mem = 128MB > > max_fsm_pages = 204800 > > > > Didn't change any of these as for my testing I don't have autovacuum > > enabled. > > #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 > > > > #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 > > > > #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? > > # 'on' requires > > stats_start_collector > > # and stats_row_level to also be > > on > > #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
