django_session table killing my MySQL server and django app

Question

I need your help I hav a Django app that is about 7 years old and have been degrading now..every time the website goes down and back up and the issue is with mysql server

Usually i need to just restart mysql server and it comes back up..i have been doing this for 2 years now and am ready to fix the issue once and for all and need your help to do this

Here is screenshot of the database tables

The django_session tables has 3,512,729,600 data Length and 24,152,301 rows I am guessing this is where the issue is coming from and why the website just dies sometimes when mysql can not handle the query and several timeouts that kills the app. If it is something else then please enlighten me.

Anyways i tried deleting the django_session table but with no luck I get the following errors

Error in query (1451): Cannot delete or update a parent row: a foreign key constraint fails (font_database.fonts_fontfile, CONSTRAINT session_id_refs_session_key_37e5d4124e1e319f FOREIGN KEY (session_id) REFERENCES django_session (session_key))

What do i do here? Need serious help here

MySQL version is 5.7.23

If you need any more information please ask me and i will try to add it to this question.

Thanks

UPDATE:

Just noticed that issue is with total number of connections on the MySQL server is maxed out at 152

mysql> show status where `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 152   |
+-------------------+-------+
1 row in set (0.74 sec)

Also when i check the connections they are all from the django_sessions which is why when i restart server it works fine but then gets back to same issue as the sessions table is just too high and too many rows

mysql> show processlist;
+------+--------------------+-----------+--------------------+---------+------+--------------+----------------------------------------------------------------------------------------------------+
| Id   | User               | Host      | db                 | Command | Time | State        | Info                                                                                               |
+------+--------------------+-----------+--------------------+---------+------+--------------+----------------------------------------------------------------------------------------------------+
| 3709 | font_database | localhost | font_database | Query   | 3164 | Sending data | SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2019-02-23 13:00:37' |
| 3710 | font_database | localhost | font_database | Query   | 3162 | Sending data | SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2019-02-23 13:00:39' |
| 3711 | font_database | localhost | font_database | Query   | 3161 | Sending data | SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2019-02-23 13:00:41' |
| 3714 | font_database | localhost | font_database | Query   | 3133 | Sending data | SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2019-02-23 13:01:09' |
| 3716 | font_database | localhost | font_database | Query   | 3131 | Sending data | SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2019-02-23 13:01:11' |
| 3717 | font_database | localhost | font_database | Query   | 3129 | Sending data | SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2019-02-23 13:01:13' |
...
...
till maxed to 152 connections

Again if you need any additional information, ask and i will provide it. This is all i believe can help give idea of what issue is

---

UPDATE: (from request in comments)

EXPLAIN SELECT COUNT(*) FROM django_session WHERE django_session.last_login >= '2019-02-23 13:00:37'

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  django_session  NULL    ALL NULL    NULL    NULL    NULL    24200442    33.33   Using where

SHOW CREATE TABLE django_session

Table   Create Table
django_session  CREATE TABLE `django_session` (
  `session_key` varchar(40) NOT NULL,
  `session_data` longtext NOT NULL,
  `expire_date` datetime NOT NULL,
  `created` datetime NOT NULL,
  `last_login` datetime NOT NULL,
  PRIMARY KEY (`session_key`),
  KEY `django_session_c25c2c28` (`expire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

---

UPDATE v2: (after running ALTER TABLE django_session ADD KEY (last_login))

EXPLAIN SELECT COUNT(*) FROM django_session WHERE django_session.last_login >= '2019-03-04 13:00:37'

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  django_session  NULL    range   last_login  last_login  5   NULL    1   100.00  Using where; Using index

Answer 1

The problem is that SELECT COUNT(*) FROM django_session WHERE django_session.last_login >= '2019-02-23 13:00:37' is a slow query. Your process list is showing this taking 3133 seconds so far and hasn't completed.

If there was an index on the last_login column in django_session this would likely be quicker.

ALTER TABLE django_session ADD KEY (last_login)

You should also check what this query is providing within django. It seems like a rather meaningless number.

I'd suspect 2M rows is probably covering old data and probably can be deleted. Try the clearing method in this answer.

Recommend clearing at least some rows before adding an index.