0

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


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
  • The problem is that SELECT COUNT(*) FROM django_session WHERE django_session.last_login >= '2019-02-23 13:00:37' is a slow query. Please show EXPLAIN SELECT COUNT(*) FROM django_session WHERE django_session.last_login >= '2019-02-23 13:00:37' and SHOW CREATE TABLE django_session – danblack Feb 25 at 22:04
  • Please post TEXT data requested by danblack on Feb 25, 19 at 22:04 plus results of SHOW INDEX FROM django_session; Thanks – Wilson Hauck Mar 2 at 0:15
  • i have updated question with output of the SQL commands at bottom of the post/question – uberrebu Mar 2 at 20:49
2

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.

  • i have updated question with output of the SQL commands at bottom of the post/question...i am not really sure what those do but i am open to share privately with you more details about the project and pay for your time..but anyways what will be the reason to add index to last_login? – uberrebu Mar 2 at 20:50
  • The SHOW CREATE TABLE shows the table structure and the EXPLAIN showed how the query is executed. Type ALL means that it needs to examine the entire table to delete entries. Added the KEY like the ALTER TABLE... part of this answer says will enable MySQL to find the old last_login entries quicker. I'm not interesting in a job or a detailed involvement in this question like many SO/DBA helpers. Minor clarification questions can be asked however the answer is here. – danblack Mar 3 at 21:46
  • the real issue is with the total number of connections which it is maxing out...even if i increase it it will still max out..so how can i solve that issue? seems the app is doing things wrong in logic..not sure why it keeps that many connection when it is only one app connecting to it. that is what i need help with. because the connections issue is why the app goes down all the time – uberrebu Mar 3 at 23:25
  • By making these queries really quick, they won't be clogging the maximum number of connections. Occasionally connections will be phantoms due to web pages going away. These normally get timed out before they are exhausted. – danblack Mar 3 at 23:28
  • so what do i need to do again? kind of lost sight on what i need to do? truncate table or sun the alter command? – uberrebu Mar 3 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.