在非常大的表上使用MySQL ALTER TABLE-运行它是否安全? [英] MySQL ALTER TABLE on very large table - is it safe to run it?

问题描述

我有一个MySQL数据库，其中MyISAM表有400万行.我每周大约更新一次此表，其中包含约2000个新行.更新之后，我将像这样修改表:

I have a MySQL database with a MyISAM table with 4 million rows. I update this table about once a week with about 2000 new rows. After updating, I then alter the table like this:

ALTER TABLE x ORDER BY PK DESC

我按主键字段的降序对表进行排序.这并没有给我的开发机器(带有3GB内存的Windows)带来任何问题.我已经在生产型Linux服务器上成功尝试了三遍(具有512MB RAM-并在大约6分钟内每次都实现了排序表)，最后一次尝试后，我不得不在30分钟后停止查询并重新构建备份中的数据库.

I order the table by the primary key field in descending order. This has not given me any problems on my development machine (Windows with 3GB memory). Three times I have tried it successfully on the production Linux server (with 512MB RAM - and achieving the resulted sorted table in about 6 minutes each time), the last time I tried it I had to stop the query after about 30 minutes and rebuild the database from a backup.

512 MB服务器可以应付这么大表上的alter语句吗?我已经读到创建一个临时表来执行ALTER TABLE命令.

Can a 512MB server cope with that alter statement on such a large table? I have read that a temporary table is created to perform the ALTER TABLE command.

问题:可以安全地运行此alter命令吗?更改表格的预计时间是几点?

Question: Can this alter command be safely run? What should be the expected time for the alteration of the table?

推荐答案

正如我刚刚阅读的，ALTER TABLE ... ORDER BY ...查询对于提高某些情况下的性能很有用.我很惊讶PK指数对此无济于事.但是，从 MySQL文档看来， InnoDB 确实使用索引.但是，与MyISAM相比，InnoDB趋向于变慢.就是说，使用InnoDB，您不需要重新排序表，但是您将失去MyISAM的超快速度.仍然值得一试.

As I have just read, the ALTER TABLE ... ORDER BY ... query is useful to improve performance in certain scenarios. I am surprised that the PK Index does not help with this. But, from the MySQL docs, it seems that InnoDB does use the index. However InnoDB tends to be slower as MyISAM. That said, with InnoDB you wouldn't need to re-order the table but you would lose the blazing speed of MyISAM. It still may be worth a shot.

在解释问题的方式中，似乎有太多数据加载到内存中(也许甚至正在进行交换吗?).您可以通过监视内存使用情况轻松地进行检查.很难说，因为我不太了解MySQL.

The way you explain the problems, it seems that there is too much data loaded into memory (maybe there is even swapping going on?). You could easily check that with monitoring your memory usage. It's hard to say as I do not know MySQL all that well.

另一方面，我认为您的问题出在一个非常不同的地方:您正在使用只有512 Megs RAM的计算机作为数据库服务器，并且该表包含的行数超过4Mio ...该计算机上整个表上的大量内存操作.看来512Megs并不能满足要求.

On the other hand, I think your problem lies at a very different place: You are using a machine with only 512 Megs of RAM as Database server with a table containing more than 4Mio rows... And you are performing a very memory-heavy operation on the whole table on that machine. It seems that 512Megs will not nearly be enough for that.

我在这里看到的一个更基本的问题:您正在与生产环境完全不同的环境中进行开发(以及很有可能进行测试).您正在解释的问题是预料之中的.开发机器的内存是生产机器的六倍.我相信我可以肯定地说，处理器也要快得多.在这种情况下，建议您创建一个模拟生产站点的虚拟机.这样一来，您就可以轻松测试您的项目，而不会中断生产站点.

A much more fundamental issue I am seeing here: You are doing development (and quite likely testing as well) in an environment that is very different to the production environment. The kind of problem you are explaining is to be expected. Your development machine has six times as much memory as your production machine. I believe I can safely say, that the processor is much faster as well. In that case, I suggest you create a virtual machine mimicking your production site. That way you can easily test your project without disrupting the production site.

这篇关于在非常大的表上使用MySQL ALTER TABLE-运行它是否安全?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！