SQLIPPool performance issue

[Roy Walker]

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/
- 
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html