VPS侦探论坛

 找回密码
 注册
查看: 6121|回复: 8

军哥,按你的方法去做了,数据库还是老死掉

[复制链接]
发表于 2014-3-28 16:05:17 | 显示全部楼层 |阅读模式

我前面发的求助贴子https://bbs.vpser.net/thread-10601-1-1.html
按您说的my-innodb-heavy-4G.cnf 替换/etc/my.cnf ,替换后发现死的频率更高一些,这几天也一直在找问题,还是没解决,我先说一下我的数据库情况
有一个数据库里面有300多个表,每个表可能有100M以上的数据,服务器做的是站群,所以网站是共用的一个数据库。
我的数据库在死掉后,用restart无法重启Mysql,非得重启服务器后才可以。
奇怪的是用top命令看的时候,mysql占用的CPU和内存好像并不高,我把top显示结果捉图了在贴子的附件里

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?注册

x
美国VPS推荐: 遨游主机LinodeLOCVPS主机云搬瓦工80VPSVultr美国VPS主机中国VPS推荐: 阿里云腾讯云。LNMP付费服务(代装/问题排查)QQ 503228080
发表于 2014-3-28 20:46:28 | 显示全部楼层


你top时占用的高峰已经过去了,看负载就可以看出来
8核的话11的负载理论上在正常范围里

mysql配置上已经调大可能就得优化程序和数据库了
Linux下Nginx+MySQL+PHP自动安装工具:https://lnmp.org
 楼主| 发表于 2014-3-29 14:33:59 | 显示全部楼层

我写了一个shell每三分钟检测一次,如果出现数据库死掉的情况就执行lnmp重启或者mysql重启,但是这两种重启都执行不成功,都会卡在Shutting down Mysql.......这里不动了,只能重启服务器才恢复正常,军哥有没有什么好的办法?
美国VPS推荐: 遨游主机LinodeLOCVPS主机云搬瓦工80VPSVultr美国VPS主机中国VPS推荐: 阿里云腾讯云。LNMP付费服务(代装/问题排查)QQ 503228080
发表于 2014-3-29 20:23:55 | 显示全部楼层

回复 3# 的帖子




看看mysql日志什么信息
Linux下Nginx+MySQL+PHP自动安装工具:https://lnmp.org
 楼主| 发表于 2014-3-29 23:51:08 | 显示全部楼层

之前我看到mysql日志占用太多空间了,我就把日志功能给关闭了

军哥运维代购:http://shop63846532.taobao.com/

发表于 2014-3-30 09:08:18 | 显示全部楼层

关闭的是二进制日志,不是日志,日志是记录错误信息的
 楼主| 发表于 2014-3-30 10:52:35 | 显示全部楼层

原帖由 licess 于 2014-3-30 09:08 发表
关闭的是二进制日志,不是日志,日志是记录错误信息的


我的数据库表是MyISAM的,四百个小站共用103fanci/mydecms_item这个表。


  1. 140330 10:38:45 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
  2. 140330 10:38:46 InnoDB: The InnoDB memory heap is disabled
  3. 140330 10:38:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
  4. 140330 10:38:46 InnoDB: Compressed tables use zlib 1.2.3
  5. 140330 10:38:46 InnoDB: Initializing buffer pool, size = 16.0M
  6. 140330 10:38:46 InnoDB: Completed initialization of buffer pool
  7. 140330 10:38:46 InnoDB: highest supported file format is Barracuda.
  8. InnoDB: The log sequence number in ibdata files does not match
  9. InnoDB: the log sequence number in the ib_logfiles!
  10. 140330 10:38:46  InnoDB: Database was not shut down normally!
  11. InnoDB: Starting crash recovery.
  12. InnoDB: Reading tablespace information from the .ibd files...
  13. InnoDB: Restoring possible half-written data pages from the doublewrite
  14. InnoDB: buffer...
  15. InnoDB: 13 transaction(s) which must be rolled back or cleaned up
  16. InnoDB: in total 13 row operations to undo
  17. InnoDB: Trx id counter is E84F00
  18. InnoDB: Last MySQL binlog file position 0 494744242, file name ./mysql-bin.000068
  19. InnoDB: Starting in background the rollback of uncommitted transactions
  20. 140330 10:39:16  InnoDB: Rolling back trx with id E845A9, 1 rows to undo
  21. 140330 10:39:16  InnoDB: Waiting for the background threads to start
  22. InnoDB: Rolling back of trx id E845A9 completed
  23. 140330 10:39:16  InnoDB: Rolling back trx with id E84468, 1 rows to undo
  24. InnoDB: Rolling back of trx id E84468 completed
  25. 140330 10:39:16  InnoDB: Rolling back trx with id E843C6, 1 rows to undo
  26. InnoDB: Rolling back of trx id E843C6 completed
  27. 140330 10:39:16  InnoDB: Rolling back trx with id E843AB, 1 rows to undo
  28. InnoDB: Rolling back of trx id E843AB completed
  29. 140330 10:39:16  InnoDB: Rolling back trx with id E84328, 1 rows to undo
  30. InnoDB: Rolling back of trx id E84328 completed
  31. 140330 10:39:16  InnoDB: Rolling back trx with id E8420A, 1 rows to undo
  32. InnoDB: Rolling back of trx id E8420A completed
  33. 140330 10:39:16  InnoDB: Rolling back trx with id E840F8, 1 rows to undo
  34. InnoDB: Rolling back of trx id E840F8 completed
  35. 140330 10:39:16  InnoDB: Rolling back trx with id E840EB, 1 rows to undo
  36. InnoDB: Rolling back of trx id E840EB completed
  37. 140330 10:39:16  InnoDB: Rolling back trx with id E84043, 1 rows to undo
  38. InnoDB: Rolling back of trx id E84043 completed
  39. 140330 10:39:16  InnoDB: Rolling back trx with id E84015, 1 rows to undo
  40. InnoDB: Rolling back of trx id E84015 completed
  41. 140330 10:39:17  InnoDB: Rolling back trx with id E83F8A, 1 rows to undo
  42. 140330 10:39:17 InnoDB: 1.1.8 started; log sequence number 83787785563
  43. 140330 10:39:17 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
  44. 140330 10:39:17 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
  45. 140330 10:39:17 [Note] Server socket created on IP: '0.0.0.0'.
  46. InnoDB: Rolling back of trx id E83F8A completed
  47. 140330 10:39:17  InnoDB: Rolling back trx with id E83F61, 1 rows to undo
  48. 140330 10:39:17 [Note] Event Scheduler: Loaded 0 events
  49. 140330 10:39:17 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
  50. Version: '5.5.28'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
  51. InnoDB: Rolling back of trx id E83F61 completed
  52. 140330 10:39:17  InnoDB: Rolling back trx with id E83F30, 1 rows to undo
  53. InnoDB: Rolling back of trx id E83F30 completed
  54. 140330 10:39:17  InnoDB: Rollback of non-prepared transactions completed
  55. 140330 10:39:20 [ERROR] /usr/local/mysql/bin/mysqld: Table './103fanci/mydecms_item' is marked as crashed and last (automatic?) repair failed
  56. 140330 10:39:20 [ERROR] /usr/local/mysql/bin/mysqld: Table './103fanci/mydecms_item' is marked as crashed and last (automatic?) repair failed
  57. 140330 10:39:20 [ERROR] /usr/local/mysql/bin/mysqld: Table './103fanci/mydecms_item' is marked as crashed and last (automatic?) repair failed
  58. 140330 10:39:21 [ERROR] /usr/local/mysql/bin/mysqld: Table './103fanci/mydecms_item' is marked as crashed and last (automatic?) repair failed
  59. 140330 10:39:22 [ERROR] /usr/local/mysql/bin/mysqld: Table './103fanci/mydecms_item' is marked as crashed and last (automatic?) repair failed
  60. 140330 10:39:23 [ERROR] /usr/local/mysql/bin/mysqld: Table './103fanci/mydecms_item' is marked as crashed and last (automatic?) repair failed
  61. 140330 10:39:23 [ERROR] /usr/local/mysql/bin/mysqld: Table './103fanci/mydecms_item' is marked as crashed and last (automatic?) repair failed
  62. 140330 10:39:26 [ERROR] /usr/local/mysql/bin/mysqld: Table './103fanci/mydecms_item' is marked as crashed and last (automatic?) repair failed
  63. 140330 10:39:27 [ERROR] /usr/local/mysql/bin/mysqld: Table './103fanci/mydecms_item' is marked as crashed and last (automatic?) repair failed
复制代码
美国VPS推荐: 遨游主机LinodeLOCVPS主机云搬瓦工80VPSVultr美国VPS主机中国VPS推荐: 阿里云腾讯云。LNMP付费服务(代装/问题排查)QQ 503228080
发表于 2014-3-30 12:12:33 | 显示全部楼层

停掉mysql
/usr/local/mysql/bin/myisamchk -c -r /usr/local/var/103fanci/mydecms_item.MYI
修复看看
Linux下Nginx+MySQL+PHP自动安装工具:https://lnmp.org
 楼主| 发表于 2014-3-31 09:07:41 | 显示全部楼层


终于也把这个困了我很久的问题也解决了
按军哥说的把mydecms_item.MYI表修复后,再把my.conf这个文件里的innodb_buffer_pool_size = 16M改成innodb_buffer_pool_size = 512M
果断数据库一天都没挂掉一次了,之前一天最少挂掉二三十次,为这事头都大了

感谢军哥
美国VPS推荐: 遨游主机LinodeLOCVPS主机云搬瓦工80VPSVultr美国VPS主机中国VPS推荐: 阿里云腾讯云。LNMP付费服务(代装/问题排查)QQ 503228080
您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|VPS侦探 ( 鲁ICP备16040043号-1 )

GMT+8, 2024-11-14 22:18 , Processed in 0.029100 second(s), 17 queries .

Powered by Discuz! X3.4

© 2001-2023 Discuz! Team.

快速回复 返回顶部 返回列表