Re: [PERFORM] Will Postgres ever lock with read only queries?
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 t...@sss.pgh.pa.us wrote: Chris dmag...@gmail.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
Re: [PERFORM] Will Postgres ever lock with read only queries?
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 srobertja...@gmail.comwrote: 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 t...@sss.pgh.pa.us wrote: Chris dmag...@gmail.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
Re: [PERFORM] Will Postgres ever lock with read only queries?
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/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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?
Re: [PERFORM] Will Postgres ever lock with read only queries?
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 Mielkem...@mielke.cc -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Will Postgres ever lock with read only queries?
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/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Will Postgres ever lock with read only queries?
Chris dmag...@gmail.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
