如何避免“与密钥缓存一起修复"?在MySQL中? [英] How can I avoid "repair with keycache" in MySQL?
问题描述
这真是令人发疯.我已经按照在互联网上找到的每条设置的说明进行操作了，我无法超越.
This is really maddening. I have followed every instruction for settings that I have found on the interwebs, and I can't get past this.
基本上，我有一个约有800万行的表.我需要像这样创建该表的备份:
Basically, I have a table with about 8 million rows. I need to create a backup of this table like so:
create table mytable_backup like mytable
这在我的生产服务器(这是通过EngineYard运行的Amazon EC2实例)上花费了几个小时.在我的MacBook Pro上只需几分钟.这是MySQL在后台执行的另一项令人烦恼的事情，您无法猜测它是如何做出做出如此愚蠢的决定的.
And that takes several hours on my production server, which is an Amazon EC2 instance running through EngineYard. It takes only minutes on my MacBook Pro. This is another one of those annoying things that MySQL does in the background, and you can't guess how it is making the decision to do something so stupidly slow.
顺便说一句，tmp目录中有超过330G的可用空间，所以这不是问题.
BTW, there is over 330G available in the tmp directory, so that is not the issue.
但这是"free -m"的结果:
But here is what "free -m" yields:
deploy@domU-12-31-39-02-35-31 ~ $ free -m
total used free shared buffers cached
Mem: 1740 1728 11 0 14 1354
-/+ buffers/cache: 359 1380
Swap: 895 2 893
我不知道该怎么读，但是free栏下的"11"看起来不太好.
I don't know how to read that, but the "11" under the free column doesn't look very good.
我正在跑步:
Server version: 5.0.51-log Gentoo Linux mysql-community-5.0.51
这是我的配置文件:
# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.3 2006/05/05 19:51:40 chtekk Exp $
# The following options will be passed to all MySQL clients
[client]
port = 3306
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
[mysqld_safe]
err-log = /db/mysql/log/mysql.err
# To allow table cache to be raised
open-file-limit = 4096
[mysqld]
max_connections = 300
innodb_file_per_table = 1
log-slow-queries = /db/mysql/log/slow_query.log
long_query_time = 2000000
ft_min_word_len = 3
max_heap_table_size = 64M
tmp_table_size = 64M
server-id = 1
log-bin = /db/mysql/master-bin
log-bin-index = /db/mysql/master-bin.index
# END master/slave configuration
character-set-server = utf8
default-character-set = utf8
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /db/mysql/log/mysqld.err
basedir = /usr
datadir = /db/mysql
key_buffer = 32M
max_allowed_packet = 32M
table_cache = 1024
thread_cache = 512
sort_buffer_size = 100M
net_buffer_length = 64K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 100M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
language = /usr/share/mysql/english
# security:
# using "localhost" in connects uses sockets by default
# skip-networking
# bind-address = 127.0.0.1
# point the following paths to different dedicated disks
tmpdir = /mnt/mysql/tmp
# log-update = /path-to-dedicated-directory/hostname
# you need the debug USE flag enabled to use the following directives,
# if needed, uncomment them, start the server and issue
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's happening in your server ;)
#log = /tmp/mysqld.sql
#gdb
#debug = d:t:i:o,/tmp/mysqld.trace
#one-thread
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 1275M
# this is the default, increase it if you have lots of tables
innodb_additional_mem_pool_size = 16M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-(
# and upstream wants things to be under /var/lib/mysql/, so that's the route
# we have to take for the moment
#innodb_data_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
#innodb_log_group_home_dir = /var/lib/mysql/
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:20M:autoextend
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 96M
# this is the default, increase it if you have very large transactions going on
innodb_log_buffer_size = 8M
# this is the default and won't hurt you
# you shouldn't need to tweak it
innodb_log_files_in_group = 2
# see the innodb config docs, the other options are not always safe
# This is not good for performance when used with bin_sync. Disabling.
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
query_cache_size = 16M
query_cache_type = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
# uncomment the next directive if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
ft_min_word_len = 3
[mysqlhotcopy]
interactive-timeout
推荐答案
您可以尝试
create table backup_table as (select * from production table) engine=myisam
这应该创建仅包含数据而不包含任何键的表.然后，您可以通过
This should create the table with only the data and none of the keys. You can then add the keys on by doing
alter table backup_table add index(column_name)
我已经成功完成了几次，通常比插入键时快2倍.
I've done this successfully several times, and it is usually a factor of 2 times faster than inserting with the keys in place.
这篇关于如何避免“与密钥缓存一起修复"?在MySQL中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
