Django SQL Query does not stop

[Ivo Brodien]

When I am in admin and I access of a certain model, Django calls mysql with an Query which increases mysql CPU load to 99% and it never returns.

If I copy the the SQL Query right into mysql the same thing happens, so it is actually kind of a mysql problem, but since Django created the query, I thought I might get help here.

I did not call any select_related or something.

On my dev machine I am using sqlite and the same query does not cause any trouble and returns within 4 ms.

There are less not more than 5 rows in each table, so database size cannot be a problem.

Any ideas? where to start debugging this?

[Tom Evans]

In the mysql shell:

EXPLAIN SELECT ...

Cheers

Tom

[Ivo Brodien]

On 01.02.2011, at 16:55, Tom Evans wrote:

> In the mysql shell:
>
> EXPLAIN SELECT ...

unfortunately same problem. CPU turns to 100% and it is stuck.

[Ivo Brodien]

The Change List that I am calling is a Intermediate Table if that is of any interest.

Is it possible that there is some sort of circular inner joints or something?

[Ivo Brodien]

I found a solution be changing the MySQL server setting optimizer_search_depth to 3 (default 62)

http://dev.mysql.com/doc/refman/5.0/en/controlling-optimizer.html
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_optimizer_search_depth

My query had over 20 INNER JOINTS and it made the optimizer search a long process.

So at the moment a value of 3 is fine.

[Chris Matthews]

Hi Ivo,

SQL is like regular expressions. You can go complex (with one mega query/expression) but it could create a maintenance nightmare. See if you cannot simplify the query into multiple queries and a bit of code (for loops and using the joining columns) to lash them together. The code sequence should be such that you limit access to a huge amount of rows; so you filter the data accessed. It is usually easier to debug as well. And using Tom's advice (EXPLAIN SELECT ...) on smaller join queries is often more useful (than the explain on a mega join query).

In my experience it often runs way faster if the query is simplified.

Regards
Chris

http://dev.mysql.com/doc/refman/5.0/en/controlling-optimizer.html
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_optimizer_search_depth

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[Ivo Brodien]

Hi Chris,

thanks for the info. My tables won’t have many rows and I don’t have the time for optimazation, especially if it is not necessary, but I will keep your words in mind!

Cheers
Ivo

[Sergiy]

Circular joins are indeed a very likely reason which usually points to
a poor model design. In fact I ran into the same problem just
yesterday. I hooked Django admin to a legacy app that had a related
field to table A and related field to table B that was also related to
A. And that created an ever running query from the admin. As changing
db structure was not feasible I had to remove those fields from my
list_display to make it useable.

Also when you have a query hanging like that, inside MySQL shell you
can run `show processlist` and then `kill <mysql_process_id>` to get
rid of the offending query.

Cheers
Sergiy

On Feb 3, 6:59 am, Ivo Brodien <i...@brodien.de> wrote:
> Hi Chris,
>
> thanks for the info. My tables won’t have many rows and I don’t have the time for optimazation, especially if it is not necessary, but I will keep your words in mind!
>
> Cheers
> Ivo
>
> On 02.02.2011, at 06:10, Chris Matthews wrote:
>
>
>
>
>
>
>
> > Hi Ivo,
>
> > SQL is like regular expressions. You can go complex (with one mega query/expression) but it could create a maintenance nightmare. See if you cannot simplify the query into multiple queries and a bit of code (for loops and using the joining columns) to lash them together. The code sequence should be such that you limit access to a huge amount of rows; so you filter the data accessed. It is usually easier to debug as well. And using Tom's advice (EXPLAIN SELECT ...) on smaller join queries is often more useful (than the explain on a mega join query).
>
> > In my experience it often runs way faster if the query is simplified.
>
> > Regards
> > Chris
> > -----Original Message-----
> > From: django...@googlegroups.com [mailto:django...@googlegroups.com] On Behalf Of Ivo Brodien
> > Sent: 01 February 2011 23:49
> > To: django...@googlegroups.com
> > Subject: Re: Django SQL Query does not stop
>
> > I found a solution be changing the MySQL server setting optimizer_search_depth to 3 (default 62)
>
> >http://dev.mysql.com/doc/refman/5.0/en/controlling-optimizer.html

> >http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#s...

>
> > My query had over 20 INNER JOINTS and it made the optimizer search a long process.
>
> > So at the moment a value of 3 is fine.
>
> > On 01.02.2011, at 21:20, Ivo Brodien wrote:
>
> >> The Change List that I am calling is a Intermediate Table if that is of any interest.
>
> >> Is it possible that there is some sort of circular inner joints or something?
>
> > --
> > You received this message because you are subscribed to the Google Groups "Django users" group.
> > To post to this group, send email to django...@googlegroups.com.
> > To unsubscribe from this group, send email to django-users...@googlegroups.com.

> > For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.