MySQL 如何避免使用 Keycache 进行修复？

How To Avoid Repair With Keycache?

提问by dvancouver

I have had some experience with optimizing the my.cnf file but my database has around 4 million records (MyISAM). I am trying to restore from a mysqldump but every time I do I eventually get the dreaded "Repair With Keycache", that may take days. Is there any way to get past this and let it roll as "Repair By Sorting"?

我在优化 my.cnf 文件方面有一些经验，但我的数据库有大约 400 万条记录 (MyISAM)。我正在尝试从 mysqldump 恢复，但每次我最终都会得到可怕的“使用密钥缓存修复”，这可能需要几天时间。有什么办法可以解决这个问题，让它滚动为“按排序修复”？

I have 2GB RAM, Dual Cores, lots of extra hard-drive space.

我有 2GB 内存、双核和大量额外的硬盘空间。

Snip out of my.cnf:

从 my.cnf 中剪下：

set-variable = max_connections=650
set-variable = key_buffer=256M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer_size=2M
set-variable = read_buffer_size=2M
set-variable = query_cache_size=32M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
set-variable = thread_concurrency=8

回答by MarkR

"Repair by sorting" uses the filesort routine, which in turn creates several temporary files (usually) in your tmpdir.

“排序修复”使用 filesort 例程，它反过来在您的 tmpdir 中创建几个临时文件（通常）。

If your tmpdir does not have enough space for them, it will revert to "Repair by keycache". This is extremely bad as it's much slower AND creates less optimal indexes.

如果您的 tmpdir 没有足够的空间供它们使用，它将恢复为“通过密钥缓存修复”。这是非常糟糕的，因为它慢得多并且创建的索引不太理想。

There are some other conditions but I haven't identified them.

还有一些其他条件，但我还没有确定它们。

Working out the size of tmpdir you need for filesort() is nontrivial; the format data are stored in the filesort buffer is not the same as MYD files, it typically uses a lot more space.

计算出 filesort() 所需的 tmpdir 大小非常重要；存储在文件排序缓冲区中的格式数据与 MYD 文件不同，它通常使用更多空间。

So if your tmpdir points at a small /tmp (or tmpfs), you might want to change it to a larger /var/tmp - if that exists.

因此，如果您的 tmpdir 指向一个小的 /tmp（或 tmpfs），您可能希望将其更改为更大的 /var/tmp - 如果存在的话。

回答by Marc Gear

MySQL will use repair by keycache for MyISAM tables whenever the maximum possible size of the tables indexes are greater than the value for the variable myisam_max_sort_file_size.

每当表索引的最大可能大小大于变量 myisam_max_sort_file_size 的值时，MySQL 将对 MyISAM 表使用 keycache 修复。

You can calculate the maximum size of the index by adding up the byte size values for all keys in all the indexes and multiplying that by the number of rows in your table.

您可以通过将所有索引中所有键的字节大小值相加并将其乘以表中的行数来计算索引的最大大小。

Increase the myisam_max_sort_file_size and your index will be rebuilt using sorting on disk, rather than with the slow keycache method.

增加 myisam_max_sort_file_size，您的索引将使用磁盘排序重建，而不是使用慢速密钥缓存方法。

回答by John Doe

I accidentially ran a repair table quick on a new database which I had not set up to be fast reg. myisam_max_sort_file_size which was way too small compared to the .MID file (which is 88279393280 byes large, about 88GB). The data file is 85GB. The table is 1.2 billion records, consisting of an ID, two dates, a tinytext ,a few bigints and a double. My server (2GB virtual linux running in a box under windows7) only have one core of the 4 on the windows server, but it is running 3+ GHZ. I was fearing this "repair by keycache" event would take forever - given horror stories with far smaller tables.

我不小心在一个我没有设置为快速注册的新数据库上快速运行了一个修复表。myisam_max_sort_file_size 与 .MID 文件（88279393280 字节大，约 88GB）相比太小了。数据文件为 85GB。该表有 12 亿条记录，由一个 ID、两个日期、一个 tinytext、几个 bigint 和一个 double 组成。我的服务器（在 windows7 下的一个盒子中运行的 2GB 虚拟 linux）在 windows 服务器上只有 4 个核心中的一个，但它运行的是 3+ GHZ。我担心这种“通过密钥缓存修复”事件会永远持续下去 - 考虑到表小得多的恐怖故事。

Fortunately it "only" took 1 day , 10 hours and 20.72 seconds to complete the repair table quick operation.

好在它“只”用了1天10小时20.72秒就完成了修复表的快速操作。

What I miss the most is some way of knowing how far into the operation that mysql is, and how soon it might be finished. This is still unknown to me.

我最想念的是某种方式来了解 mysql 的操作进行了多远，以及它可能多久完成。这对我来说仍然是未知的。

I have now changed my my.ini file and double checked with df that I have ample disk space for those large temporary files.

我现在已经更改了 my.ini 文件并使用 df 仔细检查了我有足够的磁盘空间来存放那些大型临时文件。

Anyway.. my main point, which might be very useful knowledge to the next guy who falls into this trap.. is in fact... don't panic! it might be slow, but it is possible on rather sub-par hardware to get 1+ billion records sorted out within a day or two. Got three indexes, one on a date field, one on a bigint field, and one primary on the ID field.

无论如何..我的主要观点，这对下一个落入这个陷阱的人来说可能是非常有用的知识..实际上......不要惊慌！它可能会很慢，但是在相当低的硬件上可能在一两天内整理出 1+ 十亿条记录。得到了三个索引，一个在日期字段上，一个在 bigint 字段上，一个在 ID 字段上的主索引。

I would've posted this as a comment to one of the solutions, but I can't seem to figure how to do this, with the user interface here, so I'll drop it off as a solution. Don't upvote me, it's just a note that I would have loved to have here, I was almost going to kill my "sort by keycache" thread as I thought it could take a week or more. 2 days per billion records is manageable..

我会将此作为对其中一个解决方案的评论发布，但我似乎无法弄清楚如何使用此处的用户界面来执行此操作，因此我将其作为解决方案删除。不要给我投票，这只是我很想在这里拥有的一张便条，我几乎要杀死我的“按密钥缓存排序”线程，因为我认为这可能需要一周或更长时间。每十亿条记录 2 天是可以管理的。

Edit: And now, a repair table on the same database, but with a large enough mysiam_max_sort_file_size setting took 10 hours, 20 minutes using repair by sorting. The most diskspace used was about 250GB, but i had set myisam_max_sort_file_size a lot higher, reflecting how much disk space is actually free on the server.

编辑：现在，同一数据库上的修复表，但 mysiam_max_sort_file_size 设置足够大，使用通过排序修复需要 10 小时 20 分钟。使用的最多磁盘空间约为 250GB，但我已将 myisam_max_sort_file_size 设置得更高，这反映了服务器上实际可用的磁盘空间有多少。

Tracking progress is hard. Disk space went up and down while the individual indexes were built, but there were hour long pauses where no changes were made reg. disk space usage (as reported by df).

跟踪进度很困难。构建单个索引时磁盘空间会上下波动，但会出现长达一小时的停顿，没有进行任何更改。磁盘空间使用情况（由 df 报告）。

回答by dvancouver

Thanks Mark, Yes that is exactly what I ended up trying and am seeing from the logs that that's the reason it switched to "Repair with keycache", was an out of space error.

谢谢马克，是的，这正是我最终尝试的，并且从日志中看到，这就是它切换到“使用密钥缓存修复”的原因，是空间不足错误。

This is what I did to get my solution in place as I will not go through the fact that it was pointing to /tmp/mysqltmp/, which only had a max of 2MB.

这就是我为使我的解决方案到位所做的工作，因为我不会经历它指向的事实，它/tmp/mysqltmp/最多只有 2MB。

So I did this:

所以我这样做了：

mkdir /home/mysqltmp

chown mysql:mysql /home/mysqltmp

changed my tmp dir in my.conf totmpdir=/home/mysqltmp/

将 my.conf 中的 tmp 目录更改为tmpdir=/home/mysqltmp/

Now if I use df -h /home/mysqltmp, what I see is that dir has 285 GB available, so that really was a nice sight to see, had plenty of free space, plus I could see mysql was wanting 20GB easily. So what was taking me 12 hours before now is complete in 20 minutes, that is over 3 million records insert to index.

现在，如果我使用df -h /home/mysqltmp，我看到 dir 有 285 GB 可用空间，所以这确实是一个不错的景象，有足够的可用空间，而且我可以看到 mysql 很容易想要 20GB。所以之前我花了 12 小时的时间现在在 20 分钟内完成了，即超过 300 万条记录插入到索引中。

回答by Jealie

None of the solutions here worked for me: no matter how much I increased the myisam_sort_buffer_sizevariable or where I made the tmpdirvariable point to, the table always got repaired with keycache.

这里没有一个解决方案对我有用：无论我增加了多少myisam_sort_buffer_size变量或我将tmpdir变量指向哪里，表总是用密钥缓存修复。

What worked was to use the commandline utility myisamchk:

有效的是使用命令行实用程序myisamchk：

myisamchk --sort-recover --sort_buffer_size=14G /path/to/table

where:

在哪里：

• /path/to/tableis the path of the database file without its extension (so, without the .MYIat the end). It is by default located in directory /var/lib/mysql/your_database.

• Change the buffer size from 14Gto whatever free space available you have.

• /path/to/table是不带扩展名的数据库文件的路径（因此，.MYI末尾不带）。默认情况下，它位于目录中/var/lib/mysql/your_database。

• 将缓冲区大小更改为14G您拥有的任何可用空间。

As an added bonus, it also displays the on-going progress as it churns the data.

作为一个额外的好处，它还会在搅动数据时显示正在进行的进度。

回答by twonkeys

According to the MySQL Reference Manual, disk space must be available "in the file system containing the directory where the original index file is located" (http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_myisam_max_sort_file_size) -- this applies to (at least) v5.0 and above. This contradicts some of the above answers, that claim that increasing the disk space for the tmp directory would help.

根据 MySQL 参考手册，磁盘空间必须“在包含原始索引文件所在目录的文件系统中”（http://dev.mysql.com/doc/refman/5.5/en/server-system -variables.html#sysvar_myisam_max_sort_file_size) -- 这适用于（至少）v5.0 及更高版本。这与上面的一些答案相矛盾，声称增加 tmp 目录的磁盘空间会有所帮助。

I can confirm the behaviour described in the Reference Manual: temporary disk space is used where the table's data (*.MYD) & index files (*.MYI) are stored, but not in tmpdir.

我可以确认参考手册中描述的行为：临时磁盘空间用于存储表的数据 ( *.MYD) 和索引文件 ( *.MYI)，但不在tmpdir.