Re: [sqlalchemy] watining for table metadata lock
On Thu, Aug 29, 2013 at 4:48 PM, diverman pa...@schon.cz wrote: Hi, we observed deadlock-like problem on our multi-component system with mysql database. Our setup: 1) MySQL server 5.5 with many MyISAM tables Foo_timestamp, one per day (like partitioning) 2) C++ backend daemon * issuing CREATE TABLE IF NOT EXISTS Foo_timestamp (...) once per day after midnight * filling those tables with INSERT INTO Foo_timestamp during intraday 3) pythonic multi-threaded backend daemon (we call it 'worker') * reads Foo_timestamp tables every minute via ScopedSession and mapped classes 'Foo' * fills memcache with statistics computed from Foo_* tables 4) pythonic/apache/mod_wsgi single-threaded, multi-process webserver * runs also on shared codebase 'foobar', so it uses also ScopedSession (same as 'worker') * codebase is same when runing in webserver-mode and when running in worker-mode (ScopedSession is always used). We observed that during rollover to next day the C++ BE daemon is blocked on 'Waiting for table metadata lock: CREATE TABLE IF NOT EXISTS Foo_x (...)' Webserver is also blocked. Kill the 'worker' helps, all blocked stuff continues. If anyone could know: - why it blocks since we have MyISAM,which are transaction-less - why the worker is the blockator, since it only executes SELECTs - how to fix it? This sounds like more of a MySQL issue than an SQLAlchemy issue, so you might have more luck on a MySQL mailing list. In answer to your first question, http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html suggests that non-transactional tables can also be involved in metadata locks. I'm afraid I can't help with the rest. Simon -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] watining for table metadata lock
On Thu, Aug 29, 2013 at 5:20 PM, Simon King si...@simonking.org.uk wrote: On Thu, Aug 29, 2013 at 4:48 PM, diverman pa...@schon.cz wrote: Hi, we observed deadlock-like problem on our multi-component system with mysql database. Our setup: 1) MySQL server 5.5 with many MyISAM tables Foo_timestamp, one per day (like partitioning) 2) C++ backend daemon * issuing CREATE TABLE IF NOT EXISTS Foo_timestamp (...) once per day after midnight * filling those tables with INSERT INTO Foo_timestamp during intraday 3) pythonic multi-threaded backend daemon (we call it 'worker') * reads Foo_timestamp tables every minute via ScopedSession and mapped classes 'Foo' * fills memcache with statistics computed from Foo_* tables 4) pythonic/apache/mod_wsgi single-threaded, multi-process webserver * runs also on shared codebase 'foobar', so it uses also ScopedSession (same as 'worker') * codebase is same when runing in webserver-mode and when running in worker-mode (ScopedSession is always used). We observed that during rollover to next day the C++ BE daemon is blocked on 'Waiting for table metadata lock: CREATE TABLE IF NOT EXISTS Foo_x (...)' Webserver is also blocked. Kill the 'worker' helps, all blocked stuff continues. If anyone could know: - why it blocks since we have MyISAM,which are transaction-less - why the worker is the blockator, since it only executes SELECTs - how to fix it? This sounds like more of a MySQL issue than an SQLAlchemy issue, so you might have more luck on a MySQL mailing list. In answer to your first question, http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html suggests that non-transactional tables can also be involved in metadata locks. I'm afraid I can't help with the rest. Googling mysql Waiting for table metadata lock gives a few clues. This one looks like a nice short summary: http://www.chriscalender.com/?p=1189 Simon -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] watining for table metadata lock
Hi,
of course I searched the web before submitting the question. None of them
pointed to what is the real problem.
This can be easily reproduced with sqlalchemy:
*console 1:*
mysql create table if not exists Foo (i int) engine MyISAM;
Query OK, 0 rows affected (0,00 sec)
mysql
*console 2:*
foo = engine.execute('select * from Foo;')
*now go back to console 1:*
mysql create table if not exists Foo (i int);
*Blocked! *
*now go to console 2:*
del(foo)
*console 1: has u**nblocked!*
Query OK, 0 rows affected, 1 warnings
mysql
Pavel
Dne čtvrtek, 29. srpna 2013 18:23:12 UTC+2 Simon King napsal(a):
On Thu, Aug 29, 2013 at 5:20 PM, Simon King
si...@simonking.org.ukjavascript:
wrote:
On Thu, Aug 29, 2013 at 4:48 PM, diverman pa...@schon.cz javascript:
wrote:
Hi,
we observed deadlock-like problem on our multi-component system with
mysql
database.
Our setup:
1) MySQL server 5.5 with many MyISAM tables Foo_timestamp, one per
day
(like partitioning)
2) C++ backend daemon
* issuing CREATE TABLE IF NOT EXISTS Foo_timestamp (...) once per
day
after midnight
* filling those tables with INSERT INTO Foo_timestamp during
intraday
3) pythonic multi-threaded backend daemon (we call it 'worker')
* reads Foo_timestamp tables every minute via ScopedSession and
mapped
classes 'Foo'
* fills memcache with statistics computed from Foo_* tables
4) pythonic/apache/mod_wsgi single-threaded, multi-process webserver
* runs also on shared codebase 'foobar', so it uses also
ScopedSession
(same as 'worker')
* codebase is same when runing in webserver-mode and when running in
worker-mode (ScopedSession is always used).
We observed that during rollover to next day the C++ BE daemon is
blocked on
'Waiting for table metadata lock: CREATE TABLE IF NOT EXISTS
Foo_x
(...)'
Webserver is also blocked. Kill the 'worker' helps, all blocked stuff
continues. If anyone could know:
- why it blocks since we have MyISAM,which are transaction-less
- why the worker is the blockator, since it only executes SELECTs
- how to fix it?
This sounds like more of a MySQL issue than an SQLAlchemy issue, so
you might have more luck on a MySQL mailing list. In answer to your
first question,
http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html suggests
that non-transactional tables can also be involved in metadata locks.
I'm afraid I can't help with the rest.
Googling mysql Waiting for table metadata lock gives a few clues.
This one looks like a nice short summary:
http://www.chriscalender.com/?p=1189
Simon
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] watining for table metadata lock
MyISAM doesn't support transactions, but it does support locking. According to the docs, it actually relies on table locking for certain operations. What are your sqlalchemy connection strings for the engine ? Have you tried explicitly setting autocommit mode within the query ? Have you tried verbose logging of SqlAlchemy and/pr MySql to see what commands are being sent to the server in which order to cause this ? -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] watining for table metadata lock
On Thu, Aug 29, 2013 at 6:01 PM, diverman pa...@schon.cz wrote:
Dne čtvrtek, 29. srpna 2013 18:23:12 UTC+2 Simon King napsal(a):
On Thu, Aug 29, 2013 at 5:20 PM, Simon King si...@simonking.org.uk
wrote:
On Thu, Aug 29, 2013 at 4:48 PM, diverman pa...@schon.cz wrote:
Hi,
we observed deadlock-like problem on our multi-component system with
mysql
database.
Our setup:
1) MySQL server 5.5 with many MyISAM tables Foo_timestamp, one per
day
(like partitioning)
2) C++ backend daemon
* issuing CREATE TABLE IF NOT EXISTS Foo_timestamp (...) once per
day
after midnight
* filling those tables with INSERT INTO Foo_timestamp during
intraday
3) pythonic multi-threaded backend daemon (we call it 'worker')
* reads Foo_timestamp tables every minute via ScopedSession and
mapped
classes 'Foo'
* fills memcache with statistics computed from Foo_* tables
4) pythonic/apache/mod_wsgi single-threaded, multi-process webserver
* runs also on shared codebase 'foobar', so it uses also
ScopedSession
(same as 'worker')
* codebase is same when runing in webserver-mode and when running in
worker-mode (ScopedSession is always used).
We observed that during rollover to next day the C++ BE daemon is
blocked on
'Waiting for table metadata lock: CREATE TABLE IF NOT EXISTS
Foo_x
(...)'
Webserver is also blocked. Kill the 'worker' helps, all blocked stuff
continues. If anyone could know:
- why it blocks since we have MyISAM,which are transaction-less
- why the worker is the blockator, since it only executes SELECTs
- how to fix it?
This sounds like more of a MySQL issue than an SQLAlchemy issue, so
you might have more luck on a MySQL mailing list. In answer to your
first question,
http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html suggests
that non-transactional tables can also be involved in metadata locks.
I'm afraid I can't help with the rest.
Googling mysql Waiting for table metadata lock gives a few clues.
This one looks like a nice short summary:
http://www.chriscalender.com/?p=1189
Simon
Hi,
of course I searched the web before submitting the question. None of them
pointed to what is the real problem.
This can be easily reproduced with sqlalchemy:
console 1:
mysql create table if not exists Foo (i int) engine MyISAM;
Query OK, 0 rows affected (0,00 sec)
mysql
console 2:
foo = engine.execute('select * from Foo;')
now go back to console 1:
mysql create table if not exists Foo (i int);
Blocked!
now go to console 2:
del(foo)
console 1: has unblocked!
Query OK, 0 rows affected, 1 warnings
mysql
Pavel
I'm not quite sure what your point is. I assume you can also reproduce
it with plain MySQL (you may need to set @@autocommit=0; in one or
both consoles). I can't test it myself as I don't have MySQL 5.5.
Simon
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] watining for table metadata lock
Hi Jonathan,
I don't use any special connection parameters. All are defaults.
I I execute
engine.execute('set autocommit=1;')
foo = engine.execute('select * from Foo;')
...then on other console CREATE TABLE IF NOT EXISTS Foo(i int) don't block.
So it seems it's an application bug. Autocommit should be True for
ScopedSession when running in worker's context.
On Thursday, August 29, 2013 7:17:51 PM UTC+2, Jonathan Vanasco wrote:
MyISAM doesn't support transactions, but it does support locking.
According to the docs, it actually relies on table locking for certain
operations.
What are your sqlalchemy connection strings for the engine ?
Have you tried explicitly setting autocommit mode within the query ?
Have you tried verbose logging of SqlAlchemy and/pr MySql to see what
commands are being sent to the server in which order to cause this ?
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] watining for table metadata lock
I'm not quite sure what your point is. I was trying to figure out if that's a bug in worker, sqlalchemy, mysql or elsewhere, since I was unable to reproduce it in development environment, only in laboratory conditions using commands I posted to my previous e-mail. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
