Mysql query log causing django to lock up

[Nick Craig-Wood]

I've finally got to the bottom of why django locks up on our dev server.

Our dev server has mysql query logging turned on with this in /etc/mysql/my.cnf

# Query logging
log=/var/log/mysql/mysqld.log

However with that, then simple queries (like looking at the accounts
table in the admin interface) lock up leaving this in the process
list.

+-------+---------+-----------------+--------+---------+-------+------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------+-----------------+--------+---------+-------+------------+------------------------------------------------------------------------------------------------------+
| 43726 | backend | localhost:59678 | memset | Query | 62773 | statistics | SELECT `account`.`id`, `account`.`admin_id`, `account`.`billing_id`, `account`.`currency`, `account` |
| 43880 | backend | localhost | memset | Query | 58289 | statistics | SELECT `account`.`id`, `account`.`admin_id`, `account`.`billing_id`, `account`.`currency`, `account` |
| 43882 | backend | localhost | memset | Query | 58231 | statistics | SELECT `account`.`id`, `account`.`admin_id`, `account`.`billing_id`, `account`.`currency`, `account` |
| 43883 | backend | localhost | memset | Query | 58136 | statistics | SELECT `account`.`id`, `account`.`admin_id`, `account`.`billing_id`, `account`.`currency`, `account` |
| 44190 | root | localhost | | Query | 0 | | show processlist |
+-------+---------+-----------------+--------+---------+-------+------------+----------------------------------------------------------------------------------------

As you can see the queries are not completing even after 17 hours!

Disabling the query log (commenting out the log= in my.cnf) fixes the
problems and the queries become instant again.

This is with mysql 5.0.51a-17 and django 1.0

I'm not sure whether this is a bug in django or mysql, but hopefully
this message will act as a heads-up to anyone else having the same
problem!

--
Nick Craig-Wood <ni...@craig-wood.com> -- http://www.craig-wood.com/nick

[Nick Craig-Wood]

Following up to myself...

On Wed, Feb 25, 2009 at 09:16:09AM +0000, Nick Craig-Wood wrote:
> I've finally got to the bottom of why django locks up on our dev server.
>
> Our dev server has mysql query logging turned on with this in /etc/mysql/my.cnf
>
> # Query logging
> log=/var/log/mysql/mysqld.log
>
> However with that, then simple queries (like looking at the accounts
> table in the admin interface) lock up leaving this in the process
> list.
>
> +-------+---------+-----------------+--------+---------+-------+------------+------------------------------------------------------------------------------------------------------+
> | Id | User | Host | db | Command | Time | State | Info |
> +-------+---------+-----------------+--------+---------+-------+------------+------------------------------------------------------------------------------------------------------+
> | 43726 | backend | localhost:59678 | memset | Query | 62773 | statistics | SELECT `account`.`id`, `account`.`admin_id`, `account`.`billing_id`, `account`.`currency`, `account` |
> | 43880 | backend | localhost | memset | Query | 58289 | statistics | SELECT `account`.`id`, `account`.`admin_id`, `account`.`billing_id`, `account`.`currency`, `account` |
> | 43882 | backend | localhost | memset | Query | 58231 | statistics | SELECT `account`.`id`, `account`.`admin_id`, `account`.`billing_id`, `account`.`currency`, `account` |
> | 43883 | backend | localhost | memset | Query | 58136 | statistics | SELECT `account`.`id`, `account`.`admin_id`, `account`.`billing_id`, `account`.`currency`, `account` |
> | 44190 | root | localhost | | Query | 0 | | show processlist |
> +-------+---------+-----------------+--------+---------+-------+------------+----------------------------------------------------------------------------------------
>
> As you can see the queries are not completing even after 17 hours!
>
> Disabling the query log (commenting out the log= in my.cnf) fixes the
> problems and the queries become instant again.
>
> This is with mysql 5.0.51a-17 and django 1.0

I should really check stuff more before posting :-(

It seems that the act of restarting mysql was enough to let those
queries run properly which is independent of the mysql query log.

So any clues gratefully received!

[sime]

On Feb 25, 7:24 pm, Nick Craig-Wood <n...@craig-wood.com> wrote:

Sounds like your table and/or innodb/mysql install is broken. It's
hanging on updating statistics. Try REPAIR TABLE or google for
something like "mysql state statistics".