SQLIPPool performance issue

[Kenneth Marshall]

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