5

I have a one year production site configured with django.contrib.sessions.backends.cached_db backend with a MySQL database backend. The reason why I chose cached_db is a mix of security with read performance.

The problem is, the cleanup command, responsible to delete all expired sessions, was never executed, resulting in a 2.3GB session table data length, 6 million rows and 500Mb index length.

When I try to run the ./manage.py cleanup (in Django 1.3) command, or ./manage.py clearsessions (Django`s 1.5 correspondent), the process never ends (or my patience doesn't complete 3 hours).

The code that Django use's to do this is:

Session.objects.filter(expire_date__lt=timezone.now()).delete()

In a first impression, I think that's normal because the table has 6M rows, but, after I inspect System's monitor, I discover that all memory and cpu was used by the python process, not mysqld, fullfilling my machine's resources. I think that's something terrible wrong with this command code. It seems that python iterates over all founded expired session rows before deleting each of them, one by one. In this case, a code refactoring to just raw a DELETE FROM command can resolve my problem and helps Django community, right? But, if this is the case, a Queryset delete command is acting weird and none optimized in my opinion. Am I right?

ZF007
  • 3,708
  • 8
  • 29
  • 48
Ricardo
  • 618
  • 9
  • 11
  • 3
    Django uses its ORM to delete records which isn't efficient for bulk operations - you'd be better off just doing a SQL `DELETE` where the session has expired - otherwise, it's retrieving each row, one at a time, starting a transaction, checking properties haven't changed, deleting it, committing, etc, etc – Basic Aug 15 '13 at 18:13
  • 2
    With Postgres, you can achieve exactly what Basic suggested with `DELETE FROM django_session WHERE expire_date < now();`. Don't forget to do a backup first ! – niconoe Apr 10 '15 at 09:06
  • 1
    Depending of the size table and your Postgres version, you may also need to perform a `VACUUM`, 'VACUUM FULL` (and maybe REINDEX) to give the free space back to your OS. See https://wiki.postgresql.org/wiki/VACUUM_FULL. – niconoe Apr 10 '15 at 09:52
  • Possible duplicate of [Delete session key from all users](http://stackoverflow.com/questions/30864711/delete-session-key-from-all-users) – e4c5 May 19 '16 at 13:47
  • Running into this now, watching memory spike up instantly then the process is killed when it runs out of memory. – teewuane Dec 07 '19 at 03:38

0 Answers0