Re: plpgsql function running long, but resources consumption is very low

On 4/27/09 2:56 AM, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:

> Hello
>
> without source code we cannot help
>

That's not true. We can only go so far without source, but the general
problem of "what might the bottleneck be if it doesn't appear to be CPU or
disk" can be investigated significantly without source code.

Things to try:
Check networking stats. Is this running locally -- if so is it local pipe,
localhost, or ip address? What tool / language / driver is the client?

Check pg_locks while it is running -- Observe the count and types of locks.
Find the backend that is running, and report all the locks that that backend
is associated with. Observe this multiple times and report if this is
relatively constant or changing, and if so, how.

Provide perfmon stats during this time:
CPU % (user, system)
Context switch rate (System -> Context Switches / sec)
Disk %time, iops, queue length, avg size -- per disk.
Network bandwidth used, packets/sec, avg packet size.

A bunch of the above, especially the CPU stuff, can be broken down per
process or thread. Analyzing disk I/O is possible per process or thread as
well if something looks up there (with FileMon).
The network stuff can be broken down by client / port somewhat as well if
necessary.
Check out sysinternals.com for other tools that might be useful.

In all likelihood, the above will narrow this down but not solve it, but its
better than nothing and might find it.
It would certainly be useful if those who have experienced such situations
shared their solutions, or those who know more about the inner workings
suggested possible causes or provided links to more information (or old
similar topics).

> regards
> Pavel Stehule
>
>
> 2009/4/27 Wojtek <foo(at)twine(dot)pl>:
>> Hi,
>>
>> I'm having serious performance problems with my two plpgsql functions (lots
>> of calculations inside, including distance (lat,long) using earthdistance
>> etc).
>>
>> My problem is: both functions are executing for hours (if not days) but
>> resource consumption is low (I/O, CPU, memory), like 2-5% only. I'm a bit
>> confused on what's going on there... locks? poor resources management by
>> Windows? I'm not really sure where the bottleneck might be.
>>
>> Any hints/recommendations where and how should I look for improvements?
>>
>> I'm running 8.2 on WinXP.
>>
>> I'm aware it's kind of hard to say without seeing function's body, but I
>> thought I can give a try anyway.
>>
>> Regards,
>> foo
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>