5

I follow these instructions to speed up loading a big local file (500+M, 10+M rows) into MySQL, adding configurations to /etc/mysql/my.cnf:

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
local-infile=1

# add following code
innodb_doublewrite = 0
innodb_support_xa = 0

but encounter following issues:

mysql: unknown variable 'innodb_doublewrite=0'
mysql: unknown variable 'innodb_support_xa=0'

PS: MySQL version

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.27, for debian-linux-gnu (x86_64) using  EditLine wrapper
Community
  • 1
  • 1
SparkAndShine
  • 17,001
  • 22
  • 90
  • 134

1 Answers1

17

Put them under [mysqld], not [mysql].

Also, make sure that innodb_buffer_pool_size is about 70% of available RAM.

Even then, you may hit some timeout.

Or you may need to chunk the file up. (10K rows per chunk might be reasonable.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • In other words, the CLI doesn't need an innodb buffer? So you need to specify which variables are for the daemon. – PJ Brunet Dec 17 '17 at 00:08
  • Most things in my.cnf are tunables for the _server_. That includes any innodb settings. The clients (`[mysql]` and/or `[client]`) need very little. – Rick James Dec 17 '17 at 00:45