MYSQL巨大的SQL文件插入| MyISAM插入速度突然变慢(奇怪的问题) [英] MYSQL Huge SQL Files Insertion | MyISAM speed suddenly slow down for Insertions (strange issue)

问题描述

我面临一个非常奇怪的问题，我问了

I'm facing very strange problem, I've asked the question here about speed up the insertion in MYSql, especially about the insertion of Huge SQL files multiple GB in size. They suggested me to use MyISAM engine. I did the following:

• ALTER TABLE revision ENGINE=MyISAM;
• 使用ALTER TABLE .. DISABLE KEYS.
• (仅MyISAM)将bulk_insert_buffer_size设置为500M.
• (仅适用于MyISAM)设置unique_checks = 0. 未选中.
• SET autocommit=0; ... SQL import statements ... COMMIT;
• SET foreign_key_checks=0;

• ALTER TABLE revision ENGINE=MyISAM;
• Use ALTER TABLE .. DISABLE KEYS .
• (MyISAM only) Set bulk_insert_buffer_size to 500M.
• (MyISAM only) Set unique_checks = 0 . not checked.
• SET autocommit=0; ... SQL import statements ... COMMIT;
• SET foreign_key_checks=0;

它可以将流程加快到5分钟，之前需要2个小时，这给我留下了深刻的印象.但是现在当我在其他表上尝试相同的操作时，则无法加快速度，又要花费几个小时:((...

It Speed up the process to 5 minutes that previously took 2 hours and I'm impressed. But now when i tried the same thing with other tables then there is no speed up and it takes several hours again:(...

最初获得成功的时候，我的 CPU使用率约为90％，并且插入仅用了5分钟，但是现在按照相同的程序，我的 CPU使用率约为5％最多它表明有问题..

When initially i got success than my CPU usage is around 90% and insertion took only 5 minute, but now following the same procedure my CPU usage is around 5% at max. It shows something wrong..

我还通过以下方式验证了我的表引擎是MyISAM:

I've also verified that My table engine is MyISAM by following:

SHOW TABLE STATUS WHERE Name = 'xxx';

注意:我正在使用Wikipedia数据库架构.我在类别链接来自Wikipedia数据集的表.我在修订，页面和文本表.

Note : I'm using Wikipedia database schema.I got success on Categorylinks table from Wikipedia data set. I've no sucess (Very slow insertion speed) at revision, page and text table.

请帮助我解决这个奇怪的问题.

Please help me on this strange issue.

推荐答案

问题的背后，我还没有找到理想的原因.但是，当我深入研究时，我发现它通常与具有丰富关系的表相比，性能低.尽管我已禁用了这些键..但是我仍然相信以下设置最适合插入大型文件，因为以下设置在多数表上具有最佳性能:

I've not found the ideal reason, behind the problem. But when i drill down i found that it low perofrmance ususally with tables which have enrich in relation. Although i've disable the keys.. But i still beileve that following settings are best for huge file insertions as the following setting give best performance on majority tables:

 - ALTER TABLE revision ENGINE=MyISAM;
 - Use ALTER TABLE .. DISABLE KEYS .
 - (MyISAM only) Set bulk_insert_buffer_size to 500M.
 - (MyISAM only) Set unique_checks = 0 . not checked. 
 - SET autocommit=0; ... SQL import
 - statements ... COMMIT;
 - SET foreign_key_checks=0;

有关性能调整的其他调整如下:请参阅完整的my.cnf文件.

Other tweaks regarding the perofmance tunings are following: Please see the complete my.cnf file..

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
bulk_insert_buffer_size = 1G
lc-messages-dir = /usr/share/mysql
skip-grant-tables
skip-networking
skip-external-locking
init_connect='SET autocommit=0'
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 0
max_allowed_packet = 500M
table_open_cache = 512
max_connections=100

query_cache_size=32M

table_cache=512

tmp_table_size=64M

thread_cache_size=8

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=256M

key_buffer_size=512M
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
general_log=0
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 1G
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

因此，建议总体上使用这些设置，这样您将获得可观的性能优势.

So Overall these settings are recommended and you will get considerable performance edge.

这篇关于MYSQL巨大的SQL文件插入| MyISAM插入速度突然变慢(奇怪的问题)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！