mysql 压力 死_Mysql数据库的压力

问题描述

使用django+celery时, 运行长时间的任务, 一开始操作一次数据库, 20个小时后再次操作数据库.

过了20多个小时, 再次操作数据库时报错.

raised unexpected: OperationalError(2006, 'MySQL server has gone away')

Traceback (most recent call last):

File "/usr/local/lib/python2.7/dist-packages/django/db/models/manager.py", line 85, in manager_method

return getattr(self.get_queryset(), name)(*args, **kwargs)

File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 374, in get

num = len(clone)

File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 232, in __len__

self._fetch_all()

File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 1118, in _fetch_all

self._result_cache = list(self._iterable_class(self))

File "/usr/local/lib/python2.7/dist-packages/django/db/models/query.py", line 53, in __iter__

results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)

File "/usr/local/lib/python2.7/dist-packages/django/db/models/sql/compiler.py", line 894, in execute_sql

raise original_exception

OperationalError: (2006, 'MySQL server has gone away')

原因

连接超时

我出现问题就是这个原因.

进入mysql,执行下面的命令:

mysql> show global variables like '%timeout';

+----------------------------+----------+

| Variable_name | Value |

+----------------------------+----------+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 28800 |

| lock_wait_timeout | 31536000 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| slave_net_timeout | 3600 |

| wait_timeout | 28800 |

+----------------------------+----------+

wait_timeout 是28800秒，即mysql链接在无操作28800秒后被自动关闭

解决方法

立即生效

执行下面的sql命令

mysql> set global wait_timeout=60*60*30;

1

该命令是将wait_timeout设置为30小时.

用这种方法,修改完立即生效. 如果重启mysql, 又恢复原来的28800秒.

永久生效

该方法修改完之后, 需要重启mysql才能生效.

编辑mysql配置文件my.cnf,添加或修改为下面这条命令.

(我的mysql配置文件路径: /etc/mysql/my.cnf )

wait_timeout =108000

1

其他原因

还有一些其他原因, 简单描述一下

1: mysql服务关闭

2: 数据包 过大

解决方法: 修改 max_allowed_packet 的值, 方法同上.