防止mysql MyISAM表崩溃
我一直遇到数据库反复崩溃表的问题。幸运的是,通过修复崩溃的表来修复很容易,但是必须观察表崩溃(或让客户告诉我)然后修复它,这并不是一个好的做法。崩溃往往发生在对客户端可以通过 CMS 访问的数据库进行更改之后。
我注意到最近一次表崩溃时它引用了一个数字 - 比如找到 89 中的 57;然后我在主键的基数中注意到了这一点。将 2 和 2 放在一起,我在 google 上搜索了基数,发现优化表在某种程度上相关,因此我认为通过定期优化表(例如更新后),将有助于防止崩溃。这是真的吗?还是我得到了 73 分而不是 4 分?
当客户端通过 PHP 进行更改时,我可以将 MYSQL 函数发送到数据库,因此从这个角度来看,帮助会很大。
对于表崩溃的任何其他帮助将不胜感激。
I've been having problems with a database repeatedly having crashed tables. Luckily it's easy enough to fix by repairing the crashed table, but it's hardly good practice to have to watch for the table to crash (or have the client tell me) and then fix it. The crashes tend to occur after a change has been made to the database which the client has access to via a CMS.
I noticed the most recent time the table crashed that it referenced a number - something like found 57 of 89; which I then noticed in the Cardinality for the primary key. Putting 2 and 2 together I googled cardinality and found that Optimising the table was in some way related and thus I thought that by optimising the table regularly, like after an update, it would help prevent crashes. Is this true or have I managed to get 73 rather than 4?
I can send MYSQL functions to the database when the client makes changes via PHP, so help from that perspective would be great.
Any other help with table crashes would be greatly appreciated.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
实际上,优化表将防止表崩溃
,你不应该过于频繁地运行优化表(表被锁定),尽管旨在压缩空闲数据,
请尝试
mysqlcheck -C
或<代码>mysqlcheck -c
is NOT true optimize table will prevent the table for crashing
in fact, U SHOULD NOT run optimize table too often (table get locked) although is designed to compact free data
try
mysqlcheck -C
or
mysqlcheck -c
MyISAM 表通常因以下原因而损坏:
因此,为了避免损坏,您能做的最好的事情就是拥有一个好的 UPS,运行最稳定版本的操作系统和 MySQL,确保您的硬件正常运行,确保您有足够的资源RAM 量(为了避免 OOM 杀死,并避免一般情况下诱惑魔鬼,错误经常在内存不足的情况下遇到),并且是一个好孩子 - 不要用信号 9 杀死 mysqld,并且在重新启动时确保在启动新实例之前,旧的 mysqld 实例已关闭。
您可能还想采取措施处理腐败问题。经常备份数据,并保持表相当小,以避免冗长的恢复时间。
使用 InnoDB 是当今流行的另一种选择,因为它更符合学校教授的数据库理论,但它确实有自己的问题,并且如果您尝试迁移,可能会引入新问题 - 数据膨胀、性能降低、死锁、数据损坏(InnoDB 风格),更复杂 - 更有可能遇到错误并且更难排除故障,等等。
MyISAM table usually gets corrupted for the following reasons:
So the best you can do to avoid corruption is to have a good UPS, run most stable versions of OS and MySQL, make sure your hardware is functioning properly, make sure you have adequate amount of RAM (to avoid OOM kills, and to avoid tempting the devil in general, bugs are often hit in out-of-memory conditions), and be a good boy - do not kill mysqld with signal 9, and when restarting make sure the old instance of mysqld has gone down before you start the new one.
You may also want to take measures to deal with the corruption. Back up your data frequently, and keep the tables reasonably small to avoid lengthy recovery times.
Using InnoDB is another option which is in vogue today as it aligns better with the database theory taught in schools but it does have problems of its own and may introduce new issues if you try to migrate - data bloat, slower performance, deadlocks, data corruption (InnoDB style), more complex - more likely to hit a bug and harder to troubleshoot, etc.