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

myde520

我前面发的求助贴子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显示结果捉图了在贴子的附件里

licess

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

mysql配置上已经调大可能就得优化程序和数据库了

myde520

我写了一个shell每三分钟检测一次，如果出现数据库死掉的情况就执行lnmp重启或者mysql重启，但是这两种重启都执行不成功，都会卡在Shutting down Mysql.......这里不动了，只能重启服务器才恢复正常，军哥有没有什么好的办法？

licess

看看mysql日志什么信息

myde520

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

licess

关闭的是二进制日志，不是日志，日志是记录错误信息的

myde520

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

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

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

复制代码

licess

停掉mysql
/usr/local/mysql/bin/myisamchk -c -r /usr/local/var/103fanci/mydecms_item.MYI
修复看看

myde520

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

感谢军哥