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

> Filtering out with the pid showed that it was the file
> pgdata/global/pgstat.tmp
>> Filtering out with the pid showed that it was the file
>> pgdata/global/pgstat.tmp
>
> That's the statistics collector -- which makes sense, depending
> on your settings, it has to write stats for every operation done in the
> database.
>

This smells of anti-virus software to me (usually the cause of
problems with Postgres on windows).
Are you running any?

I had almost exactly the same problem on xp and 8.3 with pctools
antivirus. This was causing the statistics collector to use 100% of a
cpu core. You won't look back moving to Ubuntu - I haven't :-).

Best regards, Will T

No, I don't have any virus programs installed.
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.

Filtering for that PID, the only action seems to be WriteFile, CreateFile,
CloseFile, SetRenameInformationFile and 2-3 other types with WriteFile being
the dominant one.
The files touched is pgstat.tmp and pgstat.stat, pgstat.tmp being the
dominant one.

As I understand it, writes occur frequently on pgstat.tmp which in the next
step gets renamed and replaces pgstat.stat with a setRenameInformationFile
with information on Detail : ReplaceIfExists: True, FileName:
....\global\pgstat.stat

I turned off AutoVacuum, and restarted PG but this was still going 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.

thanks / Jennifer

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.

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

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

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

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
>