Re: [sqlalchemy] .count() hangs indefinitely

[mike bayer]

On 11/07/2016 02:50 AM, James Burke wrote:

Thanks Mike for your response.

The query is run against a staging db and the table only contains some
500 records.

But I will check the query as you have suggested to see what is going on.


Well since it's just count() causing the problem here, count() is going 
to wrap the inner query inside of a subquery.  Postgresql shouldn't be 
thrown off by that too much but seeing the SQL here is key.






Cheers


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] .count() hangs indefinitely

[James Burke]

Thanks Mike for your response.

The query is run against a staging db and the table only contains some 500 
records.

But I will check the query as you have suggested to see what is going on.

Cheers

>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] .count() hangs indefinitely

[mike bayer]

On 11/04/2016 04:13 AM, James Burke wrote:

Hi All,

I've run into a odd problem, where calling the count function hangs my
code indefinitely. The odd thing is it was working until recently, so
I'm a little confused.

|
customer
=session.query(Customer).filter(Customer.phone_number.contains([message['metadata']['MIN']]))
logger.debug(len(customer.all()))
logger.debug('Works perfectly fine')
logger.debug(customer.count())
logger.debug('I will never see this')
|

Has anybody run into this problem before?


long running queries are a common problem when working with relational 
databases.   The "working until recently" part is also very common 
because a slow query will usually only become slow once the data size 
grows too large.


You need to first identify the queries running when this hang occurs, 
e.g. that there's no lock contention, then you need to analyze it.


1. run the program so it hangs.

2. go to the psql monitor elsewhere and run "SELECT * FROM 
pg_stat_activity".  If your query is the only one there, then it's just 
a slow query.   If there's lock contention, you'd see that it's 
"waiting" and you'd also see the other process that's doing something.


3. figure out why the query is slow using EXPLAIN ANALYZE . 
Make sure you use the identical SQL that the database is actually 
running, which you can see either in the pg_stat_activity or in the 
echo=True output of SQLAlchemy.  You will need to manually substitute 
literal values where bound parameters are.


From EXPLAIN, you'd see what's either structurally wrong with the 
query, such as if you're doing a cartesian product (e.g. selecting from 
multiple tables without correlating rows to each other) or if something 
is wrong with indexes, which you'd see in the form of table scan activity.







--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] .count() hangs indefinitely

[James Burke]

>
>  
>
Thanks for your reply Simon.

- I am using Postgresql database 
- Running the SQL generated by SQL Alchemy in Postgres also hangs.
- There is no traceback.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] .count() hangs indefinitely

[Simon King]

On Fri, Nov 4, 2016 at 8:13 AM, James Burke  wrote:
> Hi All,
>
> I've run into a odd problem, where calling the count function hangs my code
> indefinitely. The odd thing is it was working until recently, so I'm a
> little confused.
>
> customer =
> session.query(Customer).filter(Customer.phone_number.contains([message['metadata']['MIN']]))
> logger.debug(len(customer.all()))
> logger.debug('Works perfectly fine')
> logger.debug(customer.count())
> logger.debug('I will never see this')
>
> Has anybody run into this problem before?
>

What database are you using? Have you tried logging into it to see if
it is actually executing the query? If you run the exact query by
hand, does it execute quickly? Adding echo='debug' to your
create_engine() call will log the query and parameters, which may
help. If you Ctrl-C the process, what does the backtrace look like?

Simon

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] .count() hangs indefinitely

[James Burke]

Hi All,

I've run into a odd problem, where calling the count function hangs my code 
indefinitely. The odd thing is it was working until recently, so I'm a 
little confused.

customer = session.query(Customer).filter(Customer.phone_number.contains([
message['metadata']['MIN']]))
logger.debug(len(customer.all()))
logger.debug('Works perfectly fine')
logger.debug(customer.count())
logger.debug('I will never see this')

Has anybody run into this problem before?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.