MySQL&django hangs on huge session delete

I'm running django site with MySQL as DB back-end. Finally i've got 3 millions rows in django_session table. Most of them are expired, thus i want to remove them. But if i manually run delete from django_session where expire_date < "2011-04开发者_JS百科-18" whole site seems to be hanged - it cannot be accessed via browser.

Why such kind of blocking is possible? How to avoid it?

If your table is MyISAM, DELETE operations lock the table and it is not accessible by the concurrent queries.

If there are many records to delete, the table is locked for too long.

Split your DELETE statement into several shorter batches.

I am not MySQL expert, but I guess MySQL locks the table for the deleting and this might be MySQL transaction/backend related. When deleting is in progress MySQL blocks the access to the table from other connections. MyISAM and InnoDB backend behavior might differ. I suggest you study MySQL manual related to this: the problem is not limited to Django domain, but generally how to delete MySQL rows without blocking access to the table.

For the future reference I suggest you set-up a session cleaner task which will clear the sessions, let's say once in a day, from cron so that you don't end up with such huge table.

You could try the sample cleanup command - http://docs.djangoproject.com/en/dev/topics/http/sessions/?from=olddocs#clearing-the-session-table - which removes all expired sessions i think.

If I recall correctly there is no index on the expire_date column Operating on a a lot of rows will take a while. While that's happening it will keep a lock on the table too (in MyISAM at least).

You might be better off deleting based on primary key (e.g. id) until you have fewer rows in the django_session table. Once you have fewer rows you can add an index to expire_date and make sure you run the cleanup command regularly.

You'll have to estimate where to start your deletion from, but assuming that you know that all rows with a id less than 2,900,000 need removing:

delete from django_session where id < 29000000;

To avoid locking the table for too long you might want to delete a few thousand rows at a time, each in their own transaction, e.g.

begin;
delete from django_session where id < 10000;
commit;
begin;
delete from django_session where id < 20000;
commit;
...
begin;
delete from django_session where id < 29000000;
commit;

Of course you should probably create a script to do this.

In general you want to delete fewer rows at a time. It will take longer overall, but it shouldn't lock your site up for too long in one go.