I have some django applications that is killing my mysql server there are queries that are running and not timing out and clogging my mysql server
Just need help on how to resolve this issue the proper way not sure if this is how django is designed or i need to add a timeout settings i thought django should do this by default, not sure because makes zero sense to have this with no timeout
Here are the queries that are not timing out or closing
MySQL on localhost (5.7.19) load 5.27 4.81 4.69 4/2383 41852 up 0+00:18:41 [19:13:16]
Queries: 8.6k qps: 8 Slow: 0.0 Se/In/Up/De(%): 61/01/02/00
Sorts: 0 qps now: 5 Slow qps: 0.0 Threads: 99 ( 99/ 1) 44/00/00/00
Key Efficiency: 98.9% Bps in/out: 839.8/22.3k Now in/out: 566.3/22.5k
Id User Host/IP DB Time Cmd State Query
-- ---- ------- -- ---- --- ----- ----------
6 webapp_user localhost webapp_db 1096 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:44:56'
9 webapp_user localhost webapp_db 1086 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:45:09'
11 webapp_user localhost webapp_db 1085 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:45:10'
15 webapp_user localhost webapp_db 1067 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:45:28'
16 webapp_user localhost webapp_db 1056 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:45:39'
20 webapp_user localhost webapp_db 1053 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:45:42'
25 webapp_user localhost webapp_db 1037 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:45:58'
28 webapp_user localhost webapp_db 1025 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:46:11'
29 webapp_user localhost webapp_db 1022 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:46:14'
32 webapp_user localhost webapp_db 1006 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:46:29'
35 webapp_user localhost webapp_db 994 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:46:42'
36 webapp_user localhost webapp_db 990 Query Sending SELECT COUNT(*) FROM `django_session` WHERE `django_session`.`last_login` >= '2017-09-19 18:46:45'
This is just a copy of what i currently have, those queries keeps on running for a long time and till i restart mysql server and they start again and keep running and eventually they clog up cpu and i/o on the server
Below are some of the content in settings.py
file
SESSION_COOKIE_AGE = 60*60*24*365*5
SESSION_ENGINE = 'webapp.sessions.backends.db'
SESSION_SAVE_EVERY_REQUEST = True
# 'django.contrib.sessions.middleware.SessionMiddleware',
'webapp.sessions.middleware.SessionMiddleware',
# 'django.contrib.sessions',
'sessions',
What do i do to fix this?
Thanks
UPDATE:
mysql> 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 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW INDEX FROM django_session;
+----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| django_session | 0 | PRIMARY | 1 | session_key | A | 24152280 | NULL | NULL | | BTREE | | |
| django_session | 1 | django_session_c25c2c28 | 1 | expire_date | A | 20456614 | NULL | NULL | | BTREE | | |
+----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
i added
wait_timeout = 120
but still experiencing same issue
wait_timeout plays a large part in allowing long run times. Check the documentation for your version of MySQL.
Change your SELECT COUNT(*) FROM
django_session
WHEREdjango_session
.last_login
>= '2017-09-19 18:44:56' TO THIS SELECT COUNT(last_login
) FROMdjango_session
WHEREdjango_session
.last_login
>= '2017-09-19 18:44:56'