Thread: High I/O writes activity on disks causing images on browser to lag and not load
High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
Hi,
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?
Is there something that I should know? Please enlighten me :)
I would now like to move the DB activity to another disk if possible? Would I have to re-install PostgreSQL from scratch?
Thank you in advance / Jennifer
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Grzegorz Jaśkiewicz
Date:
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.
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Bill Moran
Date:
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/
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
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...
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Grzegorz Jaśkiewicz
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
On Wed, Jun 3, 2009 at 5:13 PM, Bill Moran <wmoran@potentialtech.com> wrote:
In response to Jennifer Trey <jennifer.trey@gmail.com>:>Possible causes:
> 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?
* 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.That's only going to help if the machine has enough I/O capability to
> I would now like to move the DB activity to another disk if possible?
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?
No. You can just pick up the data directory and relocate it, then config
> Would
> I have to re-install PostgreSQL from scratch?
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Grzegorz Jaśkiewicz
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Bill Moran
Date:
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/
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
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:
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 :Pbut 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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Grzegorz Jaśkiewicz
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Scott Marlowe
Date:
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.
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Bill Moran
Date:
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/
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
"Tim Bruce - Postgres"
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
On Wed, Jun 3, 2009 at 10:59 PM, Bill Moran <wmoran@potentialtech.com> wrote:
In response to Jennifer Trey <jennifer.trey@gmail.com>:> when querying it.
> 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
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.
I never advocated that Linux would fix this, and I still don't. I
> Are you sure that moving to Linux will solve this?
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.
My BSD-based systems to no do this. Doing a select count(*) on a table
> Could you please check if
> you notice the same write behavior?
with 750,000 rows produces no write activity.
Thats good to know.
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
"Tim Bruce - Postgres"
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
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?
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Bill Moran
Date:
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/
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
"BRUSSER Michael"
Date:
>> 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.
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Bill Moran
Date:
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/
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Bill Moran
Date:
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/
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
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.
Thanks / Jennifer
I don't understand what kind of research you want me to do. 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?
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.
Here is a similar one but on linux
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.. ?
Thanks / Jennifer
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Scott Marlowe
Date:
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?
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Bill Moran
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Scott Marlowe
Date:
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.
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Jennifer Trey
Date:
Sorry, went to bed yesterday :D
I have installed postgresql-8.3.7-1-windows.exe
Bill, you are right. The app does do tons of small queries, and its possible that the two drives are misconfigured. I have checked into that a little and can't rule it out completely.
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. The images are on the other 1TB disk drive, and there is almost no activity on that drive(seen from perfmon), but it seems to be going through the C:drive so if there is a queue on C: then there will be a problem fetching from I:Image as well. I would prefer if the I:Image drive just flushed out the images (not sure how I can accomplish that) it self but the overwhelming disk activity seems to be the disk writes on C... thats what I noticed on perfmon. I/O transfers and I/O writes graphs where almost identical. Would you say that these I/O writes are normal?
I could move the pgdata to the image drive and have that one busy with the writing instead. Not sure that will help though.
When it come to turning of the Statistics, I saw your link and looked into my config file.. those settings where all off or commented :
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#update_process_title = on
# - 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 = true # 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
Just turn off autovacuum ?
/ Jennifer
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Karsten Hilbert
Date:
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
Re: High I/O writes activity on disks causing images on browser to lag and not load
From
Lincoln Yeoh
Date:
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.