Re: Re: High I/O writes activity on disks causing images on browser to lag and not load

On Thu, Jun 4, 2009 at 4:58 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:

> In response to Jennifer Trey <jennifer(dot)trey(at)gmail(dot)com>:
> > Bill, you wrote earlier :
> >
> > "
> > Additionally, this convinces me further that you're chasing the wrong
> > problem. The stats collector writes tiny bits of information to disk
> > every time you execute a command. If your system is slow because of this
> > tiny bit of I/O, then something else is wrong. Either your system is
> > already near its max capacity and this is pushing it over the edge, or
> > you're fixing the wrong problem.
> > "
> >
> > If this was true, that is that only small bits should be written, why is
> the
> > total write size each time around 57kB (for 15 write ops)? Thats also the
> > size of the file pgstat.tmp. At this time, there is for that posgres
> process
> > 33,330 I/O Writes, with a total size of 129,221,526 Bytes.
>
> In a previous message you posted a snippet of your postgresql.conf file
> that showed you still had a lot of the stats logging turned on. As noted
> in the docs, the default values for many of those settings is on, so the
> fact that they're commented out means they're taking their default values.
> I'm guessing that you haven't actually turned them off yet.
>

Thank you, I apologize for being a little slow :)

Here is a new snippet of my file,

#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------

# - Query/Index Statistics Collector -

track_activities = off
track_counts = off
update_process_title = off

# - Statistics Monitoring -

log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

#autovacuum = on # Enable autovacuum subprocess? 'on'
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum = off # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least that time.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_naptime = 60 # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_vacuum_threshold = 1000 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_analyze_threshold = 250 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
# NOTE: This parameter is been added by EnterpriseDB's Tuning Wiard on
2009/04/08 13:33:58
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20 # 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

I restarded the app and now I have gotten rid of most of the writes. Great
:)
It is still writing, but this time its only doing it once per query. It
seems like its not repeating it self. If I run a query thats not been run
before it seems to do the writes, but only the first time..
I don't mind this at all and reading the Run-Time statistics I am guessing
that it was one of those parameters that was causing this. Possibly the
first one on your link Bill. Could I possibly turn on AutoVacuum and turn on
only track_counts? I am going to try it out..
Looks fine otherwise?

>
> > I turned off AutoVacuum, and restarted PG but this was still going on.
>
> That's not going to change the behaviour of this problem if you've still
> got the stats monitoring turned on.
>
> > I would like to move the PGdata to the pictures disk.
> >
> > "
> > You can just pick up the data directory and relocate it, then config
> > PostgreSQL to look for the data directory in the new location, or create
> > a symlink.
> > "
> >
> > Where can I find that file? I found out that its the pgdata variable but
> > couldn't find out what file it was.
>
> What file? If you want to move the database, then you need to pick up
> the entire directory with all its files and subdirectories.
>

I thought this got solved by the other thread but I wasn't right again :P.
I was referring to the location of the variable or parameter that points
postgres to the new location. If I move the data folder I am guessing that
Postgres is not going to find it unless i tell it the new location. I am
working on this.

>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>

Thanks once again / Jennifer