Speeding Inserts of longtext or longblob columns 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:


What are the values recommended for the batch insert size, considering the size of the columns (longtext, longblob)?
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
InnoDB Architecture (from Percona CTO Vadim Tkachenko)




  
  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 &quot;Out of Memory&quot; error when inserting a large file. From where is this filesize limitation arising?
Apr 27, 2011 : Changed max_allowed_packet and still receiving &#39;Packet Too Large&#39; error 
Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?




  
  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 !!!