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

Jennifer Trey <jennifer(dot)trey(at)gmail(dot)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