Обсуждение: High I/O writes activity on disks causing images on browser to lag and not load

move postgresql to another disc if possible. You have really slow
discs if that happen, or perhaps it does loads of seq scans, because
of lack of indices.
All in all, it seems like your server is to low spec for what you are
trying to do.

In response to Jennifer Trey <jennifer.trey@gmail.com>:
>
> I have finally found out why I have had images that lag on the website. It
> seems to be that postgre is doing allot of I/O activity and the images is
> somehow suffering because of this.
> The strange part about this is that it seems to be allot more disk writes
> than disk reads. Even though 99.9% of my application traffic is DB read.
> Looking up in tables and presenting.
> Why is postgre doing all this writing? Should it not be I/O reads?

Possible causes:
* Table reads sometimes result in the updating of hint-bits, which equate
  to disk writes.  This should not happen often, however, and not continually.
  http://wiki.postgresql.org/wiki/Hint_Bits
* If you are doing complex queries with sorting and don't have enough RAM,
  PostgreSQL will have to create temporary files.  See the config variable
  log_temp_files:
  http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html
* You have other logging settings set too high and PostgreSQL is tying
  up disk I/O writing log data.
* Your application is more write-intensive than you realize.

> I would now like to move the DB activity to another disk if possible?

That's only going to help if the machine has enough I/O capability to
process both disks.  Adding more RAM might be a better (and cheaper)
solution.

> Would
> I have to re-install PostgreSQL from scratch?

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

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

2009/6/3 Jennifer Trey <jennifer.trey@gmail.com>:

> Not sure about that Grzegorz, its a pretty powerful computer :) and I don't
> have that much data and traffic yet..
> Normally, should the writes really be more than the reads?
> Intel Quad Core 2.4 GHz
> 8GB Memory
> Asus P5B Motherboard (Standard Version)
> Windows Web Server 2008 x64 (trial)
> PostgreSQL 32bit
> Application running Java x64 and on Jetty Server 6
> 2 Samung Disks...


The spec looks pretty neat. I would try to see how is the memory
usage, considering that you use windows and java on it.
I had awful lot of troubles in past with java (well, 1.4, but still),
and I still don't recommend windows as server platform to anyone. But
the second is due to lack of ability to squeeze out from it what I can
get from Linux or Sun Os on same hardware.


--
GJ

On Wed, Jun 3, 2009 at 4:03 PM, Jennifer Trey <jennifer.trey@gmail.com> wrote:

>  Grzegorz, yes, you are right. I would not recommend Windows Server to
> anyone either. I am to weak on linux and thought that this was going to make
> my life easier. Have had lots of issues. I might be trying Ubuntu Server out
> real soon :)
> I am going to try to restart and look at the read, writes for a while.
> Perhaps it was the effective cache that was causing this (lets hope so :) )
> Thanks for the tips and ideas. It feels like I am moving closer to resolving
> this issue once and for all.

Did you reconfigured postgresql in any way, or is it running on default conf ?


--
GJ

In response to Jennifer Trey <jennifer.trey@gmail.com>:
> >
> > * If you are doing complex queries with sorting and don't have enough RAM,
> >  PostgreSQL will have to create temporary files.  See the config variable
> >  log_temp_files:
> >  http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html
>
> I have enough RAM :)
> Java has been awarded 3GB and postgreSQL (Just went to see the value and
> somehow it was set to 438MB! what!?... just changed it to 2800MB.. could
> this be the reason??? 438 should be enough though.. (it was an error) I have
> not that many tuples..)

There are multiple values for configuring Postgres' memory usage.  If you're
twiddling the wrong one, it won't help.  effective_cache_size is one of
the least important, in my experience.

Memory tuning on Windows is quite different than on POSIX systems, so I
can't directly advise you there.

And I laughed when you asserted "I have enough RAM" ... If I had a dollar
for everyone who said something like that and was wrong, I'd buy an island
in the Pacific ...

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

On Wed, Jun 3, 2009 at 8:41 PM, Jennifer Trey <jennifer.trey@gmail.com> wrote:
> Are you sure that moving to Linux will solve this? Could you please check if
> you notice the same write behavior? Any other windows users that noticed
> this?
Never used postgresql on windows myself, but from what I read here,
and comments on other lists by experienced hackers, postgresql was
designed to run on unix, and windows, lets just say, has differences.
Try it for yourself, and see. It really isn't that difficult to set it
up on ubuntu, or some other linux server. You might even want to buy
hosting somewhere cheaply with postgresql (there are sites listed at
http://www.postgresql.org/support/professional_hosting), or if you
have other box to play with.
Just to get an idea of waht's the difference. After all, it may turn
out that problem is somewhere else, not in postgresql itself.


If you contact me in private, I can help you setup postgresql+other
stuff, for small fee.

--
GJ

2009/6/3 Jennifer Trey <jennifer.trey@gmail.com>:
> Not sure about that Grzegorz, its a pretty powerful computer :) and I don't
> have that much data and traffic yet..
> Normally, should the writes really be more than the reads?
> Intel Quad Core 2.4 GHz
> 8GB Memory
> Asus P5B Motherboard (Standard Version)
> Windows Web Server 2008 x64 (trial)
> PostgreSQL 32bit
> Application running Java x64 and on Jetty Server 6
> 2 Samung Disks...

Just want to point out that from a db perspective, that's not that
powerful of a computer.  Can you at least put a battery backed caching
RAID controller into it?  Even if you use the disks in JBOD mode, the
cache on the RAID controller can have a HUGE effect on performance.

More disks after that.

In response to Jennifer Trey <jennifer.trey@gmail.com>:

> Hmm, I just noticed the same write behavior on my Windows Xp laptop but the
> values was a little less.
> I even created an DB with one table and column and this still happened
> when querying it.

By "created", you mean you created a table and populated it with data?
Once you do that, do a "SELECT count(*)" on that table, then wait for
the I/O to calm down.  That select statement will force all the hint
bits to be updated.  See if subsequent selects still cause disk
activity.

> Are you sure that moving to Linux will solve this?

I never advocated that Linux would fix this, and I still don't.  I
recommended a short list of methods to investigate the issue, most of
which you ignored.  You _still_ don't know what's being written, and
I _highly_ recommend that you isolate that before doing something
radical like switching operating systems.

If you've got the DB configured in such a way that it's causing a lot of
write ops, it's going to do it in Linux or any other Posix systems, or
on CP/M for that matter.

Posix systems have a laundry list of tools to identify what programs are
doing.  It's been a while since I've worked with Windows, but I seem to
remember MS having tools to audit disk activity.  Turn them on and see
which files are actually being written to.

> Could you please check if
> you notice the same write behavior?

My BSD-based systems to no do this.  Doing a select count(*) on a table
with 750,000 rows produces no write activity.

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

On Wed, June 3, 2009 12:59, Bill Moran wrote:
> In response to Jennifer Trey <jennifer.trey@gmail.com>:
>
>> Hmm, I just noticed the same write behavior on my Windows Xp laptop but
>> the
>> values was a little less.
>> I even created an DB with one table and column and this still happened
>> when querying it.
>
> By "created", you mean you created a table and populated it with data?
> Once you do that, do a "SELECT count(*)" on that table, then wait for
> the I/O to calm down.  That select statement will force all the hint
> bits to be updated.  See if subsequent selects still cause disk
> activity.
>
>> Are you sure that moving to Linux will solve this?
>
> I never advocated that Linux would fix this, and I still don't.  I
> recommended a short list of methods to investigate the issue, most of
> which you ignored.  You _still_ don't know what's being written, and
> I _highly_ recommend that you isolate that before doing something
> radical like switching operating systems.
>
> If you've got the DB configured in such a way that it's causing a lot of
> write ops, it's going to do it in Linux or any other Posix systems, or
> on CP/M for that matter.
>
> Posix systems have a laundry list of tools to identify what programs are
> doing.  It's been a while since I've worked with Windows, but I seem to
> remember MS having tools to audit disk activity.  Turn them on and see
> which files are actually being written to.
>
>> Could you please check if
>> you notice the same write behavior?
>
> My BSD-based systems to no do this.  Doing a select count(*) on a table
> with 750,000 rows produces no write activity.
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Jennifer,

While not the "Best" solution by any stretch, you can use Windows Task
Manager (instead of Windows Performance Manager) to track the I/O writes
for each process.

Start Task Manager (run %windir%\system32\tskmgr32.exe)

On the Process Tab check the box "Show processes from all users"

Select View->Select Columns

Check the boxes related to I/O that you want to examine.

Sort the processes by "Image Name" to group PostgreSQL related processes
(again on the Process tab).

Watch your I/O counters that you selected.

Run your query (SQL Query) again.  This is to determine if it is
PostgreSQL doing the disk I/O or Windows.

Again - this is a rough guesstimate.  Better data can be collected by
using the SysInternals tools (available for free from the Microsoft
website) or using the performance tools in Windows to watch the "real"
disk I/O parameters.

Good Luck.

Tim
--
Timothy J. Bruce

Registered Linux User #325725
kboodu

Running select count(*) from test 

On Wed, June 3, 2009 13:44, Jennifer Trey wrote:
> On Wed, Jun 3, 2009 at 10:59 PM, Bill Moran
> <wmoran@potentialtech.com>wrote:
>
>> In response to Jennifer Trey <jennifer.trey@gmail.com>:
>>
>> > Hmm, I just noticed the same write behavior on my Windows Xp laptop
>> but
>> the
>> > values was a little less.
>> > I even created an DB with one table and column and this still happened
>> > when querying it.
>>
>> By "created", you mean you created a table and populated it with data?
>> Once you do that, do a "SELECT count(*)" on that table, then wait for
>> the I/O to calm down.  That select statement will force all the hint
>> bits to be updated.  See if subsequent selects still cause disk
>> activity.
>>
>
> No, I created a new DB, created a table, and did not even populate any
> data.
>
> Running select count(*) from test
>
> just now, still caused the 10-20 I/O-writes.
>
>
>>
>> > Are you sure that moving to Linux will solve this?
>>
>> I never advocated that Linux would fix this, and I still don't.  I
>> recommended a short list of methods to investigate the issue, most of
>> which you ignored.  You _still_ don't know what's being written, and
>> I _highly_ recommend that you isolate that before doing something
>> radical like switching operating systems.
>
>
> I didn't ignore all of them.
> When it comes to the logging I am still not sure. What file should I be
> looking at ? The standard log file currently has 5 lines in it, and its
> only
> errors.
> When it comes to things set as wrong, it might be true. However, on the
> laptop I've only installed and ran Tuning Wizard and haven't touched it
> afterwards.
>
> No, I still don't know whats being written. I have tried to isolate it,
> and
> checked several folders, but can't find the path.
>
> The statement i made earlier about how there was no reads was false. There
> is reads and they are done mostly by another thread. I was checking the
> same
> process at that time. However, the combined sum of I/O shows that there
> are
> more writes than reads with postgresql. Currently on the server by 2.25
>
>
>>
>>
>> If you've got the DB configured in such a way that it's causing a lot of
>> write ops, it's going to do it in Linux or any other Posix systems, or
>> on CP/M for that matter.
>>
>> Posix systems have a laundry list of tools to identify what programs are
>> doing.  It's been a while since I've worked with Windows, but I seem to
>> remember MS having tools to audit disk activity.  Turn them on and see
>> which files are actually being written to.
>>
>
> I will try to find such a tool.
>
>
>>
>> > Could you please check if
>> > you notice the same write behavior?
>>
>> My BSD-based systems to no do this.  Doing a select count(*) on a table
>> with 750,000 rows produces no write activity.
>>
>
> Thats good to know.
>
>
>>
>> --
>> Bill Moran
>> http://www.potentialtech.com
>> http://people.collaborativefusion.com/~wmoran/
>>
>
>
> Grzegorz, i have considered the hosting solutions. Problem is money. I am
> still a student. I might take you up on the other offer though :)
>
> Scott, how much would such a controller cost me?
>
> Tim, yes, I am using the tool "ProcessExplorer" from the windows site. It
> shows all the activity but can't see to where those writes are being done
> with that tool. Any ideas?
>
>
> Thanks all, appreciate all your help and effort.
>
> Sincerely / Jennifer
>

Jennifer,

I don't think it will tell you which files are being written to by which
process.  That you'd need something else for and I don't know of any tools
that tell me that.  I know DiskMon (also from SysInternals) will watch
your disk activity, but it doesn't show processes or filenames that I can
find.

Windows PerfMon will give you some detail.  I get to it from a shortcut on
my Administrative Tools menu item:  %SystemRoot%\system32\perfmon.msc /s
).  (I don't know why there's a /s on the command line.)

You can add various performance counters to analyze the disk.  But even
that is for the disk...not by process or application.  I'm only able to
get a breakdown of processes doing disk i/o using Windows Task Manager
(unless someone else knows of a solution).  And I don't know of a way to
capture and save that to make comparisons.

Tim


--
Timothy J. Bruce

visit my Website at: http://www.tbruce.com
Registered Linux User #325725

In response to "Tim Bruce - Postgres" <postgres@tbruce.com>:
> >
> > Tim, yes, I am using the tool "ProcessExplorer" from the windows site. It
> > shows all the activity but can't see to where those writes are being done
> > with that tool. Any ideas?

FileMon

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

>> No, I still don't know whats being written.
>> I have tried to isolate it, and checked several folders, but can't
find the path.
------------------------------------------------------------------------
------------------------------------

I don't do much on Windows, but when I needed to associate files and
processes this utility
from sysinternals helped me in the past.


Process Monitor
http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx


this is also good to have:
Process Explorer
http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

Hope it helps.
Michael.

In response to Jennifer Trey <jennifer.trey@gmail.com>:

> Bill, did you see my last message on the mailing list? I have tracked down
> the file. Is this some statistics file? Could this be a bug caused by auto
> vacuum being on?

I didn't see any message saying which file was getting all the activity.

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

In response to Jennifer Trey <jennifer.trey@gmail.com>:

> Hmm.. I doesn't look it made it to the mailing list. I guess you can't
> attach a file then.----
> Finally!
> I did use the programs suggested by Michael, but I actually found it a
> little before you wrote :P
>
> 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.

I believe you can disable stats collection entirely, although it will
force you to abandon autovacuum and start vacuuming manually:
http://www.postgresql.org/docs/8.3/static/runtime-config-statistics.html

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.

In any event, go ahead and turn off stats collection and see if
performance improves, but I'll be utterly shocked if it makes any
significant difference.

My advice to you is to take a step back and define the problem in more
general terms.  You have a performance problem, drop any preconceptions
about what's causing it and start by isolating the problem itself.  Sorry
if this sounds offensive, but this thread has shown a pattern with you
of chasing things without doing proper research first, and making
assumptions about what's causing the problem, without even knowing what
the problem is.

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

On Wed, Jun 3, 2009 at 3:46 PM, Jennifer Trey <jennifer.trey@gmail.com> wrote:
> Hmm.. I doesn't look it made it to the mailing list. I guess you can't
> attach a file then.
> ----
> Finally!
> I did use the programs suggested by Michael, but I actually found it a
> little before you wrote :P
> Filtering out with the pid showed that it was the file
> pgdata/global/pgstat.tmp
> See the attached image. ( see this link instead
> : http://tinypic.com/view.php?pic=52e73n&s=5 )
> The file is being written to / replaced and closed. The size of that file is
> around 57kb which was reported earlier as well.
> The file contains just allot of stange tokens and letters. What is used for?
> / Jennifer

What version of postgres are you running?

Jennifer Trey <jennifer.trey@gmail.com> wrote:
>
> The research is based on hypotheses and assumptions which in the end can
> be discarded or not. I do not know these things, I am taking one step at
> time, learning and trying.
> I don't understand what kind of research you want me to do.

I'm surprised that you didn't track down which file was being written.

> I don't have any
> performance problem other than that images seems to get stuck on occasion,
> with a certain amount of users.This is what I've got, and I have spent
> several weeks reaching this stage. I started by removing a router and
> arranging another internet, benchmarking the application, study the network
> card, switching to Apache Server and I have ruled all of them out. I have
> now come to the disk, and here its postgre thats most active and its
> starting to feel right, but I could be wrong but what other leads do I have?

Do the HTTP requests just hang indefinitely?  Do you get an HTTP error?
Are you seeing significantly more disk activity from PostgreSQL when
the problem is occurring?

> Since even a select count(*) outside my application is causing this I do not
> understand what conclusion I should take. After all, you said it yourself,
> you do not get any writes, why do I ? With perfmon under heavy load I
> registered 4000 I/0 transefers / sec and they where all writes so I think I
> am getting closer.

4000 ops/sec on the stats temp file?  If these are all SELECT queries, then
this app must be doing a ton of small queries.  I've worked optimizing an
app that averages multiple hundreds of queries per page view, and I've
never seen this problem.

> Here is a similar one but on linux
> http://archives.postgresql.org/pgsql-admin/2005-12/msg00266.php

I saw that, and I saw that he never got an answer nor followed up.

What you (and that other poster) describe just doesn't add up.  That file
requires minimal activity, even under load.  As I suggested, turn off
stats collection and see if the problem abates.  I'll be surprised if
it does, but I've been wrong before.

I'm much more leaning toward a problem with hardware, such as a
misconfigured HDD controller or something.

> I am will try turn the stats off, but I am not sure how that might affect
> the db's internal stats on how to best run a query, what indices is good or
> not.. ?

All it will do is make it impossible for autovacuum to run (since autovac
uses those stats to determine when it needs to run).  You can work through
this by setting a cron job (or whatever the windows equivalent is) to
manually vacuum the database on a regular schedule.  Figuring out how
often to vacuum is an art in itself (which is why autovac was written)
You do _need_ to run vacuums periodically, so don't do one without the
other.

--
Bill Moran
http://www.potentialtech.com

On Wed, Jun 3, 2009 at 6:54 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Jun 3, 2009 at 3:46 PM, Jennifer Trey <jennifer.trey@gmail.com> wrote:
>> Hmm.. I doesn't look it made it to the mailing list. I guess you can't
>> attach a file then.
>> ----
>> Finally!
>> I did use the programs suggested by Michael, but I actually found it a
>> little before you wrote :P
>> Filtering out with the pid showed that it was the file
>> pgdata/global/pgstat.tmp
>> See the attached image. ( see this link instead
>> : http://tinypic.com/view.php?pic=52e73n&s=5 )
>> The file is being written to / replaced and closed. The size of that file is
>> around 57kb which was reported earlier as well.
>> The file contains just allot of stange tokens and letters. What is used for?
>> / Jennifer
>
> What version of postgres are you running?

The reason I ask is that some early versions of 8.2 had a bug where
they updated that file all the time (too often) and it caused some
performance issues.

On Thu, Jun 04, 2009 at 01:48:28PM +0300, Jennifer Trey wrote:

> and its possible
> that the two drives are misconfigured. I have checked into that a little and
> can't rule it out completely.

See, this is what others have talked about. You don't give
details on what you checked, what you found, and why you'd
think that is or isn't a problem. Things may be something
other than you may think (that's true regardless of one's
level of experience) and without giving details people
cannot comment on assumptions made or conclusions drawn.

> Yes, my images hang indefinitly, until the browser gives up. Note that, if I
> reload the page it will normally reload fine, or possibly that some other
> image hangs instead.

On-access virus scanner ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

At 04:44 AM 6/4/2009, Jennifer Trey wrote:
>No, I created a new DB, created a table, and did not even populate any data.
>Running select count(*) from test
>
>just now, still caused the 10-20 I/O-writes.

Not sure if this is the main problem, but by default windows will
write to the disk whenever files are opened, this is to update the
last accessed timestamp.

This can be turned off:

http://technet.microsoft.com/en-us/library/cc959914.aspx

Apparently there are other ways of turning it off too. But I use the
registry method.

Of course you shouldn't turn it off if your apps or users require the
"last accessed timestamp".

Link.