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: freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradiu 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/