2

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
  • What version of MySQL ? – RolandoMySQLDBA Apr 9 '15 at 15:58
  • I forgot to put the version, thanks for pointing that. The version is 5.6.13. I edited the question. – kriegu Apr 9 '15 at 16:12
  • How much RAM on the DB Server ??? – RolandoMySQLDBA Apr 9 '15 at 16:27
  • It's a small virtual server: 8 GB of RAM and 2 cores. – kriegu Apr 9 '15 at 18:05
1

InnoDB Architecture (from Percona CTO Vadim Tkachenko)

InnoDB Architecture

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

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

  • Thank you! I'll make some tests.... Would you add innodb_doublewrite=0 and innodb_autoinc_lock_mode=2? – kriegu Apr 9 '15 at 17:57
  • I would not have those in production. In your particular case, it's OK since you are doing a migration. – RolandoMySQLDBA Apr 9 '15 at 17:58
  • Thank you! It totally did the trick!! I think there was a I/O bottleneck, so increasing innodb_write_io_threads worked great! For a database migration that took about 50 hours before the tunning, it took about 25 hours after! It also allowed me to increase the batch size. – kriegu Apr 16 '15 at 17:28
0

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.

  • Thank you! Data is about 100GB, the largest table it's about 640GB! But the latter takes about 200 Hours! Without the tunning proposed. – kriegu Apr 16 '15 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.