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 .bulk_insert_buffer_size to 500M.unique_checks = 0 .
not checked.SET autocommit=0; ... SQL import statements ... COMMIT;SET foreign_key_checks=0;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:(...
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..
I've also verified that My table engine is MyISAM by following:
SHOW TABLE STATUS WHERE Name = 'xxx';
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;
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.