Re: Will Postgres ever lock with read only queries?

Hi. I'm seeing some weird behavior in Postgres. I'm running read only
queries (SELECT that is - no UPDATE or DELETE or INSERT is happening at
all). I can run one rather complicated query and the results come back...
eventually. Likewise with another. But, when I run both queries at the
same time, Postgres seems to ground to a halt. Neither one completes. In
fact, pgAdmin locks up - I need to cancel them using psql.
I'd expect this from MySQL but not Postgres. Am I doing something wrong? Or
missing something?

On 07/27/2009 08:54 PM, Robert James wrote:
> Hi. I'm seeing some weird behavior in Postgres. I'm running read
> only queries (SELECT that is - no UPDATE or DELETE or INSERT is
> happening at all). I can run one rather complicated query and the
> results come back... eventually. Likewise with another. But, when I
> run both queries at the same time, Postgres seems to ground to a halt.
> Neither one completes. In fact, pgAdmin locks up - I need to cancel
> them using psql.
> I'd expect this from MySQL but not Postgres. Am I doing something
> wrong? Or missing something?

I've never had straight queries block each other. What is the query?
What version of PostgreSQL? What operating system?

Cheers,
mark

--
Mark Mielke<mark(at)mielke(dot)cc>

Robert James wrote:
> Hi. I'm seeing some weird behavior in Postgres. I'm running read only
> queries (SELECT that is - no UPDATE or DELETE or INSERT is happening at
> all). I can run one rather complicated query and the results come
> back... eventually. Likewise with another. But, when I run both
> queries at the same time, Postgres seems to ground to a halt. Neither
> one completes. In fact, pgAdmin locks up - I need to cancel them using
> psql.
> I'd expect this from MySQL but not Postgres. Am I doing something
> wrong? Or missing something?

They're probably not blocking each other but more likely you're
exhausting your servers resources. If they return "eventually"
individually, then running both at the same time will take at least
"eventually x2".

As Mark said, what are the queries? What postgres version? What o/s?
What are your hardware specs (how much memory, disk speeds/types etc)?

--
Postgresql & php tutorials
http://www.designmagick.com/

Chris <dmagick(at)gmail(dot)com> writes:
> Robert James wrote:
>> Hi. I'm seeing some weird behavior in Postgres. I'm running read only
>> queries (SELECT that is - no UPDATE or DELETE or INSERT is happening at
>> all). I can run one rather complicated query and the results come
>> back... eventually. Likewise with another. But, when I run both
>> queries at the same time, Postgres seems to ground to a halt.

> They're probably not blocking each other but more likely you're
> exhausting your servers resources. If they return "eventually"
> individually, then running both at the same time will take at least
> "eventually x2".

It could be a lot more than x2. If the two queries together eat enough
RAM to drive the machine into swapping, where it didn't swap while
doing one at a time, the slowdown could be orders of magnitude.

Watching vmstat output might be informative --- it would at least give
an idea if the bottleneck is CPU, I/O, or swap.

regards, tom lane

Thanks for the replies. I'm running Postgres 8.2 on Windows XP, Intel Core
Duo (though Postgres seems to use only one 1 core).
The queries are self joins on very large tables, with lots of nested loops.

On Mon, Jul 27, 2009 at 9:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Chris <dmagick(at)gmail(dot)com> writes:
> > Robert James wrote:
> >> Hi. I'm seeing some weird behavior in Postgres. I'm running read only
> >> queries (SELECT that is - no UPDATE or DELETE or INSERT is happening at
> >> all). I can run one rather complicated query and the results come
> >> back... eventually. Likewise with another. But, when I run both
> >> queries at the same time, Postgres seems to ground to a halt.
>
> > They're probably not blocking each other but more likely you're
> > exhausting your servers resources. If they return "eventually"
> > individually, then running both at the same time will take at least
> > "eventually x2".
>
> It could be a lot more than x2. If the two queries together eat enough
> RAM to drive the machine into swapping, where it didn't swap while
> doing one at a time, the slowdown could be orders of magnitude.
>
> Watching vmstat output might be informative --- it would at least give
> an idea if the bottleneck is CPU, I/O, or swap.
>
> regards, tom lane
>

Can you run those two queries with psql?

I remember having some trouble running multiple queries in the same pgadmin
process. Both would get stuck until both finished I think. I went to
running a pgadmin process per query.

On Tue, Jul 28, 2009 at 9:17 AM, Robert James <srobertjames(at)gmail(dot)com>wrote:

> Thanks for the replies. I'm running Postgres 8.2 on Windows XP, Intel Core
> Duo (though Postgres seems to use only one 1 core).
> The queries are self joins on very large tables, with lots of nested loops.
>
> On Mon, Jul 27, 2009 at 9:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Chris <dmagick(at)gmail(dot)com> writes:
>> > Robert James wrote:
>> >> Hi. I'm seeing some weird behavior in Postgres. I'm running read only
>> >> queries (SELECT that is - no UPDATE or DELETE or INSERT is happening at
>> >> all). I can run one rather complicated query and the results come
>> >> back... eventually. Likewise with another. But, when I run both
>> >> queries at the same time, Postgres seems to ground to a halt.
>>
>> > They're probably not blocking each other but more likely you're
>> > exhausting your servers resources. If they return "eventually"
>> > individually, then running both at the same time will take at least
>> > "eventually x2".
>>
>> It could be a lot more than x2. If the two queries together eat enough
>> RAM to drive the machine into swapping, where it didn't swap while
>> doing one at a time, the slowdown could be orders of magnitude.
>>
>> Watching vmstat output might be informative --- it would at least give
>> an idea if the bottleneck is CPU, I/O, or swap.
>>
>> regards, tom lane
>>
>
>

Robert James wrote:
> Thanks for the replies. I'm running Postgres 8.2 on Windows XP, Intel
> Core Duo (though Postgres seems to use only one 1 core).

A single query can only use one core, but it will use both if multiple
queries come in.

> The queries are self joins on very large tables, with lots of nested loops.

If you want help optimizing them, you'll need to send through
- explain analyze
- table definitions
and of course
- the query itself

--
Postgresql & php tutorials
http://www.designmagick.com/