首页   注册   登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
guoqiao
V2EX  ›  MySQL

MySQL不断 crash 是怎么回事?

  •  
  •   guoqiao · 2014-01-09 11:52:32 +08:00 · 8572 次点击
    这是一个创建于 2201 天前的主题,其中的信息可能已经有所发展或是发生改变。
    我在 1G 内存的 linode 上跑了个 Django 站: http://readfree.me/
    数据库是 MySQL 5.5, 存储引擎是5.5开始默认的 InnoDB. 一般同时在线用户数不超过20.

    我每天会收到几十次 Django 发来的出错邮件, 内容都是各种数据库查询失败:
    ......
    OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)")

    但这些查询都是正常的查询, 本地调试从来没有失败过, 只是在服务器上偶尔会失败.

    我自己访问网站时, 也时不时会遇到500错误, 但一般刷新下又好了. 接着就会收到上面的邮件.
    偶尔也有网站挂掉起不来的情况, ssh 到服务器, 发现 mysql 服务停止了, 启动就好了.

    我很不解, 数据库明明跑得好好的, 访问量也不大, 为什么会时不时中断呢?
    看了下 MySQL 的 error.log , 发现原来数据库在频繁的 crash . 见帖子最后.
    大部分时候可以自动恢复, 但是也会出现恢复时分配内存失败, mysql 挂掉, 从而导致网站挂掉的情况.

    最近我反复调整 my.cnf 的参数, 但是问题一直没有得到彻底解决.
    请问有人遇到过类似的问题吗? 能否提供点思路?

    ==================MySQL error.log=====================
    140109 11:26:10 InnoDB: The InnoDB memory heap is disabled
    140109 11:26:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    140109 11:26:10 InnoDB: Compressed tables use zlib 1.2.3.4
    140109 11:26:10 InnoDB: Initializing buffer pool, size = 180.0M
    140109 11:26:10 InnoDB: Completed initialization of buffer pool
    140109 11:26:10 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 3846798084
    140109 11:26:10 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files...
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer...
    InnoDB: Warning: database page corruption or a failed
    InnoDB: file read of space 0 page 15686.
    InnoDB: Trying to recover it from the doublewrite buffer.
    InnoDB: Recovered the page from the doublewrite buffer.
    InnoDB: Warning: database page corruption or a failed
    InnoDB: file read of space 0 page 49.
    InnoDB: Trying to recover it from the doublewrite buffer.
    InnoDB: Recovered the page from the doublewrite buffer.
    InnoDB: Doing recovery: scanned up to log sequence number 3846799817
    140109 11:26:13 InnoDB: Starting an apply batch of log records to the database...
    InnoDB: Progress in percents: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    140109 11:26:13 InnoDB: Waiting for the background threads to start
    140109 11:26:14 InnoDB: 5.5.34 started; log sequence number 3846799817
    140109 11:26:14 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
    140109 11:26:14 [Note] - '127.0.0.1' resolves to '127.0.0.1';
    140109 11:26:14 [Note] Server socket created on IP: '127.0.0.1'.
    140109 11:26:15 [Note] Event Scheduler: Loaded 0 events
    140109 11:26:15 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.34-0ubuntu0.12.04.1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
    35 回复  |  直到 1970-01-01 08:00:00 +08:00
    Livid
        1
    Livid   V2EX Moderator   2014-01-09 12:31:40 +08:00 via iPhone
    服务器内存不够。
    fire9
        2
    fire9   2014-01-09 13:07:36 +08:00
    140109 11:26:10 InnoDB: Initializing buffer pool, size = 180.0M 如果你用innodb 这个值简直就不可想象啊!
    guoqiao
        3
    guoqiao   2014-01-09 14:04:17 +08:00
    @Livid 字面上的提示来说, 确实是内存不足. 但是没道理啊, 这么点访问量, 1G 的内存, 不至于不够吧?
    guoqiao
        4
    guoqiao   2014-01-09 14:06:48 +08:00
    @fire9 太少了吗, 能否说详细点? 因为之前经常出现因为内存不足导致数据库 crash 后无法恢复的情况, 于是我就把这个值调小了. 我用 mysqltunner 检测了下, 貌似这项提示是 OK 的.
    cloud107202
        5
    cloud107202   2014-01-09 14:17:51 +08:00
    曾遇到过类似的情况,对mysql不是很懂,把当时的步骤总结一下,希望帮到楼主:
    环境:512M内存的vps,mysql 5.6,搭了wordpress
    问题:mysql能启动,wordpress一被访问,数据库就crash
    尝试1:将innodb_buffer_pool_size=128M 改为5M,wp可以访问了,只是坚持不了多久还会down
    尝试2:
    调整以下参数至:
    performance_schema_max_table_instances=600
    table_definition_cache=400
    table_open_cache=256
    这时mysql启动后内存就只占用40–60M内存了
    以下是5.6默认的设置,会占用至少400M的内存,可能是导致crash的原因
    performance_schema_max_table_instances 12500
    table_definition_cache 1400
    table_open_cache 2000
    尝试3:经过尝试2后,问题解决了。但一个月后随着文章的增多,数据库又crash掉,直接动态扩展了vps内存到1G 问题彻底解决

    综上感觉内存不足是最可能的主因
    timchou
        6
    timchou   2014-01-09 14:19:44 +08:00
    第一感觉是内存不够,但是感觉你bp设的也不大的。。

    贴下完整的mysql error log吧。top命令,按M,查看各个进程内存使用情况。
    guoqiao
        7
    guoqiao   2014-01-09 14:55:59 +08:00
    @timchou
    =========================
    完整的 error.log:
    https://dl.dropboxusercontent.com/u/55214241/error.log
    说明: 这台 linode 上还给一个同学跑了个 discuzz 论坛, 日志中可以看到ultrax相关的表 crash 了:
    140109 8:24:35 [ERROR] /usr/sbin/mysqld: Table './ultrax/pre_forum_promotion' is marked as crashed and should be repaired

    开始我怀疑是这个discuzz 导致的. 但我把它关掉观察了一段时间, 数据库还是会不断崩溃, 所以和这个关系不大. 而且,在有这个 discuzz 之前,我也会经常收到出错邮件.
    =========================
    完整的 my.cnf:
    https://dl.dropboxusercontent.com/u/55214241/my.cnf
    =========================
    htop 的内存占用截图:
    https://dl.dropboxusercontent.com/u/55214241/htop.png
    fire9
        8
    fire9   2014-01-09 15:48:56 +08:00
    @guoqiao 如果你的表用的innodb存储引擎,你需要调整innodb buffer size的大小。你贴一下你的my.cnf文件内容吧。这个肯定只配置问题。
    mahone3297
        9
    mahone3297   2014-01-09 16:05:52 +08:00
    @guoqiao htop里,mysqld进程那么多?
    另外,我个人觉得,vps 1g,可能 innodb_buffer_pool_size 值可以调的小点。比如32M?
    虽然你有1g内仓,然后innodb_buffer_pool_size =128M,但是其他也有内仓分配,比如
    key_buffer = 64M
    query_cache_size = 64M
    tmp_table_size = 250M
    总共加起来就不少了。另外你的服务器也不止mysql,还有别的服务要占内存。
    以上只是我个人推测。。。
    VYSE
        10
    VYSE   2014-01-09 16:43:38 +08:00
    https://tools.percona.com/wizard
    看其他进程是不是突然占满剩余MEMORY,导致mysqld overcommit
    Maslino
        11
    Maslino   2014-01-09 17:08:22 +08:00
    @guoqiao error log里面不是报错嘛,一些表坏了,问题大概在这里
    aisensiy
        12
    aisensiy   2014-01-09 18:34:43 +08:00
    明显是内存不够呀...你 top 下看看还有什么东西特别占内存吧
    guoqiao
        13
    guoqiao   2014-01-09 19:03:56 +08:00
    guoqiao
        14
    guoqiao   2014-01-09 19:14:14 +08:00
    @mahone3297
    1. htop 中, mysql 的进程有20多个, 我也不太明白这个是由哪个参数决定的
    2. innodb_buffer_pool_size 之所有设置成这么多, 是因为我用mysqltuner这个工具测试了一下我的配置, 结果如下:
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 27m 23s (19K q [11.997 qps], 941 conn, TX: 48M, RX: 4M)
    [--] Reads / Writes: 96% / 4%
    [--] Total buffers: 344.0M global + 2.7M per thread (20 max threads)
    [OK] Maximum possible memory usage: 397.8M (40% of installed RAM)
    [OK] Slow queries: 0% (11/19K)
    [OK] Highest usage of available connections: 35% (7/20)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/1.5M
    [OK] Key buffer hit rate: 100.0% (456K cached / 7 reads)
    [OK] Query cache efficiency: 40.7% (6K cached / 15K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [OK] Temporary tables created on disk: 23% (191 on disk / 827 total)
    [OK] Thread cache hit rate: 99% (7 created / 941 connections)
    [OK] Table cache hit rate: 25% (450 open / 1K opened)
    [OK] Open file limit used: 62% (641/1K)
    [OK] Table locks acquired immediately: 100% (10K immediate / 10K locks)
    [OK] InnoDB data size / buffer pool: 146.0M/180.0M

    这里面最后一条,提到我的数据库大小是146M, 而我的 buffer size 要比这个大才行. 因此我才设置为180M. 如果设置成小于146M 的值, 数据库 crash 之后, 就会分配内存失败, 起不来.
    3. 其他的几个参数, 我也是根据 mysqltuner 的建议修改的.
    guoqiao
        15
    guoqiao   2014-01-09 19:32:37 +08:00
    @VYSE 从 htop 的显示看, 整个系统的内存确实几乎耗尽. 但其中, mysql 占了12%左右, django 占了4%左右, 其他都很少. 不知道问题出在哪里.
    guoqiao
        16
    guoqiao   2014-01-09 19:34:48 +08:00
    @Maslino 我前面的回复里说了, 这个 linode 上还有个 discuzz, 我也注意到它的表崩溃了. 我有几天关掉了这个 discuzz, 但是问题还在. 可见不是它的问题. 而且,就算修复了崩溃的表, 过不多久, 这些表又崩溃了.所以我懒得管它了.
    ayanamist
        17
    ayanamist   2014-01-09 20:01:16 +08:00
    @guoqiao 他是很多mysqld,每个10.9%啊,连swap都快慢了,这是爆内存的节奏啊
    guoqiao
        18
    guoqiao   2014-01-09 20:07:53 +08:00
    @ayanamist htop虽然显示20多进程,但是内存的占用并不是每个都那么多, 是总共12%左右.
    mahone3297
        19
    mahone3297   2014-01-09 20:23:14 +08:00
    @guoqiao htop虽然显示20多进程,但是内存的占用并不是每个都那么多, 是总共12%左右.
    1. 如何看出是总共12%?
    我觉得是单个10。9%,然后20个就是200%吧。。。
    free -m 结果看下?

    2. 为什么我看到我服务器上只有一个mysqld进程,你为什么会那么多?大家为什么对这个没疑问?大家都是那么多进程的?

    3. mysql配置参数,看你的意思,都是根据 mysqltuner 这个工具来配置的。这个工具有考虑到你其他情况吗(比如你开了discuz)?他的这个测试的前提条件是不是这个服务器只跑mysql呢(事实上你还跑了其他,比如web server)?

    4. 这里面最后一条,提到我的数据库大小是146M, 而我的 buffer size 要比这个大才行. 因此我才设置为180M. 如果设置成小于146M 的值, 数据库 crash 之后, 就会分配内存失败, 起不来.
    按你的说法,buffer_pool必须比数据库大。。。那假如我10g的数据库,我buffer_pool要开到10g吗?
    ps:我在我这边线上环境,buffer_pool只开了128m,我的db size肯定大雨128m(事实上是10多g)。当然,我没说我的buffer_pool配置的正确,因为我看网上都说要配置的比较大。但至少我这边运行的还好,也没出什么问题,至少没crash。当然,我这边也不是什么高并发的查询。

    以上仅供参考。。。
    pubby
        20
    pubby   2014-01-09 20:40:49 +08:00
    swap 都这样了,这db性能还能忍受的啊

    同一机房再买个vps单独跑mysql算了
    Livid
        21
    Livid   V2EX Moderator   2014-01-09 20:45:32 +08:00 via iPad
    你的表是 InnoDB 还是 MyISAM 的?InnoDB 的话是不需要开 key_buffer 的。
    liushuaikobe
        22
    liushuaikobe   2014-01-09 21:00:55 +08:00
    我突然想到知乎也时不时的500。刷新一下就好了。。
    VYSE
        23
    VYSE   2014-01-09 22:07:40 +08:00
    @guoqiao 那个htop里mysqld可能共享memory,不是累加的。按照你说的来看,mysqld和django并没有占多少内存。
    你能top -> shift-f ->n 列出占用大户么?
    Maslino
        24
    Maslino   2014-01-09 22:18:52 +08:00
    @guoqiao 多出来的不是进程,是mysql处理请求的线程。既然表修复好问题仍然出现,你看下磁盘有没有问题。
    guoqiao
        25
    guoqiao   2014-01-10 05:44:47 +08:00
    @Livid 用的InnoDB, 默认的. 好的,我把 key_buffer设置去掉
    guoqiao
        26
    guoqiao   2014-01-10 05:55:41 +08:00
    1. 我截图里当时是10.9%, 总共也是这么多. 内存怎么可能用到200%呢...
    2. 我用的是 htop 命令查看的, 它是更高级的 top, 会显示子进程. 你如果用 top 看,只会看到主进程.
    3. mysqltuner 可以根据你的机器配置以及过去24小时的运行情况, 给你的 mysql "体检".
    4. 如果访问量不高, 不会有问题吧. 但是设置成比数据库大, 是确保安全的做法.
    guoqiao
        27
    guoqiao   2014-01-10 05:56:03 +08:00
    @mahone3297 上一条是回复给你的.
    guoqiao
        28
    guoqiao   2014-01-10 05:57:47 +08:00
    @pubby 关键是其实访问量并不大,一个小站而已. 肯定是有问题, 没必要盲目堆硬件.
    guoqiao
        29
    guoqiao   2014-01-10 05:59:08 +08:00
    @Maslino 空间是够的, 用了50%多点. 还要检查什么? 损坏吗?
    guoqiao
        30
    guoqiao   2014-01-10 06:01:07 +08:00
    arbeitandy
        31
    arbeitandy   2014-01-10 07:40:37 +08:00 via Android
    * 看 errorlog,不是mysql自己的問題。似系統無法分配足夠內存,oom機制殺掉了mysql進程,可以檢查 系統日誌 syslog
    參考 http://dba.stackexchange.com/questions/25077/mysql-innodb-crash-post-mortem
    在 crash 時也許有其它進程快速佔用了比計劃多得多的內存 (比如python, php都是潛在的內存大戶,如果還有文件io操作。。)
    * 順便建議mysqltuner 的測試要啟動一段時間後再進行
    Up for: 27m 23s 這時可能cache沒warmup,hit rate會偏低。不過僅僅看數字這份my.cnf沒受這個影響。
    * http://dba.stackexchange.com/questions/25165/intermittent-mysql-crashes-with-error-fatal-error-cannot-allocate-memory-for-t
    這裡還有個比較長比較全面討論低配機器的mysql配置檢查。
    * 如果短期訪問量不會增加,又沒有慢速查詢,my.cnf裡 max_connections可以再降低點。
    guoqiao
        32
    guoqiao   2014-01-10 08:35:48 +08:00
    @arbeitandy 哇,都是干货啊, 多谢:)
    VYSE
        33
    VYSE   2014-01-10 11:09:47 +08:00
    @guoqiao OK, MEM占有也就前几个进程累加起来的,剩余的多数都被内核申请为磁盘CACHE了,所以top上面有个used mem已经满了的假象。
    可能需要观察到CRASH那一刻,各个进程的情况,个人觉得你可以限制php和python的实例创建上限。
    guoqiao
        34
    guoqiao   2014-01-10 16:36:46 +08:00
    @arbeitandy @VYSE
    多谢二位, 你们的分析引导我找到了问题所在.
    我的网站上有几个实时统计的页面, 需要联合查询大量数据, 每次查询都好慢.
    由于太卡, 我用的不多, 也一直没太在意.
    今天早上特地看了下,每次点击统计页面, mysql 某个进程的 CPU 就会彪到100%多, 整个服务器都很卡顿. 应该就是它导致了 mysql 崩溃.
    我去掉了统计页面, 今天观察了一天, 目前为止没有再收到任何 mysql 错误.
    现在想来, 这几个页面因为很卡,所以我自己很少点, 都被我遗忘了. 我想点过的用户也不想点第二次. 但是每次有新用户进来, 他可能在无意识的情况下随手点了它, 这就导致 mysql 崩溃. 根据我目前网站上的用户数, 我每天收到的出错邮件次数, 也差不多符合这个假设.
    当然,目前观察的时间还太短,不能下结论. 但是也应该差不多.
    这个问题一度让我怀疑 mysql 的性能, 现在看来, 问题出在我自己的不当查询上面.
    惭愧.
    fire9
        35
    fire9   2014-01-11 03:01:15 +08:00
    @guoqiao,这个配置需要根据你目前机器的资源和产品的应用来做调整,我简单的看了一下,这个配置文件不是很好,default-storage-engine = innodb
    事例几个需要优化
    innodb_buffer_pool_size = 180M #通常为物理内存的50%,或者70-80%
    tmp_table_size = 250M
    max_heap_table_size = 20M #这两个选项的值最好是相等的。

    只有cpu太高,那就要优化SQL了,另外估计你代码的严谨度不够,比如一些地方需要做合并请求,总不能点一次查询一次。

    如果需要额外的付费的服务,请联系我哈!
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   4207 人在线   最高记录 5168   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 26ms · UTC 06:02 · PVG 14:02 · LAX 22:02 · JFK 01:02
    ♥ Do have faith in what you're doing.