Re: psycopg2 hang with multithread frequent queries

Greetings list,

I'm using psycopg2, python2.7.3 threading along with pgsql 9.1.5. I noticed
that if I have my python process query against the database kind of
frequently, say more than 1 query per second, it's almost 100% sure to hit
some problem, but first some backgroud:

1. There are three threads, each with many SELECT/UPDATE, a little bit
INSERT, only one explicit 'LOCK TABLE'
2. One of threads queries the db every second (easier to reproduce this way)
3. The other two do their queries like every 10~30 seconds
4. It doesn't matter whether the threads share one connection or with
separate connection,

And there are two kind of symptons:
1.

On Fri, Sep 14, 2012 at 5:45 PM, David Roid <dataroid(at)gmail(dot)com> wrote:
> Greetings list,
>
> I'm using psycopg2, python2.7.3 threading along with pgsql 9.1.5. I noticed
> that if I have my python process query against the database kind of
> frequently, say more than 1 query per second, it's almost 100% sure to hit
> some problem, but first some backgroud:

No, first, what psycopg version? In the last versions we have fixed a
couple multithread-related bugs (one in 2.4.2, one in 2.4.5, see NEWS
file).

-- Daniele

Sorry I didn't finish it..

And there are two kind of symptons after the process running for a couple
of minutes:
(0. At first it seems ok, each thread does its job, the quick one with more
logs of course)
1. The quick one keeps running, the other two get no chance being called
any more
2. When one of the slow ones get called, the whole python process hang,
with the postgresql process in 'idle in transaction'; note i'm very alert
with psycopg2's all-in-transaction style, i'm very sure there is a
commit/rollback to end the trac, the python process just stucks!

I'm doing this test/investigate with freeload, so CPU/code conditions
should not be a cause, i'm starting to suspect psycopg2 or postgresql, any
hint?

Thanks and Regards
-David

2012/9/15 David Roid <dataroid(at)gmail(dot)com>

> Greetings list,
>
> I'm using psycopg2, python2.7.3 threading along with pgsql 9.1.5. I
> noticed that if I have my python process query against the database kind of
> frequently, say more than 1 query per second, it's almost 100% sure to hit
> some problem, but first some backgroud:
>
> 1. There are three threads, each with many SELECT/UPDATE, a little bit
> INSERT, only one explicit 'LOCK TABLE'
> 2. One of threads queries the db every second (easier to reproduce this
> way)
> 3. The other two do their queries like every 10~30 seconds
> 4. It doesn't matter whether the threads share one connection or with
> separate connection,
>
> And there are two kind of symptons:
> 1.
>

Hi Daniele, it's 2.4.5. -David

2012/9/15 Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>

> On Fri, Sep 14, 2012 at 5:45 PM, David Roid <dataroid(at)gmail(dot)com> wrote:
> > Greetings list,
> >
> > I'm using psycopg2, python2.7.3 threading along with pgsql 9.1.5. I
> noticed
> > that if I have my python process query against the database kind of
> > frequently, say more than 1 query per second, it's almost 100% sure to
> hit
> > some problem, but first some backgroud:
>
> No, first, what psycopg version? In the last versions we have fixed a
> couple multithread-related bugs (one in 2.4.2, one in 2.4.5, see NEWS
> file).
>
> -- Daniele
>

On 14/09/2012 18:55, David Roid wrote:
> And there are two kind of symptons after the process running for a
> couple of minutes:
> (0. At first it seems ok, each thread does its job, the quick one with
> more logs of course)
> 1. The quick one keeps running, the other two get no chance being called
> any more
> 2. When one of the slow ones get called, the whole python process hang,
> with the postgresql process in 'idle in transaction'; note i'm very
> alert with psycopg2's all-in-transaction style, i'm very sure there is a
> commit/rollback to end the trac, the python process just stucks!
>
> I'm doing this test/investigate with freeload, so CPU/code conditions
> should not be a cause, i'm starting to suspect psycopg2 or postgresql,
> any hint?

How many connections are you using? Cursors? Do you commit or keep a
very long running transaction? Please give us some more information and
compile psycopg with debugging enabled to have the logs ready for analsys.

federico

--
Federico Di Gregorio federico(dot)digregorio(at)dndg(dot)it
Studio Associato Di Nunzio e Di Gregorio http://dndg.it
There's no greys, only white that's got grubby. I'm surprised you
don't know that. And sin, young man, is when you treat people as
things. Including yourself. -- Granny Weatherwax

Hi Federico,

No long-running trac, commit is done fair often. I also tried "one conn for
all threads" vs "one conn for one thread", and "one cursor all the way" vs
"use and throw away cursor", issue remains.

I install psycopg2.4.5 with easy_install, do I need to build psycopg2 from
source to enable debug info?

Thanks!
-David

2012/9/15 Federico Di Gregorio <fog(at)dndg(dot)it>

> On 14/09/2012 18:55, David Roid wrote:
> > And there are two kind of symptons after the process running for a
> > couple of minutes:
> > (0. At first it seems ok, each thread does its job, the quick one with
> > more logs of course)
> > 1. The quick one keeps running, the other two get no chance being called
> > any more
> > 2. When one of the slow ones get called, the whole python process hang,
> > with the postgresql process in 'idle in transaction'; note i'm very
> > alert with psycopg2's all-in-transaction style, i'm very sure there is a
> > commit/rollback to end the trac, the python process just stucks!
> >
> > I'm doing this test/investigate with freeload, so CPU/code conditions
> > should not be a cause, i'm starting to suspect psycopg2 or postgresql,
> > any hint?
>
> How many connections are you using? Cursors? Do you commit or keep a
> very long running transaction? Please give us some more information and
> compile psycopg with debugging enabled to have the logs ready for analsys.
>
> federico
>
> --
> Federico Di Gregorio federico(dot)digregorio(at)dndg(dot)it
> Studio Associato Di Nunzio e Di Gregorio http://dndg.it
> There's no greys, only white that's got grubby. I'm surprised you
> don't know that. And sin, young man, is when you treat people as
> things. Including yourself. -- Granny Weatherwax
>

On Fri, Sep 14, 2012 at 6:15 PM, David Roid <dataroid(at)gmail(dot)com> wrote:

> I install psycopg2.4.5 with easy_install, do I need to build psycopg2 from
> source to enable debug info?

Yes, see setup.cfg.

If you put together a contained test case I can try debugging it.

Please see you original message: was it truncated before describing symptoms?

Couldn't the problem be your table lock instead of the driver?

Thank you.

-- Daniele

On Fri, Sep 14, 2012 at 6:26 PM, Daniele Varrazzo
<daniele(dot)varrazzo(at)gmail(dot)com> wrote:

> Please see you original message: was it truncated before describing symptoms?

Oh sorry, just seen your followup.

-- Daniele

On Fri, Sep 14, 2012 at 5:55 PM, David Roid <dataroid(at)gmail(dot)com> wrote:

> 2. When one of the slow ones get called, the whole python process hang, with
> the postgresql process in 'idle in transaction'; note i'm very alert with
> psycopg2's all-in-transaction style, i'm very sure there is a
> commit/rollback to end the trac, the python process just stucks!

Is there any query with "waiting" flag set to true in
pg_stat_activity? That would be an indication of queries stuck against
a lock.

-- Daniele

On Fri, Sep 14, 2012 at 6:15 PM, David Roid <dataroid(at)gmail(dot)com> wrote:

> No long-running trac, commit is done fair often. I also tried "one conn for
> all threads" vs "one conn for one thread", and "one cursor all the way" vs
> "use and throw away cursor", issue remains.

David has provided me further information about his setup and a gcc
traceback, from which it was obvious that he was performing database
queries from a signal handler. It seems he is not experiencing any
more problems since he got rid of this unsafe practice.

-- Daniele

On 17/09/2012 10:21, Daniele Varrazzo wrote:
> On Fri, Sep 14, 2012 at 6:15 PM, David Roid <dataroid(at)gmail(dot)com> wrote:
>
>> > No long-running trac, commit is done fair often. I also tried "one conn for
>> > all threads" vs "one conn for one thread", and "one cursor all the way" vs
>> > "use and throw away cursor", issue remains.
> David has provided me further information about his setup and a gcc
> traceback, from which it was obvious that he was performing database
> queries from a signal handler. It seems he is not experiencing any
> more problems since he got rid of this unsafe practice.

Nice to know.

federico

--
Federico Di Gregorio federico(dot)digregorio(at)dndg(dot)it
Studio Associato Di Nunzio e Di Gregorio http://dndg.it
I did appreciate the irony that I was whining about encoding issues on
a mailing list that was unable to show those chars, too.
-- Antti S. Lankila to mono-devel-list@

Hi Daniele, Federico,

Just want to know, if I keep signal handler but switch from thread to
subprocess, i.e. put the signal handler code with database queries into
another process, is that safe?

Regards
-David

2012/9/17 Federico Di Gregorio <fog(at)dndg(dot)it>

> On 17/09/2012 10:21, Daniele Varrazzo wrote:
> > On Fri, Sep 14, 2012 at 6:15 PM, David Roid <dataroid(at)gmail(dot)com> wrote:
> >
> >> > No long-running trac, commit is done fair often. I also tried "one
> conn for
> >> > all threads" vs "one conn for one thread", and "one cursor all the
> way" vs
> >> > "use and throw away cursor", issue remains.
> > David has provided me further information about his setup and a gcc
> > traceback, from which it was obvious that he was performing database
> > queries from a signal handler. It seems he is not experiencing any
> > more problems since he got rid of this unsafe practice.
>
> Nice to know.
>
> federico
>
> --
> Federico Di Gregorio federico(dot)digregorio(at)dndg(dot)it
> Studio Associato Di Nunzio e Di Gregorio http://dndg.it
> I did appreciate the irony that I was whining about encoding issues on
> a mailing list that was unable to show those chars, too.
> -- Antti S. Lankila to mono-devel-list@
>

On Tuesday 18 September 2012, David Roid wrote:
> Hi Daniele, Federico,
>
> Just want to know, if I keep signal handler but switch from thread to
> subprocess, i.e. put the signal handler code with database queries into
> another process, is that safe?
>

IMHO, doing anything more than setting some "flag" variables and waking up
regular threads is bad practice for a signal handler..

--
Say NO to spam and viruses. Stop using Microsoft Windows!

On Tue, Sep 18, 2012 at 12:39 AM, P. Christeas <xrg(at)linux(dot)gr> wrote:
> On Tuesday 18 September 2012, David Roid wrote:
>> Hi Daniele, Federico,
>>
>> Just want to know, if I keep signal handler but switch from thread to
>> subprocess, i.e. put the signal handler code with database queries into
>> another process, is that safe?
>>
>
> IMHO, doing anything more than setting some "flag" variables and waking up
> regular threads is bad practice for a signal handler..

Yeah, in a process handler you are not even supposed to call malloc.
This on top of the fact that in python interaction between threads and
signals is unpredictable.

If you want more refined synchronization across agents you may take a
look at gevent/eventlet. But without knowing your problem is hard to
guess a solution.

-- Daniele

Thank you, I'll do some homework on gevent.

-David

2012/9/18 Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>

> On Tue, Sep 18, 2012 at 12:39 AM, P. Christeas <xrg(at)linux(dot)gr> wrote:
> > On Tuesday 18 September 2012, David Roid wrote:
> >> Hi Daniele, Federico,
> >>
> >> Just want to know, if I keep signal handler but switch from thread to
> >> subprocess, i.e. put the signal handler code with database queries into
> >> another process, is that safe?
> >>
> >
> > IMHO, doing anything more than setting some "flag" variables and waking
> up
> > regular threads is bad practice for a signal handler..
>
> Yeah, in a process handler you are not even supposed to call malloc.
> This on top of the fact that in python interaction between threads and
> signals is unpredictable.
>
> If you want more refined synchronization across agents you may take a
> look at gevent/eventlet. But without knowing your problem is hard to
> guess a solution.
>
> -- Daniele
>
>
> --
> Sent via psycopg mailing list (psycopg(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg
>