SQLIPPool performance issue

[Peter Nixon]

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:
> > 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/