Re: Re: High I/O writes activity on disks causing images on browser to lag and not load - Mailing list pgsql-general

On Thu, Jun 4, 2009 at 4:58 PM, Bill Moran <wmoran@potentialtech.com> wrote:

In response to Jennifer Trey <jennifer.trey@gmail.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