SQLIPPool performance issue

[Peter Nixon]

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