我有一些 django 应用程序正在杀死我的 mysql 服务器，其中有正在运行的查询，但没有超时，并且堵塞了我的 mysql 服务器
只需要帮助如何以正确的方式解决这个问题，不确定这是 django 的设计方式还是我需要添加超时设置，我认为 django 应该默认这样做，不确定，因为没有超时设置是没有意义的
以下是未超时或未关闭的查询
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'
这只是我目前拥有的一个副本，这些查询持续运行很长时间，直到我重新启动 mysql 服务器，它们才会再次启动并持续运行，最终堵塞服务器上的 CPU 和 I/O
settings.py以下是文件中的一些内容
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',
我该怎么做才能修复这个问题？
谢谢
更新：
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)
我补充道
wait_timeout = 120
但仍然遇到同样的问题
答案1
wait_timeout 在允许长时间运行方面起着重要作用。请查看您的 MySQL 版本的文档。
答案2
将您的 SELECT COUNT(*) FROM django_sessionWHERE django_session. last_login>= '2017-09-19 18:44:56' 更改为此 SELECT COUNT( last_login) FROM django_sessionWHERE django_session. last_login>= '2017-09-19 18:44:56'