django-cms
CacheKey model in menus causing database locks
Summary
In the past few days we've been seeing OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction') coming from one of our CMS systems. Our infrastructure at this point is running about 5 servers through a load balancer with a MySQL database server.
This has been triggered from our clients trying to publish content & seems to be a result of the menu app & it's use of cache keys in a table.
Expected behaviour
I'd expect that any part of the system using cache would prepare a key and then check if that key exists in the cache, if not, set whatever data it needs in the cache with that key.
If for whatever reason there is a need to be storing cache keys in the database, I'd expect the key field to be unique.
Actual behaviour
Cache keys are stored in the database & errors appear to be triggered based on clearing those keys. Keys don't appear to be unique so we're seeing multiple objects with the same information;
Stacktrace;
OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')
File "django/core/handlers/exception.py", line 42, in inner
response = get_response(request)
File "django/core/handlers/base.py", line 249, in _legacy_get_response
response = self._get_response(request)
File "django/core/handlers/base.py", line 187, in _get_response
response = self.process_exception_by_middleware(e, request)
File "django/core/handlers/base.py", line 185, in _get_response
response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "django/utils/decorators.py", line 149, in _wrapped_view
response = view_func(request, *args, **kwargs)
File "django/views/decorators/cache.py", line 57, in _wrapped_view_func
response = view_func(request, *args, **kwargs)
File "django/contrib/admin/sites.py", line 211, in inner
return view(request, *args, **kwargs)
File "django/utils/decorators.py", line 67, in _wrapper
return bound_func(*args, **kwargs)
File "django/views/decorators/http.py", line 40, in inner
return func(request, *args, **kwargs)
File "django/utils/decorators.py", line 63, in bound_func
return func.__get__(self, type(self))(*args2, **kwargs2)
File "django/utils/decorators.py", line 185, in inner
return func(*args, **kwargs)
File "cms/admin/pageadmin.py", line 983, in publish_page
all_published = page.publish(language)
File "cms/models/pagemodel.py", line 713, in publish
public_page = self._publisher_save_public(public_page)
File "cms/models/pagemodel.py", line 1386, in _publisher_save_public
obj.save()
File "cms/models/pagemodel.py", line 589, in save
super(Page, self).save(**kwargs)
File "django/db/models/base.py", line 796, in save
force_update=force_update, update_fields=update_fields)
File "cms/models/pagemodel.py", line 604, in save_base
return super(Page, self).save_base(*args, **kwargs)
File "django/db/models/base.py", line 820, in save_base
update_fields=update_fields)
File "django/dispatch/dispatcher.py", line 191, in send
response = receiver(signal=self, sender=sender, **named)
File "cms/signals/page.py", line 21, in pre_save_page
menu_pool.clear(instance.site_id)
File "menus/menu_pool.py", line 343, in clear
cache_keys.delete()
File "django/db/models/query.py", line 609, in delete
deleted, _rows_count = collector.delete()
File "django/db/models/deletion.py", line 306, in delete
count = query.delete_batch(pk_list, self.using)
File "django/db/models/sql/subqueries.py", line 46, in delete_batch
num_deleted += self.do_query(self.get_meta().db_table, self.where, using=using)
File "django/db/models/sql/subqueries.py", line 28, in do_query
cursor = self.get_compiler(using).execute_sql(CURSOR)
File "django/db/models/sql/compiler.py", line 835, in execute_sql
cursor.execute(sql, params)
File "raven/contrib/django/client.py", line 123, in execute
return real_execute(self, sql, params)
File "django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "django/db/utils.py", line 94, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "django/db/backends/mysql/base.py", line 110, in execute
return self.cursor.execute(query, args)
File "MySQLdb/cursors.py", line 205, in execute
self.errorhandler(self, exc, value)
File "MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
Environment
- Python version: 2.7
- Django version: 1.10.8
- django CMS version: 3.4.5
@czpython given the related tickets, is there any work on this yet or any thought on a potential solution? I'm starting a load test tomorrow & if I see anymore issues coming from that related to this table I might start digging into how the CacheKeys table is used & a solution to purely generate cache keys avoiding the database.
@jrief heres my issue raised when I hit the issue with the menu cache keys.
As I said during the meeting today, we shall use Redis with wildcard key deletion. There is a workaround in memcached which achieves the same result.
