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

Reporting back.. no.. I tested the track_count and autovacuum and the writes
where back.
/ Jennifer

On Thu, Jun 4, 2009 at 6:54 PM, Jennifer Trey <jennifer(dot)trey(at)gmail(dot)com>wrote:

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