Speeding Inserts of longtext or longblob columns

Question

i'm currently migrating a DB2 database to MySQL, using SQL WORKBENCH Tool

It basically does a select from DB2 and Insert to DB2.

It works fine, but on the tables with longblob or longtext columns (for storing xml or images) it takes a huge amount of time. (about 40 hours for a table of 10M rows).

Facts:

• Server: 2CPU CORES and 8GB of RAM OS: Linux RHEL 6.5

• MySQL Server 5.6.13

• Tables are InnoDB.

• MySQL Server and the Migration tool are on the same computer so there isn't network overhead.

• Tables on MySQL have PRIMARY KEYS but not any other indexes.

• I disabled binary log for the massive insert.

• I configured the migration tool to do a batch insert of 10
  rows and commit every insert (10 rows).

Two questions here:

1. What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)?
2. Is there any tuning recommended on my.cnf to improve the insert performance?

Here's the contents of my.cnf file:

[mysqld]

open_files_limit=2048
table_open_cache=1024
query_cache_type=1
query_cache_size=64M
tmp_table_size=32M
thread_cache_size=9
default-storage-engine=INNODB
back_log=80
flush_time=0
join_buffer_size=1M
max_allowed_packet=1G
max_connect_errors=100
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
log-bin
log_bin_trust_function_creators=1

#INNODB
innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=1
innodb_data_file_path = ibdata1:5G:autoextend
innodb_log_buffer_size=16M
innodb_buffer_pool_size=6144M
innodb_log_file_size=1GB
innodb_thread_concurrency=3
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0

Answer 1

InnoDB Architecture (from Percona CTO Vadim Tkachenko)

1. What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)?

• Increase innodb_log_buffer_size to 256M
• Increase innodb_log_file_size to 2G
• I wrote about increasing these to accommodate TEXT/BLOB fields in the past
  • Apr 20, 2011 : MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?
  • Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
  • Aug 01, 2011 : How does max_allowed_packet affect the backup and restore of a database?

2. Is there any tuning recommended on my.cnf to improve the insert performance?

• Increase the Write I/O Threads (For more info, see Configuring the Number of Background InnoDB I/O Threads)
• Change innodb_thread_concurrency to default setting of 0

Proposed Changes

[mysqld]
innodb_log_buffer_size    = 256M
innodb_log_file_size      = 2G
innodb_write_io_threads   = 16
innodb_thread_concurrency = 0

GIVE IT A TRY !!!

Answer 2

Roland's ideas are good. Also

• Turn off the query_cache; it is unused overhead. (_type=0, _size=0)
• Batch size of 10 is probably good. How big are the BLOBs/TEXTs?
• How much data is there? Sounds like terabytes? To simply read and write terabytes takes hours -- just for the disk I/O. (So 40 hours may be reasonable.)
• comment out log-bin (no use writing a binlog; if something crashes too badly, start over)
• innodb_flush_log_at_trx_commit=2 -- to decrease frequency of writing to the transaction log
• innodb_file_per_table = ON -- before you CREATE the table(s). (You'll thank me much later.)

Again, these settings are not necessarily 'right' for production.