问题描述
我在优化my.cnf文件方面有一些经验，但是我的数据库有大约4百万条记录(MyISAM).我试图从mysqldump中还原，但是每次我最终都得到可怕的使用Keycache修复"时，可能要花几天的时间.有什么办法可以克服这个问题，让它滚动为排序修复"?
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"?
我有2GB RAM，双核和大量额外的硬盘空间.
I have 2GB RAM, Dual Cores, lots of extra hard-drive space.
从my.cnf中剪断:
Snip out of 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
推荐答案
排序修复"使用文件排序例程，该例程又在您的tmpdir中创建了几个临时文件(通常).
"Repair by sorting" uses the filesort routine, which in turn creates several temporary files (usually) in your tmpdir.
如果您的tmpdir没有足够的空间容纳它们，它将恢复为通过keycache进行修复".这非常糟糕，因为它要慢得多，并且创建的最佳索引也较少.
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.
还有其他一些条件，但我还没有确定.
There are some other conditions but I haven't identified them.
计算出filesort()所需的tmpdir大小是不平凡的；存储在文件排序缓冲区中的格式数据与MYD文件不同，通常会占用更多空间.
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.
因此，如果您的tmpdir指向较小的/tmp(或tmpfs)，则可能需要将其更改为较大的/var/tmp-如果存在的话.
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.
这篇关于如何避免使用Keycache进行维修?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！