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/

I accidentally replied only to Grzegorz before so I don't think it turned up
on the mailing list. Here it is again. (Bill, I will try to answer your
email in a minute) ---
Thank you for you answer :)
2009/6/3 Grzegorz Jaśkiewicz <gryzman@gmail.com>

move postgresql to another disc if possible.

I have 2 disks thats the best Samsung has to offer :
http://www.samsung.com/global/business/hdd/productType.do?group=72#(HE103UJ,
at the bottom of page)
First splitted on
C:100GB ( Windows Server drive with postgreSQL installation )
E: 800GB with PostgreSQL data.
I: Pictures
Do you suggest that I move the PostgreSQL data to another disk or all of
it? Including the installation?

You have really slow
discs if that happen,

By that you mean, that writes occur if one has to slow disks?

or perhaps it does loads of seq scans, because
of lack of indices.

Its possible that its the lack of indices, but they are pretty many. Most of
the fetching thats being done is through a primary key. However when using
search, there is a1-2 JOINS in each search. Does perhaps a JOIN force a
writing to the disk?

All in all, it seems like your server is to low spec for what you are
trying to do.

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...
Thanks / Jennifer

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 5:13 PM, Bill Moran <wmoran@potentialtech.com> wrote:

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

Ok. But as you say those should not happen so often and the size is not that
great. The combined write size is 3,254,048,805 bytes which is around 3GB
..

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

* You have other logging settings set too high and PostgreSQL is tying
up disk I/O writing log data.

Doesn't seem to be it.. just just checked a couple of things..

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

Enough I/O capability? Not sure what you mean... how can I find out?

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.

Yes.. I would like to try that(question is how I can do that, without stupid
Windows changes permission on my files!) if it turns out that it wasn't the
effective cache.. (good that I noticed at least :) )

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

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.
Sincerley / Jennifer

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/

lol, if I had a nickel for every one that said they had a dollar I'd buy a
Ferrari F430 :P
but I do.. most of my memory isn't being used.. and I have once had a deep
discussion on tuning postgres on this mailing list. Feel free to search on
my name and you will see it :)
But I have gotten even closer now to understanding this and changing the
effective_cache thingy didn't help :(
I have noticed that for every query that is ran on the DB the write happens,
no matter if it is from my application or from some other place,
15 I/O writes always occurs and has a size of approximatly 57kb each. The
size differiates a little but it is always 15 writes! It doesn't matter if
it is 2 queries (from the page) or 1 query or more. It seems to be on a
connection basis. I opened up pgadmin and ran an sql query there and same
thing happened. I don't understand how why the reads aren't increasing
either, even if there is writes, there should be reads as well, right!?
select * from ad where id >= 2 limi x ... where x could be anything and the
nbr of writes was still constant and id is a primary key.
It leaves me to believe that its either some logging like you mentioned
Bill, but I have turned off it (at least I believe so) or that there is some
PostgreSQL on Windows issues. I forgot to mention that I am running Postgre
32bit on Windows Server 2008 64bit. ??
Also, the data that Postgre uses is on the same disk but on a different
partition. Could it be that the writing is from one partion to another, but
then there is the reading that should still occur. But perhaps its from the
buffer or memory :O haha.. just guessing..
It is possible that Windows permission system is forcing postgre to this
behaviour. I have had numerous permission issues, where the OS after ( in
postgreSql's case, was when I installed Tuning Wizard) took permission over
files and therefore was no longer accisseble to Posgre (that hassle took me
some time aswell)
If you have a good suggestion or theories please let me know. Otherwise, I
think I will move over to Ubuntu Server real soon! I can't stand this
anymore! I always have to consider that its Windows screwing things up for
me :) I think with linux I will at least be free of that :)
Thank you / Jennifer

Sorry.. the formatting got screwed.. here it is again :
-----
lol, if I had a nickel for every one that said they had a dollar I'd buy a
Ferrari F430 :P
but I do.. most of my memory isn't being used.. and I have once had a deep
discussion on tuning postgres on this mailing list. Feel free to search on
my name and you will see it :)

But I have gotten even closer now to understanding this and changing the
effective_cache thingy didn't help :(

I have noticed that for every query that is ran on the DB the write happens,
no matter if it is from my application or from some other place,
15 I/O writes always occurs and has a size of approximatly 57kb each. The
size differiates a little but it is always 15 writes! It doesn't matter if
it is 2 queries (from the page) or 1 query or more. It seems to be on a
connection basis. I opened up pgadmin and ran an sql query there and same
thing happened. I don't understand how why the reads aren't increasing
either, even if there is writes, there should be reads as well, right!?

select * from ad where id >= 2 limi x ... where x could be anything and the
nbr of writes was still constant and id is a primary key.

It leaves me to believe that its either some logging like you mentioned
Bill, but I have turned off it (at least I believe so) or that there is some
PostgreSQL on Windows issues. I forgot to mention that I am running Postgre
32bit on Windows Server 2008 64bit. ??

Also, the data that Postgre uses is on the same disk but on a different
partition. Could it be that the writing is from one partion to another, but
then there is the reading that should still occur. But perhaps its from the
buffer or memory :O haha.. just guessing..

It is possible that Windows permission system is forcing postgre to this
behaviour. I have had numerous permission issues, where the OS after ( in
postgreSql's case, was when I installed Tuning Wizard) took permission over
files and therefore was no longer accisseble to Posgre (that hassle took me
some time aswell)

If you have a good suggestion or theories please let me know. Otherwise, I
think I will move over to Ubuntu Server real soon! I can't stand this
anymore! I always have to consider that its Windows screwing things up for
me :) I think with linux I will at least be free of that :)

Thank you / Jennifer

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

The problem here is the number of I/O writes. From 15 to 30... with lots of
users this is becoming an issue.

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?

Thank you in advance / Jennifer

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

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

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

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.

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/

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?