Обсуждение: Weird performance hit

I have two practically identical Debian-testing systems installed on
two harddrives on the same machine. I've compiled Postgres 8.0.3 with
exactly the same options on both. Both HDD use the same kernel, have
DMA enabled and so on. I have the same database and web applications
installed in both systems.

However, one application is for some weird reason taking a serious
performance hit on certain pages. There are some intensive joins and
selects there, but somehow one install manages a couple of seconds and
the other takes about 10.

Any ideas? I've tried copying Postgres and the repository over from
the good install, it does the same thing. Does this suggest that it's
not a Postgres issue?

On Thu, Aug 18, 2005 at 10:03:38AM +0300, WireSpot wrote:
> However, one application is for some weird reason taking a serious
> performance hit on certain pages. There are some intensive joins and
> selects there, but somehow one install manages a couple of seconds and
> the other takes about 10.

Could you post the query and the EXPLAIN ANALYZE output for both
systems?

--
Michael Fuhr

On Thu, Aug 18, 2005 at 06:26:41AM -0600, Michael Fuhr wrote:
> On Thu, Aug 18, 2005 at 10:03:38AM +0300, WireSpot wrote:
> > However, one application is for some weird reason taking a serious
> > performance hit on certain pages. There are some intensive joins and
> > selects there, but somehow one install manages a couple of seconds and
> > the other takes about 10.
>
> Could you post the query and the EXPLAIN ANALYZE output for both
> systems?

Never mind, I see that you posted that info in another thread (please
don't start multiple threads on the same subject).

--
Michael Fuhr

On Thursday 18 August 2005 12:03 am, WireSpot wrote:
> I have two practically identical Debian-testing systems installed
> on two harddrives on the same machine. I've compiled Postgres 8.0.3
> with exactly the same options on both. Both HDD use the same
> kernel, have DMA enabled and so on. I have the same database and
> web applications installed in both systems.
>
> However, one application is for some weird reason taking a serious
> performance hit on certain pages. There are some intensive joins
> and selects there, but somehow one install manages a couple of
> seconds and the other takes about 10.
>
> Any ideas? I've tried copying Postgres and the repository over from
> the good install, it does the same thing. Does this suggest that
> it's not a Postgres issue?

A number of ideas spring to mind:

1) "practically identical" != "identical" - perhaps they aren't as
similar as you assume

2) Hardware problems. Are there any indications in /var/log/messages
of drive retries or other problems? Have you used S.M.A.R.T. tools,
hdbench or similar to verify that both drives are problem-free and
have similar performance?

3) Different data layout. I assume both are using the same filesystem
- if not all bets are off. Even if they are identical, the data could
end up in different areas of the disk forcing more seeks on one drive
than the other. Does one setup seem to "work harder", id. more
seeking/thrashing than the other?

4) Different connections. Are you actually reconnecting the drives so
each is connected the same way or are they both in the system
simultaneously? IDE devices only run at the speed of the slowest
device on the bus so if your fast drive is alone while the "slow
drive" is sharing its bus with a slow device you might see this
problem. Google on "IDE slowest device" and you will find plenty on
this subject.

5) In general a fresh copy (via pg dump and restore, not filesystem
copying) will have better performance due to the fact that it's
almost like doing a vacuum full and reindex on everything. However,
you may need to run "analyze" after you do the restore to make sure
the planner makes intelligent decisions.

Cheers,
Steve