Mysql – Speeding Inserts of longtext or longblob columns

innodbinsertMySQLperformance

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

Best Answer

InnoDB Architecture (from Percona CTO Vadim Tkachenko)

InnoDB Architecture

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?

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

Related Solutions

Mysql – Capabilities of InnoDB INSERT Performance

You need to tune your InnoDB settings in the following areas:

Make InnoDB access all your cores
Increase innodb_buffer_pool_size to 12G
Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)
Increase Log File Size (innodb_log_file_size) to 2047M
support separate tablespace files for individual InnoDB tables (enaled innodb_file_per_table)
support either high performance or high durability (ACID Compliance)
- High Performance : innodb_flush_log_at_trx_commit set to 0 or 2
- High Durability : innodb_flush_log_at_trx_commit set to 1 (Default)
- Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
- Your current setting for innodb_flush_log_at_trx_commit is good
- Your current setting for innodb_flush_method is good
Increase innodb_read_io_threads to 64
Increase innodb_write_io_threads to 64
Increase innodb_io_capactity to 10000

Here are my past posts on tuning the InnoDB storage engine

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Why does InnoDB store all databases in one file? (Mar 25, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
About single threaded versus multithreaded databases performance (May 26, 2011)
How to safely change MySQL innodb variable 'innodb_log_file_size'? (Feb 16, 2011)
How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)
Howto: Clean a mysql InnoDB storage engine? (October 29, 2010)

Mysql – Cannot write to thesql database while select query in process

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before