3

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 enter image description here

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

uberrebu
  • 503
  • 6
  • 17
  • 36
  • The symptoms (having to restart your MySQL server) and the attempted fix (deleting the session table) are completely unrelated. – Sven Feb 23 '19 at 10:28
  • what do you mean they are unrelated? there is issue with the mysql server for this app and usually if i restart the db server everything works fine again temporarily..so can you explain what you mean? as you can see the row for the table is huge and can be causing issues..there are reported solution from links like this https://snipplr.com/view/62190/clean-up-expired-djangocontribsessions-in-a-huge-mysql-innodb-table/ and https://djangosnippets.org/snippets/1273/ – uberrebu Feb 23 '19 at 10:30
  • You can't DROP the table, just TRUNCATE it instead. After that, there's not enough information in this question to give you any further help, which is why it's on hold. – Michael Hampton Feb 23 '19 at 14:12
  • are you gusy django/python developers? what do you gain from putting the question on hold..i have a problem..sometimes i dont even know how to solve but i provided information that can explain what the issue is..if i knew how to solve i would not post here..not all time questions have to be perfect..i believe question is enough to start discussion of problem..this is a django issue..there is issue with the django app and mysql server and need help on what to do to solve it..leave this alone and let others help....i mean this website is getting too arrogant now – uberrebu Feb 23 '19 at 19:06
  • We aren't developers, and this isn't a site for programming. It's for operations. Which, apparently, is what your question is about. But I think there's enough information in your question now. BTW, you still should TRUNCATE your table... – Michael Hampton Feb 23 '19 at 19:17

1 Answers1

4

You have too many expired sessions for these 7 years. You may try manage.py clearsessions https://docs.djangoproject.com/en/2.2/topics/http/sessions/#clearing-the-session-store

Django does not provide automatic purging of expired sessions. Therefore, it’s your job to purge expired sessions on a regular basis. Django provides a clean-up management command for this purpose: clearsessions. It’s recommended to call this command on a regular basis, for example as a daily cron job.

Владислав Балаклейский
  • 41
  • 2